Upgrade to 11g and ensure Optimizer Plan Stability using SQL Plan Baselines
- Posted by Gavin Soorma
- On February 15, 2011
- 4 Comments
- 11g upgrade, evolve plans, SPM, sql plan baseline
One of the major concerns a DBA has related to performing a database upgrade to Oracle 11g is ‘Are Execution Plans Going To Change Post-Upgrade?’
I am quite sure we would have experienced some queries having different execution plans when we did earlier upgrades to both 9i as well as 10g. This is due to optimizer and kernel differences in every Oracle version which causes the execution plan of some SQL statements to change.
It is not as if all SQL statements have different execution plans after a database upgrade is performed. It maybe just a case where only SQL statement is now performing a full table scan or sub-optimal join as compared to the pre-upgrade plan and that query is one is which is used a great deal by the application.
So we may have taken all steps to perform an upgrade with just a few hours of application downtime, but now we have a case where the application has come to a halt after the upgrade just because that one key query has a performance issue because of a changed (for the worse) execution plan.
So how do we guarantee Plan Stability and preserve execution plans across upgrades?
Let us look at a simple case where we have an execution plan in 10.2.0.4 and we want to ensure that that same plan (even though we see it is a bad one) is executed after the upgrade to 11g Release 2.
Using the SQL Plan Baseline feature of 11g, we will see how we can use plan baselines and how plans are evolved so that only plans which offer better performance are chosen over existing plans.
Let us first create a SQL Tuning Set in Oracle 10g and we will then load that SQL Tuning Set with some SQL statements. Let us assume that these SQL statements are key SQL which is executed by the application and are demonstrative of a typical work load.
SQL> BEGIN
DBMS_SQLTUNE.CREATE_SQLSET(
sqlset_name => '10G_TO_11G'
);
END;
/
PL/SQL procedure successfully completed.
We will now capture SQL from the cursor cache of the Shared Pool of the instance and populate the SQL Tuning Set with those statements.
In this example, we are polling the cursor cache every 5 seconds over a duration of 240 seconds or 4 minutes. In real-life scenarios obviously we will need to ensure that we choose the right time of day as well as duration so as to capture the main SQL statements which our application is executing.
SQL> EXEC DBMS_SQLTUNE.CAPTURE_CURSOR_CACHE_SQLSET( -
sqlset_name => '10G_TO_11G', -
time_limit => 240, -
repeat_interval => 5);
We will see that this will now wait for about 4 minutes before returning us to the SQL command prompt.
From another session we will execute some SQL which we want to load in our SQL Tuning set. Again in real-life, we would not have to do this as the SQL statements to be loaded from the cursor cache are being generated by our application.
SQL> select /*10G_STS*/ count(*) from myobjects where object_type='TABLE';
COUNT(*)
----------
13975
SQL> select /*10G_STS*/ count(*) from myobjects where object_type='VIEW';
COUNT(*)
----------
41327
SQL> select /*10G_STS*/ count(*) from myobjects;
COUNT(*)
----------
599495
SQL> select /*10G_STS*/ count(*) from myobjects where object_type <> 'TABLE';
COUNT(*)
----------
585520
SQL> select /*10G_STS*/ count(*) from myobjects where object_type <> 'INDEX';
COUNT(*)
----------
584662
After about 4 minutes, we will see that the procedure has now completed.
SQL> EXEC DBMS_SQLTUNE.CAPTURE_CURSOR_CACHE_SQLSET( -
sqlset_name => '10G_TO_11G', -
time_limit => 240, -
repeat_interval => 5);
/
PL/SQL procedure successfully completed.
Let us now have a look at our SQL Tuning Set – “10G_TO_11G”. We can see that it has 82 SQL statements as it has gathered other SQL statements in addition to the 5 SQL statements which we have manually executed.
SQL> select owner,name, STATEMENT_COUNT from DBA_SQLSET; OWNER NAME STATEMENT_COUNT ------------------------------ ------------------------------ --------------- SYSTEM 10G_TO_11G 82 SQL> select SQL_ID,SQL_TEXT from DBA_SQLSET_STATEMENTS where 2 SQLSET_NAME='10G_TO_11G' and SQL_TEXT like '%10G%'; SQL_ID SQL_TEXT ------------- -------------------------------------------------------------------------------- 79pv1xgmxku8x select /*10G_STS*/ count(*) from myobjects where object_type='TABLE' 28cphmwj9z432 select /*10G_STS*/ count(*) from myobjects where object_type='VIEW' 3m5kbna7q3frc select /*10G_STS*/ count(*) from myobjects 4hjf4zsp6jk6g select /*10G_STS*/ count(*) from myobjects where object_type <> 'INDEX' akstg9hkmf98t select /*10G_STS*/ count(*) from myobjects where object_type='SYNONYM' b21gvmw5mqgwm select /*10G_STS*/ count(*) from myobjects where object_type like 'PACKAGE%'
Ok. So now let us pick out one SQL statement which we will use for our case study.
select /*10G_STS*/ count(*) from myobjects where object_type=’TABLE’
This SQL statement has an SQL_ID ’79pv1xgmxku8x’ and we can see that it is executing a Full Table Scan. In the 10g database, there is no index on the table MYOBJECTS.
SQL> select * from table(dbms_xplan.display_cursor('79pv1xgmxku8x',0));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
SQL_ID 79pv1xgmxku8x, child number 0
-------------------------------------
select /*10G_STS*/ count(*) from myobjects where object_type='TABLE'
Plan hash value: 3386614567
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1854 (100)| |
| 1 | SORT AGGREGATE | | 1 | 9 | | |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
|* 2 | TABLE ACCESS FULL| MYOBJECTS | 12700 | 111K| 1854 (3)| 00:00:23 |
--------------------------------------------------------------------------------
We will now create a staging table STS10G_STATING and we will load the SQL Tuning Set in the 10g database into this staging table. We will then using Export/Import, move this staging table from the 10g database to the 11g database.
This is basically how we can migrate our 10g Tuning Sets to the 11g environment.
SQL> BEGIN
2 DBMS_SQLTUNE.CREATE_STGTAB_SQLSET(
3 table_name => 'STS10G_STAGING'
4 );
5 DBMS_SQLTUNE.PACK_STGTAB_SQLSET(
6 sqlset_name => '10G_TO_11G'
7 ,sqlset_owner => 'SYSTEM'
8 ,staging_table_name => 'STS10G_STAGING'
9 ,staging_schema_owner => 'SYSTEM'
10 );
11 END;
12 /
PL/SQL procedure successfully completed.
SQL> desc STS10G_STAGING
Name Null? Type
----------------------------------------------------------------- -------- --------------------------------------------
NAME VARCHAR2(30)
OWNER VARCHAR2(30)
DESCRIPTION VARCHAR2(256)
SQL_ID VARCHAR2(13)
FORCE_MATCHING_SIGNATURE NUMBER
SQL_TEXT CLOB
PARSING_SCHEMA_NAME VARCHAR2(30)
BIND_DATA RAW(2000)
BIND_LIST SQL_BIND_SET
MODULE VARCHAR2(48)
ACTION VARCHAR2(32)
ELAPSED_TIME NUMBER
CPU_TIME NUMBER
BUFFER_GETS NUMBER
DISK_READS NUMBER
DIRECT_WRITES NUMBER
ROWS_PROCESSED NUMBER
FETCHES NUMBER
EXECUTIONS NUMBER
END_OF_FETCH_COUNT NUMBER
OPTIMIZER_COST NUMBER
OPTIMIZER_ENV RAW(1000)
PRIORITY NUMBER
COMMAND_TYPE NUMBER
FIRST_LOAD_TIME VARCHAR2(19)
STAT_PERIOD NUMBER
ACTIVE_STAT_PERIOD NUMBER
OTHER CLOB
PLAN_HASH_VALUE NUMBER
PLAN SQL_PLAN_TABLE_TYPE
SPARE1 NUMBER
SPARE2 NUMBER
SPARE3 BLOB
SPARE4 CLOB
SQL> select count(*) from STS10G_STAGING;
COUNT(*)
----------
82
Now Export the Staging Table from the 10g environment.
serverA:/export/home/oracle $ exp file=10gsts.dmp tables=STS10G_STAGING Export: Release 10.2.0.4.0 - Production on Fri Feb 11 10:51:51 2011 Copyright (c) 1982, 2007, Oracle. All rights reserved. Username: system Password: Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Export done in US7ASCII character set and AL16UTF16 NCHAR character set server uses WE8ISO8859P1 character set (possible charset conversion) About to export specified tables via Conventional Path ... . . exporting table STS10G_STAGING 82 rows exported . . exporting table STS10G_STAGING_CBINDS 0 rows exported . . exporting table STS10G_STAGING_CPLANS 256 rows exported Export terminated successfully without warnings.
Import the staging table into the 11g environment. We have created the same tables in another 11g environment to illustrate the case rather than upgrading the existing 10g database to 11g.
serverB:/export/home/oracle $ imp file=10gsts.dmp fromuser=system touser=system Import: Release 11.2.0.2.0 - Production on Fri Feb 11 11:01:12 2011 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Username: system Password: Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Export file created by EXPORT:V10.02.01 via conventional path import done in US7ASCII character set and AL16UTF16 NCHAR character set import server uses WE8MSWIN1252 character set (possible charset conversion) . importing SYSTEM's objects into SYSTEM . . importing table "STS10G_STAGING" 82 rows imported . . importing table "STS10G_STAGING_CBINDS" 0 rows imported . . importing table "STS10G_STAGING_CPLANS" 256 rows imported Import terminated successfully without warnings.
Now what we will do is that we will try and make the optimizer choose a different plan as compared to the 10g execution plan by creating an index on the table.
This should in essence change the execution plan and go for an Index scan as opposed to the full table scan which was happening in 10g.
SQL> select index_name from user_indexes where table_name='MYOBJECTS'; INDEX_NAME ------------------------------ MYOBJECTS_IND SQL> explain plan for 2 select count(*) from myobjects where object_type='TABLE'; Explained. SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------ Plan hash value: 2605150876 ----------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 9 | 46 (3)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 9 | | | |* 2 | INDEX RANGE SCAN| MYOBJECTS_IND | 15332 | 134K| 46 (3)| 00:00:01 |
But just to illustrate the point that we can make sure that the optimizer will still continue to use the old 10g plans even though there is a optimal plan available.
We will now unpack the staging table which we imported from 10g and populate the SQL Plan Management Base in 11g with the SQL Tuning Set 10G_TO_11G.
SQL> BEGIN
DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET(
sqlset_name => '10G_TO_11G'
,sqlset_owner => 'SYSTEM'
,replace => TRUE
,staging_table_name => 'STS10G_STAGING'
,staging_schema_owner => 'SYSTEM'
);
END;
/
PL/SQL procedure successfully completed.
SQL> DECLARE
my_10gplans PLS_INTEGER;
BEGIN
my_10gplans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET( sqlset_name => '10G_TO_11G');
END;
/
PL/SQL procedure successfully completed.
Since we have manually loaded the plans in the SQL Plan Management Base, we can see that they have been accepted and enabled as well.
SQL> select SQL_HANDLE,SQL_TEXT,ENABLED, ACCEPTED from dba_sql_plan_baselines 2 where sql_text like '%10G%'; SQL_HANDLE SQL_TEXT ENA ACC ------------------------------ -------------------------------------------------------------------------------- --- --- SQL_1c8e20c5c8fa7f6a select /*10G_STS*/ count(*) from myobjects where object_type='VIEW' YES YES SQL_388c048277282b1f select /*10G_STS*/ count(*) from myobjects where object_type='SYNONYM' YES YES SQL_8fca037405ff8ce7 select /*10G_STS*/ count(*) from myobjects where object_type <> 'INDEX' YES YES SQL_dda6e70a26d74f1b select /*10G_STS*/ count(*) from myobjects where object_type like 'PACKAGE%' YES YES SQL_f915be3f43387642 select /*10G_STS*/ count(*) from myobjects YES YES SQL_fc83e34606a4b991 select /*10G_STS*/ count(*) from myobjects where object_type='TABLE' YES YES PL/SQL procedure successfully completed.
Let us now check if our old 10g execution plans are being used in the 11g database – remember that for the query in question, it should now perform a full table scan as opposed to an index scan (this was what the optimizer was doing in 10g) even though an index is present on the table being queried.
Note – the explain plan shows us that the query is using the SQL plan baseline “SQL_PLAN_gt0z38s3a9fcjcaeddc3c”.
SQL> explain plan for
2 select /*10G_STS*/ count(*) from myobjects where object_type='TABLE';
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT--------------------------------------------------------------
Plan hash value: 3386614567
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 2300 (3)| 00:00:28 |
| 1 | SORT AGGREGATE | | 1 | 9 | | |
|* 2 | TABLE ACCESS FULL| MYOBJECTS | 15332 | 134K| 2300 (3)| 00:00:28 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_TYPE"='TABLE')
Note
-----
- SQL plan baseline "SQL_PLAN_gt0z38s3a9fcjcaeddc3c" used for this statement
Let us now run this query a few times and see if the Optimizer feels that there maybe a better plan for this same SQL statement.
We will see that a new plan has been generated for the SQL statement, but important to note that it has not been accepted as yet.
SQL> select /*10G_STS*/ count(*) from myobjects where object_type='TABLE';
COUNT(*)
----------
13975
SQL> /
COUNT(*)
----------
13975
SQL> select SQL_HANDLE,SQL_TEXT,ENABLED, ACCEPTED from dba_sql_plan_baselines
2 where sql_text like '%10G%';
SQL_HANDLE SQL_TEXT ENA ACC
------------------------------ -------------------------------------------------------------------------------- --- ---
SQL_1c8e20c5c8fa7f6a select /*10G_STS*/ count(*) from myobjects where object_type='VIEW' YES YES
SQL_388c048277282b1f select /*10G_STS*/ count(*) from myobjects where object_type='SYNONYM' YES YES
SQL_8fca037405ff8ce7 select /*10G_STS*/ count(*) from myobjects where object_type <> 'INDEX' YES YES
SQL_dda6e70a26d74f1b select /*10G_STS*/ count(*) from myobjects where object_type like 'PACKAGE%' YES YES
SQL_f915be3f43387642 select /*10G_STS*/ count(*) from myobjects YES YES
SQL_fc83e34606a4b991 select /*10G_STS*/ count(*) from myobjects where object_type='TABLE' YES NO
SQL_fc83e34606a4b991 select /*10G_STS*/ count(*) from myobjects where object_type='TABLE' YES YES
7 rows selected.
We will manually evolve the new better plan but this process of plan evolvement is done automatically by the optimizer if the parameter optimizer_use_sql_plan_baselines is set to TRUE which is the default anyway.
We can see that a new plan has been chosen because the optimizer feels that it is 224 times better than the original plan!
SQL> SET SERVEROUTPUT ON
SQL> SET LONG 10000
SQL> DECLARE
report clob;
BEGIN
report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(
sql_handle => 'SQL_fc83e34606a4b991');
DBMS_OUTPUT.PUT_LINE(report);
END;
/
2 3 4 5 6 7 8
-------------------------------------------------------------------------------
Evolve SQL Plan
Baseline Report
-------------------------------------------------------------------------------
Inputs:
-------
SQL_HANDLE = SQL_fc83e34606a4b991
PLAN_NAME =
TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
VERIFY = YES
COMMIT =
YES
Plan: SQL_PLAN_gt0z38s3a9fcj44cebc6b
------------------------------------
Plan was verified: Time used 3.027
seconds.
Plan passed performance criterion: 224.68 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): 318.968 8.821
36.16
CPU Time(ms): 320 8.888 36
Buffer Gets:
8256 36 229.33
Physical Read Requests: 0 0
Physical Write
Requests: 0 0
Physical Read Bytes: 0 0
Physical Write Bytes:
0 0
Executions: 1
1
-------------------------------------------------------------------------------
Report Summary
-------------------------------------------------------------------------------
Number of plans verified:
1
Number of plans accepted: 1
PL/SQL procedure successfully completed.
We will now run the same SQL statement again and find that a new SQL Plan Baseline has been chosen which is executing an improved plan as opposed to the original plan.
SQL> select SQL_HANDLE,SQL_TEXT,ENABLED, ACCEPTED from dba_sql_plan_baselines
2 where sql_text like '%10G%';
SQL_HANDLE SQL_TEXT ENA ACC
------------------------------ -------------------------------------------------------------------------------- --- ---
SQL_1c8e20c5c8fa7f6a select /*10G_STS*/ count(*) from myobjects where object_type='VIEW' YES YES
SQL_388c048277282b1f select /*10G_STS*/ count(*) from myobjects where object_type='SYNONYM' YES YES
SQL_8fca037405ff8ce7 select /*10G_STS*/ count(*) from myobjects where object_type <> 'INDEX' YES YES
SQL_dda6e70a26d74f1b select /*10G_STS*/ count(*) from myobjects where object_type like 'PACKAGE%' YES YES
SQL_f915be3f43387642 select /*10G_STS*/ count(*) from myobjects YES YES
SQL_fc83e34606a4b991 select /*10G_STS*/ count(*) from myobjects where object_type='TABLE' YES YES
SQL_fc83e34606a4b991 select /*10G_STS*/ count(*) from myobjects where object_type='TABLE' YES YES
7 rows selected.
SQL> explain plan for
2 select /*10G_STS*/ count(*) from myobjects where object_type='TABLE';
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2605150876
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 46 (3)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 9 | | |
|* 2 | INDEX RANGE SCAN| MYOBJECTS_IND | 15332 | 134K| 46 (3)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_TYPE"='TABLE')
Note
-----
- SQL plan baseline "SQL_PLAN_gt0z38s3a9fcj44cebc6b" used for this statement
4 Comments