public stored in schema of outln.

dic table: ol$, ol$hints

key knowledge:

signature column of ol$.

It is similar as sql hash value but with some different characteristic:

1, space char and line-feed char are not including to calculated signature.

2, Upper case and lower case of a character get the same signature value.

---- optimize sql by outline method guide.

1, create current sql outline .

2, tune sql code until be satisfied with the performance. Some skill can used in this stage: 1>use sql hints, 2>change system parameter 3> change table (and/or index ) statistics.4> add additional join predictor(such as col1 is not null). ...

3, create outline for the well-tuned sql.

4, exchange ol_name column in ol$ of the two sql . For example:

sql1 with outline ol1.

After tuned. sql2 with outline ol2.

then now do the following to exchange the sql outline .

/* ol_name : ol1 --> ol2 , ol2 --> ol1 */

update ol$ set ol_name = decode(ol_name,'OL1','OL2','OL2','OL1',ol_name) where ol_name in ('OL1','OL2') ;

5, Let outline Take effect.

then to take use of the created outline . You must configure session or system envirtonment .Like following:

alter session set use_stored_outlines=true ;

alter system set use_stored_outlines=true ;

Actually, you have some alternative choice like :

alter session set use_stored_outlines=true/false/my_category_name ;

alter system set use_stored_outlines=true/false/my_category_name ;

alter session set use_private_stored_outlines=true/false/my_category_name ;

alter system set use_private_stored_outlines=true/false/my_category_name ;

6, Now , past the sql1 (before tuned) , lookup the execution plan .Check which outline it is used .

----- some reasons for unexpected using outline result.

1, you do not configure the environment by designating use_stored_outlines or use_private_stored_outlines .

2, You designate the outline paramter which in effect at session level, but you check-up the sql explain plan in another session. If you really do want outline take effect in all sessions, configure the outline paramter at system level again.

3, the sql statement you issued in create outline have different signature as which you check-up it's execution plan. Note that following condition will not use the outline. 1> new sql have different tab alias. 2> new sql have different join representation order ( a>b & b

-----------

[@more@]