最近需要优化一个系统，由于多种原因不能修改原代码，故用outline来进行优化。
statspack上找出性能底下的SQL，这里以其中一个优化举例如何使用outline
--原SQL，效率极低
select it.TAG_ID tag_id,count(it.TAG_ID) count_Tag_id
from item_tag it
where it.ITEM_ID in (
select item.ITEM_ID
from Item item
where item.ITEM_TYPE ='p'
and item.ITEM_ID in (
select it.ITEM_ID from item_tag
it where it.TAG_ID in
(82652,82687,71118) group by
it.ITEM_ID
having count(it.ITEM_ID)=3
)
and trim(item.ITEM_STATE) = 'PU'
)
group by it. TAG_ID;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5251 Card=5242 Bytes
=99598)
1 0 SORT (GROUP BY) (Cost=5251 Card=5242 Bytes=99598)
2 1 HASH JOIN (SEMI) (Cost=5226 Card=5242 Bytes=99598)
3 2 HASH JOIN (Cost=4430 Card=5943 Bytes=89145)
4 3 TABLE ACCESS (FULL) OF 'ITEM' (Cost=3637 Card=1423 B
ytes=9961)
5 3 INDEX (FAST FULL SCAN) OF 'UN_ITEM_TAG_TAGID_ITEMID'
(UNIQUE) (Cost=728 Card=1958813 Bytes=15670504)
6 2 VIEW OF 'VW_NSO_1' (Cost=793 Card=1255 Bytes=5020)
7 6 FILTER
8 7 SORT (GROUP BY) (Cost=793 Card=1255 Bytes=10040)
9 8 INLIST ITERATOR
10 9 INDEX (RANGE SCAN) OF 'UN_ITEM_TAG_TAGID_ITEMI
D' (UNIQUE) (Cost=545 Card=140676 Bytes=1125408)
Statistics
----------------------------------------------------------
0 recursive calls
5 db block gets
45878 consistent gets
49667 physical reads
0 redo size
1905 bytes sent via SQL*Net to client
558 bytes received via SQL*Net from client
7 SQL*Net roundtrips to/from client
1 sorts (memory)
1 sorts (disk)
79 rows processed
--优化后
select it.TAG_ID tag_id,count(it.TAG_ID) count_Tag_id
from item_tag it
where it.ITEM_ID in (
select /*+ index(item) */item.ITEM_ID
from Item item
where item.ITEM_TYPE ='p'
and item.ITEM_ID in (
select it.ITEM_ID from item_tag
it where it.TAG_ID in
(82652,82687,71118) group by
it.ITEM_ID
having count(it.ITEM_ID)=3
)
and trim(item.ITEM_STATE) = 'PU'
)
group by it. TAG_ID;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5847 Card=5242 Bytes
=99598)
1 0 SORT (GROUP BY) (Cost=5847 Card=5242 Bytes=99598)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'ITEM_TAG' (Cost=3 Card
=4 Bytes=32)
3 2 NESTED LOOPS (Cost=5822 Card=5242 Bytes=99598)
4 3 NESTED LOOPS (Cost=2057 Card=1255 Bytes=13805)
5 4 VIEW OF 'VW_NSO_1' (Cost=793 Card=1255 Bytes=5020)
6 5 FILTER
7 6 SORT (GROUP BY) (Cost=793 Card=1255 Bytes=1004
0)
8 7 INLIST ITERATOR
9 8 INDEX (RANGE SCAN) OF 'UN_ITEM_TAG_TAGID_I
TEMID' (UNIQUE) (Cost=545 Card=140676 Bytes=1125408)
10 4 TABLE ACCESS (BY INDEX ROWID) OF 'ITEM' (Cost=2 Ca
rd=1 Bytes=7)
11 10 INDEX (RANGE SCAN) OF 'PK_ITEM' (UNIQUE) (Cost=1
Card=1)
12 3 INDEX (RANGE SCAN) OF 'IDX_ITEM_TAG_ITEMID' (NON-UNI
QUE) (Cost=2 Card=4)
Statistics
----------------------------------------------------------
0 recursive calls
5 db block gets
961 consistent gets
846 physical reads
0 redo size
1905 bytes sent via SQL*Net to client
558 bytes received via SQL*Net from client
7 SQL*Net roundtrips to/from client
1 sorts (memory)
1 sorts (disk)
79 rows processed
--建立效率较低的sql的outline
CREATE OUTLINE tag_no_index FOR
category souchang_outline on
select it.TAG_ID tag_id,count(it.TAG_ID) count_Tag_id
from item_tag it
where it.ITEM_ID in (
select item.ITEM_ID
from Item item
where item.ITEM_TYPE ='p'
and item.ITEM_ID in (
select it.ITEM_ID from item_tag
it where it.TAG_ID in
(82652,82687,71118) group by
it.ITEM_ID
having count(it.ITEM_ID)=3
)
and trim(item.ITEM_STATE) = 'PU'
)
group by it. TAG_ID;
--建立效率较高的SQL的outline
CREATE OUTLINE tag_index FOR
category souchang_outline on
select it.TAG_ID tag_id,count(it.TAG_ID) count_Tag_id
from item_tag it
where it.ITEM_ID in (
select /*+ index(item) */item.ITEM_ID
from Item item
where item.ITEM_TYPE ='p'
and item.ITEM_ID in (
select it.ITEM_ID from item_tag
it where it.TAG_ID in
(82652,82687,71118) group by
it.ITEM_ID
having count(it.ITEM_ID)=3
)
and trim(item.ITEM_STATE) = 'PU'
)
group by it. TAG_ID;
update outln.ol$hints
set ol_name = decode( ol_name,
upper('tag_no_index'),
upper('tag_index'),
upper('tag_index'),
upper('tag_no_index')
)
where ol_name in (upper('tag_no_index'),upper('tag_index'));
;
update outln.ol$ ol1
set hintcount = (
select hintcount
from ol$ ol2
where ol2.ol_name in (upper('tag_no_index'),upper('tag_index'))
and ol2.ol_name != ol1.ol_name
)
where
ol1.ol_name in (upper('tag_no_index'),upper('tag_index'))
;
--使用outline
SQL> alter session set use_stored_outlines =souchang_outline;
重新执行原来慢的SQL
select it.TAG_ID tag_id,count(it.TAG_ID) count_Tag_id
from item_tag it
where it.ITEM_ID in (
select item.ITEM_ID
from Item item
where item.ITEM_TYPE ='p'
and item.ITEM_ID in (
select it.ITEM_ID from item_tag
it where it.TAG_ID in
(82652,82687,71118) group by
it.ITEM_ID
having count(it.ITEM_ID)=3
)
and trim(item.ITEM_STATE) = 'PU'
)
group by it. TAG_ID;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5847 Card=5242 Bytes
=99598)
1 0 SORT (GROUP BY) (Cost=5847 Card=5242 Bytes=99598)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'ITEM_TAG' (Cost=3 Card
=4 Bytes=32)
3 2 NESTED LOOPS (Cost=5822 Card=5242 Bytes=99598)
4 3 NESTED LOOPS (Cost=2057 Card=1255 Bytes=13805)
5 4 VIEW OF 'VW_NSO_1' (Cost=793 Card=1255 Bytes=5020)
6 5 FILTER
7 6 SORT (GROUP BY) (Cost=793 Card=1255 Bytes=1004
0)
8 7 INLIST ITERATOR
9 8 INDEX (RANGE SCAN) OF 'UN_ITEM_TAG_TAGID_I
TEMID' (UNIQUE) (Cost=545 Card=140676 Bytes=1125408)
10 4 TABLE ACCESS (BY INDEX ROWID) OF 'ITEM' (Cost=2 Ca
rd=1 Bytes=7)
11 10 INDEX (RANGE SCAN) OF 'PK_ITEM' (UNIQUE) (Cost=1
Card=1)
12 3 INDEX (RANGE SCAN) OF 'IDX_ITEM_TAG_ITEMID' (NON-UNI
QUE) (Cost=2 Card=4)
Statistics
----------------------------------------------------------
98 recursive calls
9 db block gets
976 consistent gets
1379 physical reads
728 redo size
1905 bytes sent via SQL*Net to client
558 bytes received via SQL*Net from client
7 SQL*Net roundtrips to/from client
3 sorts (memory)
1 sorts (disk)
79 rows processed
执行计划已经改变，outline已经生效。最后需要作一个登录触发器，让每一个连接都用outline。
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/231499/viewspace-63726/，如需转载，请注明出处，否则将追究法律责任。
