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.
