Associating S_NQ_ACCT Record with the BI Query Log

Your Oracle BI Administrator can associate the records in the Oracle BI Usage Tracking table with the Oracle BI Server query log to help you troubleshoot Logical SQL query issues or to find queries related to a specific subject matter area.

The Oracle BI Server calculates a hash code from the text of the Logical SQL query and the text of the physical SQL queries. The physical SQL hash code, of any SQL queries executed from the Oracle BI Server, is recorded in the ACTION column in V$SQL. Your administrator can join the ACTION column with the PHYSICAL_HASH_ID column in the S_NQ_DB_ACCT table.

When usage tracking is enabled every Logical SQL request submitted to the Oracle BI Server is recorded in the S_NQ_ACCT table. See Setting Up Direct Insertion to Collect Information for Usage Tracking in the System Administrator's Guide for Oracle Business Intelligence Enterprise Edition.

Set the ENABLE_HASH_CODE_IN_SQL_COMMENTS parameter to YES in the NQSConfig.ini file to create an unique Hash_ID with each Logical SQL comment associated with a Logical SQL query.

You should use DISABLE_HASH_CODE after resolving the query issue.

You can associate the physical SQL hash code that is recorded in the Oracle BI Server query.log with the same hash code value that is stored in the ACTION column of the V$SQL performance view in the Oracle Database.

Your Oracle BI Server administrator can associate the physical SQL queries in the V$SQL view by doing the following:

Getting the Oracle BI EE physical query hash code from the ACTION column of the V$SQL view.
Querying the Oracle BI EE physical query usage tracking table, S_NQ_DB_ACCT, filtering on the PHYSICAL_HASH_ID column using the hash code value obtained from the ACTION column of the V$SQL view.
Querying the Oracle BI EE logical query usage tracking table, S_NQ_ACCT, joining the S_NQ_ACCT.ID column with the LOGICAL_QUERY_ID column from the S_NQ_DB_ACCT table.

You can obtain various properties of the corresponding BI logical request from the columns in the S_NQ_ACCT table including the SUBJECT_AREA_NAME column.

The relevant columns for associating the logical request record from S_NQ_ACCT table with the BI query log and the BI catalog are:

QUERY_TEXT represents the text of the logical SQL query, truncated to 4000 bytes. For the complete text of the SQL query, use the QUERY_BLOB columns or in the BI query log file.

For example:
```
select product.productid, product.qtysold, supplier.companyname, supplier.qtysold, (1.0 * product.qtysold) / supplier.qtysold from SnowflakeSales
```
HASH_ID represents the hash code of the Logical SQL query. You can use this identifier to search the query log for all occurrences of the same query.

For example:

a3a04491 as the HASH_ID value
ID represents a unique indentifier for the logical request. You can join the ID column with the LOGICAL_QUERY_ID column in the S_NQ_DB_ACCT table to get the physical SQL query details.