The Oracle BI Server provides a facility for logging query activity at the individual user level. Use logging for quality assurance testing, debugging, and troubleshooting by Oracle Support Services. In production mode, query logging is typically disabled.
The query log file is named nqquery.log, and is located in:
BI_DOMAIN/servers/obisn/logs
Oracle BI Server query logging is tracked at a user level. It is 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. In production systems, you can use usage tracking as the production-level logging facility. See Managing Usage Tracking for more information.
It is recommended that you test users only when the user name clearly indicates it is a test user and have verified that query logging is enabled. If logging is enabled for such users, then 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 administrator logins should not have query logging enabled, because it strains the available resources.
Also disable query logging for the following:
The SQL statement in the initialization string. The Initialization string field is in the Initialization Block dialog, in the General tab.
The LOGGING column references stored values for the log level.
Set the logging level to 0 (zero) for each production user. The Logging level field is in the User dialog, in the User tab. In the Administration Tool, select Identity from the Manage option on the main toolbar. In the Identity Manager dialog, double-click a user and select the User tab.
This section contains the following topics:
This section 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. You can enable logging to test that the repository is configured properly, to monitor activity on the system, to help solve performance problems, or to assist Oracle Support Services. You must enable query logging on the system for each user whose queries you want logged. You do this using the Oracle BI Administration Tool.
You can configure the amount of data query logs collect per user.
You can enable query logging levels for individual users, as described in Setting the Query Logging Level for a User. You cannot configure a logging level for a group.
A session variable overrides the logging level for a particular user. For example, if the administrator has a logging level of 4 and the session variable logging level is defined as the default 0 (zero) in the repository, then the logging level for the administrator is 0.
Set the logging level based on the amount of logging that is appropriate for your organization. In normal operations, logging is generally disabled (that is, the logging level is set to 0). If you decide to enable logging, then select a logging level of 1 or 2. These two levels are designed for use by 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 select statement by adding a prefix clause in the Advanced SQL Clauses section of the Advanced tab in Oracle BI Presentation Services. For example, for the select statement:
SELECT year, product, sum(revenue) FROM time, products, facts;
You can specify the logging level of 5 in the Prefix field as follows:
Set Variable LOGLEVEL=5;
For this query, the logging level of 5 is used regardless of the value of the underlying LOGLEVEL variable.
Note:
Use logging levels greater than 2 only with the assistance of Oracle Support Services.The query logging levels are described in the following table.
| 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:
|
|
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. |
Use the Oracle Business Intelligence Log Viewer utility (or a text editor) to view the query log.
Each entry in the query log is tagged with the name 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.
The log viewer utility allows you to search for and review specific log files.
To run the Log Viewer utility (located on UNIX in ORACLE_HOME/bi/bifoundation/server/bin), open a command prompt, and enter nqlogviewer with any combination of its arguments. The syntax is as follows:
nqlogviewer [-u user_name] [-f log_input_filename] [-o output_result_filename] [-s session_ID] [-r request_ID]
In this syntax:
user_name is the name of a user in the Oracle Business Intelligence repository. This parameter limits the scope to entries for a particular user. If not specified, all users for whom query logging is enabled are displayed.
log_input_filename is the name of an existing log file from where the content is taken. This parameter is required.
output_result_filename is the name of a file in which to store the output of the log. If the file exists, then the results are appended to the file. If the file does not exist, then a new file is created. If this argument is not specified, then output is sent to the monitor screen.
session_ID is the session ID of the user session. The BI Server assigns each session a unique ID when the session is initiated. This parameter limits the scope of the log entries to the specified session ID. If not specified, then all session IDs are displayed.
request_ID is the request ID of an individual query. The BI Server assigns each query a unique ID when the query is initiated. This parameter limits the scope of the log entries to the specified request ID. If not specified, then all request IDs are displayed.
The request ID is unique among the active requests, but not necessarily unique during the session. Request IDs are generated in a circular manner, and if a request is closed or if the session is long enough, then a request ID is reused.
You can also locate user names, session IDs, and request IDs through the Session Manager. See Security Guide for Oracle Business Intelligence Enterprise Edition for information.
Administrators can view the query log using the Manage Sessions option in the Presentation Services Administration page.
After you have logged some query information and started the log viewer, you can analyze the log. Log entries for levels 1 and 2 are generally self-explanatory.
The log entries can provide insights to help database administrators (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 BI Server.
The log is divided into the following sections:
SQL Request — This section lists the SQL statement that is issued from the client application. You can use this information 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 subject area from which the query was run. You can use this information to provide statistics on query usage that you can use to set priorities for future application development and system management.
Database Query — This section 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 BI Server is connecting. Multiple database queries can be sent to one or more data sources. Each query has an entry in the log.
The database query section has several uses, such as recording the SQL statement that was sent to the underlying databases. You can use this logged SQL statement to run queries directly against the database for performance tuning, results verification, or other testing purposes. You can also use this information 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, then 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 whether the query completed successfully, or 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 database downtime.