query delay [message #431488] |
Tue, 17 November 2009 11:32 |
newsurfgal
Messages: 12 Registered: August 2009
|
Junior Member |
|
|
Hi friends,
I'm running this below query, it is taking few minutes to start. It is returning only 11000 records, once it starts processing it is fast. But, it is taking some time to start. Can you please tell me what could be the reason?
10000 rows in cnum, 11000 rows in ariscode, 100 in dmf table.
select a.itm, b.wse, c.id, a.descr_1, a.itm_type, a.ed_date, b.scode from cnum a, ariscode b, dmf c where a.itm = b.itm and b.wse = c.wse;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 12 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 733 0.15 1.48 856 9245 0 10969
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 735 0.17 1.48 856 9257 0 10969
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 75 (TEST)
Rows Row Source Operation
------- ---------------------------------------------------
10969 HASH JOIN (cr=9245 pr=856 pw=0 time=1127898 us)
10969 MERGE JOIN (cr=4076 pr=387 pw=0 time=868260 us)
4 TABLE ACCESS BY INDEX ROWID dmf (cr=2 pr=0 pw=0 time=67 us)
4 INDEX FULL SCAN dmf_PK (cr=1 pr=0 pw=0 time=36 us)(object id 67101)
10969 SORT JOIN (cr=4074 pr=387 pw=0 time=857253 us)
10969 TABLE ACCESS BY INDEX ROWID ARISCODE (cr=4074 pr=387 pw=0 time=634439 us)
10969 INDEX FULL SCAN ARISCODE__WSE__IDX (cr=40 pr=38 pw=0 time=5363 us)(object id 65665)
10205 TABLE ACCESS BY INDEX ROWID CNUM (cr=5169 pr=469 pw=0 time=142954 us)
10205 INDEX FULL SCAN CNUM_PK (cr=757 pr=25 pw=0 time=24128 us)(object id 65587)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
10969 HASH JOIN
10969 MERGE JOIN
4 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF 'dmf'
(TABLE)
4 INDEX MODE: ANALYZED (FULL SCAN) OF 'dmf_PK' (INDEX
(UNIQUE))
10969 SORT (JOIN)
10969 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF 'ARISCODE'
(TABLE)
10969 INDEX MODE: ANALYZED (FULL SCAN) OF
'ARISCODE__WSE__IDX' (INDEX)
10205 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF 'CNUM' (TABLE)
10205 INDEX MODE: ANALYZED (FULL SCAN) OF 'CNUM_PK' (INDEX
(UNIQUE))
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 733 0.00 0.00
db file sequential read 856 0.02 1.29
SQL*Net message from client 733 0.12 12.18
Could it be the server delay causing the query to start processing slow?
Thanks
|
|
|
|
Re: query delay [message #431608 is a reply to message #431488] |
Wed, 18 November 2009 08:19 |
newsurfgal
Messages: 12 Registered: August 2009
|
Junior Member |
|
|
Yes, but its taking a while to start processing the query.. For example, when I run the query in sqlplus, I see nothing for about 1min and it starts returning rows in few sec.
Not only this query, but there are few other queries that is taking long to start processing.. not sure what is causing this delay..I'll appreciate any help..
Thank you
|
|
|
Re: query delay [message #431612 is a reply to message #431488] |
Wed, 18 November 2009 08:57 |
cookiemonster
Messages: 13961 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Run a full trace in the session becuase nothing you've posted so far even indicates the delay is happening never mind why.
|
|
|
Re: query delay [message #431651 is a reply to message #431488] |
Wed, 18 November 2009 22:20 |
|
Kevin Meade
Messages: 2103 Registered: December 1999 Location: Connecticut USA
|
Senior Member |
|
|
One thing comes to mind is parse lock. Someone is maybe changing one of the objects you are working on so you have to wait for the parse lock to clear before you can start parsing your code.
Kevin
|
|
|