Saturday, December 16, 2017

Cursor Merging

Cursor Merging

If the optimizer creates a plan for a bind-aware cursor, and if this plan is the same as an existing cursor, then the optimizer can perform cursor merging.

In cursor merging database merges cursors to save space in the library cache. The database increases the selectivity range for the cursor to include the selectivity of the new bind value.

When a query uses a new bind variable, the optimizer tries to find a cursor that it thinks is a good fit based on similarity in the selectivity or cardinality of the bind value. If the database cannot find such a cursor, then it creates a new one. If the plan for the new cursor is the same as one of the existing cursors, then the database merges the two cursors to save space in the library cache which involves storing the combined cardinality statistics into one child cursor, and deleting the other one. The merge results in the database marking one cursor as not sharable. If the library cache is under space pressure, then the database ages out the non-sharable cursor first.

For more detailed explanation on when and how optimizer merge cursors, please refer How Adaptive Cursor Sharing Works.

Related Links :-

No comments:

Post a Comment