Home » RDBMS Server » Performance Tuning » Blocking Session - blocked SQL -> SELECT SYSDATE FROM SYS.DUAL (Oracle 10.0.0.4g, Windows 2003)
Blocking Session - blocked SQL -> SELECT SYSDATE FROM SYS.DUAL [message #417739] Tue, 11 August 2009 05:59 Go to next message
kkxenon
Messages: 9
Registered: August 2009
Location: dd
Junior Member

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 #417742 is a reply to message #417739] Tue, 11 August 2009 06:07 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
Queries on dual never block anything.
So what's the script you're using to get that output becuase I suspect it's wrong.
Re: Blocking Session - blocked SQL -> SELECT SYSDATE FROM SYS.DUAL [message #417745 is a reply to message #417742] Tue, 11 August 2009 06:17 Go to previous messageGo to next message
kkxenon
Messages: 9
Registered: August 2009
Location: dd
Junior Member

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 #417750 is a reply to message #417745] Tue, 11 August 2009 06:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The current SQL may not be the blocking one.

Regards
Michel
Re: Blocking Session - blocked SQL -> SELECT SYSDATE FROM SYS.DUAL [message #417766 is a reply to message #417739] Tue, 11 August 2009 08:06 Go to previous messageGo to next message
kkxenon
Messages: 9
Registered: August 2009
Location: dd
Junior Member

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 Go to previous messageGo to next message
cookiemonster
Messages: 12422
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:
l2.id2 = l2.id2 

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 Go to previous messageGo to next message
BlackSwan
Messages: 25046
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 Go to previous messageGo to next message
kkxenon
Messages: 9
Registered: August 2009
Location: dd
Junior Member

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?


Re: Blocking Session - blocked SQL -> SELECT SYSDATE FROM SYS.DUAL [message #418391 is a reply to message #417739] Fri, 14 August 2009 09:14 Go to previous message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>What to do whit this Top DB Objects that have enq: TM - contention event?
>And how to solve this problem?
Do these indexes have a KEY which is also a SEQUENCE & new rows are frequently INSERTed into table?

[Updated on: Fri, 14 August 2009 09:17]

Report message to a moderator

Previous Topic: Impact of table partitioning on DML/Index creation Time (merged)
Next Topic: Virtual Memory Paging
Goto Forum:
  


Current Time: Fri Dec 09 08:06:03 CST 2016

Total time taken to generate the page: 0.05434 seconds