To set the query logging level for a user
1. In the Oracle BI Administration Tool, select Manage, then Identity. The Security Manager dialog box is displayed.
2. Double-click the name of the user for which you want to set the query logging level. The User dialog box is displayed.
3. Set the logging level by clicking the Up or Down arrows next to the Logging Level field. To disable query logging for a user, set the logging level to 0.
4. Click OK.
Logging Level Information That Is Logged
Level 0
Level 0 No logging.
Level 1
Level 1 Logs the SQL statement issued from the client application. Also logs the following:
■ Physical Query Response Time — The time for a query to be processed in the back-end database.
■ Number of physical queries — The number of queries that are processed by the back-end database.
■ Cumulative time — The sum of time for all physical queries for a request (that is, the sum of all back-end database processing times and DB-connect times).
■ DB-Connect time — The time taken to connect to the back-end database.
■ Query cache processing — The time taken to process the logical query from the cache.
■ Elapsed time — The time that has elapsed from when the logical query is presented to the BI Server until the result is returned to the user. Elapsed time can never be less than response time, because elapsed time takes into account the small extra time between the logical query being presented to the BI Server to the start of preparation of the query. In cases where this delta time is negligible, the elapsed time equals the response time.
■ Response time — The time taken for the logical query to prepare, execute, and fetch the last record. This matches the TOTAL_TIME_SEC that is logged in usage tracking.
■ Compilation time — The time taken to compile the logical query. ■ For each query, logs the query status (success, failure, termination, or timeout), and the user ID, session ID, and request ID.
LEVEL 2
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
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
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
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.
NQSQUERY.log can be used to view log output
Location :ORACLE_INSTANCE\diagnostics\logs\component_type\bi_component_name
LOGGING AT Presentation 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 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
Using the Log Viewer
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.
To run the Log Viewer utility (which is located on Windows in
\MW_HOME\ORACLE_HOME\bifoundation\server\bin\nqlogviewer.exe,
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]
■ 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 Oracle Fusion Middleware Security Guide for Oracle Business Intelligence Enterprise Edition for information.
Log Can also be Viewed Here
Administrators can view the query log using the Manage Sessions option in the Presentation Services Administration page.
