Optimizer Plan Stability

Using Optimizer Plan stability we can preserve our existing execution plans, and isolate out applications from these plan changes. It should be noted that in most cases its desirable that plan changes over the time due to the distribution of data changes. A Quick example to demonstrate about Optimizer plan stability.

scott@9iR2> select * from v$version;

BANNER
-------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
PL/SQL Release 9.2.0.8.0 - Production
CORE    9.2.0.8.0       Production
TNS for 32-bit Windows: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production

scott@9iR2> create table emp as select * from scott.emp;

Table created.

scott@9iR2> alter table emp add constraint emp_pk primary key(empno);

Table altered.

scott@9iR2> set autotrace traceonly explain;

scott@9iR2> select * from emp where empno > 0;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
   2    1     INDEX (RANGE SCAN) OF 'EMP_PK' (UNIQUE)

scott@9iR2> set autotrace off;

In the absence of Statistics  Rule Based Optimizer (RBO) is Invoked. lets assume this query comes from the application, where the end user would like to get some data very quickly, and the index access does this nicely for us. we are happy with this plan and we would like to always use this plan. So the next thing to do is to Create an Outline for it.

scott@9iR2> create or replace outline myoutline for category mycategory
  2  on
  3  select * from emp where empno > 0;

Outline created.

This created the Query outline, and defines our required execution plan. Now let's simply analyze our table.

scott@9iR2> begin
  2  dbms_stats.gather_table_stats(ownname=>USER,tabname=>'EMP');
  3  end;
  4  /

PL/SQL procedure successfully completed.

scott@9iR2> set autotrace traceonly explain;
scott@9iR2> select * from emp where empno > 0;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=14 Bytes=518)
   1    0   TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=14 Bytes=518)

Instead of using the Index as with RBO, we now have the statistics to allow the CBO to be invoked and it chooses Full Table scan. The CBO infact chooses the correct plan. There are only 14 rows and it understands that all of them statisfy the perdicate in this case. In order to get back to our preferred plan we need to use the Optimizer Plan Stability feature, To do that we need to issue the below command.

scott@9iR2> alter session set use_stored_outlines=mycategory;

Session altered.

This enforces use of our MYCATEGORY stored outline. If we take a look at our execution plan:

scott@9iR2> select * from emp where empno > 0 ;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=826 Card=14 Bytes=518)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=826 Card=14 Bytes=518)
   2    1     INDEX (RANGE SCAN) OF 'EMP_PK' (UNIQUE) (Cost=26 Card=14)

scott@9iR2> set autotrace off;

We find that we are back to using the original plan with the index again. This is the goal of optimizer plan stability