The table below describes each column in the S_NQ_ACCT
usage tracking table. Where appropriate, the data type and length is also included.
As you review the descriptions in the table below, you might assume that certain of the time-related columns can be added or subtracted to equal exact values. For example, you might assume that TOTAL_TIME_SEC is equal to END_TS minus START_TS. The following list explains why the columns do not provide such exact values:
The various processes run in parallel and their speed depends on the load on the BI Server and on database performance. The server-based operations might be either light or intensive.
If all connections are full, then the query enters a queue and waits to be processed. The timing depends on the load and configuration of the BI Server.
Column | Description |
---|---|
CACHE_IND_FLG |
Default is N. Y indicates a cache hit for the query; N indicates a cache miss. |
COMPILE_TIME_SEC |
The time in seconds that is required to compile the query. The number for COMPILE_TIME_SEC is included in TOTAL_TIME_SEC, as described in this table. |
CUM_DB_TIME_SEC |
The cumulative time of all queries sent to the database. Queries run in parallel, so the cumulative query time is equal to or greater than the total time connected to the database. For example, if a logical request spawns 4 physical SQL statements sent to the database, and the query time for 3 of the queries is 10 seconds, and for one query is 15 seconds. Since the queries run in parallel, nqsserver is only connected to the database for 15 seconds, but CUM_DB_TIME_SEC will show 45 seconds. |
CUM_NUM_DB_ROW |
The total number of rows that are returned by the back-end databases. |
END_DT |
The date the logical query was completed. |
END_HOUR_MIN |
The hour and minute the logical query was completed. |
END_TS |
The date and time that the logical query finished. The start and end timestamps also reflect any time that the query spent waiting for resources to become available. If the user submitting the query navigates away from the page before the query finishes, then the final fetch never happens, and a timeout value of 3600 is recorded. However, if the user navigates back to the page before the timeout, then the fetch completes at that time, and this is recorded as the end_ts time. |
ERROR_TEXT |
Default is Null. Varchar(250) Error message from the back-end database. This column is only applicable if the |
ID |
The unique row ID. |
NODE_ID |
Concatenates <hostname>:<component_name> where <component_name> can be overridden by the environment variable COMPONENT_NAME. For example, Default value of COMPONENT_NAME is obis1. |
NUM_CACHE_HITS |
Indicates the number of times that the cache result returned for the query. NUM_CACHE_HITS is a 32-bit integer (or a 10-digit integer). Default is Null. |
NUM_CACHE_INSERTED |
Indicates the number of times that the query generated a cache entry. Default is Null. NUM_CACHE_INSERTED is a 32-bit integer (or a 10-digit integer). |
NUM_DB_QUERY |
The number of queries that were submitted to back-end databases to satisfy the logical query request. For successful queries (SuccessFlag = 0) this number is 1 or greater. |
PRESENTATION_NAME |
Default is Null. Varchar(128) The name of the Oracle BI Presentation Catalog. |
QUERY_BLOB |
Contains the entire logical SQL statement without any truncation. The QUERY_BLOB column is a long character string. |
QUERY_KEY |
Default is Null. Varchar(128). An MD5 hash key that is generated by Oracle Business Intelligence from the logical SQL statement. |
QUERY_SRC_CD |
The source of the request. Values that can be inserted, for example: An analysis, or any export operation inserts 'Report'. Using the 'Value' drop down in a filter dialog, or using a dashboard prompt inserts 'ValuePrompt'. Agent to seed analytics server cache inserts 'Seed'. Online Admin Tool physical table or column row count, or view data inserts 'NULL'. |
QUERY_TEXT |
Varchar(1024). The SQL statement that was submitted for the query. You can change the length of this column (using the ALTER TABLE command), but note that the text that is written into this column is always truncated to the size that is defined in the physical layer. It is the responsibility of the repository administrator not to set the length of this column to a value greater than the maximum query length that is supported by the back-end physical database. For example, Oracle Databases enable a maximum Varchar of 4000, but Oracle Databases truncate to 4000 bytes, not 4000 characters. Hence, if you use a multi-byte character set, the actual maximum string size has a varying number of characters, depending on the character set and characters used. |
REPOSITORY_NAME |
The name of the repository that the query accesses. |
ROW_COUNT |
The number of rows that are returned to the query client. When a large amount of data is returned from a query, this column is not populated until the user displays all of the data. |
IMPERSONATOR_USER_NAME |
Default is None. Varchar(128). The user name of the impersonated user. If the request is not run as an impersonated user, then the value is 'None'. |
SAW_DASHBOARD |
The path name of the dashboard. If the query was not submitted through a dashboard, then the value is NULL. |
SAW_DASHBOARD_PG |
Default is Null. Varchar(150) The page name in the dashboard. If the request is not a dashboard request, then the value is NULL. |
SAW_SRC_PATH |
The path name in the Oracle BI Presentation Catalog for the analysis. |
START_DT |
The date that the logical query was submitted. |
START_HOUR_MIN |
The hour and minute that the logical query was submitted. |
START_TS |
The date and time that the logical query was submitted. |
SUBJECT_AREA_NAME |
The name of the business model that is being accessed. |
SUCCESS_FLG |
The completion status of the query, as defined in the following list:
|
TOTAL_TIME_SEC |
The time in seconds that the BI Server spent working on the query while the client waited for responses to its analyses. TOTAL_TIME_SEC includes the time for COMPILE_TIME_SEC. This setting is the same as the Response time in the nqquery.log file, as described in Setting the Query Logging Level. |
USER_NAME |
The name of the user who submitted the query. |
The table below describes the S_NQ_DB_ACCT
table, which supplements the usage tracking table by providing the physical SQL information for the logical queries stored in S_NQ_ACCT
. S_NQ_DB_ACCT
has a foreign key relationship back to S_NQ_ACCT
.
Column | Description |
---|---|
END_DT |
The date the physical query was completed. |
END_HOUR_MIN |
The hour and minute the physical query was completed. |
END_TS |
The date and time the physical query finished. The start and end timestamps also reflect any time that the query spent waiting for resources to become available. |
ID |
The unique row ID. |
LOGICAL_QUERY_ID |
Varchar2(50). Refers to the logical query in the S_NQ_ACCT table. |
QUERY_BLOB |
Contains the entire physical SQL statement without any truncation. The QUERY_BLOB column is a long character string. |
QUERY_TEXT |
Varchar(1024). The SQL statement that was submitted for the query. |
ROW_COUNT |
The number of rows that are returned to the query client. |
TIME_SEC |
The physical query execution time. |
START_DT |
The date that the physical query was submitted. |
START_HOUR_MIN |
The hour and minute that the physical query was submitted. |
START_TS |
The date and time that the physical query was submitted. |
The table below describes each column in the S_NQ_INITBLOCK
usage tracking table, which tracks information about initialization blocks.
Column | Description |
---|---|
USER_NAME |
Varchar2(128). The name of the user who ran the initialization block. |
REPOSITORY_NAME |
Varchar2(128). The name of the repository that the query accesses. |
TENANT_ID |
Varchar2(128). The name of the tenant of the user who ran the initialization block. |
SERVICE_NAME |
Varchar2(128). The name of the service. |
ECID |
Varchar2(1024). The system-generated execution context ID. |
SESSION_ID |
Number(10). The ID of the session. |
BLOCK_NAME |
Varchar2(128). The name of the initialization block that was executed. |
START_TS |
The date and time that the initialization block started. |
END_TS |
The date and time that the initialization block finished. The start and end timestamps also reflect any time that the query spent waiting for resources to become available. |
DURATION |
Number(13,3). The length of time it took to execute the initialization block. |
NOTES |
Varchar2(1024). Notes about the initialization block and its execution. |