NQQuery.log File

We can find the file in the following path - InstalledDrive\OracleBI\server\Log.

NQQuery.log file holds information about the user requests.

It holds information about the request like-

1.The name of the user who sent request

2. Time at which the query issued

3. Session id, Request id.

4. SQL Request generated by the Presentation Services.

5. General Query Info such as Repository type (star or snowflake), name of Subject Area, Presentation catalog name.

6. Equivalent physical query generated by the BI server to send the underlying Data source.

7. Query status (success or failure)

8. Number of rows retrieved from database.

9. Physical query response time in seconds.

10. Physical Query Summary Stats: Number of physical queries generated, Cumulative time , DB-connect time in seconds.

11. Number of rows returned to the client.

12. Logical Query Summary Stats: Elapsed time , Response time , Compilation time in seconds etc.

 The amount of information to be stored in NQQuery.log file decides by the Logging Level of the user who issued the request.

Query Logging level can be set from 1 to 7. But the logging level 2 is

recommended as the BI server logs sufficient information for this logging level in NQQuery.log file.

As the user logging level is higher, the amount of information

stored in the NQQuery.log file will be more for each query. This decreases the

performance of server and memory space.

The amount of information stored in the NQQuery.log file about query requests

varies, that depends on the logging level of the user who has issued that

query.

For Logging Level 1, the BI server logs the following information in NQQuery.log file.

For Logging Level 2, the BI server logs the following information in NQQuery.log file.

+++jaga:2f0000:2f0002:----2012/04/09 11:58:15 ############################################## -------------------- SQL Request: SET VARIABLE QUERY_SRC_CD='Report';SELECT PROD_TYPE.ptid saw_0, PROD_TYPE.ptname saw_1, PROD_TYPE.description saw_2 FROM dbo ORDER BY saw_0, saw_1, saw_2 +++jaga:2f0000:2f0002:----2012/04/09 11:58:15 -------- General Query Info: Repository: Star, Subject Area: dbo, Presentation: dbo +++jaga:2f0000:2f0002:----2012/04/09 11:58:15 --------- Sending query to database named Prac (id: <<1150>>): select distinct D1.c1 as c1, D1.c2 as c2, D1.c3 as c3 from (select T58."ptid" as c1, T58."ptname" as c2, T58."description" as c3 from "product_type" T58 ) D1 +++jaga:2f0000:2f0002:----2012/04/09 11:58:15 ------------ Query Status: Successful Completion +++jaga:2f0000:2f0002:----2012/04/09 11:58:15 ------------Rows 3, bytes 960 retrieved from database query id: <<1150>> +++jaga:2f0000:2f0002:----2012/04/09 11:58:16 ------------- Physical query response time 0 (seconds), id <<1150>> +++jaga:2f0000:2f0002:----2012/04/09 11:58:16 ------------ Physical Query Summary Stats: Number of physical queries 1, Cumulative time 0, DB-connect time 0 (seconds) +++jaga:2f0000:2f0002:----2012/04/09 11:58:16 ------------- Rows returned to Client 3 +++jaga:2f0000:2f0002:----2012/04/09 11:58:16 ------------- Logical Query Summary Stats: Elapsed time 0, Response time 0, Compilation time 0 (seconds)

If the same request is issued again by any user at any time, the BI Server will not generate and send the physical query to the database. Instead, the BI Server first do the match between incoming request and the requests which are stored at Cache (called as Query Cache). If match is found, then the BI server will get the result from that cache itself and send  it to the user.This improves the query response time.

Example : Assume that, a request and its result is stored in Cache (in my case the user jaga has issued that request). Now any user, lets say Jay, issue the same request. Then the BI server will do a match with cache. Definitely server will find the match as both the request are same. So, the BI server will not generate any physical queries. Server will just retrieve the data from cache and returns to user.

For Logging Level 3, the BI server logs the following information in NQQuery.log file.

+++jaga:340000:340004:----2012/04/06 15:14:38 ############################################## -------------------- SQL Request: SET VARIABLE QUERY_SRC_CD='Report';SELECT PROD_TYPE.ptid saw_0, PROD_TYPE.ptname saw_1 FROM dbo ORDER BY saw_0, saw_1 +++jaga:340000:340004:----2012/04/06 15:14:38 -------------------- General Query Info: Repository: Star, Subject Area: dbo, Presentation: dbo +++jaga:340000:340004:----2012/04/06 15:14:38 -------------------- Logical Request (before navigation): RqList distinct product_type.ptid as c1 GB, product_type.ptname as c2 GB OrderBy: c1 asc, c2 asc +++jaga:340000:340004:----2012/04/06 15:14:38 -------------------- Sending query to database named Prac (id: <<945>>): select distinct D1.c1 as c1, D1.c2 as c2 from (select T58."ptid" as c1, T58."ptname" as c2 from "product_type" T58 ) D1 +++jaga:340000:340004:----2012/04/06 15:14:38 -------------------- Query Result Cache: [59124] The query for user 'jaga' was inserted into the query result cache. The filename is 'C:\OracleBIData\cache\NQS_NWS1018_734601_54878_00000002.TBL'. +++jaga:340000:340004:----2012/04/06 15:14:39 -------------------- Query Status: Successful Completion +++jaga:340000:340004:----2012/04/06 15:14:39 -------------------- Rows 3, bytes 336 retrieved from database query id: <<945>> +++jaga:340000:340004:----2012/04/06 15:14:39 -------------------- Physical query response time 0 (seconds), id <<945>> +++jaga:340000:340004:----2012/04/06 15:14:39 -------------------- Physical Query Summary Stats: Number of physical queries 1, Cumulative time 0, DB-connect time 0 (seconds) +++jaga:340000:340004:----2012/04/06 15:14:39 -------------------- Rows returned to Client 3 +++jaga:340000:340004:----2012/04/06 15:14:39 -------------------- Logical Query Summary Stats: Elapsed time 1, Response time 0, Compilation time 0 (seconds)

Only for some advanced troubleshooting, higher log level may be set.

No Log Found

This is the usual message we get when we try to open the log file(ie, When we click on View Log in Session Management). This is due to the logging level the current user having.

The logging level is a parameter which control the level of information that you can retrieve in the log file.
 If we want the user to see log information in the log file, we should change the logging level in RPD.

Logging level = 0 means, no access to log file.

You can enable logging level for individual users, you cannot configure a logging level for a group.
In normal operations :

• users have a logging level set to 0
• administrator have a logging level set to 2
   
   Description of logging levels

  Logging Levels	Logging Level Information That Is Logged
  Level 0	No logging
  Level 1	Logs the SQL statement issued from the client application Logs elapsed times for query compilation, query execution, query cache processing, and back-end database processing Logs the query status (success, failure, termination, or timeout). Logs the user ID, session ID, and request ID for each query
  Level 2	Logs everything logged in Level 1 Additionally, for each query, logs the repository name, business model name, presentation catalog (called Subject Area in Answers) name, SQL for the queries issued against physical databases, 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	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
  Level 4	Logs everything logged in Level 3 Additionally, logs the query execution plan.
  Level 5	Logs everything logged in Level 4 Additionally, logs intermediate row counts at various points in the execution plan.

   
  To change the logging level follow the below steps : 
  • In the Administration Tool, select Manage > Security . The Security Manager dialog box appears.
  • Double-click the user's user ID. The User dialog box appears.
  • Set the logging level by clicking the Up or Down arrows next to the Logging Level field.