Loading....

One of the critical aspects of any database upgrade is to ensure optimizer plan stability where tried and tested execution plans are not altered in any way by the Oracle optimizer after a database upgrade has taken place.

One of the 11g new features which is SQL Plan Management and SQL Plan baselines enables us to ‘lock in’ proven execution plans and ensure that the optimizer only chooses different execution plans to those which have been baselined if they are found to offer better performance as compared to existing plans.

In the example below, we will see how execution plans can potentially change after an upgrade to 11g and how by using SQL Plan Management feature of 11g, we can ensure that only existing 10g plans are still used even after the upgrade.

In the 10g database, the optimizer_mode parameter has been set to RULE and we see for this particular SQL statement, the Hash Join method is being used.

SELECT
     S.cust_id
	,C.cust_last_name
    ,S.prod_id
	,P.prod_name
    ,S.amount_sold
    ,S.quantity_sold
  FROM
     sh.sales S
    ,sh.customers C
    ,sh.products P
 WHERE S.cust_id = C.cust_id
   AND S.prod_id = P.prod_id
   AND S.prod_id BETWEEN 40 AND 60
   AND S.cust_id BETWEEN 13000 AND 15000;


SQL>  select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------
Plan hash value: 2383008347

------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                |   223 | 13380 |   685   (1)| 00:00:09 |
|*  1 |  HASH JOIN                           |                |   223 | 13380 |   685   (1)| 00:00:09 |
|*  2 |   HASH JOIN                          |                |   451 | 21197 |   356   (1)| 00:00:05 |
|   3 |    TABLE ACCESS BY INDEX ROWID       | PRODUCTS       |    13 |   390 |     2   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN                 | PRODUCTS_PK    |    13 |       |     1   (0)| 00:00:01 |
|   5 |    PARTITION RANGE ALL               |                |  2563 | 43571 |   353   (0)| 00:00:05 |
|   6 |     TABLE ACCESS BY LOCAL INDEX ROWID| SALES          |  2563 | 43571 |   353   (0)| 00:00:05 |
|   7 |      BITMAP CONVERSION TO ROWIDS     |                |       |       |            |          |
|   8 |       BITMAP AND                     |                |       |       |            |          |
|   9 |        BITMAP MERGE                  |                |       |       |            |          |
|* 10 |         BITMAP INDEX RANGE SCAN      | SALES_PROD_BIX |       |       |            |          |
|  11 |        BITMAP MERGE                  |                |       |       |            |          |
|* 12 |         BITMAP INDEX RANGE SCAN      | SALES_CUST_BIX |       |       |            |          |
|* 13 |   TABLE ACCESS FULL                  | CUSTOMERS      |  1064 | 13832 |   328   (1)| 00:00:04 |
------------------------------------------------------------------------------------------------------

In Oracle 11g database, we will now be changing the optimizer_mode parameter to FIRST_ROWS instead of RULE. Note, now that this change will also also cause the optimizer to change its execution plan and use NESTED LOOPS join method over HASH JOIN

SQL> alter session set optimizer_mode=first_rows;

Session altered.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------
Plan hash value: 2456584415

-------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                |   223 | 13380 |  2394   (1)| 00:00:29 |
|   1 |  NESTED LOOPS                        |                |   223 | 13380 |  2394   (1)| 00:00:29 |
|   2 |   NESTED LOOPS                       |                |   451 | 21197 |  1943   (1)| 00:00:24 |
|   3 |    TABLE ACCESS BY INDEX ROWID       | PRODUCTS       |    13 |   390 |     2   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN                 | PRODUCTS_PK    |    13 |       |     1   (0)| 00:00:01 |
|   5 |    PARTITION RANGE ALL               |                |    36 |   612 |  1943   (1)| 00:00:24 |
|   6 |     TABLE ACCESS BY LOCAL INDEX ROWID| SALES          |    36 |   612 |  1943   (1)| 00:00:24 |
|   7 |      BITMAP CONVERSION TO ROWIDS     |                |       |       |            |          |
|   8 |       BITMAP AND                     |                |       |       |            |          |
|*  9 |        BITMAP INDEX SINGLE VALUE     | SALES_PROD_BIX |       |       |            |          |
|  10 |        BITMAP MERGE                  |                |       |       |            |          |
|* 11 |         BITMAP INDEX RANGE SCAN      | SALES_CUST_BIX |       |       |            |          |
|  12 |   TABLE ACCESS BY INDEX ROWID        | CUSTOMERS      |     1 |    13 |     1   (0)| 00:00:01 |
|* 13 |    INDEX UNIQUE SCAN                 | CUSTOMERS_PK   |     1 |       |     0   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------

