SQL> ALTER SESSION SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=TRUE
SQL> ALTER SESSION SET optimizer_mode='FIRST_ROWS';
Session altered.
SQL> SELECT /* TEST */
2 *
3 FROM SALES WHERE QUANTITY_SOLD > 40
4 ORDER BY PROD_ID;
no rows selected
SQL> ALTER SYSTEM FLUSH SHARED_POOL;
System altered.
SQL> ALTER SESSION SET optimizer_mode='ALL_ROWS';
Session altered.
SQL> SELECT /* TEST */
2 *
3 FROM SALES WHERE QUANTITY_SOLD > 40
4 ORDER BY PROD_ID;
no rows selected
Second execution of the query has encountered a change in the execution plan. But it has not been ‘accepted as yet even though it has been ‘enabled’.
SQL> SELECT sql_handle, plan_name, enabled, accepted FROM dba_sql_plan_baselines
WHERE sql_text like '%GAVIN%'; 2
Ena-
SQL_HANDLE PLAN_NAME bled Acpt
------------------------------ ------------------------------ ---- ----
SYS_SQL_6f3dcd88c7488035 SYS_SQL_PLAN_c748803511df68d0 YES YES
SYS_SQL_6f3dcd88c7488035 SYS_SQL_PLAN_c748803554bc8843 YES NO
We can use the EVOLVE_SQL_PLAN_BASELINE function to compare the performance
between the two plans. We note that the second plan has not been automatically
evolved or accepted because it does not pass the performance improvement
criteria which has been laid down
SQL> SET LONG 10000
SQL> SELECT DBMS_SPM.evolve_sql_plan_baseline(sql_handle => 'SYS_SQL_6f3dcd88c7488035') FROM dual;
DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(SQL_HANDLE=>'SYS_SQL_6F3DCD88C7488035')
--------------------------------------------------------------------------------
-------------------------------------------------------------------------------
Evolve SQL Plan Baseline Report
-------------------------------------------------------------------------------
Inputs:
-------
SQL_HANDLE = SYS_SQL_6f3dcd88c7488035
PLAN_NAME =
TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
VERIFY = YES
COMMIT = YES
Plan: SYS_SQL_PLAN_c748803554bc8843
-----------------------------------
Plan was verified: Time used 1.457 seconds.
Failed performance criterion: Compound improvement ratio <= 1.16.
Baseline Plan Test Plan Improv. Ratio
------------- --------- -------------
Execution Status: COMPLETE COMPLETE
Rows Processed: 0 0
Elapsed Time(ms): 1036 304 3.41
CPU Time(ms): 1020 300 3.4
Buffer Gets: 1929 1727 1.12
Disk Reads: 0 0
Direct Writes: 0 0
Fetches: 0 0
Executions: 1 1
-------------------------------------------------------------------------------
Report Summary
-------------------------------------------------------------------------------
Number of SQL plan baselines verified: 1.
Number of SQL plan baselines evolved: 0.
We can also manually evolve the plan if we ourselves feel that we know that
the plan is a better one
SQL> var spm number;
SQL> exec :spm := dbms_spm.alter_sql_plan_baseline(sql_handle =>'SYS_SQL_6f3dcd88c7488035',
- plan_name =>'SYS_SQL_PLAN_c748803554bc8843', attribute_name => 'ACCEPTED',attribute_value => 'YES');
PL/SQL procedure successfully completed.
SQL> SELECT sql_handle, plan_name, enabled, accepted FROM dba_sql_plan_baselines
2 WHERE sql_text like '%GAVIN%';
Ena-
SQL_HANDLE PLAN_NAME bled Acpt
------------------------------ ------------------------------ ---- ----
SYS_SQL_6f3dcd88c7488035 SYS_SQL_PLAN_c748803511df68d0 YES YES
SYS_SQL_6f3dcd88c7488035 SYS_SQL_PLAN_c748803554bc8843 YES YES
Posts