由于SQL都封装在package之中,在v$sql中抓到性能较差的SQL如下所示：
select count(*)
from sales_account a,sales_detail b
where a.billnumber = b.billnumber and a.billnumber like '%'||:b10||'%'
and b.realname like '%'||:b9||'%' and b.buyicard like '%'||:b8||'%'
and a.status like '%'||:b7||'%' and a.confirm like ''||:b6||'%' and a.paytype like '%'||:b5||'%'
and a.statustime between to_date(:b4,'yyyy-mm-dd hh24:mi:ss')
and to_date(:b3,'yyyy-mm-dd hh24:mi:ss')
and b.is_sended like '%'||:b2||'%' and a.order_id like '%'||:b1||'%'
该SQL的物理读较少,但逻辑读却很大(buffer_gets/executions=40,0000),sales_account表与sales_detail表都有23万条左右的记录,通过分析发现,只要该SQL使用/*+ use_hash(a,b) */只性能会好很多,逻辑读可以降到40,000左右吧.
create or replace outline ol_sales on
select count(*)
from sales_account a,sales_detail b
where a.billnumber = b.billnumber and a.billnumber like '%'||:b10||'%'
and b.realname like '%'||:b9||'%' and b.buyicard like '%'||:b8||'%'
and a.status like '%'||:b7||'%' and a.confirm like ''||:b6||'%' and a.paytype like '%'||:b5||'%'
and a.statustime between to_date(:b4,'yyyy-mm-dd hh24:mi:ss')
and to_date(:b3,'yyyy-mm-dd hh24:mi:ss')
and b.is_sended like '%'||:b2||'%' and a.order_id like '%'||:b1||'%'
创建完毕,还需要对该SQL产生的outline做少许处理,现在进入outln模式,在9i以后的版本中,outln模式默认被安装,在使用outln模式时需要先解除outln用户的锁定状态,然后就可以登陆到outnl模式对ol_sales outline进行处理啦.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
OL$ TABLE
OL$HINTS TABLE
OL$NODES TABLE
老实讲,outline确实很好用,但也简单了点,这三个关于outline的表记录了outlnie的一些必须的属性,比如偶想知道什么时间oracle的优化器使用了该outline？总共使用了多少次该等在ol$/ol$hints/ol$nodes表却找不到答案！
如果在其它模式中,用户可以通过user/all/dba_outlines、user/all/dba_outline_hints等字典来查看关于outline的情况,比如在user_outlines中记录了outline是否被使用,创建时间等等;在user_outline_hints中可以查看该outline的hints等。
SQL> select name,used from user_outlines;
NAME USED
------------------------------------------------------ ---------
OL_SALES USED
SQL> select ol_name,sql_text from ol$;
OL_NAME SQL_TEXT
------------------------------ ---------------------------------
OL_SALES SELECT count(*) FROM SALES_ACCOUNT ......
SQL> select ol_name,hint#,hint_text from ol$hints;
OL_NAME HINT# HINT_TEXT
------------------------------ ---------- ------------------------
OL_SALES 3 USE_HASH(A)
其实新创建一个outline时,oracle会生产6个hints,如果你确定使用某个hints后,你可以将其余的5个hints删除,然后将留下的一个hints更改为你指定的hints却可,针对OL_SALES这个outline来讲,偶将留下的一个hints更改为use_hash(a)就可以啦,oracle的优化器引擎会为这些hints添加/*+ */的提示。
SQL> create or replace outline a on select * from dual;
Outline created.
SQL> select ol_name,hint#,hint_text from ol$hints order by ol_name,hint#;
OL_NAME HINT# HINT_TEXT
------------------------------ ---------- --------------------------------
A 1 NO_EXPAND
A 2 ORDERED
A 3 NO_FACT(DUAL)
A 4 FULL(DUAL)
A 5 NOREWRITE
A 6 NOREWRITE
这时还需要更新ol$表中的hintcount字段,告诉优化器该outline共有多少个hints.outln用category来管理outline,你可以创建不同的category,如果你在创建outln时没有为outline指定category的话,将被oracle放到default category中. 创建触发器后,每个新建会话将会使用存储的outline,如果你在创建outline时没有为outline指定category,就用default即可.
SQL> select ol_name,category from ol$;
OL_NAME CATEGORY
------------------------------ ------------------------------
OL_SALES DEFAULT
OL_SALES_2 DEFAULT
创建触发器的脚本：
create or replace trigger tri_after_login_on_database
after logon on database
begin
if (user = 'CYBERCAFE') or (user = 'CARDSALE') then
execute immediate 'alter session set use_stored_outlines = DEFAULT';
end if;
end;
http://www.cnoug.org/viewthread.php?tid=27598
这是偶在测试outline时提的一些简单问题,如果你没能在system级将cursor_sharing设置为force或similar,创建outline时不会适应bind variable的,如果你的SQL没有以bind variable的形式提供,那就没能办法使用outline.
select count(*) from test where id = 123
如偶所示的SQL,如果cursor_sharing=EXACT的话,无论你如何创建outline，你都没有办法使用你新创建的outline的,如果你的SQL是以bind variable的形式,寻你无须修改系统参数cursor_sharing即可使用outline.
SQL> show parameter cursor_sharing
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing string EXACT
关于使用outline的文章网上也有一些,但在业务系统上正式应用outline的却不多见,偶维护的一个系统是购买的网上交易平台,程序员将所有的业务处理都封装在pagckage之中,在24x7的系统中,修改与维护package就变得比较困难,偶决定通过使用outline来提高系统的性能。
