DBA_Hist_Active_Sess_History | Active Session History (ASH) in Oracle

I will explain DBA_Hist_Active_Sess_History | Active Session History (ASH) in Oracle in this post.

 

DBA_Hist_Active_Sess_History | Active Session History (ASH) in Oracle

dba_hist_active_sess_history view is one of the most important Oracle views that displays history of the contents of the in-memory active session history of recent system activity. It contains snapshots of V$ACTIVE_SESSION_HISTORY

 

 

 

 

 

dba_hist_active_sess_history columns are as follows.

 

ColumnDatatypeNULLDescription
SNAP_IDNUMBERNOT NULLUnique snapshot ID
DBIDNUMBERNOT NULLDatabase ID for the snapshot
INSTANCE_NUMBERNUMBERNOT NULLInstance number for the snapshot
SAMPLE_IDNUMBERNOT NULLID of the sample
SAMPLE_TIMETIMESTAMP(3)NOT NULLTime of the sample
SESSION_IDNUMBERNOT NULLSession identifier
SESSION_SERIAL#NUMBERSession serial number (used to uniquely identify a session’s objects)
SESSION_TYPEVARCHAR2(10)Session type:

  • FOREGROUND
  • BACKGROUND
FLAGSNUMBERReserved for future use
USER_IDNUMBEROracle user identifier
SQL_IDVARCHAR2(13)SQL identifier of the SQL statement that is currently being executed
IS_SQLID_CURRENTVARCHAR2(1)Indicates whether the SQL identifier in the SQL_ID column is being executed (Y) or not (N)
SQL_CHILD_NUMBERNUMBERChild number of the SQL statement that is currently being executed
SQL_OPCODENUMBERIndicates what phase of operation the SQL statement is in
SQL_OPNAMEVARCHAR2(64)SQL command name
FORCE_MATCHING_SIGNATURENUMBERSignature used when the CURSOR_SHARING parameter is set to FORCE
TOP_LEVEL_SQL_IDVARCHAR2(13)SQL identifier of the top level SQL statement
TOP_LEVEL_SQL_OPCODENUMBERIndicates what phase of operation the top level SQL statement was in
SQL_PLAN_HASH_VALUENUMBERNumerical representation of the SQL plan for the cursor
SQL_FULL_PLAN_HASH_VALUENUMBERNumerical representation of the complete SQL plan for the cursor being executed by this session
SQL_ADAPTIVE_PLAN_RESOLVEDNUMBERIndicates whether the SQL plan of the sampled database session is a resolved adaptive plan or not
SQL_PLAN_LINE_IDNUMBERSQL plan line ID
SQL_PLAN_OPERATIONVARCHAR2(64)Plan operation name
SQL_PLAN_OPTIONSVARCHAR2(64)Plan operation options
SQL_EXEC_IDNUMBERSQL execution identifier
SQL_EXEC_STARTDATETime when the execution of the SQL started
PLSQL_ENTRY_OBJECT_IDNUMBERObject ID of the top-most PL/SQL subprogram on the stack (or NULL if there is no PL/SQL subprogram on the stack)
PLSQL_ENTRY_SUBPROGRAM_IDNUMBERSubprogram ID of the top-most PL/SQL subprogram on the stack (or NULL if there is no PL/SQL subprogram on the stack)
PLSQL_OBJECT_IDNUMBERObject ID of the currently executing PL/SQL subprogram (or NULL if executing SQL)
PLSQL_SUBPROGRAM_IDNUMBERSubprogram ID of the currently executing PL/SQL object (or NULL if executing SQL)
QC_INSTANCE_IDNUMBERQuery coordinator instance ID
QC_SESSION_IDNUMBERQuery coordinator session ID
QC_SESSION_SERIAL#NUMBERQuery coordinator session serial number
PX_FLAGSNUMBERReserved for internal use
EVENTVARCHAR2(64)If SESSION_STATE = WAITING, then the event for which the session was waiting at the time of sampling.

If SESSION_STATE = ON CPU, then this column will be NULL.

EVENT_IDNUMBERIdentifier of the resource or event for which the session is waiting or for which the session last waited
SEQ#NUMBERSequence number that uniquely identifies the wait (incremented for each wait)
P1TEXTVARCHAR2(64)Text of first additional parameter
P1NUMBERFirst additional parameter
P2TEXTVARCHAR2(64)Text of second additional parameter
P2NUMBERSecond additional parameter
P3TEXTVARCHAR2(64)Text of third additional parameter
P3NUMBERThird additional parameter
WAIT_CLASSVARCHAR2(64)Wait class name of the event for which the session was waiting at the time of sampling. Interpretation is similar to that of the EVENT column. Maps to V$SESSION.WAIT_CLASS.
WAIT_CLASS_IDNUMBERWait class identifier of the event for which the session was waiting at the time of sampling. Interpretation is similar to that of the EVENT column. Maps to V$SESSION.WAIT_CLASS_ID.
WAIT_TIMENUMBERTotal wait time (in microseconds) for the event for which the session last waited (0 if currently waiting)
SESSION_STATEVARCHAR2(7)Session state:

  • WAITING
  • ON CPU
