ora-04031是常见的数据库报错,根据以往经验,根本原因都是因为应用SQL解析次数增多,导致shared pool内存吃紧,最后报ORA-04031的错。
严重的情况下会导致数据库hang住,sqlplus / as sysdba都无法连接数据库,节点重启等问题。
一般临时解决这个问题有如下几种方法:
1、尝试刷shared pool(多次未解决)
2、重启数据库(除了释放内存,还会清理内存碎片),针对无法正常使用sqlplus连接数据库的,可以采用sqlplus -prelim / as sysdba连接到数据库中
3、增大SHARED POOL大小
根本解决问题方法:
1、找到硬解析高的SQL,当然要在数据库重启前查看,或者是重启后,数据库运行一段时间后采集SQL,将SQL改为使用绑定变量,减少硬解析次数
2、部分系统内存归档的确存在问题,需要增加主机内存,然后调整SGA,增大SHARED POOL,特别是在SGA自动管理时,SHARED POOL不断增加,
导致BUFFER CACHE减少,影响数据库性能。
一般报错如下:
< ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","DBMS_LOGSTDBY","KGLH0^fdf0611a","kglHeapInitialize:temp")
< ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","unknown object","KGLH0^1b1a56b6","kglHeapInitialize:temp")
诊断时用到的SQL如下:
--空闲内存
select * from v$sgastat a where a.NAME = 'free memory';
--每个子池
SELECT subpool, NAME, SUM (BYTES), ROUND (SUM (BYTES) / 1048576, 2) mb
FROM (SELECT 'shared pool (' || DECODE (TO_CHAR (ksmdsidx), '0', '0 - Unused', ksmdsidx)
|| '):' subpool, ksmssnam NAME, ksmsslen BYTES
FROM x$ksmss WHERE ksmsslen > 0 AND LOWER (ksmssnam) LIKE LOWER ('%free memory%'))
GROUP BY subpool, NAME ORDER BY subpool ASC, SUM (BYTES) DESC;
--解析高的SQL
select substr(a.SQL_TEXT, 1, 50), count(*)
from v$sql a
where last_load_time like '2014-05-27%' and first_load_time like '2014-05-27%'
group by substr(a.SQL_TEXT, 1, 50)
having count(*)>10
order by count(*);
--占sharedpool的SQL
col Stmt for a55
SELECT substr(sql_text,1,50) "Stmt", count(*),
sum(sharable_mem) "Mem",
sum(users_opening) "User",
sum(executions) "Exec"
FROM v$sql where last_load_time like '2014-05-27%' and first_load_time like '2014-05-27%'
GROUP BY substr(sql_text,1,50)
HAVING sum(sharable_mem) > 10000000
order by 3;
--查看sharedpool各个分区的使用情况
select name,bytes/1024/1024 from v$sgastat where pool ='shared pool' and bytes>100000000 order by 2;
--查看高version的
SELECT address,
sql_id,
hash_value,
version_count,
users_opening,
users_executing,
sql_text
FROM v$sqlarea
WHERE version_count > 100;
--查看SQL的module
select distinct MODULE from v$sql where sql_text like 'select * from TABLE_NAME where 1=1 and%';
--查看历史硬解析
select *
from (select pre_snap_id,
snap_id,
to_char(end_interval_time,'yyyymmdd hh24:mi:ss'),
round((value - pre_value) /
(EXTRACT(DAY FROM
b.END_INTERVAL_TIME - b.begin_INTERVAL_TIME) *
86400 + EXTRACT(HOUR FROM b.END_INTERVAL_TIME -
b.begin_INTERVAL_TIME) * 3600 +
EXTRACT(MINUTE FROM
b.END_INTERVAL_TIME - b.begin_INTERVAL_TIME) * 60 +
EXTRACT(SECOND FROM
b.END_INTERVAL_TIME - b.begin_INTERVAL_TIME)),
2) hard_parse
from (select a.snap_id,
begin_INTERVAL_TIME,
end_interval_time,
lag(a.snap_id) over(order by a.snap_id) pre_snap_id,
value,
lag(value) over(order by a.snap_id) pre_value
from DBA_HIST_SYSSTAT a, dba_hist_snapshot b
where stat_name = 'parse count (hard)'
and a.dbid = b.dbid
and a.snap_id = b.snap_id
and a.instance_number = b.instance_number
--and a.dbid=280689037
and a.instance_number = 1) b
where pre_snap_id is not null
order by 1)
--查看历史解析次数
select *
from (select pre_snap_id,
snap_id,
to_char(end_interval_time,'yyyymmdd hh24:mi:ss'),
round((value - pre_value) /
(EXTRACT(DAY FROM
b.END_INTERVAL_TIME - b.begin_INTERVAL_TIME) *
86400 + EXTRACT(HOUR FROM b.END_INTERVAL_TIME -
b.begin_INTERVAL_TIME) * 3600 +
EXTRACT(MINUTE FROM
b.END_INTERVAL_TIME - b.begin_INTERVAL_TIME) * 60 +
EXTRACT(SECOND FROM
b.END_INTERVAL_TIME - b.begin_INTERVAL_TIME)),
2) hard_parse
from (select a.snap_id,
begin_INTERVAL_TIME,
end_interval_time,
lag(a.snap_id) over(order by a.snap_id) pre_snap_id,
value,
lag(value) over(order by a.snap_id) pre_value
from DBA_HIST_SYSSTAT a, dba_hist_snapshot b
where stat_name = 'parse count (total)'
and a.dbid = b.dbid
and a.snap_id = b.snap_id
and a.instance_number = b.instance_number
--and a.dbid=280689037
and a.instance_number = 1) b
where pre_snap_id is not null
order by 1);
调整SHARED POOL步骤如下:
sys@CRM>show parameter sga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_memory_imm_mode_without_autosga boolean FALSE
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 85G
sga_target big integer 0
sys@CRM>show parameter pool
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_PX_use_large_pool boolean TRUE
buffer_pool_keep string
buffer_pool_recycle string
global_context_pool_size string
java_pool_size big integer 1792M
large_pool_size big integer 1792M
olap_page_pool_size big integer 0
shared_pool_reserved_size big integer 483183820
shared_pool_size big integer 9G
streams_pool_size big integer 1G
sys@CRM>show parameter cache
sys@CRM>select sum(CURRENT_SIZE)/1024/1024/1024 from v$sga_dynamic_components ;
SUM(CURRENT_SIZE)/1024/1024/1024
--------------------------------
82.5
sys@CRM>select * from v$sgastat a where a.NAME = 'free memory'
2 /
POOL NAME BYTES
------------ -------------------------- ----------
shared pool free memory 559410608
large pool free memory 1862664064
java pool free memory 1879048192
streams pool free memory 1053041520
sys@CRM>alter system set shared_pool_size=10g sid='crm2';
System altered.
sys@CRM>select * from v$sgastat a where a.NAME = 'free memory';
POOL NAME BYTES
------------ -------------------------- ----------
shared pool free memory 1617013376
large pool free memory 1862664064
java pool free memory 1879048192
streams pool free memory 1053041520
sys@CRM>alter system set shared_pool_size=10g sid='crm1';
--转自