ORA-04031:unable to allocate 4120 bytes of shared memory ‘obj stat mem’故障解决_unable to allocate 2424 bytes of shared memory-优快云博客

本文档介绍了在Oracle 10.2.0.5 RAC环境中遇到ORA-04031错误，具体涉及'obj stat memo'内存分配导致的问题。通过分析SGA参数、共享池使用情况和相关脚本，发现'obj stat memo'持续增长。解决方案是设置隐藏参数:disable_objstat_del_broadcast为FALSE，重启数据库实例后，内存得到释放。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

客户报表库，HP-ux B11.31 IA64 FOR Oracle 10.2.0.5.0 RAC系统短信过滤alert日志告警

ORA-00604: error occurred at recursive SQL level 1

ORA-04031:unable to allocate 4120 bytes of shared memory ("shared pool","select f.file#, f.block#, f....","Typecheck","kgghteInit")

询问客户SGA、share pool、主机资源等相关情况

SQL> show parameter sga

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

lock_sga boolean TRUE

pre_page_sga boolean FALSE

sga_max_size big integer 60G

sga_target big integer 0

SQL> show parameter pool

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

buffer_pool_keep string

buffer_pool_recycle string

global_context_pool_size string

java_pool_size big integer 512M

large_pool_size big integer 512M

olap_page_pool_size big integer 0

shared_pool_reserved_size big integer 644245094

shared_pool_size big integer 12G

streams_pool_size big integer 416M

SGA总大小60G，share pool总大小12G。有了这个直观的感受，随即将4031收集相关信息的脚本4031_OK-ForAll.sql发给客户，收集现在内存使用情况，脚本内容如下：

/**********************************************************************

* File: 4031.sql

* Date: 2012/01/1

* Modifications:

* 2012/02/12 Changed v1

*********************************************************************/

spool spinfo.txt

SET PAGESIZE 1024

SET LINESIZE 2000

set echo off;

set feedback off;

set heading on;

set trimout on;

set trimspool on;

COL BYTES FORMAT 999999999999999

COL CURRENT_SIZE FORMAT 999999999999999

/* Script Run TimeStamp */

set serveroutput on;

exec dbms_output.put_line('Script Run TimeStamp');

select to_char(sysdate, 'dd-MON-yyyy hh24:mi:ss') "Script Run TimeStamp" from dual;

set serveroutput on;

exec dbms_output.put_line('Instance Startup Time');

/*Instance Startup time */

select to_char(startup_time, 'dd-MON-yyyy hh24:mi:ss') "Instance Startup Time" from v$instance;

/* shared pool related hidden parameter */

set serveroutput on;

exec dbms_output.put_line('shared pool related hidden parameter ');

col name format a40

col value format a80;

select nam.ksppinm NAME,val.KSPPSTVL VALUE from x$ksppi nam,x$ksppsv val where nam.indx = val.indx and nam.ksppinm like '%shared%' order by 1;

/* SUB Pool Number */

set serveroutput on;

exec dbms_output.put_line('SUB Pool Number ');

col 'Parameter' format a40

col 'Session Value' format a40;

col 'Instance Value' format a40;

select a.ksppinm "Parameter",

b.ksppstvl "Session Value",

c.ksppstvl "Instance Value"

from sys.x$ksppi a, sys.x$ksppcv b, sys.x$ksppsv c

where a.indx = b.indx and a.indx = c.indx

and a.ksppinm like '%_kghdsidx_count%';

/* Each Subpool Size */

set serveroutput on;

exec dbms_output.put_line('Each Subpool Size');

select ksmchidx poolnumer , sum(ksmchsiz) poolsize

from x$ksmsp

group by ksmchidx ;

/* Researved Shared Pool 4031 information */

set serveroutput on;

exec dbms_output.put_line('Researved Shared Pool 4031 information');

select REQUEST_FAILURES, LAST_FAILURE_SIZE from V$SHARED_POOL_RESERVED;

/* Reaserved Shared Pool Reserved 4031 information */

set serveroutput on;

exec dbms_output.put_line('Reaserved Shared Pool 4031 information');

select REQUESTS, REQUEST_MISSES, free_space, avg_free_size, free_count, max_free_size from V$SHARED_POOL_RESERVED;

/* Current SGA Buffer & Pool sizes */

set serveroutput on;

exec dbms_output.put_line('Current SGA Buffer Pool sizes');

select component, current_size from v$sga_dynamic_components;

/* Shared Pool Memory Allocations by Size */

set serveroutput on;

exec dbms_output.put_line('Shared Pool Memory Allocations by Size');

select name, bytes from v$sgastat

where pool = 'shared pool' and (bytes > 999999 or name = 'free memory')

order by bytes desc;

set serveroutput on;

exec dbms_output.put_line('show component of shared pool which is bigger than 10MB');

select name, round((bytes/1024/1024),0) "more than 10" from v$sgastat where pool='shared pool' and bytes > 10000000 order by bytes desc;

select sum(bytes) "SHARED POOL TOTAL SIZE" from v$sgastat where pool='shared pool';

/* Total Free of Shared Pool */

set serveroutput on;

exec dbms_output.put_line('Total Free(not Free) of Shared Pool ');

COL 'Total Shared Pool Usage' FORMAT 999999999999999

select sum(bytes)/1024/1024 "Free MB in Shared Pool" from v$sgastat where pool = 'shared pool' and name = 'free memory';

select sum(bytes) "Not Free MB Shared Pool" from v$sgastat where pool = 'shared pool' and name != 'free memory';

/* current KGLH* usage */

set serveroutput on;

exec dbms_output.put_line('current KGLH* usage');

select name, bytes from v$sgastat where pool = 'shared pool' and name in ('KGLHD','KGHL0');

/* Hisotry KGLH* usage */

set serveroutput on;

exec dbms_output.put_line('Hisotry KGLH* usage');

select bytes/1024/1024 , s.snap_id, begin_interval_time START_TIME

from dba_hist_sgastat g, dba_hist_snapshot s

where name='KGLHD'

and pool='shared pool'

and trunc(begin_interval_time) >= '30-DEC-2011'

and s.snap_id = g.snap_id

order by 2;

set serveroutput on;

exec dbms_output.put_line('Hisotry KGLH0* usage');

select bytes/1024/1024 , s.snap_id, begin_interval_time START_TIME

from dba_hist_sgastat g, dba_hist_snapshot s

where name='KGLH0'

and pool='shared pool'

and trunc(begin_interval_time) >= '30-DEC-2011'

and s.snap_id = g.snap_id

order by 2;

/* History of Shared pool allocations in a speciifed Day*/

set serveroutput on;

exec dbms_output.put_line('history of Shared pool allocations in a speciifed Day');

col name format a30

select n,

max(decode(to_char(begin_interval_time, 'hh24'), 1,bytes, null)) "1",

max(decode(to_char(begin_interval_time, 'hh24'), 2,bytes, null)) "2",

max(decode(to_char(begin_interval_time, 'hh24'), 3,bytes, null)) "3",<