OUTLINES

From: <dbaoracleind_at_yahoo.com>
Date: 14 Jul 2006 13:42:54 -0700
Message-ID: <1152909774.835529.281700@m79g2000cwm.googlegroups.com>

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 Received on Fri Jul 14 2006 - 15:42:54 CDT