Re: OUTLINES

dbaoracleind_at_yahoo.com wrote:
> 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

alter session set use_stored_outlines=my_test;

You should start using your stored outlines.

David Fitzjarrell Received on Fri Jul 14 2006 - 16:45:41 CDT