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 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.

+ | -