Tuesday, October 18, 2011

Adaptive Cursor Sharing

Oracle 11g has introduced the new mode of cursor sharing to handle bind peeking.
Bind-peeking is the occurrence of shared cursors leading to non-optimal explain plans.
For eg.

A SQL "select name from emp where dept=:B". This SQL is executed from a package which is called multiple times for different values.
On the first execution, the cursor are cached in the shared pool. The first execution is


'select name from emp where dept=5'


and returns 3 rows. This execution plan involves a index which is optimal give that only 3 out of 253 rows are returned.

On the second execution, the cached cursor gets reused.


'select name from emp where dept=10'


But returns 250 rows out of 253 rows in the table. But sadly the same execution plan gets called because the cursor is shared.

In 11g, Oracle uses an adaptive cursor sharing where Oracle creates child cursors for the matching cursors in the shared pool. This then checks for any optimizations that can be done to plan apart from the parent cursor.
Hence when upgrading its necessary to have some extra memory allocations for the shared pool. In Oracle 11g, cursor_sharing should only be used in two modes EXACT and FORCE.

EXACT mode, this is the default in Oracle 11g. Oracle creates child cursors for SQLs which differ in bind variables. This is desirable approach. The application is making best use of bind variables and Oracle is making sure there is no bind peeking.

In FORCE mode, Oracle forces reuse of the cursors. This is best for legacy applications bind variables are not effectively used.
In this mode Oracle, forces SQLs with similar literal and binds to reuse the same cursors. It does not generate child cursors for different bind variables.

The SIMILAR mode although available in Oracle 11g is not recommended as it forces Oracle to replace literals with binds and allow similar child cursors. Oracle also says it heavily impacts the number of child cursor creation and upsets the optimizer operations.

Although there is not explicit child cursor limit(can be controlled with _ parameter), they can fill up your shared pool quiet rapidly with one bad SQL. Oracle reckons that excessive child cursors may be more detrimental to performance than using separate cursors. We noticed that SIMILAR floods the shared pool and leads to some bugs like Bug 11657468 - Excessive mutex waits with adaptive cursor sharing .

Thanks to Senthil for assisting in the research

No comments:

Post a Comment