« 过长的In List引起的共享池内存问题 »
Oracle » http://www.anysql.net/oracle/in_list_sql_memory.html 2007-03-22开发人员经常想用一个长长的In List来作为Where条件, 但这样的语句如果查执行计划不对的话, 很容易造成共享池(Shared Pool)内存问题, 下面是一个拥有40个值的In List的测试. 占用的内存如下所示:
SQL> SELECT HASH_VALUE,SHARABLE_MEM,PERSISTENT_MEM,RUNTIME_MEM
2 FROM V$SQL WHERE HASH_VALUE IN (1981294536 ,3511787793);
HASH_VALUE SHARABLE_MEM PERSISTENT_MEM RUNTIME_MEM
---------- ------------ -------------- -----------
1981294536 31836 1088 5336
3511787793 171534 1088 127936
其中1981294536的计划中用的是INLIST ITERATOR方法, 3511787793语句用的是CONCATENATION方法. 源语句如下:
SQL> SELECT HASH_VALUE,SQL_TEXT FROM V$SQL
2 WHERE HASH_VALUE IN (1981294536 ,3511787793);
HASH_VALUE SQL_TEXT
---------- ----------------------------------------
1981294536 SELECT /* INTEST1_TEST */ /*+ ALL_ROWS *
/ OBJECT_ID, OBJECT_NAME FROM T_OBJECT
S WHERE OBJECT_ID IN ( 1,2,3,4,5,6,7,8
,9,10,11,12,13,14,15,16,17,18,19,20, 2
1,22,23,24,25,26,27,28,29,30,31,32,33,34
,35,36,37,38,39,40)
3511787793 SELECT /* INTEST2_TEST */ /*+ USE_CONCAT
*/ OBJECT_ID, OBJECT_NAME FROM T_OBJE
CTS WHERE OBJECT_ID IN ( 1,2,3,4,5,6,7
,8,9,10,11,12,13,14,15,16,17,18,19,20,
21,22,23,24,25,26,27,28,29,30,31,32,33,
34,35,36,37,38,39,40)
如果DBA发现了这样的情况, 应当建议开发人员减少In List的长度, 分为多次执行, 或者确保SQL语句的执行计划是用了INLIST ITERATOR方法.
解决之道:
String to table/rows
1) Table Function
CREATE OR REPLACE function ABELISTING.str2tbl( p_str in varchar2, p_delimiter in varchar2 default ‘,’ )
return IDTable
pipelined
as
l_str varchar2(5000) default p_str || p_delimiter;
l_n PLS_Integer;
begin
loop
l_n := instr( l_str, p_delimiter );
exit when (nvl(l_n,0) = 0);
pipe row( ltrim(rtrim(substr(l_str,1,l_n-1))) );
l_str := ltrim( substr( l_str, l_n+1 ) );
end loop;
return;
end;
/
2) SQL CONNECT, covnert string to rows.
WITH sq
AS
(
SELECT SUBSTR(x, INSTR(x,’,',1,LEVEL)+1, INSTR(x,’,',1,LEVEL+1) – INSTR(x,’,',1,LEVEL) -1) token
FROM (SELECT ‘,’||l_str||’,’ x,l_str FROM (SELECT ‘1,2,3,4′ l_str FROM dual))
CONNECT BY LEVEL
以前还有一些不定个数的in-list语句, 全部否定了, 进行改进.
我的留言有被拒了
因为包含了一个 HTTP URL 链接.
就在 “参考:” 后面,想给你看几个样本>
and …
是防spam级别设得有些高, 不好意思.