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 |
John Watson
Messages: 8951 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 #495741 is a reply to message #495603] |
Tue, 22 February 2011 02:48 |
John Watson
Messages: 8951 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 |
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.
|
|
|
|
Goto Forum:
Current Time: Sat Sep 28 15:41:27 CDT 2024
|