| Blocking Session - blocked SQL -> SELECT SYSDATE FROM SYS.DUAL [message #417739] |
Tue, 11 August 2009 05:59  |
|
|
Oracle 10.0.0.4g
When database execute some big and long queries/operations my system is slow and some users wait, can't work (they work with some Oracle forms applications ) because I often have blocking session.
I found up that this blocking sessions block only this query of another user:
SELECT SYSDATE FROM SYS.DUAL
Or:
10-АВГ-2009 08:51:10 User X1 ( SID= 222 ) with the statement: SELECT ... is blocking the SQL statement on Y1 ( SID=333 ) blocked SQL -> SELECT SYSDATE FROM SYS.DUAL
11-АВГ-2009 10:07:24 User Y2 ( SID= 323) with the statement: SELECT SYSDATE FROM SYS.DUAL is blocking the SQL statement on X2 ( SID=400 ) blocked SQL -> SELECT SYSDATE FROM SYS.DUAL
11-АВГ-2009 10:00:36 User Y1 ( SID= 555 ) with the statement: SELECT SYSDATE FROM SYS.DUAL is blocking the SQL statement on X1 ( SID=888 ) blocked SQL -> DELETE ...
When I kill one of the blocking session another session take his place and do the same.
When long queries finished everything is OK.
Please Help Me!!!
|
|
|
|
|
|
| Re: Blocking Session - blocked SQL -> SELECT SYSDATE FROM SYS.DUAL [message #417745 is a reply to message #417742] |
Tue, 11 August 2009 06:17  |
|
|
SELECT TO_CHAR(sysdate, 'DD-MON-YYYY HH24:MI:SS')
|| ' User '
||s1.username
|| '@'
|| s1.machine
|| ' ( SID= '
|| s1.sid
|| ' ) with the statement: '
|| sqlt2.sql_text
||' is blocking the SQL statement on '
|| s2.username
|| '@'
|| s2.machine
|| ' ( SID='
|| s2.sid
|| ' ) blocked SQL -> '
||sqlt1.sql_text AS blocking_status
FROM v$lock l1, v$session s1, v$lock l2 ,
v$session s2,v$sql sqlt1, v$sql sqlt2
WHERE s1.sid =l1.sid
AND s2.sid =l2.sid AND sqlt1.sql_id= s2.sql_id AND sqlt2.sql_id= s1.prev_sql_id AND l1.BLOCK =1
AND l2.request > 0 AND l1.id1 = l2.id1 AND l2.id2 = l2.id2
|
|
|
|
|
|
| Re: Blocking Session - blocked SQL -> SELECT SYSDATE FROM SYS.DUAL [message #417766 is a reply to message #417739] |
Tue, 11 August 2009 08:06 |
|
|
Now I got this:
11-АВГ-2009 13:36:49 User S ( SID= 435 ) with the statement: UPDATE ... is blocking the SQL statement on B ( SID=376 ) blocked SQL -> SELECT SYSDATE FROM SYS.DUAL
11-АВГ-2009 13:36:57 User S ( SID= 435 ) with the statement: SELECT ... is blocking the SQL statement on B ( SID=376 ) blocked SQL -> SELECT SYSDATE FROM SYS.DUAL
With:
SELECT wait_class, event, sid, state, wait_time, seconds_in_wait
FROM v$session_wait
where sid in ('376')
ORDER BY wait_class, event, sid;
Output:
---------------
SID - 376
WAIT_CLASS - Application
EVENT - enq: TM - contention
STATE - WAITING
WAIT_TIME - 0
SECONDS_IN_WAIT - 114
|
|
|
|
| Re: Blocking Session - blocked SQL -> SELECT SYSDATE FROM SYS.DUAL [message #417771 is a reply to message #417739] |
Tue, 11 August 2009 08:35 |
cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
First off you can't get the sql that's causing the block. It might not be the current or previous sql statement by that session. The session might have done an update (for example) that locked a row then issued a dozen sql statements after it.
So give up trying to get the blockers sql.
The blocked sessions sql on the other hand can be got to see what objects it's trying to lock.
There's a problem with the joins in your script as well:
One of those should be l1.id2.
And while it doesn't affect the outcome of your sql if you're going to use 1 for one session and 2 for the other, it'd be a lot less confusing if you stuck to that convention throughout.
|
|
|
|
| Re: Blocking Session - blocked SQL -> SELECT SYSDATE FROM SYS.DUAL [message #417777 is a reply to message #417739] |
Tue, 11 August 2009 09:22 |
 |
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
Senior Member |
|
|
If query below returns rows, then you have additional clues.
SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid sess,
id1, id2, lmode, request, type
FROM V$LOCK
WHERE (id1, id2, type) IN
(SELECT id1, id2, type FROM V$LOCK WHERE request>0)
ORDER BY id1, request
/
If no rows returned, you are no worse for the effort.
|
|
|
|
| Re: Blocking Session - blocked SQL -> SELECT SYSDATE FROM SYS.DUAL [message #418340 is a reply to message #417739] |
Fri, 14 August 2009 04:25 |
|
|
I create ASH report:
Top User Events
Avg Active
Event Event Class % Activity Sessions
----------------------------------- --------------- ---------- ----------
enq: TM - contention Application 55.87 0.96
db file sequential read User I/O 18.87 0.32
CPU + Wait for CPU CPU 16.33 0.28
db file scattered read User I/O 3.02 0.05
-------------------------------------------------------------
Top Event P1/P2/P3 Values
Event % Event P1 Value, P2 Value, P3 Value % Activity
------------------------------ ------- ----------------------------- ----------
Parameter 1 Parameter 2 Parameter 3
-------------------------- -------------------------- --------------------------
enq: TM - contention 55.87 "xxxxxxxxxxxxxxxxxxxx" 38.35
name|mode object # table/partition
"1111111111","xxxxxxx","0" 17.44
db file sequential read 19.21 "xxxxxxxxxxxxxxx'' 0.00
file# block# blocks
db file scattered read 3.03 "xxxxxxxxxxxxxxxxxxxxxx'' 0.01
file# block# blocks
Top SQL Statements ..............
SQL ID Planhash % Activity Event % Event
------------- ----------- ---------- ------------------------------ ----------
fnxxxxxxxxx N/A 25.09 enq: TM - contention 23.47
** SQL Text Not Available **
N/A 25.09 db file sequential read 1.19
** *SQL Text Not Available* **
byxxxxxxxxxxxxx 1111111 10.11 enq: TM - contention 7.43
SELECT SYSDATE FROM SYS.DUAL
db file sequential read 2.10
fnxxxxxxxxx 11111111111 2.57 enq: TM - contention 2.16
** SQL Text Not Available **
Top DB Objects
Object ID % Activity Event % Event
--------------- ---------- ------------------------------ ----------
Object Name (Type) Tablespace
----------------------------------------------------- -------------------------
11111 10.33 enq: TM - contention 10.30
XXXXXXXXXXXXXXXXXXXXXXXX (INDEX) CC
99999 10.18 enq: TM - contention 10.16
XXXXXXXXXXXXXXXXXXXXXXXXX (INDEX) IND
933333 6.67 enq: TM - contention 6.55
FFFFFFFFFFFFFFFF (TABLE) T3
114545 3.88 enq: TM - contention 3.85
RRRRRRRRRRRRRRRRRRRRRR (INDEX) JJJ
1136664 2.96 enq: TM - contention 2.93
FFFFFFFFFFFFFFFFFFFFFFFFF (INDEX) G
How to found sql text that is not available ** SQL Text Not Available **?
What to do whit this Top DB Objects that have enq: TM - contention event?
And how to solve this problem?
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
