OBIEE 10G/11G - How to see the Physical SQL generated by a request in Presentation Service ?
Table of Contents
1 - 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
2 - Articles Related
3 - 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.
- 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.
4 - 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 saw.dll?ManageSessions&Action=ViewLog&ID=logId in order to see the log.
5 - Support
5.1 - No Log Found
If when clicking on the link “View log”, you obtain this message :
No Log Found
5.2 - 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.
[[email protected] 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.
[[email protected] bin]$ ls -l|grep nqlog -rwxr--r-- 1 oracle oinstall 1671920 Jan 24 16:41 nqlogviewer