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

• Waited ----------

  ---

  control file sequential read 4 0.00 0.00 db file scattered read 1 0.01 0.01 direct path read (lob) 5161 0.02 6.58 direct path write (lob) 4390 0.09 1.37 direct path write 1 0.00 0.00 log file sync 1 0.75 0.75 SQL*Net break/reset to client 1 0.00 0.00 SQL*Net message to client 1 0.00 0.00 SQL*Net message from client 1 0.05 0.05

  ---

Received on Tue Oct 12 2004 - 02:08:43 CDT