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
LOGICAL_QUERY_ID
column in the S_NQ_DB_ACCT
table to get the physical SQL query details.