Poor performance while selecting or deleting from DR$WAITING

From: Naveen <new_dba_on_the_block_at_yahoo.com>
Date: Tue, 1 Mar 2005 05:52:46 -0800 (PST)
Message-ID: <20050301135246.66199.qmail@web53301.mail.yahoo.com>

I pasted incomplete TKRPOFed output in my previous email. Here is the complete output:

DELETE FROM DR$WAITING
WHERE
 WTG_CID = :b1 AND WTG_PID = :b2 AND EXISTS (SELECT 1 FROM DR$PENDING
  WHERE DR$PENDING.PND_CID = DR$WAITING.WTG_CID AND DR$PENDING.PND_PID =
  DR$WAITING.WTG_PID AND DR$PENDING.PND_ROWID = DR$WAITING.WTG_ROWID )

call     count       cpu    elapsed       disk     
query    current        rows

------- ------ -------- ---------- ----------

• ---------- ---------- Parse 0 0.00 0.00 0 0 0 0 Execute 4 14.85 14.54 47720 80454 0 0 Fetch 0 0.00 0.00 0 0 0 0
  ------- ------ -------- ---------- ----------
  
• ---------- ---------- total 4 14.85 14.54 47720 80454 0 0

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 29 (recursive depth: 2)

Elapsed times include waiting on following events:

  Event waited on                             Times  

Max. Wait Total Waited

• Waited
  ---------- ------------
  db file sequential read 2013 0.00 0.08 db file scattered read 7838 0.00 1.06

  ---

.
.
SELECT WTG_ROWID,ROWID
FROM
 DR$WAITING WHERE WTG_CID = :b1 AND WTG_PID = :b2

call     count       cpu    elapsed       disk     
query    current        rows

------- ------ -------- ---------- ----------

• ---------- ---------- Parse 0 0.00 0.00 0 0 0 0 Execute 4 0.00 0.00 0 0 0 0 Fetch 4 29.71 29.23 85752 118639 0 0
  ------- ------ -------- ---------- ----------
  
• ---------- ---------- total 8 29.71 29.24 85752 118639 0 0

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 29 (CTXSYS) (recursive depth: 2) error during execute of EXPLAIN PLAN statement ORA-00942: table or view does not exist

parse error offset: 101

Elapsed times include waiting on following events:

  Event waited on                             Times  

Max. Wait Total Waited

• Waited
  ---------- ------------
  db file sequential read 4837 0.00 0.19 db file scattered read 17903 0.00 2.05

  ---

Do you Yahoo!?
Yahoo! Mail - now with 250MB free storage. Learn more. http://info.mail.yahoo.com/mail_250
--

http://www.freelists.org/webpage/oracle-l Received on Tue Mar 01 2005 - 08:56:13 CST