In a previous article, I discussed stored outlines and described one mechanism for abusing the system to produce the stored outline that you needed to have. I also pointed out that there was some risk in using this method with Oracle 9, as the details stored in the database had become much more complex. In this follow-up article, I present a legal way of manipulating a stored outline that can be applied both in Oracle 8 and in Oracle 9. Details in this article were based on experiments carried out on default installations of Oracle 8.1.7.0 and Oracle 9.2.0.1.

Review

What are you supposed to do when you know how to make a piece of DML run much more quickly by adding a few hints, but don't have access to the source code to put those hints in the right place ?

In the last article I showed how you might be able to take advantage of stored outlines (also known as plan stability) to get the database engine to do the job for you.

A stored outline consists (loosely speaking) of two components - an SQL statement that you wish to control, and a list of hints that Oracle should apply to that SQL statement whenever it sees it being optimised. Both components are stored in the database in a schema called outln.

We can check the list of stored SQL statements, and the hints that will be attached to them, using a couple of queries like those in figure 1.

select name, used, sql_text
from user_outlines
where category = 'DEFAULT'
;

select stage, node, hint
from user_outline_hints
where name = '{one of the names}'
;

Figure 1: Examining stored outlines.

In the previous article, I introduced the idea of deceiving the system by creating a stored outline using legal methods, and then patching the outln tables by using a couple of SQL statements to swap the actual result for a stored outline you had created for a similar, but hinted, statement.

At the time I mentioned that this was probably safe for Oracle 8, but could lead to problems in Oracle 9 because of changes made in the newer version.

This article examines those changes, and introduces a legal way of getting your preferred set of hints registered in the outln tables against your problem queries.

The Changes

If you connect to the outln schema (which is locked by default in Oracle 9) and list the available tables, you will find that Oracle 9 has one more table than Oracle 8. The tables are:

ol$       The sql
ol$hints  The hints
ol$nodes  The query blocks

Descriptions of all three tables appear in figure 2, with the new columns for Oracle 9 marked by stars.

 ol$

 OL_NAME          VARCHAR2(30)
 SQL_TEXT         LONG
 TEXTLEN          NUMBER
 SIGNATURE        RAW(16)
 HASH_VALUE       NUMBER
 HASH_VALUE2      NUMBER          ***
 CATEGORY         VARCHAR2(30)
 VERSION          VARCHAR2(64)]
 CREATOR          VARCHAR2(30)
 TIMESTAMP        DATE
 FLAGS            NUMBER
 HINTCOUNT        NUMBER
 SPARE1           NUMBER          ***
 SPARE2           VARCHAR2(1000)  ***

 Ol$hints
 
 OL_NAME          VARCHAR2(30)
 HINT#            NUMBER
 CATEGORY         VARCHAR2(30)
 HINT_TYPE        NUMBER
 HINT_TEXT        VARCHAR2(512)
 STAGE#           NUMBER
 NODE#            NUMBER
 TABLE_NAME       VARCHAR2(30)
 TABLE_TIN        NUMBER
 TABLE_POS        NUMBER
 REF_ID           NUMBER          ***
 USER_TABLE_NAME  VARCHAR2(64)    ***
 COST             FLOAT(126)      ***
 CARDINALITY      FLOAT(126)      ***
 BYTES            FLOAT(126)      ***
 HINT_TEXTOFF     NUMBER          ***
 HINT_TEXTLEN     NUMBER          ***
 JOIN_PRED        VARCHAR2(2000)  ***
 SPARE1           NUMBER          ***
 SPARE2           NUMBER          *** 


 ol$nodes (completely new in 9)

 OL_NAME          VARCHAR2(30)
 CATEGORY         VARCHAR2(30)
 NODE_ID          NUMBER
 PARENT_ID        NUMBER
 NODE_TYPE        NUMBER
 NODE_TEXTLEN     NUMBER
 NODE_TEXTOFF     NUMBER

Figure 2: The outln tables.

A couple of details you might notice immediately -- the views defined on top of these tables clearly exclude a lot of useful information. Despite the ten extra columns in ol$hints the view definition for user_outline_hints has not changed. In fact, this view was sadly deficient in Oracle 8, omitting, as it did, the rather informative hint#.

You will also notice that Oracle 9 now has two hash_value columns. If you build identical statements in an Oracle 8 and an Oracle 9 database, you will find that they match on their hash_value, but the Oracle 9 hash_value2 is probably completely different.

