Shulou(Shulou.com)05/31 Report--

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!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.