ORA-04031故障诊断及shared pool调整

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';