1. Administering Oracle Analytics Server
  2. Resolve Issues
  3. Diagnose and Resolve Issues
  4. Manage the Query Log
  5. Use the Log Viewer

Use the Log Viewer

Use the 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.

Run the Log Viewer Utility

The log viewer utility allows you to search for and review specific log files.

To run the Log Viewer utility (located on Linux 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 semantic model. 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 doesn't exist, then a new file is created. If this argument isn't 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.

Administrators can view the query log using the Manage Sessions option in the Presentation Services Administration page.

Interpret the Log Records

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 semantic model, 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.