So we need to ensure now that all the 10g execution plans get carried over to 11g database and do not get altered in any way unless they have been verified and accepted.

Create a SQL Tuning Set in the Oracle 10g database

SQL> BEGIN
SYS.DBMS_SQLTUNE.CREATE_SQLSET (
sqlset_name => 'SPM_STS',
description => '10g plans');
END;
/

PL/SQL procedure successfully completed.

Populate the SQL Tuning Set with the required SQL statements – in this case we are loading all SQL statements from the cursor cache belonging to the schema SH

SQL> DECLARE
  2  stscur dbms_sqltune.sqlset_cursor;
  3  BEGIN
  4  OPEN stscur FOR
  5  SELECT VALUE(P)
  6  FROM TABLE(dbms_sqltune.select_cursor_cache(
  7  'parsing_schema_name = ''SH''',
  8  null, null, null, null, 1, null, 'ALL')) P;
  9  -- populate the sqlset
 10  dbms_sqltune.load_sqlset(sqlset_name => 'SPM_STS',
 11  populate_cursor => stscur);
 12  END;
 13  /

PL/SQL procedure successfully completed.

At this point the database has now been upgraded to Oracle 11g and the optimizer_mode parameter changed to FIRST_ROWS from RULE.

SQL> show parameter optimizer_mode

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode                       string      FIRST_ROWS

We will now load all the plans from the SQL tuning set we created in 10g into the SQL Plan Management Base in 11g.

SQL> variable cnt number

SQL> execute :cnt := DBMS_SPM.LOAD_PLANS_FROM_SQLSET( -
sqlset_name => 'SPM_STS');>

PL/SQL procedure successfully completed.

We now run the same SQL statement and find that even though the optimizer_mode parameter has been changed from 10g, the execution plan that is being chosen by the optimizer is the very same plan that existed in Oracle 10g and Hash Join method is still being used.

We can also see that the optimizer has used a SQL Plan baseline to execute this statement

SQL> SELECT * FROM table(dbms_xplan.display(null, null, 'basic +note'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------
Plan hash value: 2383008347

---------------------------------------------------------------
| Id  | Operation                            | Name           |
---------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                |
|   1 |  HASH JOIN                           |                |
|   2 |   HASH JOIN                          |                |
|   3 |    TABLE ACCESS BY INDEX ROWID       | PRODUCTS       |
|   4 |     INDEX RANGE SCAN                 | PRODUCTS_PK    |
|   5 |    PARTITION RANGE ALL               |                |
|   6 |     TABLE ACCESS BY LOCAL INDEX ROWID| SALES          |
|   7 |      BITMAP CONVERSION TO ROWIDS     |                |
|   8 |       BITMAP AND                     |                |
|   9 |        BITMAP MERGE                  |                |
|  10 |         BITMAP INDEX RANGE SCAN      | SALES_PROD_BIX |
|  11 |        BITMAP MERGE                  |                |
|  12 |         BITMAP INDEX RANGE SCAN      | SALES_CUST_BIX |
|  13 |   TABLE ACCESS FULL                  | CUSTOMERS      |
---------------------------------------------------------------

Note
—–
SQL plan baseline “SQL_PLAN_7zx6j5vpunkjm07e0351f” used for this statement

Please follow and like us:

Last Update: May 30, 2020  

January 25, 2010 28 Gavin Soorma
Total 0 Votes:
0

Tell us how can we improve this post?

+ = Verify Human or Spambot ?

Add A Knowledge Base Question !

You will receive an email when your question will be answered.

+ = Verify Human or Spambot ?

, , , ,

Leave a Comment

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

*
*

Social media & sharing icons powered by UltimatelySocial
Back To Top

Add A Knowledge Base Question !

You will receive an email when your question will be answered.

+ = Verify Human or Spambot ?