OBIEE Interview Question: In OBIEE 10g, what is log level? Or what are different log levels? Or tell me about your experience with query logs in OBIEE 10g.
When enabled, OBIEE creates a query log in NQQuery.log file which is located by default at {installation drive}\OracleBI\server\Log. The log can be viewed using a text editor or the nQLogViewer. The query long becomes very handy in testing environment and troubleshooting.
The size of NQQuery log file is defined in USER_LOG section of NQSConfig.INI file located by default at {installation drive}\OracleBI\server\Config.
A sample Log file definition from NQSConfig.INI file is given below:
#################################################################
[ USER_LOG ]
USER_LOG_FILE_SIZE = 10 MB; // default size
CODE_PAGE = "UTF8"; // ANSI, UTF8, 1252, etc.
#################################################################
How to set logging level?
Logging level is enables and set at individual level. To define log level
In the Administration Tool, select Manage > Security.
The Security Manager dialog box appears.
Double-click the user’s user ID.
The User dialog box appears.
Set the logging level by clicking the Up or Down arrows next to the Logging Level field.
To disable logging level set the logging level to 0.
Log levels and corresponding information is given below in the table.
Logging Level | Information That Is Logged |
Level 0 | No logging. |
Level 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. |
Level 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. |
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 Technical Support. |
Level 4 | Logs everything logged in Level 3. Additionally, logs the query execution plan. Do not select this level without the assistance of Technical Support. |
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 Technical Support. |
Level 6 and Level 7 | Reserved for future use. |
It is also important to note that logging level can be set at session level by using a session variable. In case if a user has two different log levels sat at the repository and at the session level, the session level will have precedence over the log level set in the repository.
In case of troubleshooting, I have also used temporary log level. This can be done by using the following just before the query.
Set Variable LOGLEVEL=n;
For more details about query logs and log level refer to Oracle Business Intelligence Server Administration Guide available here.
Cheers!
Deepak
