shared pool過大問題

shared pool過大肯能出現的問題在shared pool中解析，請求free空間分配、分割，產生更多、更細碎片chunk select count(*) from x$ksmsp;--內存碎片數(chunk數) shared pool內存碎片(chunk)過多

ORA-04031：當分配大塊的連續的內存時不足時出現，很多時候是因為內存碎片過多，而并非內存不足造成。oracle首先會清除shared pool中當前沒有使用的對象，使空間合并，如果仍然沒有足夠大的單塊內存可以滿足需要，就會發生04031

 案例： 2011/10/26 Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production +Red Hat Enterprise Linux AS release 4 (Nahant Update 5)

用戶程序突然ORA-04031:無法分配共享記憶體的4072位元組()

SELECT free_space, avg_free_size,used_space, avg_used_size,request_failures, last_failure_size FROM v$shared_pool_reserved; FREE_SPACE|AVG_FREE_SIZE|USED_SPACE|AVG_USED_SIZE|REQUEST_FAILURES|LAST_FAILURE_SIZE 155403224|226205.566229985|10824904|15756.7743813683|5170|432

看到有請求失敗,最后一次失敗大小位432<4400（_shared_pool_reserved_min_alloc）

SELECT * FROM X$KSPPCV A,X$KSPPI B
WHERE A.INDX=B.INDX
AND B.KSPPINM = '_shared_pool_reserved_min_alloc'

 

SELECT a.ksmchcom,
SUM (a.CHUNK) CHUNK,
SUM (a.recr) recr,
SUM (a.freeabl) freeabl,
SUM (a.SUM) SUM
 FROM (SELECT ksmchcom, COUNT (ksmchcom) CHUNK,
DECODE (ksmchcls, 'recr', SUM (ksmchsiz), NULL) recr,
DECODE (ksmchcls, 'freeabl', SUM (ksmchsiz), NULL) freeabl,
SUM (ksmchsiz) SUM
FROM x$ksmsp
GROUP BY ksmchcom, ksmchcls) a
GROUP BY a.ksmchcom;

我的方法找出多次沒有綁定變量的sql：
select count exe_count,module,SQL_TEXT from (
select ROW_NUMBER()over(partition by substr(SQL_TEXT,1,50) order by SQL_TEXT) num,a.* from (
select count(*)over(partition by substr(SQL_TEXT,1,50)) count ,SQL_TEXT,
   SHARABLE_MEM,
   PERSISTENT_MEM,
   RUNTIME_MEM,
   SORTS,
   LOADED_VERSIONS,
   OPEN_VERSIONS,
   USERS_OPENING,
   FETCHES,
   EXECUTIONS,
   USERS_EXECUTING,
   LOADS,
   FIRST_LOAD_TIME,
   INVALIDATIONS,
   PARSE_CALLS,
   DISK_READS,
   BUFFER_GETS,
   ROWS_PROCESSED,
   COMMAND_TYPE,
   OPTIMIZER_MODE,
   OPTIMIZER_COST,
   PARSING_USER_ID,
   PARSING_SCHEMA_ID,
   KEPT_VERSIONS,
   ADDRESS,
   TYPE_CHK_HEAP,
   HASH_VALUE,
   PLAN_HASH_VALUE,
   CHILD_NUMBER,
   MODULE,
   MODULE_HASH,
   ACTION,
   ACTION_HASH,
   SERIALIZABLE_ABORTS,
   OUTLINE_CATEGORY,
   CPU_TIME,
   ELAPSED_TIME,
   OUTLINE_SID,
   CHILD_ADDRESS,
   SQLTYPE,
   REMOTE,
   OBJECT_STATUS,
   LITERAL_HASH_VALUE,
   LAST_LOAD_TIME,
   IS_OBSOLETE,
   CHILD_LATCH from v$sql) a
   where count>20    order by count desc
) b
where num=1