Now that we are getting data into the tables and have set up the BI repository to access the data, we can start creating analyses to see what is going on. Typical analyses that I would create are:
Before using some of the measures, you need to understand what each one does and how they are relevant. The following describes each field in the main usage tracking table:
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. This setting is the same as the response time in the NQQuery.log
file. Usually it is the difference between the start time and the end time. The same results are returned from the following function:ROUND((CAST(END_TS as DATE)-CAST(START_TS as DATE))*86400)
COMPILE_TIME_SEC
: The time (in seconds) required to compile the query.ROW_COUNT
: The number of rows returned to the querying client.CUM_DB_TIME_SEC
: The total amount of time (in seconds) that the Oracle BI Server waited for backend physical databases on behalf of a logical query.CUM_NUM_DB_ROW
: The total number of rows returned by the backend databases.NUM_DB_QUERY
: The number of queries submitted to backend databases in order to satisfy the logical query request. For successful queries (SuccessFlag = 0
), this number will be 1 or greater.NUM_CACHE_HITS
: Indicates the number of times the existing cache was returned.NUM_CACHE_INSERTED
: Indicates the number of times the query-generated cache was returned.QUERY_TEXT
: The SQL submitted for the query.QUERY_BLOB
: The data type is ntext
when using SQL Server, and CLOB when using Oracle, DB2, or Terradata databases. It contains the entire logical SQL statement without any truncation.QUERY_KEY
: An MD5 hash key that is generated by Oracle Business Intelligence from the logical SQL statement.SUBJECT_AREA_NAME
: The name of the business model being accessed.REPOSITORY_NAME
: The name of the repository the query accesses.IMPERSONATOR_USER_NAME
: User ID of an impersonated user. If the request is not run as an impersonated user, the value will be NULL
.USER_NAME
: The name of the user who submitted the query, from the USER
variable.PRESENTATION_NAME
: The name of the Presentation Catalog in Oracle BI Presentation Services.QUERY_SRC_CD
: The source of the request; for example, Drill or Report.SAW_DASHBOARD
The path of the dashboard. If the query was not submitted through an Interactive Dashboard, the value will be NULL
.SAW_DASHBOARD_PG
: The page's name in the Interactive Dashboard. If the request is not a dashboard request, the value will be NULL
.SAW_SRC_PATH
: The path name in the Oracle BI Presentation Catalog for the request.START_DT
: The date the logical query was submitted.START_HOUR_MIN
: The hour and minute the logical query was submitted.START_TS
: The date and time the logical query was submitted.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 the logical query finished. The start and end timestamps also reflect the time that the query spent waiting for resources to become available.ERROR_TEXT
: The error message from the backend database. This column is only applicable if SUCCESS_FLG
is set to a value other than 0
(zero). Multiple messages will concatenate and will not be parsed by Oracle BI Server.CACHE_IND_FLG
: The default value is N
. Y
indicates a cache hit for the query and N
indicates a cache miss.SUCCESS_FLG
: This indicates the completion status of the query:0
: The query completed successfully with no errors1
: The query timed out2
: The query failed because row limits were exceeded3
: The query failed due to some other reason
The following describes each field in the extra usage tracking table used to store physical SQL sent to the database:
QUERY_TEXT
: The first 1024
characters of the SQL queryQUERY_BLOB
: The whole SQL query sent to the databaseTIME_SEC
: The time taken to return data from the databaseROW_COUNT
: The number of rows returned from the databaseSTART_TS
: Start timeSTART_DT
: Start dateSTART_HOUR_MIN
: The time (in hours and minutes) that the query was sent to the databaseEND_TS
: The time the results were returned to the BI ServerEND_DT
: The date the results were returned to the BI ServerEND_HOUR_MIN
: The hours and minutes in the HHMM format that the data returned3.14.145.82