OBIEE 10G/11G - Setting a Query Logging Level

Bi Server Architecture With Client


The login level is a parameter which control the number of information that you will retrieve in the log file from nothing (level 0 - no_log_found) to a lot of information (level 5).

You can enable logging level for individual users, you cannot configure a logging level for a group.

In normal operations :

  • users have a logging level set to 0
  • administrator have a logging level set to 2

Description of logging levels

Logging Levels 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
Level 4 Logs everything logged in Level 3
Additionally, logs the query execution plan.
Level 5 Logs everything logged in Level 4
Additionally, logs intermediate row counts at various points in the execution plan.

How to set a logging level ?

For one request (Temporary log level)

You might want to diagnose performance or data issues by setting a temporary log level for a query. You can enable query logging for a specific query by preceding your Select statement with the following:

Set Variable LOGLEVEL=n;

This instruction sets the loglevel system session variable only for this SQL.

See the following example:

Set Variable LOGLEVEL=5; select year, product, sum(revenue) from time, products, facts

For this query, the logging level of five is used regardless of the value of the underlying LOGLEVEL variable.

See this article to see how to do : OBIEE - How and where can I set a Request variable (SET VARIABLE) ?


Available in 11G

You can find a default logging level in the option of the repository:

Obiee11g Default Logging Level

This option determines the default query logging level for the internal BISystem user.

A query logging level of 0 (the default) means no logging. Set this logging level to 2 to enable query logging for internal system processes like event polling and initialization blocks.

For one user

loglevel session variable

By setting the loglevel system session variable via an initialization block, you can control the loglevel of each user.

The loglevel must be an integer datatype

user dialog

The session variable LOGLEVEL overrides a user's logging level. For example, if the Oracle BI Administrator has a logging level defined as 4 and LOGLEVEL is defined as default 0 (zero) in the repository, the Oracle BI Administrator's logging level will be 0.

  • In the Administration Tool, select Manage > Security (10g) or Identity (11g). 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.

Obiee Repository Logging Level

odbc functions

Through the call of ODBC functions, you can see and set session variables included LOGLEVEL.

Example on how to:

call NQSSetSessionValue('Integer LOGLEVEL=5;')

Nqssetsessionvalue Loglevel

  • get its value:
call NQSGetSessionValues('NQ_SESSION.LOGLEVEL')

For a group

You cannot configure a logging level for a group.

Documentation / Reference

Discover More
Obiee Integer Format Wide Default
OBIEE - Integer Datatype

In OBIEE, the integer datatype: follows the rule of an integer division (without the decimal part) is mandatory for some OBIEE intern use such as the LOGLEVEL variable or the indexcol function ...
Obiee Dashboard Prompt Scope
OBIEE - Request variable

Request Variables are used to update Session Variables in the scope of a logical sql. You will find it in a OBIEE logical Sql in its SET VARIABLE part. For instance : Its aim is to set an OBI server...
Exp Builder Ago Td
OBIEE - Time Series Conversion Functions : AGO and TODATE

Time series functions in OBIEE. The implementation of the time serie function is based on the time dimension. ( See set up time dimensions) At query time, the Oracle BI Server then generates SQL that...
Obiee Physical Table Cache
OBIEE - Why a Query is Not Added to the Query Cache ?

You may found that one SQL query is not added to the query cache. This article try to give you some reasons and tools to diagnose this behaviour. Oracle BI ServerKPIscaching parameters The following...
Obiee Setting Admnistration
OBIEE 10G/11G - How to see the Physical SQL generated by a request in Presentation Service ?

A quickway to see the content of the Nqquery.log to grab the SQL information is to follow this quick instructions. Click Settings > Administration > Manage Sessions > View Log to view the query log and...
Obi Edition
OBIEE 10G/11G - Nqquery.log (Query logging)

It's the log file of BI Server showing information the process of a logical SQL query. It's located in the directory: 10g: BI_ORACLE_HOME/server/Log 11g: ORACLE_INSTANCE/diagnostics/logs/OracleBIServerComponent/coreapplication_obis1...
Obi Edition
OBIEE 11G/10G - Administrator Account

OBIEE Administrator user. An administrator is a user that belongs to the BIAdministrator role. The Oracle BI Administrator user account (user ID: Administrator): is a default user account...

Share this page:
Follow us:
Task Runner