Tweaking Stored Outlines - Pontis Corporation

Home > Technical Articles > Tweaking Stored Outlines

Tweaking stored outlines.

by Paul Tabashov

Quite often DBAs are put into situations where their ability to tune application's SQL is limited due to the fact that the source code is not available and not easy to modify. Application developers are not always eager to follow the comments from DBAs and even if they do acknowledge the performance problem, it takes the next release of application to address the issues.
In this article I will tell how you can actually alter the execution plan of the SQL statement without having to change application code.

First, some background information. Since Oracle 8 the stored outlines feature, also known as optimizer stability, is available to "stabilize" the execution plans by saving the sets of hints that are used by optimizer when executing the particular statement.
All the information on stored outlines is stored in OUTLN schema in two tables: OL$ and OL$HINTS. We will work with them to make the things happen. Below is the DESCRIBE listing of them:

SQL> desc ol$ Name Null? Type ----------------------------------------- -------- ---------------------------- 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) SQL> desc ol$hints Name Null? Type ----------------------------------------- -------- ---------------------------- 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

Basically, the OL$ table holds a "header" information about the stored outline, such as the name, category, information to identify which statement outline applies to and some other info. There is one row per each outline in this table. The OL$HINTS table has one row per each hint that Oracle saves for this stored outline. Tables are "joined" on two columns - OL_NAME and OL_CATEGORY. Data in this table actually defines the plan that will be applied to the according SQL statement.

So, with this background information, let's face the situation described in my previous article . What we have is the statement that uses select from a collection type and because of wrong cardinality assumption optimizer uses wrong plan. Here is the statement:

SELECT a.data, b.data2
FROM table1 a,
      table2 b,
      (SELECT /*+ NO_MERGE */ column_value
       FROM TABLE (CAST (coll_test_type (1, 2, 3) AS coll_test_type)) d) c
WHERE a.id1 = c.column_value and a.id2=b.id2;

As we figured out in the previous article , we can hint optimizer with proper cardinality value like this:

SELECT a.data, b.data2
FROM table1 a,
      table2 b,
      (SELECT /*+ NO_MERGE CARDINALITY(d 3) */ column_value
       FROM TABLE (CAST (coll_test_type (1, 2, 3) AS coll_test_type)) d) c
WHERE a.id1 = c.column_value and a.id2=b.id2;

But in our case we do not have access to the statement code (it's in wrapped package), so what we will do is following:

Create a stored outline for original statement by running our wrapped package (Oultine 1)

Create stored outline for hinted statement (Outline 2)

"Swap" the hints between Outline 1 and Outline 2
This way we will get the outline for our original statement that will have the hints that will make it use execution plan as if it was hinted as we need.
Let's start with creating the stored outline for our SQL "hidden" in wrapped package pkg_wrapped in procedure proc1.

SQL> alter session set create_stored_outlines=test_outln;

System altered.

SQL> execute wrapped_pkg.proc1;

PL/SQL procedure successfully completed.

SQL> alter session set create_stored_outlines=false;

System altered.

Please note, that in order to create stored outline, you need to grant CREATE ANY OUTLINE privilege directly (not through role) to owner of wrapped_pkg. In the script above we've created the stored outline in TEST_OUTLN category. Let's what we have in OL$ table:

SQL> set long 4000
SQL> select ol_name, sql_text from outln.ol$ where category='TEST_OUTLN'; OL_NAME
------------------------------
SQL_TEXT
--------------------------------------------------------------------------------
SYS_OUTLINE_031028174923638
SELECT a.data, b.data2 FROM table1 a, table2 b,
(SELECT /*+ NO_MERGE */ column_value
FROM TABLE (CAST (coll_test_type (1, 2, 3) AS coll_test_type))) c
WHERE a.id1 = c.column_value and a.id2=b.id2

SQL> spool off

As you see, Oracle has created the outline named SYS_OUTLINE_031028174923638 for our statement. Usually you will have stored outlines created for all statements executed in the procedure tahtyou call. My test procedure just executes the SQL we are interested in. Using the statement from above you can see what outlines are created and whch one is the one you are looking for. You might want to remove all unnecessary outlines:

delete outln.ol$ where category='TEST_OUTLN' and ol_name not in ('SYS_OUTLINE_031028174923638');
delete outln.ol$hints where category='TEST_OUTLN' and ol_name not in ('SYS_OUTLINE_031028174923638');

Now let's create the outline for a hinted version of the statement:

SQL> alter session set create_stored_outlines=test_outln;

Session altered.

SQL> SELECT a.data, b.data2 FROM table1 a, table2 b,
2 (SELECT /*+ NO_MERGE CARDINALITY(d 5)*/ column_value
3 FROM TABLE (CAST (coll_test_type (1, 2, 3) AS coll_test_type)) d) c
4 WHERE a.id1 = c.column_value and a.id2=b.id2;

420 rows selected.

SQL> alter session set create_stored_outlines=false;

Session altered.

The new stored outline is name SYS_OUTLINE_031028201739552. Now we only need to replace the hints of original outline with newly created one:

SQL> delete ol$hints where ol_name='SYS_OUTLINE_031028174923638';

22 rows deleted.

SQL> update ol$hints set ol_name='SYS_OUTLINE_031028174923638' where ol_name='SYS_OUTLINE_031028201739552';

21 rows updated.

SQL> commit;

Commit complete.

SQL> spool off

As you can see, the number of hints is different and we need to correct this in OL$ table:

SQL> select ol_name, hintcount from ol$ where category='TEST_OUTLN'; OL_NAME HINTCOUNT ------------------------------ ---------- SYS_OUTLINE_031028174923638 22 SYS_OUTLINE_031028201739552 21 SQL> update ol$ set hintcount=21 where ol_name='SYS_OUTLINE_031028174923638'; 1 row updated. SQL> commit; Commit complete. SQL> spool off

Now everything is ready for test if the outline will be used when we turn the usage of outlines on:

SQL> select ol_name, flags from ol$;
OL_NAME FLAGS ------------------------------ ---------- SYS_OUTLINE_031028174923638 0 SYS_OUTLINE_031028201739552 0 SQL> connect scott/tiger Connected. SQL> alter session set use_stored_outlines=test_outln; Session altered. SQL> execute wrapped_pkg.proc1; PL/SQL procedure successfully completed. SQL> connect outln/outln Connected. SQL> select ol_name, flags from ol$; OL_NAME FLAGS ------------------------------ ---------- SYS_OUTLINE_031028174923638 1 SYS_OUTLINE_031028201739552 0

The FLAGS column in OL$ table is set to 1 when outline is used for the first time. It corresponds to USED column of DBA_OUTLINES view. The last thing to check is actually peek at V$SQL_PLAN and see if the plan is the one that we wanted. Since in Oracle 8i there is no this view, the only way you can be sure that the plan is the one that you want is to look if the statistics of execution of the statement have changed the way we wanted.

This method is actually described in article 92202.1 on Metalink, although we came to it in our own work. I am not sure that Oracle will say that this is a supported method, but all I can say that we did use it and it does help a lot when you just can't change SQL statement directly. Hope this article helps you in your work and feel free to ask me any questions.

View Responses (522)

Post Response