執行計劃大剛OUTLINE的管理

10G,LINUX as3

[@more@]

更改最佳化器
alter session set optimizer_boal=choose;－－first_rows

建立OUTLINE,看執行計劃，如果計劃合適，就為SQL建立OUTLINE
create or replace outline myoutline
for category mycategory
on
select empno ,ename from emp where empno>0;
或者
alter session set create_stored_outlines=true;
alter session set create_stored_outlines=false;

-----注意，以上兩種方式產生的OUTLINE有區別： 用例子來說明。

讓SESSION使用OUTLINE
alter session set use_stored_outline=mycategory;
alter session set use_stored_outline=false;

重建OUTLINE
select * from user_outlines;
alter session set optimizer_boal=first_rows;
alter outline outline_name rebuild;

檢視大剛所使用到的索引
select * from dba_outline_hints ;

讓OUTLINE用上HINT
create or replace view dept as
select /*+ use_hash(dept) */ from scott.dept
create or replace view emp as
select /*+ use_hash(emp) */ * from scott.emp

create or replace outline my_outline
for category my_category
on select * from emp,dept where emp.deptno=dept.deptnot;

讓使用者登陸後就用OUTLINE
create or replace trigger hyf_logon
after logon on database
begin
if (user='HYF') then
execute immediate 'alter session set use_stored_outlines=hyfcategory';
endif;
end;

大剛的改名
alter outline my_outline rename to NEW_NAME;

改變類別
alter outline outline_name chang category to new_category_name;
exec outln_pkg.update_by_cat(old_category_name,new_category_name);
用於把一個類別併入另一個類別

刪除大剛
drop outline outline_name;
exec outln_pkg.drop_unused; --刪除所有使用過的大剛
exec outln_pkg.drop_by_cat('categoryname'); --按類別刪除
－－注意，DROP USER 不刪除大剛

CURSOR_CHARING=FORCE與大剛
SQL> alter session set cursor_sharing=force;

Session altered.

SQL> select * from Test2 where object_name='CON$';
OWNER OBJECT_NAME
-------------- -------------------
SYS CON$

SQL> create or replace outline my_outline1
2>for category mycat1
3>on
4>select * from Test2 where object_name='CON$' ;

Outline created.

SQL> alter session set create_stored_outlines=true; --隱式產生大剛

Session altered.

SQL> select * from Test2 where object_name='CON$' ;

OWNER OBJECT_NAME
-------------- -------------------
SYS CON$

SQL> alter session set create_stored_outlines=flase;

Session altered.

SQL> select name,category,sql_text sql from user_outlines;

NAME CATEGORY SQL
---------------------------- ------------ -------------------------------------
MY_OUTLINE1 MYCAT1 select * from Test2 where object_name='CON$'
SYS_OUTLINE_08040812260903806 DEFAULT select * from Test2 where object_name=:"SYS_B_0"

在這裡我們看到，兩種方式產生的OUTLINE不一樣，用ALTER SESSION方式產生的OUTLINE使用了繫結變數;