Home » RDBMS Server » Performance Tuning » Identify blocking sessions (11.1.0.7, Standard Edition)
Identify blocking sessions [message #495599] Mon, 21 February 2011 05:30 Go to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
My statspack reports are sometimes showing figures such as this from the Foreground Wait Events section:
Avg          %Total
                                          %Tim Total Wait   wait    Waits   Call
Event                               Waits  out   Time (s)   (ms)     /txn   Time
---------------------------- ------------ ---- ---------- ------ -------- ------
PL/SQL lock timer                   1,430  100      6,982   4883      0.0   18.0
enq: TM - contention                    3    0      6,438 ######      0.0   16.6
db file sequential read           310,385    0      1,765      6      3.5    4.6
TCP Socket (KGAS)                  12,593   13        180     14      0.1     .5
db file scattered read             23,673    0         87      4      0.3     .2
log file sync                      19,409    0         82      4      0.2     .2
ksfd: async disk IO                 1,680    0         29     17      0.0     .1
log buffer space                       40    0          8    199      0.0     .0
enq: UL - contention                1,667    0          4      3      0.0     .0
SQL*Net more data to client       146,013    0          4      0      1.6     .0
db file parallel read                 159    0          2     15      0.0     .0
control file sequential read       36,267    0          2      0      0.4     .0
SQL*Net break/reset to clien        2,486    0          1      1      0.0     .0
read by other session                 341    0          1      4      0.0     .0
latch: shared pool                  1,277    0          1      1      0.0     .0
enq: TX - row lock contentio           23    0          0     14      0.0     .0



and this from the Enqueue activity section:

Enqueue Type (Request Reason)
------------------------------------------------------------------------------
    Requests    Succ Gets Failed Gets       Waits Wt Time (s)  Av Wt Time(ms)
------------ ------------ ----------- ----------- ------------ --------------
TM-DML
     215,988      215,988           0           3        6,592   2,197,439.67
UL-User-defined
       5,120        5,120           0       1,667            4           2.63
CF-Controlfile Transaction
       8,944        8,935           8           7            0          49.86
TX-Transaction (row lock contention)
          23           23           0          23            0          14.57



The TM lock that occurred 3 times appears to be disastrous. Questions:

Historically, this could be caused by missing indexes on foreign key columns. I thought that this problem was fixed with 10g, is it still an issue?

How can I be alerted when this event occurs, so that I can do some real-time investigation into the sessions and the SQL that hit it? I suppose I could schedule a job to query v$lock/dba_waiters/dba_blockers every few minutes, but is there a better way? Does anyone have any standard edition scripts for this?

Any other ideas for what might be the cause and how I can investigate?

Thank you for any advice.
Re: Identify blocking sessions [message #495603 is a reply to message #495599] Mon, 21 February 2011 05:41 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I thought that this problem was fixed with 10g, is it still an issue?

Afaik, only in 11g.

Quote:
How can I be alerted when this event occurs, so that I can do some real-time investigation into the sessions

Spawn a job every minute that check v$lock.ctime for waiting sessions.

Quote:
I suppose I could schedule a job to query v$lock/dba_waiters/dba_blockers every few minutes, but is there a better way?

Well, I see we have the same idea. Wink
I have no other idea for the moment.

Regards
Michel

Re: Identify blocking sessions [message #495741 is a reply to message #495603] Tue, 22 February 2011 02:48 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Thank you for replying. Enterprise Edition would be nice.

And just for completeness, the foreign-key issue is that DML on the child table locks the parent table, stupid of me to forget that. I hope no-one else noticed. I've found a script that will detect all un-indexed foreign keys, I'll upload it later.
Re: Identify blocking sessions [message #495744 is a reply to message #495741] Tue, 22 February 2011 03:09 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
This one?

tkyte@TKYTE816> select table_name, constraint_name,
  2       cname1 || nvl2(cname2,','||cname2,null) ||
  3       nvl2(cname3,','||cname3,null) || nvl2(cname4,','||cname4,null) ||
  4       nvl2(cname5,','||cname5,null) || nvl2(cname6,','||cname6,null) ||
  5       nvl2(cname7,','||cname7,null) || nvl2(cname8,','||cname8,null)
  6              columns
  7    from ( select b.table_name,
  8                  b.constraint_name,
  9                  max(decode( position, 1, column_name, null )) cname1,
 10                  max(decode( position, 2, column_name, null )) cname2,
 11                  max(decode( position, 3, column_name, null )) cname3,
 12                  max(decode( position, 4, column_name, null )) cname4,
 13                  max(decode( position, 5, column_name, null )) cname5,
 14                  max(decode( position, 6, column_name, null )) cname6,
 15                  max(decode( position, 7, column_name, null )) cname7,
 16                  max(decode( position, 8, column_name, null )) cname8,
 17                  count(*) col_cnt
 18             from (select substr(table_name,1,30) table_name,
 19                          substr(constraint_name,1,30) constraint_name,
 20                          substr(column_name,1,30) column_name,
 21                          position
 22                     from user_cons_columns ) a,
 23                  user_constraints b
 24            where a.constraint_name = b.constraint_name
 25              and b.constraint_type = 'R'
 26            group by b.table_name, b.constraint_name
 27         ) cons
 28   where col_cnt > ALL
 29           ( select count(*)
 30               from user_ind_columns i
 31              where i.table_name = cons.table_name
 32                and i.column_name in (cname1, cname2, cname3, cname4,
 33                                      cname5, cname6, cname7, cname8 )
 34                and i.column_position <= cons.col_cnt
 35              group by i.index_name
 36           )
 37  /


There's a better one in his book, but I'm not sure about posting that one, the above is on asktom so I figure that's alright.
Re: Identify blocking sessions [message #495767 is a reply to message #495744] Tue, 22 February 2011 03:49 Go to previous message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Actually, this one
http://www.jonadams.net/fk_constraints_and_indexing.htm
but I'll use TKs code instead, perhaps some of his knowledge will enter my head by a process of intellectual osmosis.
Thanks.



Previous Topic: Slow Sql query
Next Topic: Cursor_sharing setting in OLTP environment
Goto Forum:
  


Current Time: Thu Mar 28 09:36:55 CDT 2024