【xplan】创建存储提纲稳定执行计划

创建提纲

1、为该sql创建一个存储提纲

create outline  otln_tb_ctrlscheme for category outline2 on

          <sql>

2、激活该提纲

Alter system set use_stored_outlines= outline2(categorg名)

 

侵入提纲（适用于不能加hint的场景）          

          1、为该sql创建一个存储提供

          create outline  otln_tb_ctrlscheme for category outline2 on

          <sql>

          

          2、为该公共提纲创建一个私有提纲

          create private outline otln_pri from otln_tb_ctrlscheme;

          

          3、创建另一个hint的私有提纲

          create private outline otln_hint on

          <sql>

          

          4、交换两个私有提纲的内容

          update ol$hints

          set ol_name =

              case ol_name

                when 'otln_hint'

                  then 'otln_pri'

                when 'otln_pri'

                   then 'otln_hint'

                else ol_name

                end

           where ol_name in ('otln_hint','otln_pri')

           

           update ol$ ol1

             set  hintcount =

                  (select hintcount

                     from ol$ ol2

                    where ol2.ol_name in ('otln_hint','otln_pri')

                      and ol2.ol_name != ol1.ol_name

                  )

             where ol1.ol_name in ('otln_hint','otln_pri')

             

          5、私有提供复制会共有存储提纲

            create or replace outline otln_tb_ctrlscheme

                   from private otln_pri for category outline2;

          

          6、激活公有提纲

                Alter system set use_stored_outlines= outline2(categorg名)