Re: OUTLINES
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- Date: Fri, 14 Jul 2006 22:39:04 +0100
<dbaoracleind@xxxxxxxxx> wrote in message
news:1152909774.835529.281700@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Hi
I am testing the outlines in a simple table select scenario and am
obviously missing something.
The test case is to make the optimizer run with the rule hint when the
default behavior is cost. I just used this as a test case so that the
same can be done for adding an index hint.
Thanks in advance for your valuable time and inputs,
The steps I followed are as follows:
Issues :
1. Created an outline for a normal plan as cbo_sql;
1a. Ran the query again. But the status of the outline cbo_sql is
unused.
2. Created an outline for a hinted query as rbo_sql;
3. Swapped the ol$hints for cbo_sql with rbo_sql(plan with rule hint)
4. The optimizer is not using the outline even after setting the
use_stored_outlines=true at session level.
The steps are outlined below:
Parameter details at session level:
cursor_sharing string SIMILAR
query_rewrite_enabled string TRUE
star_transformation_enabled string TRUE
optimizer_mode string CHOOSE
optimizer_features_enable string 9.2.0
-- Original Query Plan ( created an outline as CBO_SQL for this )
set autotrace on explain;
select e.ename from emp e,dept d where e.deptno=d.deptno;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=7 Card=14 Bytes=168)
1 0 MERGE JOIN (Cost=7 Card=14 Bytes=168)
2 1 SORT (JOIN) (Cost=4 Card=4 Bytes=12)
3 2 TABLE ACCESS (FULL) OF 'DEPT' (Cost=3 Card=4 Bytes=12)
4 1 SORT (JOIN) (Cost=4 Card=14 Bytes=126)
5 4 TABLE ACCESS (FULL) OF 'EMP' (Cost=3 Card=14 Bytes=126)
select /*+ rule */ e.ename from emp e,dept d where e.deptno=d.deptno (
Create an outline rbo_sql for this hinted query)
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: RULE
1 0 MERGE JOIN
2 1 SORT (JOIN)
3 2 TABLE ACCESS (FULL) OF 'DEPT'
4 1 SORT (JOIN)
5 4 TABLE ACCESS (FULL) OF 'EMP'
-- Create outline
create or replace outline cbo_sql for category MY_TEST
on
select e.ename from emp e,dept d where e.deptno=d.deptno;
select * from user_outlines;
CBO_SQL MY_TEST UNUSED 14-JUL-06 9.2.0.7.0 select e.ename from emp
e,dept d where e.deptno=d.deptno
select ol_name, hint#, category, hint_text from outln.ol$hints;
CBO_SQL 2 MY_TEST PQ_DISTRIBUTE(E NONE NONE)
CBO_SQL 3 MY_TEST USE_MERGE(E)
CBO_SQL 4 MY_TEST ORDERED
CBO_SQL 5 MY_TEST NO_FACT(E)
CBO_SQL 6 MY_TEST NO_FACT(D)
CBO_SQL 7 MY_TEST FULL(E)
CBO_SQL 8 MY_TEST FULL(D)
CBO_SQL 9 MY_TEST NOREWRITE
CBO_SQL 10 MY_TEST NOREWRITE
CBO_SQL 1 MY_TEST NO_EXPAND
alter session set use_stored_outlines=true;
select e.ename from emp e,dept d where e.deptno=d.deptno;
select * from user_outlines;
CBO_SQL MY_TEST UNUSED 14-JUL-06 9.2.0.7.0 select e.ename from emp
e,dept d where e.deptno=d.deptno
The outline is still in unused stage ? why is that ?
Create another outline with some hint for eg RULE hint in this case.
create or replace outline rbo_sql for category MY_TEST
on
select /*+ rule */ e.ename from emp e,dept d where e.deptno=d.deptno
select * from user_outlines;
RBO_SQL MY_TEST UNUSED 14-JUL-06 9.2.0.7.0 select /*+ rule */ e.ename
from emp e,dept d where e.deptno=d.deptno
CBO_SQL MY_TEST UNUSED 14-JUL-06 9.2.0.7.0 select e.ename from emp
e,dept d where e.deptno=d.deptno
select ol_name, hint#, category, hint_text from outln.ol$hints where
ol_name = 'RBO_SQL';
RBO_SQL 1 MY_TEST NO_EXPAND
RBO_SQL 2 MY_TEST USE_MERGE(E)
RBO_SQL 3 MY_TEST ORDERED
RBO_SQL 4 MY_TEST NO_FACT(E)
RBO_SQL 5 MY_TEST NO_FACT(D)
RBO_SQL 6 MY_TEST FULL(E)
RBO_SQL 7 MY_TEST FULL(D)
RBO_SQL 8 MY_TEST NOREWRITE
RBO_SQL 9 MY_TEST NOREWRITE
RBO_SQL 10 MY_TEST RULE
Now to swap the CBO_SQL outline for RBO_SQL so that the query uses the
rule hint.
1 UPDATE OUTLN.OL$HINTS
2 SET OL_NAME =
3 DECODE(OL_NAME,'RBO_SQL','CBO_SQL','CBO_SQL','RBO_SQL')
4* Where OL_NAME IN ('RBO_SQL','CBO_SQL')
SQL> /
20 rows updated.
select ol_name, hint#, category, hint_text from outln.ol$hints where
ol_name = 'RBO_SQL';
RBO_SQL 1 MY_TEST NO_EXPAND
RBO_SQL 2 MY_TEST PQ_DISTRIBUTE(E NONE NONE)
RBO_SQL 3 MY_TEST USE_MERGE(E)
RBO_SQL 4 MY_TEST ORDERED
RBO_SQL 5 MY_TEST NO_FACT(E)
RBO_SQL 6 MY_TEST NO_FACT(D)
RBO_SQL 7 MY_TEST FULL(E)
RBO_SQL 8 MY_TEST FULL(D)
RBO_SQL 9 MY_TEST NOREWRITE
RBO_SQL 10 MY_TEST NOREWRITE
select ol_name, hint#, category, hint_text from outln.ol$hints where
ol_name = 'CBO_SQL';
CBO_SQL 1 MY_TEST NO_EXPAND
CBO_SQL 2 MY_TEST USE_MERGE(E)
CBO_SQL 3 MY_TEST ORDERED
CBO_SQL 4 MY_TEST NO_FACT(E)
CBO_SQL 5 MY_TEST NO_FACT(D)
CBO_SQL 6 MY_TEST FULL(E)
CBO_SQL 7 MY_TEST FULL(D)
CBO_SQL 8 MY_TEST NOREWRITE
CBO_SQL 9 MY_TEST NOREWRITE
CBO_SQL 10 MY_TEST RULE
Sam
You have cursor_sharing = similar.
When you create the outline, the stored SQL text is literally
as supplied. When you run the statement, it is first rewritten
to change any constants to things like ":SYS_B1" - so it no
longer matches the text stored in the database.
You have to work to CAPTURE the outline if you want to
use stored outlines with cursor_sharing enabled; or you
have to work out / discover the actual text optimized, and
"create stored outline" for that text somehow.
--
Regards
Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
.
- Follow-Ups:
- Re: OUTLINES
- From: Mladen Gogala
- Re: OUTLINES
- References:
- OUTLINES
- From: dbaoracleind@xxxxxxxxx
- OUTLINES
- Prev by Date: Re: how to know target and source db size in 9i
- Next by Date: Re: OUTLINES
- Previous by thread: OUTLINES
- Next by thread: Re: OUTLINES
- Index(es):
Relevant Pages
|
