Create Session Level Contention Object: sesswaithistory

sesswaithistory
This object holds historical session information relating to session waits.

Column Datatype Description
SERVER_ID NUMBER Server ID
SESSION_ID NUMBER Connection ID
USER_NAME VARCHAR Name of the user
USER_HOST VARCHAR Name of the user host
PROGRAM_NAME VARCHAR Program user was executing
SQL_HASH VARCHAR Unique identifier for the SQL statement issued
WAIT_TIMESTAMP DATETIME Date/time the wait occurred
WAIT_NAME VARCHAR The name of the wait event
WAIT_TIME NUMBER Total wait time for occurrence of wait
OBJECT_SCHEMA VARCHAR Database or schema of the object involved in wait
OBJECT_NAME VARCHAR Name of object involved in the wait
OBJECT_TYPE VARCHAR Type of object involved in the wait (table, index)
Notes:

This may want to have a rolling mechanism where only the last day or two’s worth of data is retained given the size it could grow to. Another option is that it contains the last ‘n’ number of user wait events.
To be meaningful, the sample rate must, at a minimum, be once a second, and preferably several times a second.