Home » RDBMS Server » Performance Tuning » query delay (10.2.0.3, Windows 2003)
query delay [message #431488] Tue, 17 November 2009 11:32 Go to next message
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 #431514 is a reply to message #431488] Tue, 17 November 2009 20:24 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
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; 


>Could it be the server delay causing the query to start processing slow?

DELAY? query finished in about 1.5 seconds
SQL is well optimized

Re: query delay [message #431608 is a reply to message #431488] Wed, 18 November 2009 08:19 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous message
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
Previous Topic: ORA-00060 Deadlock issue with trigger
Next Topic: how to enable trace for other sessions?
Goto Forum:
  


Current Time: Wed Dec 11 23:40:25 CST 2024