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
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.
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
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
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.
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.
a3a04491 as the
LOGICAL_QUERY_IDcolumn in the
S_NQ_DB_ACCTtable to get the physical SQL query details.