Jayesh Pande

SQL plan management (SPM) is designed to prevent performance regression for all SQL statements used by an application (assuming that the SQL statements in question are used more than once). SPM uses SQL plan baselines that are associated with individual SQL statements to control what execution plans they are permitted to use. It’s a simple but powerful idea that opens the door to the possibility of using SQL plan baselines in a more selective and reactive way: to influence the SQL execution plans of individual queries without having to modify application queries or change the application itself.  The PL/SQL below will work in both Oracle Database 11g and Oracle Database 12c. Consider the scenario where you have a SQL statement used by an application that’s got a sub-optimal plan and you need to do something about it. For the sake of argument, let’s assume that you know that there’s a hint you can use to achieve a better plan. I’m going to assume from now on that yo...

The query optimizer normally uses information like object and system statistics, compilation environment, bind values and so on to determine the best plan for a SQL statement. In some cases, defects in either these inputs or the optimizer can lead to a sub-optimal plan.  A SQL profile contains auxiliary information that mitigates these problems.  When used together with its regular inputs, a SQL profile helps the optimizer minimize mistakes and thus more likely to select the best plan. A SQL plan baseline for a SQL statement consists of a set of accepted plans. When the statement is parsed, the optimizer will only select the best plan from among this set. If a different plan is found using the normal cost-based selection process, the optimizer will add it to the plan history but this plan will not be used until it is verified to perform better than the existing accepted plan and is evolved.So, SQL profiles provide additional information to the optimizer to help select th...