V$Active_session_history | Active Session History in Oracle

I will explain V$Active_session_history | Active Session History in Oracle in this post.

 

V$Active_session_history | Active Session History in Oracle

V$ACTIVE_SESSION_HISTORY is one of the most important Oracle views that displays sampled session activity in the database. It contains snapshots of active database sessions taken once a second.

A database session is considered active if it was on the CPU or was waiting for an event that didn’t belong to the Idle wait class. Refer to the V$EVENT_NAME view for more information on wait classes.

 

V$Active_session_history Columns

ColumnDatatypeDescription
SAMPLE_IDNUMBERID of the sample
SAMPLE_TIMETIMESTAMP(3)Time at which the sample was taken
SESSION_IDNUMBERSession identifier; maps to V$SESSION.SID
SESSION_SERIAL#NUMBERSession serial number (used to uniquely identify a session’s objects); maps to V$SESSION.SERIAL#
USER_IDNUMBEROracle user identifier; maps to V$SESSION.USER#
SQL_IDVARCHAR2(13)SQL identifier of the SQL statement that the session was executing at the time of sampling
SQL_CHILD_NUMBERNUMBERChild number of the SQL statement that the session was executing at the time of sampling
SQL_PLAN_HASH_VALUENUMBERNumerical representation of the SQL plan for the cursor. This information might not be available for all session samples. V$SESSION does not contain this information.
FORCE_MATCHING_SIGNATURENUMBERThe signature used when the CURSOR_SHARING parameter is set to FORCE
SQL_OPCODENUMBERIndicates what phase of operation the SQL statement was in; maps to V$SESSION.COMMANDSee Also: “V$SESSION” for information on interpreting this column
SERVICE_HASHNUMBERHash that identifies the Service; maps to V$ACTIVE_SERVICES.NAME_HASH
SESSION_TYPEVARCHAR2(10)Session type:

  • FOREGROUND
  • BACKGROUND
SESSION_STATEVARCHAR2(7)Session state:

  • WAITING
  • ON CPU
QC_SESSION_IDNUMBERQuery coordinator session ID. This information is only available if the sampled session is a parallel query slave. For all other sessions, the value is 0.
QC_INSTANCE_IDNUMBERQuery coordinator instance ID. This information is only available if the sampled session is a parallel query slave. For all other sessions, the value is 0.
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_STATUSVARCHAR2(11)Status of the blocking session:

  • VALID
  • NO HOLDER
  • GLOBAL
  • NOT IN WAIT
  • UNKNOWN
BLOCKING_SESSION_SERIAL#NUMBERSerial number of the blocking session
EVENTVARCHAR2(64)If SESSION_STATE = WAITING, then the event for which the session was waiting for at the time of sampling.If SESSION_STATE = ON CPU, then this column will be NULL.

See Also: Appendix C, “Oracle Wait Events”

EVENT_IDNUMBERIdentifier of the resource or event for which the session is waiting or for which the session last waited. Interpretation is similar to that of the EVENT column.
EVENT#NUMBERNumber of the resource or event for which the session is waiting or for which the session last waited. Interpretation is similar to that of the EVENT column.
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_TIMENUMBER0 if the session was waiting at the time of samplingTotal wait time for the event for which the session last waited if the session was on the CPU when sampled

Whether or not WAIT_TIME = 0 is what is useful to find the SESSION_STATE at the time of sampling, rather than the actual value of WAIT_TIME itself. Maps to V$SESSION.WAIT_TIME.

TIME_WAITEDNUMBERIf SESSION_STATE = WAITING, then the time that the session actually spent waiting for that EVENT. This column is set for waits that were in progress at the time the sample was taken.If a wait event lasted for more than a second and was caught waiting in more than one session sample row, then the actual time spent waiting for that wait event will be populated in the last of those session sample rows. At any given time, this information will not be available for the latest session sample.
XIDRAW(8)Transaction ID that the session was working on at the time of sampling. V$SESSION does not contain this information.
CURRENT_OBJ#NUMBERObject ID of the object that the session is 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 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 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_BLOCK#.
PROGRAMVARCHAR2(48)Name of the operating system program
MODULEVARCHAR2(48)Name of the executing module when sampled, as set by the DBMS_APPLICATION_INFO.SET_MODULE procedure
ACTIONVARCHAR2(32)Name of the executing module when sampled, as set by the DBMS_APPLICATION_INFO.SET_ACTION procedure
CLIENT_IDVARCHAR2(64)Client identifier of the session; maps to V$SESSION.CLIENT_IDENTIFIER

Oracle Database Admin should know what is the database load profile and Active Session load to compare it when the complaint comes from Customer.

 

 

 

Oracle Database admin should find average database load profile metric values for normal times. Because When customer complain from Slowness then DBA can find actual load profile metric values and compare it from average load profile value.

 

You can find average Active session of database with following script.

 

 SELECT 'Load',
CASE
WHEN ( ( CAST (end_time.sample_time AS DATE)
- CAST (start_time.sample_time AS DATE))
* 24
* 60
* 60) = 0
THEN
0
ELSE
ROUND (
( COUNT (ash.sample_id)
/ ( ( CAST (end_time.sample_time AS DATE)
- CAST (start_time.sample_time AS DATE))
* 24
* 60
* 60)),
2)
END
AS Average_Active_Session
FROM (SELECT MIN (sample_time) sample_time
FROM v$active_session_history ash
WHERE sample_time BETWEEN SYSDATE - 1 / 1440 AND SYSDATE) start_time,
(SELECT MAX (sample_time) sample_time
FROM gv$active_session_history
WHERE sample_time BETWEEN SYSDATE - 1 / 1440 AND SYSDATE) end_time,
gv$active_session_history ash
WHERE ash.sample_time BETWEEN start_time.sample_time
AND end_time.sample_time
GROUP BY end_time.sample_time, start_time.sample_time;

 

 

When you take many complaints from customer about slowness of database, you should check Oracle database wait events.

You can check Top Oracle database wait events from Active session history which is v$active_session_history queries with below script.

select * from (
select active_session_history.event,
sum(active_session_history.wait_time +
active_session_history.time_waited) ttl_wait_time
from gv$active_session_history active_session_history
where active_session_history.event is not null
group by active_session_history.event
order by 2 desc)
where rownum <= 10;

 

 

Query result is like below screenshot.

 

 

Read the following post to learn more details about V$ views.

V$SQLTEXT and V$SQLSTATS 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

 

 22,554 views last month,  3 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