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.