Overview of SQL Plan management

Overview of SQL Plan Management

SQL plan management is a preventative mechanism that records and evaluates the execution plans of SQL statements over time. This mechanism can build aSQL plan baseline, which is a set of accepted plans for a SQL statement. The accepted plans have been proven to perform well.

The plan history is the set of plans, both accepted and not accepted, that the optimizer generates for a SQL statement over time Because only accepted plans are in the SQL plan baseline, the plans in the baseline form a susbset of the plan history. For example, after the optimizer generates the first acceptable plan for a SQL plan baseline, subsequent plans are part of the plan history but not part of the plan baseline.

When automatic plan capture is enabled, the database automatically creates and maintains the plan history for SQL statements using information provided by the optimizer. The plan history includes relevant information used by the optimizer to reproduce an execution plan, such as the SQL text, outline, bind variables, and compilation environment.

To enable automatic plan capture, set the OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES initialization parameter to TRUE. By default, this parameter is FALSE.

To load plans from a SQL tuning set, use the LOAD_PLANS_FROM_SQLSET function of the DBMS_SPM package. The following example loads the plans stored in the SQL tuning set named tset1:

DECLARE   my_plans PLS_INTEGER; BEGIN   my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET( sqlset_name => 'tset1'); END; / 

To load plans from Automatic Workload Repository (AWR), load the plans stored in AWR snapshots into a SQL tuning set before using theLOAD_PLANS_FROM_SQLSET function as described in this section.

like this

BEGIN   DBMS_SQLTUNE.CREATE_SQLSET(     sqlset_name => 'my_sql_tuning_set',      description  => 'I/O intensive workload'); END; / 

-- Load the SQL set from the Automatic Workload Repository (AWR). DECLARE   l_cursor  DBMS_SQLTUNE.sqlset_cursor; BEGIN   OPEN l_cursor FOR     SELECT VALUE(p)     FROM   TABLE (DBMS_SQLTUNE.select_workload_repository (                     765,  -- begin_snap                     766,  -- end_snap                     NULL, -- basic_filter                     NULL, -- object_filter                     NULL, -- ranking_measure1                     NULL, -- ranking_measure2                     NULL, -- ranking_measure3                     NULL, -- result_percentage                     10)   -- result_limit                   ) p;    DBMS_SQLTUNE.load_sqlset (     sqlset_name     => 'my_sql_tuning_set',     populate_cursor => l_cursor); END; / 

To load plans from the shared SQL area, use the LOAD_PLANS_FROM_CURSOR_CACHE function of the DBMS_SPM package. In the following example, Oracle Database loads the plans located in the shared SQL area for the SQL statement identified by its sql_id:

DECLARE   my_plans PLS_INTEGER; BEGIN   my_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE( sql_id => '99twu5t2dn5xd'); END; 

/

To view the plans stored in the SQL plan baseline for a given statement, use the DISPLAY_SQL_PLAN_BASELINE function of the DBMS_XPLAN package. The following example displays one or more execution plans for the specified SQL statement, specified by the handle (sql_handle):

SELECT * FROM TABLE(      DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(          sql_handle=>'SYS_SQL_209d10fabbedc741',          format=>'basic')); 

Alternatively, you can display a single plan by supplying a plan name (plan_name).

You can also display SQL plan baseline information using a SELECT statement directly on the DBA_SQL_PLAN_BASELINES view, as shown in the following example:

SELECT SQL_HANDLE, PLAN_NAME, ENABLED, ACCEPTED, FIXED  FROM   DBA_SQL_PLAN_BASELINES;   SQL_HANDLE                PLAN_NAME                      ENA  ACC    FIX ------------------------------------------------------------------------ SYS_SQL_209d10fabbedc741  SYS_SQL_PLAN_bbedc741a57b5fc2  YES  NO     NO SYS_SQL_209d10fabbedc741  SYS_SQL_PLAN_bbedc741f554c408  YES  YES    NO

ENABLED : Bu SQL için yeni planlar oluşturulduğunda Baseline 'a alınır.

ACCEPTED : Kullanılan exec plan'ı tutan Baseline

FIXED : Artık bu SQL için yeni plan aranmaz. Yeni plan oluştuğunda base line'a alınmaz 

The optimizer does not add new plans to a fixed SQL plan baseline. Because the optimizer does not automatically add new plans, the database does not evolve a fixed SQL plan baseline when you execute DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE. However, you can evolve a fixed SQL plan baseline by manually loading new plans into it from the shared SQL area or a SQL tuning set.

During the SQL plan baseline selection phase, Oracle Database detects plan changes based on the stored plan history, and selects plans to avoid potential performance regressions for a set of SQL statements.

Each time the database compiles a SQL statement, the optimizer does the following:

1. Uses a cost-based search method to build a best-cost plan

2. Tries to find a matching plan in the SQL plan baseline

3. Does either of the following depending on whether a match is found:

   • If found, then the optimizer proceeds using the matched plan

   • If not found, then the optimizer evaluates the cost of each accepted plan in the SQL plan baseline and selects the plan with the lowest cost