You will also find that the signature in Oracle 9 is different from its value in Oracle 8. This is because of a major change in strategy between the two versions aimed at increasing the re-use of stored outlines. Under Oracle 8 you could only use a stored outline if your SQL matched the stored SQL exactly - to the last space, capital, and line-feed. Under Oracle 9, the rules are relaxed, so that a statement is matched after repetitive "white space" is eliminated and the text has been folded to the same case. For example, the following two statements will use the same outline.

select * from t1 where id = 5;

SELECT  *
FROM    T1
WHERE   ID = 5;

This change in strategy results in a change in the signature for the SQL that first generates the plan; and if you upgrade from Oracle 8 to Oracle 9, you will have to regenerate stored outlines or you may find that they don't appear to work any more. (In fact, the package outln_pkg, aliased to dbms_outln, includes a special procedure update_signatures to handle this problem).

The most significant thing about the version 9 tables, however, is the extreme level of detail about the text and objects involved in the query. Create the example shown in figure 3, and take a look at the content of the ol$hints table before reading on.

drop table t1;

create table t1 
nologging
as
select
        rownum         id,
        rownum         n1,
        object_name, 
        rpad('x',500)  padding
from
        all_objects
where 
        rownum <= 100

alter table t1
add constraint t1_pk primary key (id);
 
create index t1_i1 on t1(n1);

analyze table t1 compute statistics;

create or replace outline demo_1 on
select * from t1 
where  id = 5
and    n1 = 10
;

Figure 3: Sample code.

The example is based on a small, simple table, with two identical columns, one defined (and therefore indexed) as a primary key, one with a simple, non-unique index. We generate a stored outline for a typical query and then see what we can do with it.

If we run our sample queries from figure 1 against the demo_1 plan, generated by this example, we find the following six hints attached to the query:

     STAGE  NODE  HINT
         3     1  NO_EXPAND
         3     1  ORDERED
         3     1  NO_FACT(T1)
         3     1  INDEX(T1 T1_PK)
         2     1  NOREWRITE
         1     1  NOREWRITE

As expected, the fourth line shows us that we have used the primary key index (T1_Pk) to access the table. But what could we do about the stored outline if we really wanted Oracle to use the non-unique index T1_I1? Ideally we would like to tweak this stored outline so that the line reading

     3 1 INDEX(T1 T1_PK)

became

     3 1 INDEX(T1 T1_I1)

New Features

The first thing we could do is look at the package dbms_outln_edit. This appeared in Oracle 9 and, as its name suggests, it is a package aimed at editing stored outlines, so this looks promising.

However, describing the package, and checking the manuals, we note that the package contains only the following 'edit-related' procedures:

   CREATE_EDIT_TABLES
   DROP_EDIT_TABLES
   CHANGE_JOIN_POS

The first two procedures allow us to create and drop local copies of the tables normally owned by outln. The third allows us to swap the order of table joins in a stored plan. There is nothing that lets us simply modify a single hint. At present, the package seems to be virtually useless -- but it's bound to become more sophisticated.

Plan B, of course, is to hack ! If we connect as outln, and examine the contents of the ol$hints table (which underpins the user_outline_hints view) we could try the following update:

       update ol$hints
       set 
               hint_text = 'INDEX(T1 T1_I1)'
       where
               ol_name = 'demo_1'
       and hint# = 4
       ;

Connecting back to our test schema, flushing the shared pool, and switching on stored outlines:

       connect test_user/test
       alter   system 
               flush shared_pool;
       alter session 
               set use_stored_outline=true;

we find that the hacked plan does, indeed, work as required. But it's not a comfortable solution given the strict warnings we are usually given about 'updating the data dictionary'.

Old Methods (1)

Our aim, then, is to find a devious, but seemingly innocent method of changing the content of the outline tables without hacking them directly.
Historically (before version 9) we could achieve this in a couple of ways, based on the fact that the effect of an outline was dictated purely by the text of the incoming SQL statement, and not by any consideration of object type or ownership.

One option (originally described, I believe, by Tom Kyte in his book Expert One on One: Oracle) works by replacing tables with hinted views.

Connect to another schema that has access to the T1 table and create a hinted view of the same name with the following definition:

       Create or replace view t1 as
       Select /*+ index(t1,t1_i1) */
               * 
       from test_user.t1;

Once this view is in place, use this schema to 'recompile' the existing outline with the command:

    alter outline demo_1 rebuild;

Note - you need the privilege alter any outline to be able to execute this command.

If we go back to the original schema, flush the shared pool, and switch on stored outlines, we find that our original query now uses the T1_I1 index as required.

