Tuning SQL Statement Execution in 10g, Part 2

Tuesday I wrote about a client in Houston and removing the RULE hint from queries as part of their 10g migration. This led into a discussion about the four ways to control SQL statement execution: rewriting the SQL, hints, stored outlines and SQL profiles. I then discussed the first two methods – rewriting SQL and using hints – and gave examples of both.

However these first two methods rest on the assumption that you are able to control the SQL statements. If you are using a third-party application where you do not have control over the SQL statements that are generated then these methods will not work. But fortunately Oracle introduced a feature almost 10 years ago in 8i release 1 (8.1.5) that can address this dilemma: stored outlines. Stored outlines are an intriguing feature of Oracle that seem to be rather underutilized by the Oracle community in general. They’re almost a throwback to the pre-oracle days of compiled queries in the sense that you can specify and hardcode exactly how you want a query to be executed.

Let’s have a closer look.

Creating Stored Outlines

The most common usage of stored outlines is to achieve plan stability. In other words, if you have already tuned your environment and want to make sure that nothing changes then you can use stored outlines to “lock” the current plans in place. After enabling the stored outlines the plans will not change when system parameters change, new indexes are added, data distributions and statistics change or anything else happens that might normally cause a plan to change. Stored outlines were originally designed with RBO to CBO transitions in mind; you could store the plans generated by the RBO before turning on the CBO and then incrementally review the effects of removing outlines and letting the CBO optimize statements.

Internally, a stored outline is nothing more than a full set of hints. (That’s why you really need a thorough understanding of hints before you can master stored outlines.) Outlines come in two flavors: PRIVATE and PUBLIC. Public outlines are stored in the OUTLN schema and are activated system-wide (or session-only) by the setting USE_STORED_OUTLINES. Private outlines are primarily intended to be used only temporarily while editing an outline and are activated with the session parameter USE_PRIVATE_OUTLINES.

There are two methods for creating stored outlines: automatic and manual creation. The most straightforward way to create a stored outline is manually with the CREATE OUTLINE statement:

SQL> set autotrace traceonly explain;
SQL> create table test as select * from all_objects;

Table created.

SQL> exec dbms_stats.gather_table_stats('JEREMY','TEST');

PL/SQL procedure successfully completed.

SQL> variable id number;
SQL> exec :id:=1000;

PL/SQL procedure successfully completed.

SQL> select * from test where object_id=:id;

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   464 | 59392 |   160   (3)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| TEST |   464 | 59392 |   160   (3)| 00:00:02 |
--------------------------------------------------------------------------

SQL> create outline on
  2  select * from test where object_id=:id;

Outline created.

SQL> create index test_pk on test(object_id);

Index created.

SQL> select * from test where object_id=:id;

---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |   464 | 59392 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST    |   464 | 59392 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | TEST_PK |   186 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

SQL> alter session set use_stored_outlines=true;

Session altered.

SQL> select * from test where object_id=:id;

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   577 | 73856 |   160   (3)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| TEST |   577 | 73856 |   160   (3)| 00:00:02 |
--------------------------------------------------------------------------

As you can see, when stored outlines were enabled the plan reverted to the original plan that was saved before we created the index. Of course in real life you would want to use the index – but the point of this demo was to show plan stability, meaning that we could control plan changes.

The CREATE OUTLINE statement is convenient for demos, testing and individual statements but there are at least two situations where it does not work well: if you need to stabilize plans for an entire application that includes a large number of statements and if you need to stabilize plans containing literals with the parameter CURSOR_SHARING set to SIMILAR or FORCE. Automatic outline creation can address both of these situations. You only need to set the system or session parameter CREATE_STORED_OUTLINES and Oracle will automatically create and store an outline for every single SQL statement processed.

SESSION 1
============
SQL> alter session set use_stored_outlines=false;

Session altered.

                              SESSION 2
                              ============
                              SQL> alter system set create_stored_outlines=true;

                              System altered.

SESSION 1
============
SQL> select * from test where data_object_id=:id;

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   464 | 59392 |   160   (3)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| TEST |   464 | 59392 |   160   (3)| 00:00:02 |
--------------------------------------------------------------------------

                              SESSION 2
                              ============
                              SQL> alter system set create_stored_outlines=false;

                              System altered.

SESSION 1
============
SQL> create index test_x2 on test(data_object_id);

Index created.

SQL> select * from test where data_object_id=:id;

---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |   464 | 59392 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST    |   464 | 59392 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | TEST_X2 |   186 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

                              SESSION 2
                              ============
                              SQL> alter system set use_stored_outlines=true;

                              System altered.

SESSION 1
============
SQL> select * from test where data_object_id=:id;

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   577 | 73856 |   161   (4)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| TEST |   577 | 73856 |   161   (4)| 00:00:02 |
--------------------------------------------------------------------------

