 |
|
Managing Stored Outlines
Oracle Tips by Burleson Consulting |
Because the stored outline feature is new
with Oracle8i, there are only a few rudimentary views and
procedures to aid in the management of SQL stored outlines. As
Oracle continues to enhance the functionality of optimizer plan
stability, these tools will grow more robust.
Oracle provides two dictionary structures to
aid in stored outline management, the Remote DBA_OUTLINES view and the
ol$hints table. Oracle also provides the outline package to aid
in the categorization and management of stored outlines.
Using the Dictionary Views and Tables for Stored Outlines
Oracle provides several views to help display
stored outlines, most notably the Remote DBA_OUTLINES view and the
ol$hints table. Together, these structures will tell you
everything you need to know about the status of stored outlines in
your database. Here are the columns in the Remote DBA_OUTLINES view:
SQL> desc
Remote DBA_outlines;
Name
Null? Type
----------------------------------------- -------- ----------------
NAME
VARCHAR2(30)
OWNER
VARCHAR2(30)
CATEGORY
VARCHAR2(30)
USED
VARCHAR2(9)
TIMESTAMP
DATE
VERSION
VARCHAR2(64)
SQL_TEXT
LONG
We also have a Remote DBA_OUTLINE_HINTS view, but it
does not contain as much useful information as ol$hints, and
it is seldom used because it lacks detail about the sequence of the
stored outline steps.
SQL> desc
Remote DBA_outline_hints;
Name
Null? Type
----------------------------------------- --------
------------------
NAME VARCHAR2(30)
OWNER
VARCHAR2(30)
NODE
NUMBER
STAGE
NUMBER
JOIN_POS
NUMBER
HINT
VARCHAR2(512)
To see the stored execution plans for a SQL
statement, you must reference the outln.ol$hints view. Note
that we created a public synonym for ol$hints to make it
easily accessible by all users:
SQL> create
public synonym ol$hints for outln.ol$hints;
Synonym created.
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
Once a stored outline is used, the used
column in Remote DBA_OUTLINES will change. Here we execute the original
query and check to see if the stored outline was used:
SQL> select
* from Remote DBA_outlines where name = ‘CBO_SQL’;
NAME
OWNER
------------------------------ ------------------------------
CATEGORY
USED TIMESTAMP
------------------------------ --------- ---------
VERSION
----------------------------------------------------------------
SQL_TEXT
---------------------------------------------------------------
CBO_SQL
OPS$ORACLE
DEFAULT
USED 14-APR-01
8.1.6.1.0
select
dname,
loc,
sum(sal)
from
emp,
dept
where
emp.deptno(+)
Here we can run a query to see our automatically
generated outlines, as evidenced by their names in the for
SYS_OUTLINE-nnn.
SQL> select
* from Remote DBA_outlines
2 where name like ‘SYS_OUTLINE%’;
NAME
OWNER
------------------------------ ------------------------------
CATEGORY
USED TIMESTAMP
------------------------------ --------- ---------
VERSION
----------------------------------------------------------------
SQL_TEXT
---------------------------------------------------------------------
SYS_OUTLINE_0104142013050001 READER
DEFAULT
UNUSED 14-APR-01
8.1.6.1.0
SELECT ATTRIBUTE,SCOPE,NUMERIC_VALUE,CHAR_VALUE,DATE_VALUE FROM
SYSTEM.PRODUCT_P
SYS_OUTLINE_0104142013050002 READER
DEFAULT
UNUSED 14-APR-01
8.1.6.1.0
SELECT CHAR_VALUE FROM SYSTEM.PRODUCT_PRIVS WHERE (UPPER('SQL*Plus')
LIKE UPPE
SYS_OUTLINE_0104142013050003
READER
DEFAULT
UNUSED 14-APR-01
8.1.6.1.0
SELECT DECODE('A','A','1','2') FROM DUAL
SYS_OUTLINE_0104142013270004 READER
DEFAULT
USED 14-APR-01
8.1.6.1.0
select nbr_pages from book
Next, let’s look at how the outline package
is used to help us manage our stored outlines.
Using the Outline Package
The outline package contains several
procedures that can help us manage our stored outlines. These
procedures allow us to drop, categorize, and manage all stored
outlines. The outline package contains the following
functions and stored procedures.
-
Procedures drop_collision,
drop_extras, drop_unrefd_hints, drop_unused, update_by_cat
-
Functions drop_collision_expact,
drop_extras_expact, drop_unrefd_hints_expact
Let's begin by looking at a procedure to identify and
drop unused stored outlines.
Identify and Drop Unused Stored Outlines
One of the problems with systems that
generate SQL with literal values is that there will be a huge number
of nonreusable SQL statements. In these cases, cursor_sharing=force
should be set to rewrite the SQL to replace the literals with host
variables, thereby making the SQL reusable.
For example, cursor_sharing will
transform this statement, removing the literal and replacing it with
a host variable:
select *
from customer where cust_name = ‘JONES’;
into a
reusable form:
select *
from customer where cust_name = :var1;
Oracle provides a procedure for dropping
stored outlines that are not reused. Let’s begin by running a query
to see if our database has SQL statements that have never been
reused.
SQL> set
long 1000
SQL> select * from Remote DBA_outlines where used='UNUSED';
NAME OWNER
CATEGORY USED TIMESTAMP VERSION
SQL_TEXT
------------ ------ ---------- --------- ---------- -------------
TEST_OUTLINE SYSTEM TEST UNUSED 08-MAY-99 8.1.3.0.0
select a.table_name,
b.tablespace_name,
c.file_name from
Remote DBA_tables a,
Remote DBA_tablespaces b,
Remote DBA_data_files c
where
a.tablespace_name =
b.tablespace_name
and b.tablespace_name
= c.tablespace_name
and c.file_id =
(select
min(d.file_id) from
Remote DBA_data_files d
where
c.tablespace_name =
d.tablespace_name)
Now, we are ready to drop any stored outlines that
have not been used. We do this by running the drop_unused
procedure in the outline package.
SQL>
execute outline_pkg.drop_unused;
PL/SQL procedure successfully completed.
SQL> select * from Remote DBA_outlines where used='UNUSED';
no rows selected
Remember, there is no recovery for this
procedure, so you should always make sure that you no longer want
the unused stored outlines before running drop_unused.
This is an excerpt from "Oracle High-Performance SQL Tuning" by
Donald K. Burleson, published by Oracle Press.
