News
Perth, Australia
+ (61) 417713124
prosolutions@gavinsoorma.com

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

Mohammed Yousuf
  • Feb 17 2011
Hi GS, Hope you are doing well, Its really great to see your detail implementation plan and really excellent observation. One question, are you conducting Oracle database performance tunning classes.. if yes please let me know. Thanks ================== Mohammed Y DBA Manager IBM, Bangalore moyousuf@in.ibm.com
Bala
  • Mar 14 2011
Excellent Article...well
MT
  • May 22 2011
Awesome doc ..
Ramachandra
  • May 30 2013
Great Article

Leave Reply

Your email address will not be published. Required fields are marked *