Funny to read this because I ran into the exact same thing this
weekend. You would think WebMethods would have built in a robust
archiving / removal feature since the whole application basically
boils down to a big queue on the database side of things. I am going
to try to get something working this week, I'll let you know if I find
anything.
mathewbutler_at_yahoo.com (Matt Butler) wrote in message news:<19f48a45.0410112308.4af4065f_at_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 - 12:10:23 CDT
