About
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.
Location:
- 11g: Oracle_BI_HOME/bifoundation/server/bin/nqlogviewer . You must run first the bi-init initialization file.
- 1og: Oracle_BI/server/bin/nqlogviewer
Syntax
./nqlogviewer
------------------------------------------------------------------
Oracle BI Log Viewer
Copyright (c) 1997-2011 Oracle Corporation, All rights reserved
------------------------------------------------------------------
nqlogviewer requires -f. nqlogviewer -f <path>\NQQuery.log
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:
- user_ID
The name of a user in the Oracle BI repository. This limits the scope to entries for a particular user. If not specified, all users for whom query logging is enabled are shown.
- log_input_filename
The name of an existing log file. This parameter is required.
- output_result_filename
The name of a file in which to store the output of the log viewer. If the file exists, results are appended to the file. If the file does not exist, a new file is created. If not specified, output is sent to the monitor screen.
- session_ID
The session ID of the user session. The Oracle BI Server assigns each session a unique ID when the session is initiated. This limits the scope of the log entries to the specified session ID. If not specified, all session IDs are shown.
- request_ID
The request ID of an individual query. The Oracle BI Server assigns each query a unique ID when the query is initiated. This limits the scope of the log entries to the specified request ID. If not specified, all request IDs are shown.
The request id will be 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, a request id will be reused.
You can also locate user IDs, session IDs and request IDs through the session manager.
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.