Re: Tuning LOB: Where is all my time going?

For what it is worth, Webmethods do provide an archiving service that may be implemented - see WMTN-programmersref.pdf in the supplied doco and the section market "archiving Services". I was advised that this archiving service has some shortcomings.

Webmethods provides a means to transfer data to a set of archive tables. However, there is no support for querying this archived data using the standard Trading Network Console (TNC) tools. Additionally, the standard process also loses the document id during the transfer to these tables.

I've taken a different tack as ad-hoc investigations are easily supported using the TNC tool, so I wanted to tranfer data to a new schema and keep the original table names.

I'd be interested to see what you come up with (and prepared to share the results of my efforts, also).

Cheers.

Mat.

nytimesjdarrah_at_hotmail.com (John) wrote in message news:<289173ea.0410120910.2962c7d3_at_posting.google.com>...
> 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 Thu Oct 14 2004 - 00:19:41 CDT