Oracle by Example: Investigating locks in V$ACTIVE_SESSION_HISTORY

=========================
General
=========================
For some reason, response time from database was very slow.
From several milliseconds response time grew to several seconds
Likely locks were involved.

Checking the performance of single SELECT or UPDATE, the response times were consistently around 0.01-0.02 seconds
How to find out what went wrong?

=========================
Table to look at:
=========================
V$ACTIVE_SESSION_HISTORY

=========================
Investigation
=========================

SELECT event, count(*)

FROM V$ACTIVE_SESSION_HISTORY WHERE sample_time > SYSDATE -1
   AND event IS NOT NULL  
   AND event LIKE 'enq%'
GROUP BY event
ORDER BY count(*) DESC

enq: TX - row lock contention 1590
enq: US - contention 87
enq: KO - fast object checkpoint 62
enq: RO - fast object reuse 18
enq: TM - contention 10
enq: TC - contention 8
enq: JD - contention 7
enq: CF - contention 6
enq: JS - queue lock 5
enq: TQ - DDL contention 1

SELECT distinct session_id, 

       session_serial#, 

       user_id,

       sql_id, 

       blocking_session, 

       blocking_session_serial#

  FROM V$ACTIVE_SESSION_HISTORY 

 WHERE sample_time > SYSDATE -1 

   AND event = 'enq: TX - row lock contention' 

363 23157 44 2g52c7knzrfpr 390 39507

260 1193 44 cyn6n1hvduxmh 420 54621

613 20 44 cyn6n1hvduxmh 420 54621

280 2806 44 cyn6n1hvduxmh 558 18098

631 53132 44 cyn6n1hvduxmh 420 54621

410 7104 44 cyn6n1hvduxmh 420 54621

349 29520 44 cyn6n1hvduxmh 420 54621

422 54674 44 cyn6n1hvduxmh 420 54621

641 70 44 cyn6n1hvduxmh 420 54621

281 29642 44 cyn6n1hvduxmh 420 54621

558 18098 44 61jfdsgugn6n0 590 14439

475 57553 44 cyn6n1hvduxmh 420 54621

558 18098 44 c05wv95dkr9b4 579 12

579 12 44 cyn6n1hvduxmh 420 54621

267 471 44 cyn6n1hvduxmh 420 54621

290 6424 44 cyn6n1hvduxmh 420 54621

265 7 44 cyn6n1hvduxmh 420 54621

280 2806 44 cyn6n1hvduxmh 420 54621

479 7935 44 cyn6n1hvduxmh 420 54621

590 14439 44 cyn6n1hvduxmh 420 54621

540 7513 44 cyn6n1hvduxmh 420 54621

302 22168 44 cyn6n1hvduxmh 420 54621

586 5 44 cyn6n1hvduxmh 420 54621

266 15832 44 cyn6n1hvduxmh 420 54621

292 32714 44 cyn6n1hvduxmh 420 54621

557 10526 44 cyn6n1hvduxmh 420 54621

598 12 44 cyn6n1hvduxmh 420 54621

570 10 44 cyn6n1hvduxmh 420 54621

359 7551 44 cyn6n1hvduxmh 420 54621

Get the SQL which was locked.

SELECT sql_text FROM V$SQLAREA WHERE sql_id = 'cyn6n1hvduxmh'

Get the SQLs which the locking session was running.

In one case, this session was locked by another session.

SELECT distinct session_id, 

       session_serial#, 

       user_id, 

       sql_id, 

       blocking_session, 

       blocking_session_serial#

  FROM V$ACTIVE_SESSION_HISTORY 

 WHERE sample_time > SYSDATE -1 

   AND session_id = 420

   AND session_serial# = 54621

420 54621 44 673cf65krjps5

420 54621 44 673cf65krjps5 655 5

420 54621 44 3v6w37hcvvmvz

420 54621 44 gmz7xvphdw1av

420 54621 44 15qjfzxtcmsgm

420 54621 44 79nkqzps5jtxg

420 54621 44 cw3y32q9jfj46

One of these SQLs was the locking SQL.

SELECT sql_text FROM V$SQLAREA WHERE sql_id = '673cf65krjps5'