ITPub博客

首页 > Linux操作系统 > Linux操作系统 > outline优化一例

outline优化一例

原创 Linux操作系统 作者:space6212 时间:2019-07-20 10:45:02 0 删除 编辑

最近需要优化一个系统,由于多种原因不能修改原代码,故用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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2005-01-25

  • 博文量
    245
  • 访问量
    168507