About
The table S_NQ_ACCT is a database table that holds all data log of the usage tracking functionality.
Articles Related
Description of the Usage Tracking Data
Functional
| Group | Column | Description |
|---|---|---|
| BI Server | COMPILE_TIME_SEC | The time in seconds required to compile the query. |
| BI Server | NODE_ID | The host name of the computer where the Oracle BI Server is running. If this field has the value instance1:corea, see the bug 14083146 |
| BI Server | REPOSITORY_NAME | The name of the repository the query accesses. |
| BI Server | RUNAS_USER_NAME (IMPERSONATOR_USER_NAME 11g) | “Default is Null. Varchar(128) User Id of impersonated user. If the request is not run as an impersonated user, the value will be NULL.” |
| BI Server | TOTAL_TIME_SEC | The time in seconds that the Oracle BI Server spent working on the query while the client waited for responses to its query requests. |
| BI Server | USER_NAME | The name of the user who submitted the query. |
| Logical Query | END_DT | The date the logical query was completed. |
| Logical Query | END_HOUR_MIN | The hour and minute the logical query was completed. |
| Logical Query | END_TS | The date and time the logical query finished. The start and end timestamps also reflect any time the query spent waiting for resources to become available. |
| Logical Query | QUERY_TEXT | The logical SQL submitted for the query. |
| Logical Query | QUERY_BLOB | 11g Contains the entire logical SQL statement without any truncation. |
| Logical Query | QUERY_KEY | 11g - An MD5 hash key that is generated by Oracle Business Intelligence from the logical SQL statement. |
| Logical Query | ROW_COUNT | The number of rows returned to the query client. |
| Logical Query | START_DT | The date the logical query was submitted. |
| Logical Query | START_HOUR_MIN | The hour and minute the logical query was submitted. |
| Logical Query | START_TS | The date and time the logical query was submitted. |
| Logical Query | SUBJECT_AREA_NAME | The name of the business model being accessed. |
| Physical Query | CUM_DB_TIME_SEC | The total amount of time in seconds that the Oracle BI Server waited for back-end physical databases on behalf of a logical query. |
| Physical Query | CUM_NUM_DB_ROW | The total number of rows returned by the back-end databases. |
| Physical Query | ERROR_TEXT | “Default is Null. Varchar(250) Error message from the back-end database. This column is only applicable if the SUCCESS_FLG is set to a value other than 0 (zero). Multiple messages will concatenate and will not be parsed by Oracle BI Server.” |
| Physical Query | NUM_DB_QUERY | The number of queries submitted to back-end databases in order to satisfy the logical query request. For successful queries (SuccessFlag = 0) this number will be 1 or greater. |
| Presentation Catalog | PRESENTATION_NAME | “Default is Null. Varchar(128) Name of the Presentation Catalog in Oracle BI Presentation Services.” |
| Presentation Catalog | QUERY_SRC_CD | The source of the request, for example, Drill or Report. |
| Presentation Catalog | SAW_DASHBOARD | Path of the dashboard. If the query was not submitted through an Interactive Dashboard, the value will be NULL. |
| Presentation Catalog | SAW_DASHBOARD_PG | “Default is Null. Varchar(150) Page name in the Interactive Dashboard. If the request is not a dashboard request, the value will be NULL.” |
| Presentation Catalog | SAW_SRC_PATH | The path name in the Oracle BI Presentation Catalog for the request. |
| Query Cache | CACHE_IND_FLG | “Default is N. Y indicates a cache hit for the query, N indicates a cache miss.” |
| Query Cache | NUM_CACHE_HITS | “Default is Null. Number(10,0). Indicates the number of times existing cache was returned.” |
| Query Cache | NUM_CACHE_INSERTED | “Default is Null. Number(10,0). Indicates the number of times query generated cache was returned.” |
| Success | SUCCESS_FLG | The completion status of the query: 0 - The query completed successfully with no errors. 1 - The query timed out. 2 - The query failed because row limits were exceeded. 3 - The query failed due to some other reason. |
| ID | 11g - The unique row ID (in order to link to the physical table S_NQ_DB_ACCT ?) | |
| HASH_ID | 11.9 ? | |
| TENANT_ID | 11.9 The name of the tenant of the user who ran the query ? | |
| SERVICE_NAME | 11.9 The name of the service. | |
| Query | ECID | 11.9 The system-generated execution context ID. |
| User | SESSION_ID | 11.9 The ID of the session |
Structure
| Column | Data Type | Precision | Scale | Nullable |
|---|---|---|---|---|
| CACHE_IND_FLG | CHAR | 1 | 0 | NOT NULLABLE |
| COMPILE_TIME_SEC | DOUBLE | 10 | 0 | NULLABLE |
| CUM_DB_TIME_SEC | DOUBLE | 10 | 0 | NULLABLE |
| CUM_NUM_DB_ROW | DOUBLE | 20 | 0 | NULLABLE |
| ECID | VARCHAR | 1024 | 0 | NULLABLE |
| END_DT | DATETIME | 1024 | 0 | NULLABLE |
| END_HOUR_MIN | CHAR | 5 | 0 | NULLABLE |
| END_TS | TIMESTAMP | 5 | 6 | NULLABLE |
| ERROR_TEXT | VARCHAR | 250 | 0 | NULLABLE |
| HASH_ID | VARCHAR | 128 | 0 | NULLABLE |
| ID | VARCHAR | 50 | 0 | NOT NULLABLE |
| IMPERSONATOR_USER_NAME | VARCHAR | 128 | 0 | NULLABLE |
| NODE_ID | VARCHAR | 100 | 0 | NULLABLE |
| NUM_CACHE_HITS | DOUBLE | 10 | 0 | NULLABLE |
| NUM_CACHE_INSERTED | DOUBLE | 10 | 0 | NULLABLE |
| NUM_DB_QUERY | DOUBLE | 10 | 0 | NULLABLE |
| PRESENTATION_NAME | VARCHAR | 128 | 0 | NULLABLE |
| QUERY_BLOB | LONGVARCHAR | 32768 | 0 | NULLABLE |
| QUERY_KEY | VARCHAR | 128 | 0 | NULLABLE |
| QUERY_SRC_CD | VARCHAR | 30 | 0 | NULLABLE |
| QUERY_TEXT | VARCHAR | 1024 | 0 | NULLABLE |
| REPOSITORY_NAME | VARCHAR | 128 | 0 | NULLABLE |
| ROW_COUNT | DOUBLE | 20 | 0 | NULLABLE |
| SAW_DASHBOARD | VARCHAR | 150 | 0 | NULLABLE |
| SAW_DASHBOARD_PG | VARCHAR | 150 | 0 | NULLABLE |
| SAW_SRC_PATH | VARCHAR | 250 | 0 | NULLABLE |
| SERVICE_NAME | VARCHAR | 128 | 0 | NULLABLE |
| SESSION_ID | DOUBLE | 10 | 0 | NULLABLE |
| START_DT | DATETIME | 10 | 0 | NULLABLE |
| START_HOUR_MIN | CHAR | 5 | 0 | NULLABLE |
| START_TS | TIMESTAMP | 5 | 6 | NULLABLE |
| SUBJECT_AREA_NAME | VARCHAR | 128 | 0 | NULLABLE |
| SUCCESS_FLG | DOUBLE | 10 | 0 | NULLABLE |
| TENANT_ID | VARCHAR | 128 | 0 | NULLABLE |
| TOTAL_TIME_SEC | DOUBLE | 10 | 0 | NULLABLE |
| USER_NAME | VARCHAR | 128 | 0 | NULLABLE |
Ibot
If the ibot are used for seeding cache then In the table which stores Usage Tracking data there is a column called “QUERY_SRC_CD”. A value of 'seed' in the Query source (QUERY_SRC_CD) is indicative of a cache seeding query. So you can differenciate the cache seeding query in usgae tracking based on the value in this column.
However in this case customer had set delivers destination as “dashboard”. In this case we cannot differenctiate between queries issued from the web versus ibot - Because ibot is run as impersonated user and there is no separate mechanism to track impersonation.