Data Description
The ORA_DB02_CACH event is used in SAP to check the shared cursor cache and related resource information. In addition, you can see an execution plan and the SQL statement. If the SQL statement is part of an ABAP program you can display the ABAP Source.
Potential Use Cases
This event could be used in the following scenarios:
Identify poorly performing SQL statements in the environment.
Correlate SQL statements to other system activity such as batch jobs or changes implemented.
Dashboard poorly performing SQL statements in the environment.
Splunk Event
The event will look like this in Splunk:
SAP Navigation
Log into the managed system and execute the dbacockpit transaction. Then expand the Performance menu on the left side of the screen, and then expand the SQL Statement Analysis menu. Then double-click the Shared Cursor Cache option. The information displayed on the screen will match the data that is extracted and sent to Splunk.
Field Mapping
Field | Description | Unit of Measure |
|---|---|---|
ACTION | Action name | String |
ADDRESS | Address of statement in the cache | String |
APPLICATION_WAIT_TIME | Application Wait Time (in Microseconds) | Number |
BUFFER_GETS | Total number of "buffer gets" | Number |
CLUSTER_WAIT_TIME | Cluster Wait Time (in Microseconds) | Number |
COMMAND_TYPE | Oracle command type definition | Number |
CONCURRENCY_WAIT_TIME | Concurrency Wait Time (in Microseconds) | Number |
CPU_TIME | CPU Time in Miroseconds | Number |
CURRENT_TIMESTAMP | The date time stamp when the information was collected | YYYYMMDDHHMMSS |
DIRECT_WRITES | Sum of all 'disk writes' | Number |
DISK_READS | Sum of all 'disk reads' | Number |
ELAPSED_TIME | Elapsed Time in Microseconds | Number |
END_FETCH_CNT | Fetch count | Number |
EVENT_SUBTYPE | String | |
EVENT_TYPE | ORA_DB02_CACH | String |
EXECUTIONS | Total number of executions | Number |
FETCHES | Total Number of Fetches | Number |
FIRST_LOAD_TIME | Time SQL statement loaded | YYYY-MM-DD/HH:MM:SS |
HASH_VALUE | Hash Value of Statement (DEC22) | Number |
INST_ID | Oracle Instance ID | Number |
INVALIDATIONS | Number of 'invalidations' for all versions | Number |
LAST_ACTIVE_TIME | Time at which the query plan was last active | YYYYMMDDHHMMSS |
LAST_LOAD_TIME | Last Load Time | YYYY-MM-DD/HH:MM:SS |
LOADED_VERSIONS | Loaded cursor versions | Number |
LOADS | Number of loads or reloads | Number |
MODULE | Program Name | String |
OPEN_VERSIONS | Number of opened cursor versions | Number |
OPTIMIZER_MODE | Optimizer mode | String |
OUTLINE_CATEGORY | Category of outline, if used; otherwise blank | String |
OUTLINE_SID | Outline Session Identifier | String |
PARSE_CALLS | Sum of all 'parse calls' | Number |
PARSING_USER_ID | ID of the user who built this child cursor | Number |
PERSISTENT_MEM | Total 'persistent memory' | Number |
PLAN_HASH_VALUE | Hash Value of Statement (DEC22) | Number |
PLSQL_EXEC_TIME | SQL Elapsed Time per Execution | Number |
ROWS_PROCESSED | Number of processed rows | Number |
RUNTIME_MEM | Total "runtime memory" | Number |
SHARABLE_MEM | Total shareable memory | Number |
SORTS | Sum of the number of sorts | Number |
SQL_ID | SQL_ID | Integer |
SQL_TEXT | SQL statement | String |
USERS_EXECUTING | Number of users executing | Number |
USERS_OPENING | Number of users who have opened cursor version | Number |
USER_IO_WAIT_TIME | User I/O Wait Time (in Microseconds) | Number |
UTCDIFF | The UTC OFFSSET in HHMMSS that the data was collected in | HHMMSS |
UTCSIGN | The UTC positive or negative OFFSET indicator. Positive (+) means add UTCDIFF to find the time zone of the data, negative (-) means subtract the UTCDIFF to find the time zone adjusted date time the data was collected in. | + | - |