I’ve performed many upgrades of the Oracle RDBMS across many different versions, and one of the most problematic things about an upgrade is when the ocasional SQL query performance goes really badly wrong. In previous versions I’ve seen some truly terrible changes of execution plan in going from one version to the next.
It makes for an unpleasant first few days/weeks after an upgrade if you are constantly having to firefight poorly performing SQL queries that were running absolutely fine (or indeed good enough) under the previous Oracle version. It also, obviously can lead to a lot of frustration for the users of the database and can generally give a poor impression to management/end-users of the whole upgrade.
With this in mind one of the nicest features that you can deploy when you upgrade to 11g is SQL Plan Management. For a recent 10gR2 to 11gR2 upgrade, I grabbed all SQL Plans that was seen by the optimizer in the previous month and ensured that post-upgrade these plans were the ones that were going to be used.
This is reasonably well documented. What this article is about documenting how to actually evolve a plan when you have decided that the plan that has been captured and is being used is no longer the best plan available. . If something changes that makes a new plan actually better, this is recorded in the DBA_SQL_PLAN_BASELINES table, but the OLD plan is still used, until you tell the optimizer to start considering the new one.
select sql_handle, sql_text, enabled, accepted from DBA_SQL_PLAN_BASELINES where sql_text like '%messages%'; SQL_HANDLE SQL_TEXT ENA ACC ------------------------------ -------------------------------------------------------------------------------- --- --- SYS_SQL_51dcc66dae94c669 select min(ID) into :b0:b1 from messages where ((decode(processed,null ,1,null )=1 and bitand(system,:b2)0) and bitand(system_sent,:b2)=0) YES YES SYS_SQL_51dcc66dae94c669 select min(ID) into :b0:b1 from messages where ((decode(processed,null ,1,null )=1 and bitand(system,:b2)0) and bitand(system_sent,:b2)=0) YES NO
So for this statement we have the plan as it was captured under 10gR2, and this plan is both ENABLED and ACCEPTED and this is the plan that will be used by the optimizer. A plan since the upgrade has been found but has ACCEPTED set to NO and will not be used as long as the ACCEPTED plan is able to be used (i.e. indexes have not been dropped since the plan was accepted). Also note below the original 10gR2 plan has an origin of MANUAL-LOAD, while the newer plan produced at 11gR2 has an origin of AUTO-CAPTURE.
To see all the plans for a particular SQL_HANDLE you can run:
select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle => 'SYS_SQL_51dcc66dae94c669', format => 'BASIC')); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ -------------------------------------------------------------------------------- SQL handle: SYS_SQL_51dcc66dae94c669 SQL text: select min(ID) into :b0:b1 from account.automaton_messages where ((decode(processed,null ,1,null )=1 and bitand(system,:b2)<>0) and bitand(system_sent,:b2)=0) -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- Plan name: SQL_PLAN_53r66dqr99jm98a727c3d Plan id: 2322758717 Enabled: YES Fixed: NO Accepted: YES Origin: MANUAL-LOAD PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ -------------------------------------------------------------------------------- Plan hash value: 2210778196 ------------------------------------------------- | Id | Operation | Name | ------------------------------------------------- | 0 | SELECT STATEMENT | | | 1 | SORT AGGREGATE | | | 2 | TABLE ACCESS FULL| MESSAGES | ------------------------------------------------- PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ -------------------------------------------------------------------------------- Plan name: SQL_PLAN_53r66dqr99jm996175d1b Plan id: 2518113563 Enabled: YES Fixed: NO Accepted: NO Origin: AUTO-CAPTURE -------------------------------------------------------------------------------- Plan hash value: 1110431463 -------------------------------------------------------------------- | Id | Operation | Name | -------------------------------------------------------------------- PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | | 1 | SORT AGGREGATE | | | 2 | TABLE ACCESS BY INDEX ROWID| MESSAGES | | 3 | INDEX RANGE SCAN | IX_MESSAGES_UNPRO | -------------------------------------------------------------------- 38 rows selected.
Essentially what has happened is that since the upgrade this full table scan was becoming slower and slower and an index was added to speed up the query. Of course what (probably) would have sped up the query straight away without SQL Plan Management, does not work as you (might) anticipate as the new plan is not (yet) in the ACCEPTED state.
To move this plan to the ACCEPTED state you need to Evolve the SQL Plan Baseline:
set serveroutput on DECLARE report clob; BEGIN report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE( sql_handle => 'SYS_SQL_51dcc66dae94c669'); DBMS_OUTPUT.PUT_LINE(report); END; /
This produces output like the following:
------------------------------------------------------------------------------- Evolve SQL Plan Baseline Report ------------------------------------------------------------------------------- Inputs: ------- SQL_HANDLE = SYS_SQL_51dcc66dae94c669 PLAN_NAME = TIME_LIMIT = DBMS_SPM.AUTO_LIMIT VERIFY = YES COMMIT = YES Plan: SQL_PLAN_53r66dqr99jm996175d1b ------------------------------------ Plan was verified: Time used 10.16 seconds. Plan passed performance cr iterion: 4.44 times better than baseline plan. Plan was changed to an accepted plan. Baseline Plan Test Plan Stats Ratio ------------- --------- ----------- Execution Status: COMPLETE COMPLETE Rows Processed: 1 1 Elapsed Time(ms): 4452.225 158.285 28.13 CPU Time(ms): 2749.583 140.979 19.5 Buffer Gets: 86591 19549 4.43 Physical Read Requests: 132 2 66 Physical Write Requests: 0 0 Physical Read Bytes: 1441792 16384 88 Physical Write Bytes: 0 0 Executions: 1 1 ------------------------------------------------------------------------------- Report Summa ry ------------------------------------------------------------------------------- Number of plans verified: 1 Number of plans accepted: 1 PL/SQL procedure successfully completed.
However, there are now 2 ACCEPTED and ENABLED plans, the original full table scan AND the new index lookup. This was an ongoing production issue and we found the application continued to be taking too long, as it was still using the original full table scan even after the above evolving. I found making the original plan to be not accepted made the application immediately switch to the new index lookup plan. You can accomplish this with the following:
DECLARE plans_dropped PLS_INTEGER; BEGIN plans_dropped := DBMS_SPM.drop_sql_plan_baseline ( sql_handle => 'SYS_SQL_51dcc66dae94c669', plan_name => 'SQL_PLAN_53r66dqr99jm98a727c3d'); DBMS_OUTPUT.put_line(plans_dropped); END; / 1 PL/SQL procedure successfully completed.
After the above was run the application then sped up as it was using the faster index access. The real point of the article of course, is that when using SQL Plan Management that doing the evolve might not be enough, and you may have to set the original plan you no longer want into the not accepted state to ensure your application uses the execution path that you want.
I still reckon that SQL Plan Management is great way of ensuring a stable and successful upgrade.
jarneil 
2 thoughts on “Changing Plans and SQL Plan Management”