WAIT #0: nam='enq: DX - contention' ela= 2930053 name|mode=1146617862 transaction entry #=24 0=0 obj#=43432 tim=1201558492770
361
WAIT #0: nam='enq: DX - contention' ela= 2929801 name|mode=1146617862 transaction entry #=24 0=0 obj#=43432 tim=1201558495700
983
WAIT #0: nam='inactive transaction branch' ela= 977434 branch#=0 waited=0 p3=0 obj#=43432 tim=1201558496678494
WAIT #0: nam='enq: DX - contention' ela= 2930533 name|mode=1146617862 transaction entry #=24 0=0 obj#=43432 tim=1201558499609
116
In trace file found systemstate dump (Wait Event: "enq: DX - contention", "SQL*Net message from client" ):
last wait for 'enq: DX - contention' blocking sess=0x(nil) seq=5412 wait_time=1157384 seconds since wait started=3
name|mode=44580006, transaction entry #=18, 0=0
Dumping Session Wait History
for 'enq: DX - contention' count=1 wait_time=1157384
name|mode=44580006, transaction entry #=18, 0=0
for 'SQL*Net message from client' count=1 wait_time=121
driver id=54435000, #bytes=1, =0
for 'SQL*Net message to client' count=1 wait_time=3
driver id=54435000, #bytes=1, =0
for 'enq: DX - contention' count=1 wait_time=179663
name|mode=44580006, transaction entry #=18, 0=0
for 'inactive transaction branch' count=1 wait_time=977509
branch#=0, waited=0, =0
when find out on metalink (Doc ID: 730423.1), that is a bug.
-- Problem Statement:
Two databases interacting via a private DBLINK.The query at the LOCAL Site includes references for remote objects (via a dblink) as well as a LOCAL FUNCTION (that does not exist at the remote Site)When executing the query it hangs and systemstate dumps (at the remote site)
This case can make blocking lock on remote database. Because that session (dblink hangs) still connected...
Bug:
Oracle Server - Enterprise Edition - Version: 9.2.0.1 to 10.2.0.4
This problem can occur on any platform.
Bug 6236841
Bug 6062854
Bug 3998386
Posts
9 comments:
So, if I have this in a statspack report:
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
db file sequential read 3,690,752 18,608 65.56
inactive transaction branch 6,222 6,243 22.00
enqueue 8,747 1,643 5.79
Avg
Total Wait wait Waits
Event Waits Timeouts Time (s) (ms) /txn
---------------------------- ------------ ---------- ---------- ------ --------
db file sequential read 3,690,752 0 18,608 5 16.8
inactive transaction branch 6,222 6,190 6,243 1003 0.0
enqueue 8,747 15 1,643 188 0.0
Avg Wt Wait
Eq Requests Succ Gets Failed Gets Waits Time (ms) Time (s)
-- ------------ ------------ ----------- ----------- ------------- ------------
TX 239,378 239,357 21 8,627 195.03 1,683
The explanation can be an incorrect use of a dblink?
Thanks a lot.
i don't think so... you should use Execution Plan to check SQL... (Check SQL statements and then Tune SQL Statements.)
read metalink:34559.1
Or
http://surachartopun.com/2008/04/db-file-sequential-read.html
Example: use Execution Plan
SQL> set autot trace explain
SQL> select * from xxxx .........
Or You can use "oradebug" to trace event 10046
to trace some session (you think it have problem).. and then use tkprof...
http://surachartopun.com/2009/02/relax-time-and-work-again.html
That was the first thing I thought, but the "inactive transaction branch" surprised me.
I'm going to study your suggestions.
Thanks,
Angel.
Hello again.
May I send you the whole statspack report?
Bye,
Angel.
statspack report?
Sure... surachart @ gmail dot com
Or You can post on forums.oracle.com
That's a good way to discuss.
Hi Surachart,
did you resolve the issue? If so, did you apply the patch or changed the query? I was trying to find a patch but do not see those numbers on MOS site.
Thanks,
Andrey
what is your Oracle Version?
solved... you may change logic in pl/sql or sql statement.
it is 10.2.0.4
and looks like logic should be changed.
Thank you
yes ..
change logic...
make sure your sql filter on remote, not transfer many data to source database and then filter.
I mean use hint "DRIVING_SITE"
Post a Comment