玉面飞龙的BLOG

Oracle的In (………..)查询条件里最多可以有1000个变量。让我们来看看这1000个变量对shared pool的使用情况。

使用 CONCATENATION方法，SQL占用了7M左右。如果这1000个变量不是bind variable且SQL执行的稍微频繁一点的话，繁忙的数据库会因为shared pool争用太多而马上down掉。

SQL> select SHARABLE_MEM,PERSISTENT_MEM,RUNTIME_MEM from v$sql where hashvalue=3288616472;
SHARABLE_MEM         PERSISTENT_MEM         RUNTIME_MEM
———— –    ———— ———–
7706417 2924160                 2910600

——————————————————
| Id | Operation | Name |
——————————————————
| 0 | SELECT STATEMENT | |
| 1 | CONCATENATION | |
| 2 | TABLE ACCESS BY INDEX ROWID| TEST_TABLE |
|* 3 | INDEX UNIQUE SCAN | TEST_TABLE_PK |
| 4 | TABLE ACCESS BY INDEX ROWID| TEST_TABLE |
|* 5 | INDEX UNIQUE SCAN | TEST_TABLE_PK |
……………………………………………..
Predicate Information (identified by operation id):
—————————————————
3 - access(”USER_ID”=42284537)
5 - access(”USER_ID”=119971500)
7 - access(”USER_ID”=2227338)
9 - access(”USER_ID”=109039007)
……………………………………………..
……………………………………………..

当使用NO_EXPAND Hint的时候，使用的shared pool内存大大减少，200k左右，是因为Plan占用更少的内存。

SQL> select SHARABLE_MEM,PERSISTENT_MEM,RUNTIME_MEM from v$sql where hashvalue=2765611740;
SHARABLE_MEM PERSISTENT_MEM RUNTIME_MEM
———— ————– ———–
284465 35456                      34480

———————————————————————-
| Id | Operation | Name | Rows | Bytes |
—————————————————- ————-
| 0 | SELECT STATEMENT | | 20 | 520 |
| 1 | INLIST ITERATOR | |
| 2 | TABLE ACCESS BY INDEX ROWID| TEST_TABLE | 20 | 520 |
|* 3 | INDEX RANGE SCAN | TEST_TABLE_PK | 8 | 20 |
———————————————————————-
Predicate Information (identified by operation id):
—————————————————
3 - access(”USER_ID”=178426 OR “USER_ID”=466817 OR “USER_ID”=853367 OR “USER_ID”=1031091
OR ………….