TIME_WAITEDNUMBERTime that the current session actually spent waiting for the event (in microseconds). This column is set for waits that were in progress at the time the sample was taken.
BLOCKING_SESSION_STATUSVARCHAR2(11)Status of the blocking session:

  • VALID
  • NO HOLDER
  • GLOBAL
  • NOT IN WAIT
  • UNKNOWN
BLOCKING_SESSIONNUMBERSession identifier of the blocking session. Populated only when the session was waiting for enqueues or a “buffer busy” wait. Maps to V$SESSION.BLOCKING_SESSION.
BLOCKING_SESSION_SERIAL#NUMBERSerial number of the blocking session
BLOCKING_INST_IDNUMBERInstance number of the blocker shown in BLOCKING_SESSION
BLOCKING_HANGCHAIN_INFOVARCHAR2(1)Indicates whether the information about BLOCKING_SESSION comes from the hang chain (Y) or not (N)
CURRENT_OBJ#NUMBERObject ID of the object that the session is currently referencing. This information is only available if the session was waiting for Application, Cluster, Concurrency, and User I/O wait events. Maps to V$SESSION.ROW_WAIT_OBJ#.
CURRENT_FILE#NUMBERFile number of the file containing the block that the session is currently referencing. This information is only available if the session was waiting for Cluster, Concurrency, and User I/O wait events. Maps to V$SESSION.ROW_WAIT_FILE#.
CURRENT_BLOCK#NUMBERID of the block that the session is currently referencing
CURRENT_ROW#NUMBERRow identifier that the session is referencing
TOP_LEVEL_CALL#NUMBEROracle top level call number
TOP_LEVEL_CALL_NAMEVARCHAR2(64)Oracle top level call name
CONSUMER_GROUP_IDNUMBERConsumer group ID
XIDRAW(8)Transaction ID that the session was working on at the time of sampling. V$SESSION does not contain this information.
REMOTE_INSTANCE#NUMBERRemote instance identifier that will serve the block that this session is waiting for. This information is only available if the session was waiting for cluster events.
TIME_MODELNUMBERTime model information
IN_CONNECTION_MGMTVARCHAR2(1)Indicates whether the session was doing connection management at the time of sampling (Y) or not (N)
IN_PARSEVARCHAR2(1)Indicates whether the session was parsing at the time of sampling (Y) or not (N)
IN_HARD_PARSEVARCHAR2(1)Indicates whether the session was hard parsing at the time of sampling (Y) or not (N)
IN_SQL_EXECUTIONVARCHAR2(1)Indicates whether the session was executing SQL statements at the time of sampling (Y) or not (N)
IN_PLSQL_EXECUTIONVARCHAR2(1)Indicates whether the session was executing PL/SQL at the time of sampling (Y) or not (N)
IN_PLSQL_RPCVARCHAR2(1)Indicates whether the session was executing inbound PL/SQL RPC calls at the time of sampling (Y) or not (N)
IN_PLSQL_COMPILATIONVARCHAR2(1)Indicates whether the session was compiling PL/SQL at the time of sampling (Y) or not (N)
IN_JAVA_EXECUTIONVARCHAR2(1)Indicates whether the session was executing Java at the time of sampling (Y) or not (N)
IN_BINDVARCHAR2(1)Indicates whether the session was doing bind operations at the time of sampling (Y) or not (N)
IN_CURSOR_CLOSEVARCHAR2(1)Indicates whether the session was closing a cursor at the time of sampling (Y) or not (N)
IN_INMEMORY_QUERYFoot 1VARCHAR2(1)Indicates whether the session was querying the In-Memory Column Store (IM column store) at the time of sampling (Y) or not (N)
IN_INMEMORY_POPULATEFootref 1VARCHAR2(1)Indicates whether the session was populating the IM column store at the time of sampling (Y) or not (N)
IN_INMEMORY_PREPOPULATEFootref 1VARCHAR2(1)Indicates whether the session was prepopulating the IM column store at the time of sampling (Y) or not (N)
IN_INMEMORY_REPOPULATEFootref 1VARCHAR2(1)Indicates whether the session was repopulating the IM column store at the time of sampling (Y) or not (N)
IN_INMEMORY_TREPOPULATEFootref 1VARCHAR2(1)Indicates whether the session was trickle repopulating the IM column store at the time of sampling (Y) or not (N)
IN_SEQUENCE_LOADVARCHAR2(1)Indicates whether the session is loading in sequence (in sequence load code) (Y) or not (N)
CAPTURE_OVERHEADVARCHAR2(1)Indicates whether the session is executing capture code (Y) or not (N)
REPLAY_OVERHEADVARCHAR2(1)Indicates whether the session is executing replay code (Y) or not (N)
IS_CAPTUREDVARCHAR2(1)Indicates whether the session is being captured (Y) or not (N)
IS_REPLAYEDVARCHAR2(1)Indicates whether the session is being replayed (Y) or not (N)
SERVICE_HASHNUMBERHash that identifies the Service
PROGRAMVARCHAR2(64)Name of the operating system program
MODULEVARCHAR2(64)Name of the currently executing module as set by the DBMS_APPLICATION_INFO.SET_MODULE procedure
ACTIONVARCHAR2(64)Name of the currently executing action as set by the DBMS_APPLICATION_INFO.SET_ACTION procedure
CLIENT_IDVARCHAR2(64)Client identifier of the session
MACHINEVARCHAR2(64)Client’s operating system machine name
PORTNUMBERClient port number
ECIDVARCHAR2(64)Execution context identifier (sent by Application Server)
DBREPLAY_FILE_IDNUMBERIf the session is being captured or replayed, then DBREPLAY_FILE_ID is the file ID for the workload capture or workload replay; otherwise it is NULL.
DBREPLAY_CALL_COUNTERNUMBERIf the session is being captured or replayed, then DBREPLAY_CALL_COUNTER is the call counter of the user call that is being captured or replayed; otherwise it is NULL.
TM_DELTA_TIMENUMBERTime interval (in microseconds) over which TM_DELTA_CPU_TIME and TM_DELTA_DB_TIME are accumulated
TM_DELTA_CPU_TIMENUMBERAmount of time this session spent on CPU over the last TM_DELTA_TIME microseconds
TM_DELTA_DB_TIMENUMBERAmount of time spent by this session in database calls over the last TM_DELTA_TIME microseconds
DELTA_TIMENUMBERTime interval (in microseconds) since the last time this session was sampled or created, over which the next five statistics are accumulated
DELTA_READ_IO_REQUESTSNUMBERNumber of read I/O requests made by this session over the last DELTA_TIME microseconds
DELTA_WRITE_IO_REQUESTSNUMBERNumber of write I/O requests made by this session over the last DELTA_TIME microseconds
DELTA_READ_IO_BYTESNUMBERNumber of I/O bytes read by this session over the last DELTA_TIME microseconds
DELTA_WRITE_IO_BYTESNUMBERNumber of I/O bytes written by this session over the last DELTA_TIME microseconds
DELTA_INTERCONNECT_IO_BYTESNUMBERNumber of I/O bytes sent over the I/O interconnect over the last DELTA_TIME microseconds
PGA_ALLOCATEDNUMBERAmount of PGA memory (in bytes) consumed by this session at the time this sample was taken
TEMP_SPACE_ALLOCATEDNUMBERAmount of TEMP memory (in bytes) consumed by this session at the time this sample was taken
DBOP_NAMEVARCHAR2(64)Database operation name. If the type is SQL, the DBOP_NAME will be NULL.
DBOP_EXEC_IDNUMBERDatabase operation execution identifier for the current execution. If the type is SQL, the DBOP_EXEC_ID will be NULL.
CON_DBIDNUMBERThe database ID of the PDB for the sampled session
CON_IDNUMBERThe ID of the container that CON_DBID identifies. Possible values include:

  • 0: This value is used for rows containing data that pertain to the entire CDB. This value is also used for rows in non-CDBs.
  • 1: This value is used for rows containing data that pertain to only the root
  • n: Where n is the applicable container ID for the rows containing data

 

 

You can find the sql statements that are waiting on Gc current block busy and Gc cr block busy wait event using the following script.

select v.sql_text,v.sql_fulltext,sub.* from gv$sql v,
(select sample_time,s.sql_id sql_id, session_state, blocking_session,
owner||'.'||object_name||':'||nvl(subobject_name,'-') obj_name,s.program,s.module,s.machine
from dba_hist_active_sess_history s, dba_objects o
where sample_time between
to_date('02/08/2021  07:30:02','DD/MM/YYYY HH24:MI:SS')
and
to_date('02/08/2021 15:10:02','DD/MM/YYYY HH24:MI:SS')
and event like '%gc%'
and o.data_object_id = s.current_obj#
order by 1 desc) sub where sub.sql_id=v.sql_id;

 

 

You can read the following post to read for v$active_session_history view and its details.

V$Active_session_history | Active Session History in Oracle

 

 

Do you want to learn Oracle Database Performance Tuning detailed, then read the following articles.

Performance Tuning and SQL Tuning Tutorial in the Oracle Database

 

 38,881 views last month,  132 views today

About Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress [email protected].-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  [email protected] a mail atabilirsiniz.

Leave a Reply