outline優化一例

最近需要優化一個系統，由於多種原因不能修改原始碼，故用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;