|
Administrator
|
The Query Optimizer in Oracle is typically quite clever. It uses crucial information—like object and system statistics, the compilation environment, and bind values—to determine the most efficient execution plan for your SQL statement. However, sometimes things go wrong. If any of those inputs have an issue (a defect), or if the optimizer itself runs into a bug, you can end up with a sub-optimal plan. This is a headache for performance.
SQL Profile is a helping hand for the optimizer... Think of a SQL Profile as auxiliary information—extra intelligence—that helps the optimizer overcome these input or internal defects. When the optimizer uses the SQL Profile alongside its regular inputs, it can minimize mistakes and is much more likely to select the best plan.
A SQL Plan Baseline (SPB) takes a much more conservative approach. An SPB consists of a set of accepted plans for a given SQL statement. When the statement is parsed, the optimizer is strictly constrained to only select the best plan from within this accepted set.
The cost-based approach is still used to choose a plan, but only within this set of plans. SQL plan baselines are a more conservative plan selection strategy than SQL profiles.
You should deploy a SQL Profile when your primary objective is to simply assist the optimizer in its crucial costing process.
Conversely, you should utilize a SQL Plan Baseline if your philosophy is more conservative and you need to strictly control which plans are permitted for a given statement.
Keep this in mind that, if you are already managing plans with an SPB but observe that the optimizer struggles—either by failing to select the truly best plan from the accepted set or by not finding a good-cost new plan to add to the history—you can, and often should, use a SQL Profile alongside the SPB. The Profile can enhance the optimizer's intelligence, allowing it to work more effectively even within the baseline's constraints.
-----------------
Anyways, enough for the background info..
Sql profile(s) may not be able to change the plan on based upon the Cost of the Query.
If so; check where there are any baselines on the SQL; and create one ..
Ref: Oracle Support / SQL Profile created through SQL Tuning Advisor (STS) not used (Doc ID 2927523.1)
|
Locked
