What is peeking?
Peeking means to check all potential optimization by looking at the USING values during the query parsing time and use those to pick efficient path to satisfy the request.
What is generic plan?
Generic plan is a generic plan for all the like queries, which is cached and reused to save parsing time, it is the time that CPU takes to parse and generate a plan and sending it across to the Dispatcher, but a cached plan is not always the right and correct plan.
What is specific plan?
A plan that is generated specific to a query and is different for different USING values.
What determines an Optimizer to use a specific plan?
DisablePeekUsing field in the DBS control settings determines to use whether to use specific or generic plan. By default it is shipped as FALSE meaning Optimizer Performance enhancements are enabled. The Optimizer has the ability to check various USING values and pick an efficient path. By making it to TRUE , the optimizer enhancements are disabled.
When the setting is set to TRUE, parameterized queries will no longer be eligible for specific plans, generic plans will be used for all parameterized queries, by doing so the parsing overhead is eliminated when the peeking is turned off.
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment