OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES initialization parameter to TRUE. By default, this parameter is FALSE.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;/
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ınmazThe 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:
Uses a cost-based search method to build a best-cost plan
Tries to find a matching plan in the SQL plan baseline
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
