We plan to turn on service auditing (on error only and persist pipeline) and use WmMonitor to resubmit service for error recovery

We would like to estimate the size of the WMSERVICE Table.

I have developed a flow service to invoke the service (with difference size
input pipeline) for 700 * 90 (700 errors * 90 days storage requirement)

Is there any way to find out the size of the Oracle Table WMSERVICE (which contains BLOB columns for Pipeline) using SQLPLUS only ?

Please advise

Here is a query from sqlplus,
SQL> select segment_name, sum(bytes) from dba_extents where owner=‘WEBM11’ and s
egment_name=‘WMERROR’ group by segment_name order by sum(bytes) desc;

You may or may not have access to do this depending on how your DBA has setup your access. You could just ask your dba what the table size is after you run your test as well.

You may always use user_segmetns instead of dba_segments, and user_Segments is a VIEW much more frequently availble to users.

As for the size of the WMSERVICE table, It highly depends on the size of the pipeline. Save the sample input pipeline to the XML-file, check it size, double it, and it will be sample size of the BLOB.

Then consider that:
having blobs larger than 4000 bytes forces out of line storage, so the space will be alocated in 4k chunks.

If the bobs are smaller than 4000 bytes, they will be stored inline, and they will take exactly the space, the blob size is.

So, considering, that 90% will fit in 4kb and 10% will fit in 8kb, it looks like you need about 3GB + indexes.

To efficentli store 90 days of history, you may consider table partitioning, this is what we have done, but we have ~1.2M entries a month, and the table takes 5-8GB per month.

Cheers

Hi Wiktorn,

I am not sure how you calculated that 3GB of space would be needed.

This is how I calculated
70090=63000 errors
90% of them takes 4KB = 630000.94kb = 226800KB = 221MB
10% takes 8KB = 630000.1*8kb = 50400KB = 49MB

In total, around 270MB of space would be needed.

Cheers,
Anil.