Setting LOGLEVEL from Answers

Normally, we check query in NQQuery.log file or from Answers also we can check the query directly.

 Difference is: NQQuery.log contains all the queries and need to identify them by time the report run.

If it’s directly from Answers (go to Settings > Administration > Manage Sessions), log available for every report runs until the query is changed.

 For every query of report there will be created one cursor, by accessing the cursor you can see the physical query generated by BI server.

 If we are unable to see the log file or if we find any error message saying that, No Log found while trying to watch the query in log the first step in the mind is to check the LOGLEVEL. When creating rpd by default, loglevel takes 0 values for Administrator user.

If it’s set to 0, we go for 2 or 3 accordingly, such that, the query will be appeared.

For suppose, if you are accessing presentation service of another system and unable to see the query. You need not to set the LOGLEVEL=2 in the rpd presented in another system.

I am assuming that all cursors are closed and LOGLEVEL is zero for particular user.

Log into answers with user who has LOGLEVEL =0, run any report then go to Settings > Administration > Manage Sessions. Click on View Log option.

You will have error message or you may open the view log but you can’t find the query run at this moment because of loglevel – “0″. (To test this error message, you better delete the content of NQQuery.log completely but before doing this you need to stop your BI Server service then clear the content and save file again start BI Server service)

Close the window as well as cursors

 Just go to advanced tab of particular report.

Scroll down to see the option: Prefix

Here write SET VARIABLE LOGLEVEL=2; (semi-colon at the end is must)

 

Now click on Results tab to re – run the report.

 Now you go Settings > Administration > Manage Sessions

Click on View Log to view the query. Now you are able to see the query and according to the LOGLEVEL – 2

 Go back to Advanced tab, observe the Logical query.

Before SQL, you find the text you written in Prefix field.

By mentioning the semi colon, BI Server executes these statements one after another.

This post explain you, how to set loglevel from answers it self, with out going to rpd,

This process overrides the loglevel set in the rpd.

 

Setting logging Levels

Login level is used to control the number of information generated in the log file. There are couple of benefits associated with using logging levels like to monitor activity on your system, to help solve performance problems etc.

The logging system is turned off by default. You need to enable logging on the system for each user whose queries you want logged and You cannot configure logging levels for a group, it has to be on individual users level.

Type of Login Levels
There are 5 types of login levels available in OBIEE.

Login Levels	Descriptions of Results
0	No logging
1	Logs the SQL statement issued from the client application. Logs elapsed times for query compilation, query execution, query cache processing, and back-end database processing. Logs the query status (success, failure, termination, or timeout). Logs the user ID, session ID, and request ID for each query.
2	Logs everything logged in Level 1. Additionally, for each query, logs the repository name, business model name, presentation catalog (called Subject Area in Answers) name, SQL for the queries issued against physical databases, queries issued against the cache, number of rows returned from each query against a physical database and from queries issued against the cache, and the number of rows returned to the client application.
3	Logs everything logged in Level 2. Additionally, adds a log entry for the logical query plan, when a query that was supposed to seed the cache was not inserted into the cache, when existing cache entries are purged to make room for the current query, and when the attempt to update the exact match hit detector fails. Do not select this level without the assistance of Technical Support.
4	Logs everything logged in Level 3. Additionally, logs the query execution plan. Do not select this level without the assistance of Technical Support.
5	Logs everything logged in Level 4. Additionally, logs intermediate row counts at various points in the execution plan. Do not select this level without the assistance of Technical Support.
6 & 7	Reserved for future use

Source Reference: OBIEE Guide

How to set up logging level for users
Navigate to Admin tool -> Manage – > Security -> Users
Double click on a user and change the Logging level
To disable the logging level, set the level to Zero

Note: A session variable overrides a user’s logging level.

Setting for a specific Request
You can set the LOGLEVEL variable for a specific request
Select to Modify a request or create a new one
Navigate to the Advanced Tab
In the Prefix Text Box type “SET VARIABLE LOGLEVEL=0;” (set to any log between 0-5)
Now when you navigate to the results tab an entry will be made to the log file

Example:

Oracle BI EE 10.1.3.3/2 – Enable logging – Getting Database queries

I had 2 users asking yesterday about logging in OBI EE. What they wanted to know was a way, from within OBI EE, to find out what actual SQL Query generated by OBI EE for a report. Though it is very simple, i thought i would blog about it here since this seems to be a very common question. There are different types of logging in OBI EE. But for the above requirement we would just need the BI Server logging. Logging of BI Server is enabled per user. There are different levels of logging for BI Server. Let me paste the levels here directly from the docs.

      

As you see above the logging level needed to find out the sql query is 2( i have not included the remaining logging levels since those are not desired here). By default the logging level for BI Server is 0. So, in order to change the logging level to 2, go to the Administration Console and open the repository in online mode. Go to Manage-> Security -> Users and then double click on the user for whom you would like to change the logging level to.

      

Change the logging level to 2. Once done check in the changes. Log in to presentation Services and go to Settings->Administration->Manage Sessions. You would see different sessions that are running/have run. Click on view for the session that you need and you should see the SQL fired in the logs now. Of course, this will show the updated logging for sessions that started after enabling the logging.

      

      

Very simple but

OBIEE Query LOGLEVEL

To analyze or diagnose OBIEE report performance we need to see its log files.These diagnostics will be shown to us depends on the LOGLEVEL we set for the user.

The following Oracle note gives us a clear understanding of each LOGLEVEL we set for the users.

Query Logging Levels:

Logging Level	Information That Is Logged
Level 0	No logging.
Level 1	Logs the SQL statement issued from the client application. Also logs the following:  Physical Query Response Time — The time for a query to be processed in the back-end database. Number of physical queries — The number of queries that are processed by the back-end database. Cumulative time — The sum of time for all physical queries for a request (that is, the sum of all back-end database processing times and DB-connect times). DB-Connect time — The time taken to connect to the back-end database. Query cache processing — The time taken to process the logical query from the cache. Elapsed time — The time that has elapsed from when the logical query is presented to the BI Server until the result is returned to the user. Elapsed time can never be less than response time, because elapsed time takes into account the small extra time between the logical query being presented to the BI Server to the start of preparation of the query. In cases where this delta time is negligible, the elapsed time equals the response time. Response time — The time taken for the logical query to prepare, execute, and fetch the last record. This matches the TOTAL_TIME_SEC that is logged in usage tracking. Compilation time — The time taken to compile the logical query. For each query, logs the query status (success, failure, termination, or timeout), and the user ID, session ID, and request ID.
Level 2	Logs everything logged in Level 1.   Additionally, for each query, logs the repository name, business model name, subject area name, SQL statement issued against the physical database, queries issued against the cache, number of rows returned from each query against a physical database and from queries issued against the cache, and the number of rows returned to the client application.
Level 3	Logs everything logged in Level 2.   Additionally, adds a log entry for the logical query plan, when a query that was supposed to seed the cache was not inserted into the cache, when existing cache entries are purged to make room for the current query, and when the attempt to update the exact match hit detector fails. Do not select this level without the assistance of Oracle Support Services.
Level 4	Logs everything logged in Level 3.   Additionally, logs the query execution plan. Do not select this level without the assistance of Oracle Support Services.
Level 5	Logs everything logged in Level 4.  Additionally, logs intermediate row counts at various points in the execution plan. Do not select this level without the assistance of Oracle Support Services.
Level 6 and 7	Not used.