Associate S_NQ_ACCT Record with the Query Log

Your administrator can associate the records in the 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 run 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 Set Up Direct Insertion to Collect Information for Usage Tracking in the Administering Oracle Analytics Server.

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's recorded in the query.log with the same hash code value that's stored in the ACTION column of the V$SQL performance view in the Oracle Database.

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

Getting the physical query hash code from the ACTION column of the V$SQL view.
Querying the 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 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 query log and the 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 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 identifier 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.