Plan Stability in Oracle 8i/9i

Find out how you can use "stored outlines" to improve the performance of an application even when you can't touch the source code, change the indexing, or fiddle with the configuration.. by Jonathan Lewis (jonathan@jlcomp.demon.co.uk) Toolbox: For the purposes of experimentation, this article restricts itself to simple SQL and PL/SQL code running from an SQL*Plus session. The reader will need to have some privileges that a typical end-user would not normally be given, but otherwise need only be familiar with basic SQL. The article starts with Oracle 8i, but moves on to Oracle 9I, where several enhancements have appeared in the generation and handling of stored outlines. The back door to the Black Box.
If you are a DBA responsible for a 3rd party application running on an Oracle database, you are sure to have experienced the frustration of finding a few extremely slow and costly SQL statements in the library_cache that would be really easy to tune - if only you could add a few hints to the source code. Starting from Oracle 8.1 you no longer need to rewrite the SQL to add the hints - you can make hints happen without touching the code. This feature is known as Stored Outlines, or Plan Stability, and the concept is simple: you store information in the database that says: "if you see an SQL statement that looks like XXX then insert these hints in the following places". This actually gives you three possible benefits. First of all, you can optimize that handful of expensive statements. Secondly, if there are other statements that Oracle takes a long time to optimize (rather than execute), you can save time and reduce contention in the optimization stage. Finally it gives you an option for using the new cursor_sharing parameter without paying the penalty of losing optimum execution paths. There are a few issues to work around in Oracle 8 (largely eliminated in Oracle 9), but in general it is very easy to take advantage of this feature; and this article describes some of the things you can do. Background / Overview To demonstrate how to make best use of stored outlines, we will start with a stored procedure with untouchable source code that (in theory) is running some extremely inefficient SQL. We will see how we can trap the SQL and details of its current execution path in the database, find some hints to improve the performance of the SQL, then make Oracle use our hints whenever that SQL statement is run in the future. In this demonstration, we will create a user, create a table in that user's schema, and create a procedure to access that table - but just for fun we will use the wrap utility on the procedure so that we can't reverse-engineer the code. We will then set ourselves the task of tuning the SQL executed by that procedure. The demonstration will assume that the stored outline infrastructure was installed automatically at database creation time. Preliminary Setup Create a user with the privileges: create session, create table, create procedure, create any outline, and alter session. Connect as this user and run the following script to create a table: create table so_demo ( n1 number, n2 number, v1 varchar2(10) ) ; insert into so_demo values (1,1,'One'); create index sd_i1 on so_demo(n1); create index sd_i2 on so_demo(n2); analyze table so_demo compute statistics; Now you need the code to create a procedure to access this table. Create a script called c_proc.sql containing the following: create or replace procedure get_value ( i_n1 in number, i_n2 in number, io_v1 out varchar2 ) as begin select v1 into io_v1 from so_demo where n1 = i_n1 and n2 = i_n2 ; end; / You could simply execute this script to build the procedure of course but, just for effect, go to the operating system prompt and issue the command: wrap iname=c_proc.sql The response should be: Processing c_proc.sql to c_proc.plb Instead of executing the c_proc.sql script to generate the procedure, execute the incomprehensible c_proc.plb script and you will find that there is no trace of our target SQL statement anywhere in the user_source view. What does the application want to do? Now that we have our pretend application we can run it, perhaps with sql_trace switched on, to see what happens. It won't be a great surprise to discover that the SQL performs a full tablescan to get the required data. In this little test, a full tablescan is probably the most efficient thing to do - but let us assume that we have proved that we get the best performance when Oracle uses an execution path that combines our single column indexes using the and-equal option. How can we make this happen without hinting the code ? With stored outlines, the answer is simple. There are actually several ways to achieve what I am about to do, so don't take this example as the definitive strategy. Oracle is always improving features to make life easier, and the mechanism described here will no doubt become obsolete in a future release. What do you want the application to do ? There are three stages to making Oracle do what we want:  Start a new session and re-run the procedure, first telling Oracle that we want it to trap each incoming SQL statement, along with information about the path that the SQL took. These "paths" are our first example of stored outlines.  Create better stored outlines for any problem SQL statements, and "exchange" the bad stored outlines with the good ones.  Start a new session and tell Oracle to start using the new stored outlines instead of using normal optimization methods when next it sees matching SQL; then run the procedure again. We have to keep stopping and starting new sessions to ensure that existing cursors are not kept open by the pl/sql cache. Stored outlines are only generated and/or applied when a cursor is parsed, so we have to make sure that pre-existing similar cursors are closed. So start a session, and issue the command: alter session set create_stored_outlines = demo; Then run a little anonymous block to execute the procedure, for example: declare m_value varchar2(10); begin get_value(1, 1, m_value); end; / Then stop collecting execution paths (otherwise the next few bits of SQL that you run will also end up in the stored outline tables, making things harder to follow). alter session set create_stored_outlines = false; To see the results of our activity, we can query the views that allow us to see details of the outlines that Oracle has created and stored for us: select name, category, used, sql_text from user_outines where category = 'DEMO'; NAME CATEGORY USED ------------------------------ ------------------------------ --------- SQL_TEXT -------------------------------------------------------------------------------- SYS_OUTLINE_020503165427311 DEMO UNUSED SELECT V1 FROM SO_DEMO WHERE N1 = :b1 AND N2 = :b2 select name, stage, hint from user_outline_hints where name = ' SYS_OUTLINE_020503165427311'; NAME STAGE HINT ------------------------------ ---------- -------------------------------- SYS_OUTLINE_020503165427311 3 NO_EXPAND SYS_OUTLINE_020503165427311 3 ORDERED SYS_OUTLINE_020503165427311 3 NO_FACT(SO_DEMO) SYS_OUTLINE_020503165427311 3 FULL(SO_DEMO) SYS_OUTLINE_020503165427311 2 NOREWRITE SYS_OUTLINE_020503165427311 1 NOREWRITE We can see that there is a category named demo that has only one stored outline, and looking at the sql_text for that outline we can see something that is similar to, but not quite identical to, the SQL that exists in our original PL/SQL source. This is an important point as Oracle will only spot an opportunity to use a stored outline if the stored sql_text is a very close match to the SQL it is about to execute. In fact under Oracle 8i the SQL has to be an exact match, and this was initially a big issue when experimenting with stored outlines. You can see from the listing that stored outlines are just a set of hints that describe the actions Oracle took (or will take) when it runs the SQL. This plan uses a full tablescan - and doesn't Oracle use a lot of hints to ensure the execution of something as simple as a full tablescan. Notice that a stored outline always belongs to a category; in this case the demo category, which we specified in our initial alter session command. If our original command had simply specified true rather than demo we would have found our stored outline in a category named default. Stored outlines also have names, and the names have to be unique across the entire database. No two outlines can have the same name, even if different users generated them. In fact outlines do not have owners they only have creators. If you create a stored outline which happens to match a piece of SQL that I subsequently execute, then Oracle will apply your list of hints to my text - even if those hints are meaningless in the context of my schema. (This gives us a couple of completely different options for faking stored outlines but that's another article). You may note that when Oracle is automatically generating stored outlines, the names have a simple format that includes a timestamp to the nearest millisecond. Moving on with the process of "tuning" our problem SQL, we decide that if we can inject the hint /*+ and_equal(so_demo, sd_i1, sd_i2) */ Oracle will use the execution path we want, so we now explicitly create a stored outline as follows: create or replace outline so_fix for category demo on select /*+ and_equal(so_demo, sd_i1, sd_i2) */ v1 from so_demo where n1 = 1 and n2 = 2; This creates an explicitly named stored outline called so_fix in our demo category. We can see what the stored outline looks like by repeating our queries against user_outlines and user_outline_hints, with the predicate name = 'SO_FIX'. NAME CATEGORY USED ------------------------------ ------------------------------ --------- SQL_TEXT -------------------------------------------------------------------------------- SO_FIX DEMO UNUSED select /*+ and_equal(so_demo, sd_i1, sd_i2) */ v1 from so_demo where n1 = 1 and n2 = 2 NAME STAGE HINT ------------------------------ ---------- -------------------------------- SO_FIX 3 NO_EXPAND SO_FIX 3 ORDERED SO_FIX 3 NO_FACT(SO_DEMO) SO_FIX 3 AND_EQUAL(SO_DEMO SD_I1 SD_I2) SO_FIX 2 NOREWRITE SO_FIX 1 NOREWRITE Note, in particular that the line FULL(SO_DEMO) has been replaced with a line AND_EQUAL(SO_DEMO SD_I1 SD_I2), which is what we wanted to see. And now we have to "swap" the two stored outlines over. We want Oracle to use our new hint list whenever it sees the original text; and to do this, we have to cheat. The views user_outlines and user_outline_hints are generated from two tables (ol$ and ol$hints respectively) owned by the schema outln, and we are going to have to modify these tables directly; which means connecting to the database as outln, or using an account with the privilege to update the tables. Fortunately, the outln tables do not have any enabled referential integrity constraints. Conveniently the relationship between the ol$ (outlines) table and the ol$hints (hints) table is defined by the name of the outline (stored in column ol_name). So, checking names extremely carefully, we can exchange hints between stored outlines by swapping names on the ol$hints table, as follows: update outln.ol$hints set ol_name = decode( ol_name, 'SO_FIX','SYS_OUTLINE_020503165427311', 'SYS_OUTLINE_020503165427311','SO_FIX' ) where ol_name in ('SYS_OUTLINE_020503165427311','SO_FIX') ; You may feel a little uncomfortable with hacking something which is so close to the Oracle kernel, especially given the comments in the manuals - but this update is actually sanctioned in Metalink Note: 92202.1 Dated 5th June 2000. However, the note fails to mention that you may also need to do a second update to ensure that the numbers of hints associated with each stored outline stays consistent. If you fail to do this, you may find that some of your stored outlines get damaged or destroyed on an export/import cycle. update outln.ol$ ol1 set hintcount = ( select hintcount from ol$ ol2 where ol2.ol_name in ('SYS_OUTLINE_020503165427311',' SO_FIX') and ol2.ol_name != ol1.ol_name ) where ol1.ol_name in ('SYS_OUTLINE_020503165427311','SO_FIX') ; Once the exchange is complete you can connect to a new session, tell it to start using stored outlines, re-run the procedure and exit; again using sql_trace to check what Oracle actually does with the SQL. The mechanism to tell Oracle to use the (hacked) stored outline is the command: alter session set use_stored_outline = demo; Examining the trace file, you should find that the SQL now uses the and_equal path. (If you use tkprof to process and explain the trace file you could well find that the output shows two contradictory paths. The first, correct, path should show the and_equal that took place, and the second path will probably show a full tablescan because the stored outline may not be invoked as tkprof runs explain plan against the traced SQL). From Development to Production. Now that we have managed to create a single outline, we need to transfer it into the production environment. There are numerous little features of stored outlines that help us. For example, we could rename the stored outline, export it from development, import it to the production system, check that it still works properly on production in a 'test' category, and then move it into the production category. Useful commands are: alter outline SYS_OUTLINE_020503165427311 rename to AND_EQUAL_SAMPLE; alter outline AND_EQUAL_SAMPLE change category to PROD_CAT;