oracle 11g new concepts

Oracle 11g new concepts

Adaptive cursor sharing :

depends on the selectivity of a query different execution plan is created for a single sql query with bind variable .

This concept is introduced in 11g and prior to that a new concept called peeking was used where the sameplan will be generated and used for queries with the bind variables.

when the selectivity differs very much this will increase the performance many fold:

eg:

first query returns 50 rows.
2nd query returns 10000 rows out of 12000

so an index scan is needed for the first and a full table scan for the second.

in adaptive cursor sharing two different plans will be generated when running both the queries seperately.

two types:

bind sensitive:
bind aware

The cursor will be bind sensitive if the selectivity  remains the same for the queries.
the cursor will become bind aware when  there needs 2 seperate plans needed for both the plans .
whe a new plan is generated the old plan will become unusable .

Cursor merging :

when both the cursor is open for the same plan it will merge both into a single cursor to reduce the memory usage .

Baselines:

This is yet another concept introduced in 11g .when it generates the execution  plan ,it compares the plan with the old ones and if it is better than the old one ,it will add this in the list .if the performance is not better then the old plan is used .this is called as baselines