81542 Slow post performance on Oracle 11.2.0.2.2 (4324687)

Post queue backlogged and extremely slow, posting rate about 24 operations per minute on Oracle 11.2.0.2.2

Issue is related to the Oracle 11g R2 changes not invalidating the parent curosrs when the child cursors reach 1024 for soft parses and filling up your shared pool causing a performance degradaction. High version count and bind mismatch Oracle bugs.

 1. Contact Oracle Support and and request upgrade from 11.2.0.2.2 want them to upgrade to 11.2.0.2.3 PSU + MLR 12938190 with fix for bug 12431716 9530750.

 2. Check with Oracle for Bug fix 10157392 High version counts for SQL with binds (BIND_MISMATCH)

 3. Check with Oracle for Bug 8981059 fix High Version Count (due to USER_BIND_PEEK_MISMATCH)

 4. Enable the following Oracle hidden parameters with the guidance of Oracle Support:

_optim_peek_user_binds

_optimizer_adaptive_cursor_sharing

WORKAROUND:

1. Flush the shared pool frequently

ALTER SYSTEM FLUSH SHARED POOL

2. Disable post sql caching temporarilly:

sp_ctrl> list param all post

Ensure that SP_OPO_SQL_CACHE_DISABLE 1

If SP_OPO_SQL_CACHE_DISABLE 0, disable sql caching by:

Sp_ctrl> set param SP_OPO_SQL_CACHE_DISABLE 1

Sp_ctrl> stop post

Sp_ctrl> start post

3. Isolate the problem table in its own queue

Here the steps to separate he CMS.ECH table into its own queue. When isolating the table into its own queue to try to improve performance, all related tabled need to be grouped into this same queue to avoid referencial integrity issues:

On Target

3.1. Stop post.

On source

3.2. Copy the active config to new config name

3. Edit the new config name and create a new named post queue for only the problem table and any related tables if it has child parent relationships to other tables.

3.4. Activate the configuration with named post queue.

On target

3.5. Notice that you see existing post queue and the new post queue are stopped.

3.6. Start existing post queue first ( the old queue)

sp_ctrl> start post queue {queue name}

sp_ctrl> show post detail queue {queue name}

3.7. Check and verify if you see the new actid, once you see the new actid or once the existing post queue drains, then you can start the new post queue

sp_ctrl> start post queue <new post queue name>

Additional Information:

 AWR reports shows the top 5 wait being related to latch shared pool, cursor mutex x/s which oracle will fix in 12g. An insert has about 5 or 6 sessions on this problem table concurrently. They are waiting on each other on concurrency wait and cursor mutex etc. This insert has a high cursor version count. Increasing the init_trans won't help as if there are no ITL wait see from oracle

Example:

SQL ordered by Version Count

• Only Statements with Version Count greater than 20 are displayed