慎用函数内存表
作者：八神 | 分类： 大话技术 | 标签： | 日期：2009-03-03
也许这个名字不太准确，函数内存表也就是通过一个函数运算，返回一个array,这个返回的数组可以作为普通表与其他的
表做join,或者别的操作，比如我们这边就有个函数
Pkg_Privilege_Util.str2varlist，传入一个字符串，就返回一个数组，当inlist的传值超过1000个时，就是用这个函数
testuser@testdb>explain plan for
2 SELECT sah.ACCOUNT_ID
3 FROM sfa_account_history sah, sfa_opportunity so
4 WHERE (sah.member_id LIKE :1 AND sah.account_id = so.account_id AND
5 (sales_id in
6 (select login_id
7 from admin_user_role aur, admin_user au
8 where aur.user_id = au.login_id
9 and aur.org_id in (SELECT *
10 FROM THE (SELECT CAST(pkg_privilege_util.str2numlist(’30′, ”, ”, ”, ”) AS
11 numtabletype) FROM dual))
12 and au.status in (’enable’, ‘disenable’))));
Explained.
————————————————————————————————————-
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost |
————————————————————————————————————-
| 0 | SELECT STATEMENT | | 1163 | 63965 | | 13271 |
|* 1 | HASH JOIN | | 1163 | 63965 | | 13271 |
|* 2 | HASH JOIN SEMI | | 2965 | 118K| 9M| 12190 |
| 3 | INDEX FAST FULL SCAN | SFA_OPPORTUNITY_SGRI_IND | 296K| 6654K| | 4 |
| 4 | VIEW | VW_NSO_1 | 88224 | 1550K| | 11988 |
| 5 | NESTED LOOPS | | 88224 | 4480K| | 11988 |
|* 6 | HASH JOIN | | 1080 | 54000 | | 108 |
|* 7 | TABLE ACCESS FULL | ADMIN_USER | 872 | 24416 | | 104 |
| 8 | TABLE ACCESS FULL | ADMIN_USER_ROLE | 1700 | 37400 | | 3 |
|* 9 | COLLECTION ITERATOR PICKLER FETCH| STR2NUMLIST | | | | |
| 10 | TABLE ACCESS FULL | DUAL | 8168 | | | 11 |
|* 11 | INDEX RANGE SCAN | SFA_ACCOUNT_HISTORY_MAID_U | 116K| 1588K| | 1076 |
————————————————————————————————————-
在这个查询计划里面，有利用两张小表的hash结果集去ne loop函数内存表，如果结果集有5000笔记录，就意味着这个函数要
调用5000次，如果并发量稍微大点，CPU消耗将不堪设想，上周就因为这个函数内存表的顺序没有调整对，导致项目发布后系统
load飙升
其实这个过程很容易测试：
在函数里面引入一个sequence,每调用一次，就做一次nextval,通过ns loop观察下sequence的大小
testuser@testdb>select count(*) from dropme;
COUNT(*)
———-
5148
testuser@testdb>select SEQ_ME_TEST.nextval from dual;
NEXTVAL
———-
13728
–使用ns loop
testuser@testdb>select /*+ ordered use_nl(a b) */count(*) from
2 dropme a,
3 (SELECT *
4 FROM THE (SELECT CAST(pkg_privilege_util.str2numlist(’30′, ”, ”, ”, ”) AS
5 numtabletype) FROM dual)) b
6 where a.object_id = b.column_value;
testuser@testdb>select SEQ_ME_TEST.nextval from dual;
NEXTVAL
———-
18877
–使用HASH
testuser@testdb> select /*+ ordered use_hash(a b) */count(*) from
2 dropme a,
3 (SELECT *
4 FROM THE (SELECT CAST(pkg_privilege_util.str2numlist(’30′, ”, ”, ”, ”) AS
5 numtabletype) FROM dual)) b
6 where a.object_id = b.column_value;
testuser@testdb>select SEQ_ME_TEST.nextval from dual;
NEXTVAL
———-
18879
所以，在使用这种表的时候，千万不要让别的表来ns loop驱动他，可以使用hash,或者让他自己做驱动表
使用 WITH clause/factor子查询短语, 或者在加上 /*+ materialize */ materialize SQL hint.
就可以保证仅运行一次,然后把结果集cache起来.
跟Pipeline table function没有关系. 适用于任何场景.
木匠Charlie @ March 4, 2009 |
(⊙ˍ⊙) 学习学习！
0537 @ March 9, 2009 |
这次优化内存表的SQL比较棘手, 因为它不依赖一个实体表, 所以无法通过常用的analyze方法正确取得统计信息, 最终用了下下策, 一个很死板的hint—cardinality, 才让SQL走上了正确的执行计划.
内存表还是不要大规模应用于底层架构中.
我爱小白 @ March 11, 2009 |