OBIEE 10G/11G - How to see the Physical SQL generated by a request in Presentation Service ?
Quick Way
A quickway to see the content of the Nqquery.log to grab the SQL information is to follow this quick instructions.
Click Settings > Administration > Manage Sessions > View Log to view the query log and examine the Physical SQL generated by the request.
To be able to see the log, you must set the log level at least with the level 2. See this article : OBIEE 10G/11G - Setting a Query Logging Level
Articles Related
Detail Way
Logged in the Oracle Interactive Dashboard :
- Click Settings > Administration to open the Oracle BI Presentation Services Administration Window.
- Click the Manage Sessions link to open the Session Management window.
- In the Session Management window, under Cursor Cache, click the View Log link for the last entry.
- The log displays the last query. The log file should look similar to the picture.
Locate :
- the SQL Request section. This section contains the logical SQL issued from Answers.
- the General Query Info section, just below the SQL Request section. This section identifies the repository, subject area, and presentation catalog from which the query was run.
- the Sending query to database named SH section, just below the General Query Info section. This section identifies the physical data source to which Oracle BI Server is connecting and the physical sql that was generated.
The rest of the file contains information such as query status, number of rows returned, and so forth.
Security
To be able to access to the Session Management, the user must have the privileges :
- Access to Administration
- Manage Session because Presentation Service will call this saw url <wrap>saw.dll?ManageSessions&Action=ViewLog&ID=logId</note>
in order to see the log.
Support
No Log Found
If when clicking on the link “View log”, you obtain this message :
No Log Found
- set the log level for the user to a level greater than 0.
- and verify that it has an integer as datatype
Log Could Not Be Retrieved
Error
Log Could Not Be Retrieved
Odbc driver returned an error (SQLExecDirectW).
Error Details
Error Codes: OPR4ONWY:U9IM8TAC
State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000]
[nQSError: 10058] A general error has occurred.
[nQSError: 43113] Message returned from OBIS.
[nQSError: 43100] Log Viewer process reported error 107:@Û.à�. (HY000)
When you click on the log link, you are calling the log viewer utility. Verify that you can call it from the command line. In my case, the file permissions was not good.
It was:
[oracle@Exalytics-01 bin]$ ls -l|grep nqlog
-rw-r--r-- 1 oracle oinstall 1671920 Jan 24 16:41 nqlogviewer
As you can see the script as no permission to execute. After giving the right to execute on the owner (0744), the problem disappeared.
[oracle@Exalytics-01 bin]$ ls -l|grep nqlog
-rwxr--r-- 1 oracle oinstall 1671920 Jan 24 16:41 nqlogviewer