In Oracle 11g, we can ensure that the same proven execution plans are used by the Optimizer regardless of any change which can effect the optimizer like a version change, index drop or recreate, init.ora parameter change etc.
Once plans are marked as enabled and accepted, the optimizer will not use any new plans unless there is proven performance benefits of adopting a new plan. Plans can also be manually adopted or “evolved”.
One of the dangers of upgrading from 10g to 11g (or between any release) is that execution plans may change in the new version which could affect application performance.
But with this new 11g feature, if we have tried and trusted 10g execution plans in place, we can ensure that the same plans will be used by the optimizer even after the migration unless new 11g plans are found to offer better performance. This can be achieved by capturing 10g plans in SQL tuning sets (STS) and exporting those to the 11g database.
Let us demonstrate the same with a small example using the SALES table in the SH schema.
We need to enable the use of SQL Plan management by setting the parameter optimizer_capture_sql_plan_baselines to TRUE. The default value is FALSE.
Initially the OPTIMIZER_MODE is set to FIRST_ROWS which should optimize the statement for best response time. So in this case the index on the SALES table is used.
SQL> ALTER SESSION SET optimizer_mode='FIRST_ROWS'; Session altered. SQL> EXPLAIN PLAN FOR 2 SELECT * FROM SALES 3 WHERE QUANTITY_SOLD > 40 ORDER BY PROD_ID; Explained. SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); | Id | Operation | Name | Bytes | Cost (%CPU)| Time | Pstart| Pstop | --------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 29 | 5063 (2)| 00:01:01 | | | | 1 | SORT ORDER BY | | 29 | 5063 (2)| 00:01:01 | | | | 2 | PARTITION RANGE ALL | | 29 | 5062 (2)| 00:01:01 | 1 | 28 | |* 3 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES | 29 | 5062 (2)| 00:01:01 | 1 | 28 | | 4 | BITMAP CONVERSION TO ROWIDS | | | | | | | 5 | BITMAP INDEX FULL SCAN | SALES_PROMO_BIX | | | | 1 | 28 | ---------------------------------------------------------------------------------------------------------------
if we change the OPTIMIZER_MODE to ALL_ROWS, we now see from the explain plan that a FULL TABLE SCAN is being used as the statement is optimized for best throughput.
SQL> ALTER SESSION SET optimizer_mode='ALL_ROWS'; Session altered. SQL> EXPLAIN PLAN FOR 2 SELECT * FROM SALES 3 WHERE QUANTITY_SOLD > 40 ORDER BY PROD_ID; Explained. SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------- Plan hash value: 3147563666 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 29 | 1196 (3)| 00:00:15 | | | | 1 | SORT ORDER BY | | 1 | 29 | 1196 (3)| 00:00:15 | | | | 2 | PARTITION RANGE ALL| | 1 | 29 | 1195 (3)| 00:00:15 | 1 | 28 | |* 3 | TABLE ACCESS FULL | SALES | 1 | 29 | 1195 (3)| 00:00:15 | 1 | 28 | ----------------------------------------------------------------------------------------------
We will run the query – initially with OPTIMIZER_MODE set to FIRST_ROWS and then the same query with OPTIMIZER_MODE set to ALL_ROWS. We will use a label ‘GAVIN’ in the SQL statement to help us easily identify it from the other SQL statements in the shared pool.
We explicitly flush the shared pool to force a hard parse the next time the same query is run but with the ALL_ROWS optimizer session setting.
SQL> ALTER SESSION SET optimizer_mode='FIRST_ROWS'; Session altered. SQL> SELECT /* GAVIN */ 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 /* GAVIN */ 2 * 3 FROM SALES WHERE QUANTITY_SOLD > 40 4 ORDER BY PROD_ID; no rows selected
We will notice that the 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