As you can see from the output, the stored outline was automatically created as soon as the SQL statement was executed. Because we enabled outline creation system-wide the SQL could have been executed in any session by any user. One word of caution; Oracle really will create a plan for every SQL statement executed by every user – so be careful! This could generate a lot of outlines! As you can see, after we had created the outline we could enable it system-wide to achieve plan stability. Simple and very effective!

Should You Manipulate Stored Outlines

So far I’ve shown how to nail down a satisfactory execution plan and prevent it from changing. However I started out this article by saying that with outlines “you can specify and hardcode exactly how you want a query to be executed” – and I haven’t yet shown how this might be possible! In my opinion this is what makes stored outlines such an amazing feature: the ability to edit your outline and effectively make the optimizer execute your query in any legal way you can dream up. But my prior warnings are still valid: this a dangerous capability. The optimizer is very good at finding optimal execution paths and you should only override it when you have a good reason for doing so.

Unfortunately there is some sad news here. The Oracle 9i Tuning Pack included perhaps the coolest tool I have seen so far in Oracle: a graphical outline editor. This java program, which was part of OEM, allowed you to choose your access paths and join methods from drop-down lists and see a graphical representation of your plan. But it mysteriously disappeared from 10g and there don’t appear to be any plans to include it again in the future. (haha… “plans”)

There’s one more bit of disheartening news. Usually Oracle is rather good about documenting and exposing API’s which are used by OEM and other graphical tools – so you can also perform these tasks from SQLPlus and from scripts. But in the case of editing outlines there really is not an API – simply a set of tables which need to be edited directly – and the documentation is very sparse. There is one function in the DBMS_OUTLN_EDIT to “change a join position” but it doesn’t even seem to work in 10g. (It seems to update the TABLE_POS field in OL$HINTS – and can apparently only make the number smaller but not bigger. In 10g the LEADING hint specifies the table order and this field seems to be ignored by the optimizer… but I could easily be missing something.) The performance tuning guide’s example of editing an outline alludes to directly editing the table ( “if you want to change join order, modify the appropriate LEADING hint” ) but gives no examples or anything else helpful. If you trace the 9i Outline Editor you will see that this is exactly what it’s doing: issuing DML against the user’s private outline tables.

So can you edit these tables yourself to specify exactly what plan you want to use? This seems to be a fuzzy area regarding official support from Oracle. As I just pointed out the 10gR2 manual specified that you should modify the LEADING hint to change the join order – so presumably Oracle would support this sort of manipulation. (Although clearly this section of the manual is severely out of date since it still references the OEM Outline Editor which is no longer available in 10g.) However metalink note 122812.1 seems to indicate in its related documents footer that the manipulating stored outlines as demonstrated in note 92201.1 is not supported. So the moral of the story is that you should be very cautious and careful if you choose to manipulate stored outlines; this is clearly a very powerful capability but it’s unclear how much help you will get from Oracle support if you run into problems.

How To Manipulate Stored Outlines

Directly changing system tables in Oracle is always a delicate enterprise. Jonathan Lewis’ article about Faking Stored Outlines concluded that in 9i it was no longer safe to tamper much with Stored Outlines. Perhaps an examination of the trace files from the OEM Outline Editor could yield a wealth of information about safely editing these files. Regardless, I will leave a discussion about more complicated edits for another time and in this article I will only demonstrate a simple manipulation like that in metalink note 144194.1.

To get started, lets create an additional table and a stored outline:

SQL> alter system set use_stored_outlines=false;

System altered.

SQL> create table lookup as
  2  select object_type, max(object_name) name, count(*) data
  3  from test group by object_type;

Table created.

SQL> create index test_fk on test(object_type);

Index created.

SQL> exec dbms_stats.gather_table_stats('JEREMY','LOOKUP');

PL/SQL procedure successfully completed.

SQL> set autotrace traceonly explain;
SQL> select * from test t, lookup l where t.object_type=l.object_type;

-----------------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        | 46394 |  7656K|   164   (4)| 00:00:02 |
|*  1 |  HASH JOIN         |        | 46394 |  7656K|   164   (4)| 00:00:02 |
|   2 |   TABLE ACCESS FULL| LOOKUP |    36 |  1476 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| TEST   | 46394 |  5799K|   160   (3)| 00:00:02 |
-----------------------------------------------------------------------------

SQL> create private outline test2ol on
  2  select * from test t, lookup l where t.object_type=l.object_type;

Outline created.

SQL> set autotrace off
SQL> select ol_name, hint#, hint_text from ol$hints order by ol_name,hint#;

