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:45:01 -0800 (PST)
Message-ID: <20050301134501.63949.qmail@web53301.mail.yahoo.com>

Hi All,

Firstly, I have changed my Display name from New DBA to my original name. The reason I kept it as New DBA was to highlight the fact that I was just a novice and be treated likewise :-)

My apologies if it offended anyone.

Back to the question. After tracing a slow running transaction, I noticed that the 2 queries which took close to 95% of the response time were on DR$WAITING. One was a SELECT and the other was a DELETE.

I don't know a heck about this table except that it is in CTXSYS schema and probably has got something to do with Oracle Text or Intermedia option.

Please see the relevant extract from the trace file (formatted using tkprof). Please bear with me if the output doesn't look properly formated in this email.

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

Here are a few details about the table:

Last Analyzed: 28-FEB-05
Freelists: 1
Blocks: 29579

The table was analyzed using
FND_STATS.GATHER_SCHEMA_STATS('CTXSYS') procedure.

There is an index on the table for the following columns: (WTG_CID, WTG_PID, WTG_ROWID)

Any help or pointers in understanding and optimizing will be appreciated.

Regards
Naveen

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

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Mar 01 2005 - 08:48:10 CST