 |
|
Create a Stored Outline for the Hinted Query
Oracle Tips by Burleson Consulting |
In the next step, we create a new execution
plan for the modified SQL statement. After creating this stored
outline, we can then swap the stored outlines, replacing the
original execution plan with our hinted execution plan.
create or
replace outline
rbo_sql
on
select /*+ rule */
dname,
loc,
sum(sal)
from
emp,
dept
where
emp.deptno(+) = dept.deptno
and
dept.deptno = 10
group by
dname,
loc
;
Now, let's run outline.sql and see the
new execution plan:
SQL>
@outline rbo_sql
SQL_TEXT
--------------------------------------------------------------------
select /*+ rule */
dname,
loc,
sum(sal)
from
emp,
dept
where
emp.deptno(+) = dept.deptno
and
dept.deptno = 10
group by
dname,
loc
HINT
HINT# TABLE_TIN STAGE#
----------------------------------- ---------- ---------- ----------
NOREWRITE 9
0 1
RULE
10 0
1
NOREWRITE
8 0
2
NO_EXPAND
1 0
3
USE_NL(EMP)
2 1
3
ORDERED
3 0
3
NO_FACT(EMP)
4 1
3
NO_FACT(DEPT)
5 2
3
INDEX(EMP DEPT_DEPT)
6 1
3
FULL(DEPT)
7 2
3
Here we see that we have the stored outline for the
original query and another stored outline for our tuned query. We
are now ready to swap the stored outlines for these two queries so
that the original query will use the improved stored outline.
Swap the Stored Outlines
The final
step in the tuning is to swap the desired rbo_sql outline
with our original cbo_sql outline. We will do this by running
the swap_outlines.sql script. It will prompt you for the name
of the original stored outline and the name of the improved stored
outline. It will then swap the outlines between the two statements,
transferring our desired execution plan to the original SQL
statement (Figure 13-2).
Figure 2: Swapping a
stored outline
swap_outlines.sql
set echo
off;
set feedback off;
prompt Enter the old outline name:
accept old
prompt Enter the new outline name:
accept new
update
outln.ol$hints
set
ol_name=decode(ol_name,upper('&new'),
upper('&old'),upper('&old'),upper('&new'))
where
ol_name IN (upper('&new'),upper('&old'));
commit;
prompt Here is the new outline for
&old
@outline &old@outline &old
Here is the listing from swap_outlines.sql.
Note that it displays the original SQL source, but with our improved
execution plan:
SQL> @swap_outlines
Enter the old outline name:
cbo_sql
Enter the new outline name:
rbo_sql
Here is the new outline for cbo_sql
SQL_TEXT
------------------------------------------------------------------------
select
dname,
loc,
sum(sal)
from
emp,
dept
where
emp.deptno(+)
HINT
HINT# TABLE_TIN STAGE#
----------------------------------- ---------- ---------- ----------
NOREWRITE
9 0
1
RULE
10 0
1
NOREWRITE
8 0
2
NO_EXPAND
1 0
3
USE_NL(EMP)
2 1
3
ORDERED
3 0
3
NO_FACT(EMP)
4 1
3
NO_FACT(DEPT)
5 2
3
INDEX(EMP DEPT_DEPT)
6 1
3
FULL(DEPT)
7 2
3
Now that we understand how stored outlines
are used to improve query performance, and how to tune a query using
stored outlines, let’s move to a higher level and examine how you
can manage stored outlines for your whole database.
This is an excerpt from "Oracle High-Performance SQL Tuning" by
Donald K. Burleson, published by Oracle Press.
