Hi
In the CAIM infrastructure the Task Persistence DB can be the major bottlenecks when the system is under a large load.
The following article is designed to help you try to improve the performance of the DB when using Oracle 11gR2.
Background
The main table for the TP DB is the object12_5 table. if we look at the table, we can see that is has a BLOB column.
From: Oracle LOB - Working with BLOB and CLOB Data
LOB stands for Large OBject, a stream of data stored in a database. Maximum capacity of a LOB is (4 gigabytes-1) bytes. In Oracle three kinds of LOB data type exist:
- BLOB datatype stores unstructured binary large objects. BLOB objects can be thought of as bitstreams with no character set semantics.
- The CLOB datatype stores single-byte and multibyte character data. Both fixed-width and variable-width character sets are supported, and both use the database character set.
- The NCLOB datatype stores Unicode data.
In the CAIM TP logic, all the actions you see in the "View Submitted Tasks" are saved in the BLOB. in order to keep the TP persistence, the CAIM will lock the table and COMMIT the data after every stage which might cause unnecessary waits on a large loads.
Over the lifetime of a task, as it goes through the different states, more objects are inserted into this object12_5 table. So if the task has 10 states, there will be 10 records in the table. Each with a BLOB object.
This consumes tablespace. Thus tablespace needs to be auto-incremented by Oracle fairly often which is an expensive operation.
This leads to task persistence inserts and update statements being queued up as Oracle is doing this; and thus leads to slow IDM performance/pending tasks etc.
The HW High Water enqueue – contention wait event is being caused by 2 sql statements:
• update object12_5 set object=:1 where objectid=:2 and objecttype=:3
• insert into object12_5 (objectid,objecttype,object) values (:1 ,:2 ,:3 )
The HW High Water enqueue (enq: HW – contention) occurs when competing processing are inserting/updating the same table and are trying to increase the high water mark of a table simultaneously. We see this type of contention mainly when the table contains a lob/blob. When lobs/blobs are updated/inserted into a table the HWM changes, extents are allocated (or de-allocated if it’s a delete statement). In our case, the table object12_5 contains a blob and there were 2 statements running against that table: an insert and an update. There were many sessions hitting both of those tables running the same statements causing contention
When using Oracle 11gR2 DB you have an option to use the SecureFiles LOB method to try to solve this problems.
What is SecureFiles LOBs?
SecureFiles is a completely new infrastructure inside the Oracle Database for managing unstructured data. It offers the best of both the file system and database worlds. SecureFiles includes many design innovations that incorporate years of research in file systems and databases.
Write performance using SecureFiles is enhanced by a new cache that buffers writes before writing to disk. Advanced features such as compression and deduplication reduce disk I/Os further at the cost of a slight CPU overhead. Intelligent pre-fetching especially during streaming reads offer better performance during reads. SecureFiles uses an advanced space management routine that is optimized for large, contiguous I/Os. In addition, there are several innovations with the wire protocol, locking algorithms etc that make SecureFiles far more scalable than LOBs or file systems. For more information on SecureFiles, read the technical white paper here.
How to convert to SecureFiles LOB
It is recommended that you have a discussion with the customer DBA team before making the change to make sure they are on-board.
the following is an example of creating the table with the secureFiles method:
CREATE TABLE IMTASK.OBJECT12_5 ( OBJECTID NVARCHAR2(50) NOT NULL, OBJECTTYPE NUMBER(16,0) NOT NULL, OBJECT BLOB NULL, CONSTRAINT PK_OBJECT12_5 PRIMARY KEY (OBJECTID,OBJECTTYPE) USING INDEX TABLESPACE IDMDATA PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(BUFFER_POOL DEFAULT) ENABLE VALIDATE ) ORGANIZATION HEAP LOB(OBJECT) STORE AS SECUREFILE /*SYS_LOB0000054277C00003$$*/ ( TABLESPACE IDMDATA STORAGE(INITIAL 104K BUFFER_POOL DEFAULT) ENABLE STORAGE IN ROW NOCACHE LOGGING CHUNK 8192 PCTVERSION 10 ) TABLESPACE IDMDATA LOGGING PCTFREE 10 PCTUSED 0 INITRANS 1 MAXTRANS 255 STORAGE(BUFFER_POOL DEFAULT) NOPARALLEL NOCACHE NOROWDEPENDENCIES /
Note about Oracle RAC
One important remark for using Oracle RAC. we found out that using Oracle RAC as a load balance can contribute to low performance due that fact that all the DB servers are competing on the same storage and the fact the CAIM is using a lot of table locks. the recommendation was to use the RAC as a failover setup.
i hope this helps, please let me know if you have more questions.