Changing Plans and SQL Plan Management

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.

About these ads
Leave a comment

2 Comments

  1. October 2010 Blogroll Report | AskDba.org Weblog
  2. SQL Plan Baselines « Oracle Scratchpad

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 51 other followers

%d bloggers like this: