How to view the session information corresponding to historical session wait events in the database
Editor to share with you how to view the session information corresponding to historical session waiting events in the database. I hope you will gain something after reading this article. Let's discuss it together.
Take the enq: TX-row lock contention wait time as an example.
If this reply occurs within the default number of days to keep the awr snapshot information.
The relevant session information can be queried at the following sql.
Select * from DBA_HIST_ACTIVE_SESS_HISTORY where event like'% enq: TX-row lock contention%'
The blocking_session field in the DBA_HIST_ACTIVE_SESS_HISTORY associates the session_id in the DBA_HIST_ACTIVE_SESS_HISTORY to find the corresponding sql_id to get the reply message.
You can obtain information directly through the following query:
Select t.instance_number
T.sample_time
Lpad ('-', 2 * (level-1),'-') | | t.client_id
T.session_id
T.blocking_session
T.session_serial#
T.sql_id
T.event
T.session_state
Level
Connect_by_isleaf
Connect_by_iscycle
From dba_hist_active_sess_history t
Where snap_id between 36878 and 36879
Start with blocking_session is not null
And event like 'enq: TX-row lock contention%'
Connect by nocycle sample_time = prior sample_time
And session_id = prior blocking_session
And session_serial# = prior blocking_session_serial#
Where blocking session is the session that is blocking the reply.
Actual combat cases:
View the session where the wait event is a row lock
Select a.snap_id
A.sql_id
A.session_id
A.session_serial#
A.blocking_session
A.blocking_session_serial#
A.blocking_session_status
From DBA_HIST_ACTIVE_SESS_HISTORY a
Where event like'% enq: TX-row lock contention%'
And snap_id between 20399 and 20400
Write subqueries, view blocking replies, and count the number of blocking times
Select a.blocking_session
A.blocking_session_serial#
Count (a.blocking_session)
From DBA_HIST_ACTIVE_SESS_HISTORY a
Where event like'% enq: TX-row lock contention%'
And snap_id between 20399 and 20400
Group by a.blocking_session, a.blocking_session_serial#
Order by 3 desc
Check the sql_id of the blocking call and the blocked sql_id if the block is greater than 19 times
Select distinct b.sql_id,c.blocked_sql_id
From DBA_HIST_ACTIVE_SESS_HISTORY b
(select a.sql_id as blocked_sql_id
A.blocking_session
A.blocking_session_serial#
Count (a.blocking_session)
From DBA_HIST_ACTIVE_SESS_HISTORY a
Where event like'% enq: TX-row lock contention%'
And snap_id between 20399 and 20400
Group by a.blocking_session, a.blocking_session_serial#,a.sql_id
Having count (a.blocking_session) > 19
Order by 3 desc) c
Where b.session_id = c.blocking_session
And b.session_serial# = c.blocking_session_serial#
And b.snap_id between 20399 and 20400
Dynamic performance View Notes:
V$ACTIVE_SESSION_HISTORY 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. After reading this article, I believe you have a certain understanding of "how to view the session information corresponding to historical session waiting events in the database". If you want to know more about it, welcome to follow the industry information channel, thank you for reading!