OL_NAME         HINT# HINT_TEXT
---------- ---------- -------------------------------------------------------
TEST2OL             1 USE_HASH(@"SEL$1" "T"@"SEL$1")
TEST2OL             2 LEADING(@"SEL$1" "L"@"SEL$1" "T"@"SEL$1")
TEST2OL             3 FULL(@"SEL$1" "T"@"SEL$1")
TEST2OL             4 FULL(@"SEL$1" "L"@"SEL$1")
TEST2OL             5 OUTLINE_LEAF(@"SEL$1")
TEST2OL             6 ALL_ROWS
TEST2OL             7 OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
TEST2OL             8 IGNORE_OPTIM_EMBEDDED_HINTS

8 rows selected.

Suppose we want to make two changes to the plan. First I want to switch to a nested loop join and second I want to use the index-based access path into the table TEST. Following the example from metalink all I need to do is make two quick updates to the OL$HINTS table:

SQL> update ol$hints set hint_text='USE_NL(@"SEL$1" "T"@"SEL$1")'
  2  where ol_name='TEST2OL' and hint#=1;

1 row updated.

SQL> update ol$hints set hint_text='INDEX(@"SEL$1" "T"@"SEL$1" ("TEST"."OBJECT_TYPE"))'
  2  where ol_name='TEST2OL' and hint#=3;

1 row updated.

SQL> commit;

Commit complete.

SQL> select ol_name, hint#, hint_text from ol$hints order by ol_name,hint#;

OL_NAME         HINT# HINT_TEXT
---------- ---------- -------------------------------------------------------
TEST2OL             1 USE_NL(@"SEL$1" "T"@"SEL$1")
TEST2OL             2 LEADING(@"SEL$1" "L"@"SEL$1" "T"@"SEL$1")
TEST2OL             3 INDEX(@"SEL$1" "T"@"SEL$1" ("TEST"."OBJECT_TYPE"))
TEST2OL             4 FULL(@"SEL$1" "L"@"SEL$1")
TEST2OL             5 OUTLINE_LEAF(@"SEL$1")
TEST2OL             6 ALL_ROWS
TEST2OL             7 OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
TEST2OL             8 IGNORE_OPTIM_EMBEDDED_HINTS

8 rows selected.

SQL> alter session set use_private_outlines=true;

Session altered.

SQL> set autotrace traceonly explain;
SQL> select * from test t, lookup l where t.object_type=l.object_type;

---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         | 49838 |  6035K|  2196   (1)| 00:00:27 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST    |  1424 |   129K|    83   (0)| 00:00:01 |
|   2 |   NESTED LOOPS              |         | 49838 |  6035K|  2196   (1)| 00:00:27 |
|   3 |    TABLE ACCESS FULL        | LOOKUP  |    35 |  1085 |     3   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN         | TEST_FK |  1917 |       |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Shazam! We just specified and hardcoded exactly how we wanted the query to be executed. However there are two important notes about my example: first, a FIRST_ROWS goal would have been a much much better way to create this outline. (I.e. set the optimizer goal to FIRST_ROWS before creating the outline.) Second, as Jonathan Lewis keenly points out in his paper, there are quite a few additional fields on the OL$HINTS table that appear to have some interdependencies with the HINT_TEXT field. Just because a metalink note gives an example like this doesn’t mean it’s a great idea. Granted: it seems to work on Oracle 10.2.0.1.0 where I tested it – but we have definitely introduced a few internal inconsistencies by directly editing these tables. Use this method with tremendous care.

The Future of Stored Outlines

I can’t quite make out the future of stored outlines. The sorely lacking DBMS_OUTLN_EDIT package has hardly been updated since 9i and I haven’t yet found any mention of outlines in 10g OEM. The fact that the Outline Manager/Editor was not included in 10g also seems to indicate that Oracle is not continuing to develop this feature. In metalink note 277066.1 (Overview Comparison of EM 9i to EM10g Features) it is the only database management component which is not included in 10g.

Nonetheless, there are no indications that stored outlines will be desupported in the near future. (Oracle does often give advanced warning about features that are going to be desupported.) If this is a feature that could be useful to you then you should take full advantage of it!

Resources

• Oracle Performance Tuning Guide: Chapter 18 – Using Plan Stability
• Metalink Note 144194.1: Editing Stored Outlines in Oracle9i – an example
• Metalink Note 92202.1: How to specify hidden hints on SQL statements (unsupported)
• PSOUG Outlines Reference
• Jonathan Lewis’ articles about plan stability in 8i, faking stored outlines in oracle 9i, and hacking outlines
• Dan Bikle’s stored outlines article/demo
• Tim Hall’s article on oracle-base
• Harald van Breederode’s Tips Presentation slides
• Chris Foot’s article Optimizer Plan Stability in Oracle9i (includes screenshots of 9i OEM Outline Editor)