Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Tuning LOB: Where is all my time going?

Tuning LOB: Where is all my time going?

From: Matt Butler <mathewbutler_at_yahoo.com>
Date: 12 Oct 2004 00:08:43 -0700
Message-ID: <19f48a45.0410112308.4af4065f@posting.google.com>


Oracle Standard 9.2.0.4 on WIN2K.

I'm in the process of trying implement an archiving strategy for an application. As part of this I have come across the following problem:

Some of the application tables contain LOB's. The tables containing LOBs are taking some time to slice. Below is a TKPROF report for one CTAS that ran for approx. 5 min. It processed 100 out of 35000 blocks (taken from v$session_longops). In this case the table tst.bizdoccontent contained a few rows (2) and bizdoccontent contained many (300,000 or so). Both tables are full scanned.

My problem is that at this work rate, this query will take over a day to complete. Looking at the discrepancy between cpu and elapsed time I am obviously waiting for something, but that something is not on the "waited for events list". At this point I am unsure how to proceed.

Can anyone shed any light on what might be happening here? Below is the output from user_lobs which descibes the LOB definition. Please let me know if additional diagnostic info is required.

Thanks in advance,

Mat.

TABLE_NAME	COLUMN_NAME	SEGMENT_NAME	INDEX_NAME	CHUNK	PCTVERSION	RETENTION	FREEPOOLS	CACHE	LOGGING	IN_ROW
BIZDOCCONTENT	CONTENTSYS_LOB0000026859C00005$$	SYS_IL0000026859C00005$$
	8192	10	10800		NO	YES	YES

Here is the TKPROF.


create table to_save_bizdoccontent
tablespace ebxml_med_data as
select * from bizdoccontent orig_bdc
 where not exists ( select null from tst.bizdoccontent subset_bdc

                    where orig_bdc.docid = subset_bdc.docid 
                      and orig_bdc.partname = subset_bdc.partname) 

call     count       cpu    elapsed       disk      query    current  
     rows

------- ------ -------- ---------- ---------- ---------- ----------
Parse        1      0.00       0.00          0          0          0  
        0
Execute      1      0.09     283.13       5165      32128      38154  
        0
Fetch        0      0.00       0.00          0          0          0  
        0

------- ------ -------- ---------- ---------- ---------- ----------
total        2      0.09     283.13       5165      32128      38154  
        0

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 42

Elapsed times include waiting on following events:

  Event waited on                             Times   Max. Wait  Total
Waited Received on Tue Oct 12 2004 - 02:08:43 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US