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 thePHYSICAL_HASH_ID
column using the hash code value obtained from theACTION
column of theV$SQL
view. -
Querying the logical query usage tracking table,
S_NQ_ACCT
, joining theS_NQ_ACCT.ID
column with theLOGICAL_QUERY_ID
column from theS_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 theQUERY_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 theS_NQ_DB_ACCT
table to get the physical SQL query details.