Why does this work? Because stored outlines do not belong to a schema. When we rebuilt the outline called demo_1 from the new schema, the name T1 applied to a local view which contained a hint, so Oracle folded the hint into the actual execution plan, and therefore into the outline. Looking at the view user_outline_hints view, we find that the critical line has indeed become

          3 1INDEX(T1 T1_I1)

Unfortunately, we will also note that there are now three lines of the form:

          2 1 NOREWRITE
            1     2  NOREWRITE
            1     1  NOREWRITE

Originally we had only the two lines:

         2  1 NOREWRITE
             1    1  NOREWRITE

We have introduced a hint that applies to ‘Stage 1, Node 2.’ I don't claim to know exactly what this means, but it must relate to the fact that in parsing and optimising the query from the other schema, Oracle has performed an extra step of converting a view reference to a base table reference.

Although, at present, this does not stop the outline from being applied correctly (or so it seems in this simple case) who can say how fussy Oracle might become in future releases.

Old Methods (2)

Because views introduce an anomaly that might turn into an error in a future release, we have to be fussier. So let's try the following:

    Create a new schema.
    Create table T1 in that schema.
    Create ONLY the index T1_I1.
    Rebuild the outline in that schema

If we compare the contents of view user_outline_hints for our outline before and after the rebuild (we have to recconect to the original schema to do so), we will find that they are identical apart from the one line that we wanted to alter. Connecting back to our original schema and doing the usual check of flushing the shared pool and switching on outlines, we find that the modified outline is used.

However there is a hidden threat, this time a little more subtle. Go back to figure 2 with its definitions of the new columns that appear in Oracle 9 - what information do you think is kept in the column user_table_name? It is the qualified table name; i.e.,

{User_name}.{table_name}

In our example this will tell Oracle that table T1 is actually a table belonging to the new schema, not to the original schema. Even though Oracle is using the stored outline, the information in the table is sufficient to tell it that it is applying the plan to the wrong object.

Again, it works at present, but why is the information there -- possibly because of enhancements coming in future releases.

The Safe Bet

It seems that there is only one way to generate a stored outline which doesn't expose you to future risk -- be as honest as possible. Do it in the right schema with the right objects.

In this case, you need to drop the primary key index, generate the plan, and then replace the primary key !

Of course you might not want to do this on a production system, and even if you did it is possible that the outline would switch to a full tablescan.

The bottom line is that you need to have at least one spare copy of the schema (i.e. with the same name) on another database, and then you need to manipulate that copy very carefully to get the outline you need. Once you have the outline, you can export from one database and import it to the other.

For example: on the spare database, it would be okay to drop the primary key to avoid the PK unique scan. If Oracle didn't then take the other index automatically, you can tell all sorts of lies, such as:

• Change the optimizer_mode to first_rows_1.
• Create data that is unique across column N1. (Don't make it a unique index, though, or the generated outline will be a unique scan instead of a range scan).
• Use dbms_stats to say that the index has a fantastic clustering_factor.
• Use optimiser_index_caching to say that the index is 100% cached.
• Use optimiser_index_cost_adj to say that a multiblock read is 100 times as slow as a single block read.
• Use dbms_stats to make the same claim through aux_stats$, and add in the fact that the typical size of a multiblock read is two blocks.
• Rebuild the index to include both the columns in the where clause.

Given the current content of the outline tables, almost anything goes provided that table owners don't change, object types don't change, and indexes don't change their uniqueness. If you can construct a data set and environment that produces an outline that has no internal inconsistencies on the production system, then you can cheat in almost any way you like.

Conclusion

The information that goes into a Stored Outline in Oracle 9 is much subtler than it was in Oracle 8. It used to be quite easy and apparently risk-free to 'adjust' outlines. The methods still work, but the huge volume of extra information collected in Oracle 9 tends to suggest that earlier methods now carry a future risk.

Although Oracle 9 has introduced a package to edit stored outlines, it is currently limited to swapping table orders. Short of using a second system with changed indexes, an altered environment and contrived statistics, it no longer seems safe to tamper with stored outlines.

References

Oracle 9i Release 2: Database Performance Tuning Guide and Reference -- Chapter 7.

Oracle 9I Release 2: Supplied PL/SQL Packages and Types Reference -- Chapters 41 - 42.

--

Jonathan Lewis is a freelance consultant with more than 17 years' experience in Oracle. He specialises in physical database design and the strategic use of the Oracle database engine, is author of Practical Oracle 8i - Building Efficient Databases published by Addison-Wesley, and is one of the best-known speakers on the UK Oracle circuit. Further details of his published papers, tutorials, and seminars can be found at www.jlcomp.demon.co.uk, which also hosts The Co-operative Oracle Users' FAQ for the Oracle-related Usenet newsgroups.