Oracle^® Business Intelligence Server Administration Guide > Administering the Oracle BI Server Query Environment >

Administering the Query Log

The Oracle BI Server provides a facility for logging query activity at the individual user level. The query log file is named the NQQuery.log file. This file is in the Log subdirectory in the Oracle BI installation folder. Logging should be used for quality assurance testing, debugging, and troubleshooting by Oracle Technical Support. In production mode, query logging is typically disabled.

Oracle BI Server query logging is tracked at a user level. It will be a resource intensive process if you track the entire user community.

NOTE:  For production systems, it is recommended that query logging be enabled only for a very targeted user community. Most users should have a log level of 0 (zero). In production systems, you can use the Usage Tracking as the production level logging facility. For more information, see Administering Usage Tracking.

It is recommended that you only test users when the user name clearly indicates it is a test user and have verified that query logging enabled. If logging is enabled for such users, it is recommended that they be given names such as sales_admin_with_logging, sales_dev_with_logging, or sales_test_with_logging, so that you can readily identify them. Even production Oracle BI Administrator logins should not have query logging enabled because it could strain the available resources.

You should also disable query logging for the following:

• The SQL statement in the initialization string. The Initialization string text box is in the Initialization Block dialog box, in the General tab.

  NOTE:  The LOGGING column references stored values for the log level.

• The logging level should be set to 0 (zero) for each production user. The Logging level field is in the User dialog box, in the User tab.

Configuring the Logging System

This section describes the logging system and includes information about setting the size of the query log, choosing a logging level, and enabling query logging for a user.

Because query logging can produce very large log files, the logging system is turned off by default. It is sometimes useful to enable logging to test that your repository is configured properly, to monitor activity on your system, to help solve performance problems, or to assist Technical Support. You need to enable logging on the system for each user whose queries you want logged.

Controlling the Size of the Log File

The parameter USER_LOG_FILE_SIZE in the User Log section of the NQSConfig.INI file determines the size of the NQQuery.log file. When the log file grows to one-half the size specified by the USER_LOG_FILE_SIZE parameter, the file is renamed to NQQuery.log.old, and a new log file is created automatically. (This helps to make sure that the disk space allocated for the log file does not exceed the size specified in the configuration file.) Only one copy of the old file is kept.

You can set the file size as high as you like, limited only by the amount of space available on the device. If you change the value of the USER_LOG_FILE_SIZE parameter, you need to restart the Oracle BI Server for the change to take effect. For the syntax of the USER_LOG_FILE_SIZE parameter, refer to Oracle Business Intelligence Infrastructure Installation and Configuration Guide.

Setting a Logging Level

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

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

Set the logging level based on the amount of logging you want to do. In normal operations, logging is generally disabled (the logging level is set to 0). If you decide to enable logging, choose a logging level of 1 or 2. These two levels are designed for use by Oracle BI Administrators.

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;

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.

NOTE:  Logging levels greater than 2 should be used only with the assistance of Technical Support.

The logging levels are described in Table 23.

To set a user's logging level

1. In the Administration Tool, select Manage > Security.

   The Security Manager dialog box appears.

2. Double-click the user's user ID.

   The User dialog box appears.

3. Set the logging level by clicking the Up or Down arrows next to the Logging Level field.

To disable a user's logging level

• Set the logging level to 0.

Using the Log Viewer

Use the Oracle BI log viewer utility nQLogViewer (or a text editor) to view the query log. Each entry in the query log is tagged with the user ID of the user who issued the query, the session ID of the session in which the query was initiated, and the request ID of the individual query.

To run the nQlogViewer utility, open a Command window and type nQlogViewer with any combination of its arguments. The syntax is as follows:

nqlogviewer [-u<user_ID>] [-f<log_input_filename>]
          [-o<output_result_filename>]
          [-s<session_ID>] [-r<request_ID>]

where:

You can also locate user IDs, session IDs and request IDs through the Session Manager. For more information, refer to Using the Session Manager.

NOTE:  Oracle BI Presentation Services Administrators can view the query log using the Manage Sessions option in Presentation Services Administration.

Interpreting the Log Records

After you have logged some query information and started the log viewer, you can analyze the log. The log is divided into several sections, some of which are described in the next section. Log entries for levels 1 and 2 are generally self-explanatory. The log entries can provide insights to help DBAs in charge of the underlying databases tune them for optimum query performance. The query log can also help you check the accuracy of applications that use the Oracle BI Server.

SQL Request

This section lists the SQL issued from the client application. This can be used to rerun the query from the same application, or from a different application.

General Query Information

This section lists the repository, the business model, and the presentation catalog from which the query was run. You can use this information to provide statistics on query usage that could be used to set priorities for future application development and system management.

Database Query

This section of the log begins with an entry that reads Sending query to the database named <data_source_name>, where data_source_name is the name of the data source to which the Oracle BI Server is connecting. Multiple database queries can be sent to one or more data sources. Each query will have an entry in the log.

The database query section has several uses. It records the SQL sent to the underlying databases; you can then use the logged SQL to run queries directly against the database for performance tuning, results verification, or other testing purposes. It allows you to examine the tables that are being queried to verify that aggregate navigation is working as you expect. If you understand the structure of the underlying database, it might also provide some insights into potential performance improvements, such as useful aggregate tables or indexes to build.

Query Status

The query success entry in the log indicates if the query completed successfully or if it failed. You can search through the log for failed queries to determine why they failed. For example, all the queries during a particular time period might have failed due to a database downtime.