The obiee query log, NQQuery.log, exists in the directory …/OracleBI/server/Log. As the name suggests it records the queries requested from the BI Server. The initials NQ remind us of obiee’s history, being originally created by a company named NQuire; these clues are littered throughout the application.
The Logging Level
The contents of the log file are, or at least the level of detail is determined by a Repository Variable, LOGLEVEL. The variable can accept values from 0 to 5, 5 being the most verbose. As a general rule I set the variable to 0 in production, effectively disabling loggin and improving performance; 2 in development, setting to 3 on occassion when 2 is not enough to resolve the issue.
Setting as a System-Wide Session Variable:
We can set the logging level globally for all users. We can set this in the RPD using theAdministration Tool and this logging level will take precendence of other logging settings.
We can also set the LOGLEVEL by user, however, this value will be overridden by the Session Variable if it has been created. To set the Logging Level for a specific user, follow these steps.
You can set the LOGLEVEL variable for a specific request. In this example you will update the session variable LOGLEVEL before the request is run and, as such, this will take precedence over the Session Variable set in the RPD.
Viewing the Log File
There are a couple of ways to view the query log. All query logging is recorded in the Query Log File, NQQuery.log; we can view this file using a text editor if we have access to the OS. OBIEE uses a log viewer utility nQLogViewer to structure this log file into a more useful format called the Session Monitor; we can use the Session Monitor online if we have administrative access. And finally we can use the nQLogViewer utility ourselves to view specific information, but again we require OS access.
Using the Session Monitor:
The session records show sessions by user ID and other details such as the client machine name, IP address, browser details, timestamp of login and timestamp of last request.
And the Query records show each request by ID; each record detailing the user ID, the status of the query, how long it has taken to run, when it was run, the query itself, its location in the catalog (if appropriate) and the number of records returned. In the Action column you can select to view the log in more detail.
Using the Viewer Utlity:
We can use the utility nQLogViewer to view log records; as stated above, 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 query; and we can use these details to restrict the utility to a spcific query.
To use the nQLogViewer utility we must open a command window in either Linux or Windows. Navigate to …/OracleBI/server/Bin. Use the command with the syntax below.
nqlogviewer [-u<user_ID>] [-f<log_input_filename>]
[-o<output_result_filename>]
[-s<session_ID>] [-r<request_ID>]
I would like to note that the request ID is generated in a circular manner; when IDs run out then new requests will re-use existing IDs. This may explain why different queries can sometimes display in a single log record.
Interpreting Log Records
Whether looking at the Query log directly, or using the Viewer Utility or via the Session Monitor the Log Record is the same; they all come from same log file. A log record can be broken down into sections; these sections are described brielfy below.
The log will start with the user ID for the requesting user (followed by the user Account code in hex) and the timestamp for running the request.
SQL Request:
This section provides the Application SQL produced in the Presentation Layer; it does not define relationships, just the columns used. It can be used to rerun the exact query in the Answer part of the application; although it is less reliable than using the request xml (not available in the log). It will also show additional attributes such as Pre and Post SQL and the path or location of a saved request.
General Query Information:
This section describes the repository, business model and catalog from which the report has been run; usually useful in putting together query statistics. I will providing a later log that goes into more detail on this.
Logical Request:
Displays the columns used in the report from the Logical Layer; this section will be displayed when the Log Level is set to 2 or above.
Execution Plan:
When we set the Log Level to 2 or above then we also get to see the Execution Plan.
Database Query:
Identify this section from the initial text Sending query to the database named <data_source_name>; the data_source_name is the name of the data source to which the Oracle BI Server is connecting. If the server is connecting to Multiple database each query will have an entry in the log.
This is the section of the log that you are most likely to be interested in and is the most useful. It provides the actual SQL generated and executed against the database. It can be used to verify that the application is behaving as required; especially useful when troubleshooting; and also to support performance tuning.
I would like to point out that the SQL that you read at times may not refelct the SQL that is actually executed on the database. You may notice sometimes that the SQL in the log executes very well, but the OBIEE query seems to perform poorly. This is due to this unexpected difference. I have blogged about this particular risk to OBIEE Performance.
Query Status:
Essentially the status section will tell you whether the query has succeeded or failed. You will usually know this before looking at the log, but I guess its worth having it in there.
Log Retention
In terms of configuring the query log we can use the parameter USER_LOG_FILE_SIZE to set the limit of the Query Log. When the size of the log file grows to half of that value it will be renamed to NQQuery.log.old and fresh queries will be logged to the recreated file NQQuery.log. Other than the size of the file system there are no further limitations to the size of the log file. An example of the NQSConfig.INI file is given in the screenshot below.
The Logging Level
The contents of the log file are, or at least the level of detail is determined by a Repository Variable, LOGLEVEL. The variable can accept values from 0 to 5, 5 being the most verbose. As a general rule I set the variable to 0 in production, effectively disabling loggin and improving performance; 2 in development, setting to 3 on occassion when 2 is not enough to resolve the issue.
Setting as a System-Wide Session Variable:
We can set the logging level globally for all users. We can set this in the RPD using theAdministration Tool and this logging level will take precendence of other logging settings.
- Select to manage Variables from the Manage Menu
- In Variable Manager Select New, Session, Variable from the Action menu
- Give the Variable the name of LOGLEVEL, a value of 2 and click OK
- Close Variable Manager
We can also set the LOGLEVEL by user, however, this value will be overridden by the Session Variable if it has been created. To set the Logging Level for a specific user, follow these steps.
- Select to manage Security from the Manage Menu
- In Security Manager Select the Users Object Type from the left pane
- The right pane lists the user objects, open the properties for an a user object
- Give the Logging Level a vlue 0 to 5 and click OK
- Close Security Manager
You can set the LOGLEVEL variable for a specific request. In this example you will update the session variable LOGLEVEL before the request is run and, as such, this will take precedence over the Session Variable set in the RPD.
- Select to Modify a request or create a new one
- Navigate to the Advanced Tab
- In the Prefix Text Box type SET VARIABLE LOGLEVEL=2;
- Now when you navigate to the results tab an entry will be made to the log file
- Save the changed request if you would like always to be the case
Viewing the Log File
There are a couple of ways to view the query log. All query logging is recorded in the Query Log File, NQQuery.log; we can view this file using a text editor if we have access to the OS. OBIEE uses a log viewer utility nQLogViewer to structure this log file into a more useful format called the Session Monitor; we can use the Session Monitor online if we have administrative access. And finally we can use the nQLogViewer utility ourselves to view specific information, but again we require OS access.
Using the Session Monitor:
- Select Administration from the Settings Menu
- From the Administration Window select to Manage Sessions
- The Session Monitor will open
The session records show sessions by user ID and other details such as the client machine name, IP address, browser details, timestamp of login and timestamp of last request.
And the Query records show each request by ID; each record detailing the user ID, the status of the query, how long it has taken to run, when it was run, the query itself, its location in the catalog (if appropriate) and the number of records returned. In the Action column you can select to view the log in more detail.
Session Monitor Query Record
We can use the utility nQLogViewer to view log records; as stated above, 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 query; and we can use these details to restrict the utility to a spcific query.
To use the nQLogViewer utility we must open a command window in either Linux or Windows. Navigate to …/OracleBI/server/Bin. Use the command with the syntax below.
nqlogviewer [-u<user_ID>] [-f<log_input_filename>]
[-o<output_result_filename>]
[-s<session_ID>] [-r<request_ID>]
I would like to note that the request ID is generated in a circular manner; when IDs run out then new requests will re-use existing IDs. This may explain why different queries can sometimes display in a single log record.
Interpreting Log Records
Whether looking at the Query log directly, or using the Viewer Utility or via the Session Monitor the Log Record is the same; they all come from same log file. A log record can be broken down into sections; these sections are described brielfy below.
The log will start with the user ID for the requesting user (followed by the user Account code in hex) and the timestamp for running the request.
SQL Request:
This section provides the Application SQL produced in the Presentation Layer; it does not define relationships, just the columns used. It can be used to rerun the exact query in the Answer part of the application; although it is less reliable than using the request xml (not available in the log). It will also show additional attributes such as Pre and Post SQL and the path or location of a saved request.
General Query Information:
This section describes the repository, business model and catalog from which the report has been run; usually useful in putting together query statistics. I will providing a later log that goes into more detail on this.
Logical Request:
Displays the columns used in the report from the Logical Layer; this section will be displayed when the Log Level is set to 2 or above.
Execution Plan:
When we set the Log Level to 2 or above then we also get to see the Execution Plan.
Database Query:
Identify this section from the initial text Sending query to the database named <data_source_name>; the data_source_name is the name of the data source to which the Oracle BI Server is connecting. If the server is connecting to Multiple database each query will have an entry in the log.
This is the section of the log that you are most likely to be interested in and is the most useful. It provides the actual SQL generated and executed against the database. It can be used to verify that the application is behaving as required; especially useful when troubleshooting; and also to support performance tuning.
I would like to point out that the SQL that you read at times may not refelct the SQL that is actually executed on the database. You may notice sometimes that the SQL in the log executes very well, but the OBIEE query seems to perform poorly. This is due to this unexpected difference. I have blogged about this particular risk to OBIEE Performance.
Query Status:
Essentially the status section will tell you whether the query has succeeded or failed. You will usually know this before looking at the log, but I guess its worth having it in there.
Log Retention
In terms of configuring the query log we can use the parameter USER_LOG_FILE_SIZE to set the limit of the Query Log. When the size of the log file grows to half of that value it will be renamed to NQQuery.log.old and fresh queries will be logged to the recreated file NQQuery.log. Other than the size of the file system there are no further limitations to the size of the log file. An example of the NQSConfig.INI file is given in the screenshot below.
Query Log Sizing in the NQSConfig.INI
No comments:
Post a Comment