Split CLOB column to improve performance
742198Aug 11 2011 — edited Aug 11 2011Hi All,
We have a transactional table which has 3 columns and one among those is CLOB which holds XML data.Inserts are coming at 35K/hr to this table and data will be deleted as soon as job is completed. So anytime the total records in this table will be less than 1000.
The XML data contains binary info of images and the size of each XML file ranges any where between 200KB to 600KB and the elapsed time for each insert varies from 1 to 2 secs depending upon the concurrency. As we need to achieve 125K/hour soon we were planning to do few modifications on table level.
1. Increase the CHUNK size from 8KB to 32KB.
2. Disabling logging for table,clob and index.
3. Disable flashback for database.
4. Move the table to a non default blocksize of 32KB. Default is 8KB
5. Increase the SDU value.
6. Split the XML data and store it on multiple CLOB columns.
We don't do any update to this table. Its only INSERT,SELECT and DELETE operations.
The major wait events I'm seeing during the insert is
1. direct path read
2. direct path write
3. flashback logfile sync
4. SQL*Net more data from client
5. Buffer busy wait
My doubt over here is ,
1. If I allocate a 2G memory for the non default block size and change the clob to CACHE, will my other objects in buffer_cache gets affected or gets aged out fast?
2. And moving this table to a SECUREFILE from BASICFILE will help?
3. Splitting the XML data to insert into different columns in the same table will give a performance boost?
Oracle EE 11.2.0.1,ASM
Thanks,
Arun