ORA-04031故障诊断及shared pool调整_MySQL, Oracle及数据库讨论区_Weblogic技术|Tuxedo技术|中间件技术|Oracle论坛|JAVA论坛|Linux/Unix技术|hadoop论坛_联动北方技术论坛  
网站首页 | 关于我们 | 服务中心 | 经验交流 | 公司荣誉 | 成功案例 | 合作伙伴 | 联系我们 |
联动北方-国内领先的云技术服务提供商
»  游客             当前位置:  论坛首页 »  自由讨论区 »  MySQL, Oracle及数据库讨论区 »
总帖数
2
每页帖数
101/1页1
返回列表
0
发起投票  发起投票 发新帖子
查看: 2988 | 回复: 1   主题: ORA-04031故障诊断及shared pool调整        下一篇 
johnnyfox
注册用户
等级:新兵
经验:41
发帖:90
精华:0
注册:2011-12-12
状态:离线
发送短消息息给johnnyfox 加好友    发送短消息息给johnnyfox 发消息
发表于: IP:您无权察看 2015-9-24 10:36:39 | [全部帖] [楼主帖] 楼主

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




--转自



赞(0)    操作        顶端 
联动大白
注册用户
等级:列兵
经验:91
发帖:0
精华:0
注册:2015-5-27
状态:离线
发送短消息息给联动大白 加好友    发送短消息息给联动大白 发消息
发表于: IP:您无权察看 2019-8-1 0:30:00 | [全部帖] [楼主帖] 2  楼

为了方便大家阅读,我对文章中错误号来解释一下吧!

Error Id: ORA-04031

Title: unable to allocate string bytes of shared memory ("string","string","string","string")

Description:

unable to allocate string bytes of shared memory ("string","string","string","string")

Action:

If the shared pool is out of memory, either use the dbms_shared_pool package to pin large packages, reduce your use of shared memory, or increase the amount of available shared memory by increasing the value of the INIT.ORA parameters "shared_pool_reserved_size" and "shared_pool_size". If the large pool is out of memory, increase the INIT.ORA parameter "large_pool_size".

Cause:

More shared memory is needed than was allocated in the shared pool.


也许你已明白,但对一个人有用也是我存在的理由!^_^ By:持之以恒的大白

-- 来自: 北京联动北方科技有限公司



赞(0)    操作        顶端 
总帖数
2
每页帖数
101/1页1
返回列表
发新帖子
请输入验证码: 点击刷新验证码
您需要登录后才可以回帖 登录 | 注册
技术讨论