暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / 案例分享：Oracle数据库ora-4031分析

案例分享：Oracle数据库ora-4031分析

IT那活儿 2023-08-01
1223

点击上方“IT那活儿”公众号，关注后了解更多内容，不管IT什么活儿，干就完了！！！


背 景
数据库实例 DB1 的ALERT 日志中出现大量Ora-4031 报错，业务运行受到影响。14:16 左右重启该实例后恢复。

数据库版本：

VERSION INFORMATION:
  • TNS for Linux: Version 11.2.0.3.0 - Production
  • Oracle Bequeath NT Protocol Adapter for Linux: Version 11.2.0.3.0 - Production

  • TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.3.0 - Production


故障分析
2.1 alert日志分析
1）确认问题发生的具体时间
Fatal NI connect error 12170.

VERSION INFORMATION:
    TNS for Linux: Version 11.2.0.3.0 - Production
    Oracle Bequeath NT Protocol Adapter for Linux: Version 11.2.0.3.0 - Production
    TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.3.0 - Production
Time: 06-JUL-2023 11:58:28
Tracing not turned on.
Tns error struct:
  ns main err code: 12535
  
TNS-12535: TNS:operation timed out
  ns secondary err code: 12560
  nt main err code: 505
  
TNS-00505: Operation timed out
  nt secondary err code: 110
  nt OS err code: 0
Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=9.7.136.51)(PORT=57727))

Thu Jul 06  12:03:34  2023   <<<<< 报错最早出现时间 
Errors in file /u01/app/oracle/diag/rdbms/db/db1/trace/db1_ora_9129.trc (incident=90227):
ORA-04031: 无法分配 32 字节的共享内存 ("shared pool","userUERY","KKTIN^d991f2de","kglHeapInitialize:temp") 
Incident details in: u01/app/oracle/diag/rdbms/db/db1/incident/incdir_90227/db1_ora_9129_i90227.trc
Thu Jul 06 12:13:32 2023


-------
Fatal NI connect error 12170.

VERSION INFORMATION:
    TNS for Linux: Version 11.2.0.3.0 - Production
    Oracle Bequeath NT Protocol Adapter for Linux: Version 11.2.0.3.0 - Production
    TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.3.0 - Production
Time: 06-JUL-2023 12:13:32
Tracing not turned on.
Tns error struct:
  ns main err code: 12535
  
TNS-12535: TNS:operation timed out
  ns secondary err code: 12560
  nt main err code: 505
  
TNS-00505: Operation timed out
  nt secondary err code: 110
  nt OS err code: 0
Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=9.30.248.157)(PORT=2953))
Thu Jul 06 12:13:46 2023
Process 0x0xa18e73258 appears to be hung while dumping
Current time = 4158747642, process death time = 4158686896 interval = 60000
Attempting to kill process 0x0xa18e73258 with OS pid = 9129
OSD kill succeeded for process 0xa18e73258
Thu Jul 06 12:22:20 2023
Errors in file /u01/app/oracle/diag/rdbms/db/db1/trace/db1_ora_28003.trc (incident=89555):
ORA-04031: 无法分配 32 字节的共享内存 ("shared pool","select SYS_CONTEXT('USERENV'...","SQLA","tmp")
Incident details in: /u01/app/oracle/diag/rdbms/db/db1/incident/incdir_89555/db1_ora_28003_i89555.trc
Thu Jul 06 12:22:20 2023
Errors in file /u01/app/oracle/diag/rdbms/db/db1/trace/db1_ora_35711.trc (incident=87211):
ORA-04031: 无法分配 32 字节的共享内存 ("shared pool","CD_RISK","KGLS^1cc94fb1","kglHeapInitialize:temp")
Incident details in: /u01/app/oracle/diag/rdbms/db/db1/incident/incdir_87211/db1_ora_35711_i87211.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
……
……
Thu Jul 06 14:16:24 2023
Errors in file /u01/app/oracle/diag/rdbms/db/db1/trace/db1_smon_11364.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","select f.file#, f.block#, f....","SQLA","tmp")
Thu Jul 06 14:16:36 2023
Shutting down instance (immediate)
Shutting down instance: further logons disabled
License high water mark = 1485
All dispatchers and shared servers shutdown
ALTER DATABASE CLOSE NORMAL
Thu Jul 06 14:16:39 2023
Errors in file /u01/app/oracle/diag/rdbms/db/db1/trace/db1_smon_11364.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","select f.file#, f.block#, f....","SQLA","tmp")
ORA-4031 signalled during: ALTER DATABASE CLOSE NORMAL...
Thu Jul 06 14:16:55 2023
Errors in file /u01/app/oracle/diag/rdbms/db/db1/trace/db1_smon_11364.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","select f.file#, f.block#, f....","SQLA","tmp")
Thu Jul 06 14:17:03 2023    <<<<<<<<<<< 14:17 shutdown abort
Shutting down instance (abort)
License high water mark = 1485
USER (ospid: 19180): terminating the instance
Instance terminated by USER, pid = 19180
复制
2）对Ora-4031 报错进行统计
[oracle@01db01 trace]$ cat alert_db1.log | grep "ORA-04031" |sort | uniq -c
1 ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","SELECT  con_id,trunc(sysdate...","SQLA","tmp")
1 ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","SELECT COUNT(TRIGGER_NAME) F...","SQLA","tmp")
3 ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","SELECT levels_code F...","SQLA","tmp")
1 ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","UPDATE ISP_MASTER_ELECTION S...","SQLA","tmp")
4 ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","select SYS_CONTEXT('USERENV'...","SQLA","tmp")
399 ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","select a.default_cpu_cost, a...","SQLA","tmp")
2 ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","select code as 渠道代码, cod...","SQLA","tmp")
4 ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","select count(*) from log    ...","SQLA","tmp")
5 ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","select count(*) from sys.job...","SQLA","tmp")
277 ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","select f.file#, f.block#, f....","SQLA","tmp")
2 ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","select local_tran_id, global...","SQLA","tmp")
2 ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","select o.owner#,o.name,o.nam...","SQLA","tmp")
2 ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","select t.comlevel from  RE...","SQLA","tmp")
2 ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","unknown object","SQLA","tmp")
2 ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","update sys.job$ set failures...","SQLA","tmp")
1 ORA-04031: 无法分配 字节的共享内存 ("","","","")
1 ORA-04031: 无法分配 32 字节的共享内存 ("shared pool"," QUERY","KKTIN^d991f2de","kglHeapInitialize:temp") 
1 ORA-04031: 无法分配 32 字节的共享内存 ("shared pool","CD_RISK","KGLS^1cc94fb1","kglHeapInitialize:temp")
1 ORA-04031: 无法分配 32 字节的共享内存 ("shared pool","DBMS_SQLTUNE","PLMCD^7b7936a5","kglHeapInitialize:temp")
4 ORA-04031: 无法分配 32 字节的共享内存 ("shared pool","ODS_DCOMPANY","KGLS^119a2654","kglHeapInitialize:temp")
1 ORA-04031: 无法分配 32 字节的共享内存 ("shared pool","SELECT  con_id,trunc(sysdate...","SQLA","tmp")
2 ORA-04031: 无法分配 32 字节的共享内存 ("shared pool","SELECT COUNT(*) AS COUNT FRO...","SQLA","tmp")
1 ORA-04031: 无法分配 32 字节的共享内存 ("shared pool","SELECT T1.SYS_ID AS SYSID,
1 ORA-04031: 无法分配 32 字节的共享内存 ("shared pool","SELECT ug.GradeID,UserCode F...","SQLA","tmp")
1 ORA-04031: 无法分配 32 字节的共享内存 ("shared pool"," PORTAL","KKTIN^813f91d6","kglHeapInitialize:temp")
1 ORA-04031: 无法分配 32 字节的共享内存 ("shared pool","insert into wechat_message_l...","SQLA","tmp")
5 ORA-04031: 无法分配 32 字节的共享内存 ("shared pool","select SYS_CONTEXT('USERENV'...","SQLA","tmp")
1 ORA-04031: 无法分配 32 字节的共享内存 ("shared pool","select file# from file$ wher...","SQLA","tmp")
1 ORA-04031: 无法分配 32 字节的共享内存 ("shared pool","select name,intcol#,segcol#,...","SQLA","tmp")
4 ORA-04031: 无法分配 32 字节的共享内存 ("shared pool","select rowid as "__Oracle_JD...","SQLA","tmp")
1 ORA-04031: 无法分配 32 字节的共享内存 ("shared pool","select type#,blocks,extents,...","SQLA","tmp")
4 ORA-04031: 无法分配 32 字节的共享内存 ("shared pool","unknown object","SQLA","tmp")
复制
报错的主要在SQLA 内存结构上，同时还有 少量 KKTIN,PLMCD, KGLS 等内存结构。
3）Trace 也显示SQLA 申请内存失败
db_ora_28003.trc
******************************************************
HEAP DUMP heap name="SQLA" desc=0x93ff34b60
extent sz=0xfe8 alt=32767 het=368 rec=0 flg=2 opc=2
parent=0x60001190 owner=(nil) nex=(nil) xsz=0x8000000 heap=(nil)
fl2=0x26, nex=(nil) 
Subheap has 0 bytes of memory allocated
复制
2.2 数据库参数分析
NAME TYPE VALUE
------------------------------------ ----------- --------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 40G
sga_target big integer 40G
shared_pool_reserved_size big integer 355676979
shared_pool_size big integer 0 <<<<<< share pool 没有设置初始值
pga_aggregate_target big integer 60G
memory_max_target big integer 0
memory_target big integer 0
db_cache_advice string ON
db_cache_size big integer 0 <<<<<<<<<<<<< db cache 没有设置初始值

SYS@D1 as AS SYSDBA>select * from v$version;

BANNER
-------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
复制
2.3 AWR 分析
采集7/6 实例1 11：00~12：00 的AWR 进行分析，即出现报错前的1小时。
Share pool 的组件 KGH: NO ACCESS 达到了31G。
参考Common Cause for ORA-4031 in 10gR2, Excess "KGH: NO ACCESS" Memory Allocation [Video] (Doc ID 801787.1) 和  ORA-04031 in 11g & 11gR2, Excess "KGH: NO ACCESS" Memory Allocation (Doc ID 1127833.1)
文档中提到的bug 在当前环境 11.2.0.3 版本已经修复，排除BUG 的可能性。
Too frequent resizes of Shared Pool and Buffer Cache, leading to excess "KGH: NO ACCESS" memory allocation, that consumes SGA memory.
综上：
  • AWR 显示在出现报错前，Shared pool 的组件 KGH: NO ACCESS 就已经达到了31G,  消耗了大部分的SGA(40G) 。 
  • Shared pool 与 Buffer cache  Reszie过于频繁，导致“KGH:NO ACCESS”内存分配过多，从而消耗SGA内存。
2.4 实例重启后检查
SYS@db1 as AS SYSDBA>SELECT *
2    FROM ( SELECT name, bytes / POWER (1024, 2) MB
3              FROM v$sgastat
4             WHERE pool = 'shared pool'
5          ORDER BY bytes DESC)
6  WHERE ROWNUM < 11;

NAME                               MB
-------------------------- ----------
free memory                2204.99093
KGH: NO ACCESS             1169.72131   <<<<<<<<
gcs resources              863.664917
gcs shadows                503.804535
SQLA                       428.456902
KGLH0                      254.144691
db_block_hash_buckets      178.003906
gc name table                      72
ASH buffers                        64
ges enqueues               60.0942535

10 rows selected.
复制
约1~2小时后我们再次查询。
SYS@db1 as AS SYSDBA>
SYS@db1 as AS SYSDBA>SELECT *
2    FROM ( SELECT name, bytes / POWER (1024, 2) MB
3              FROM v$sgastat
4             WHERE pool = 'shared pool'
5          ORDER BY bytes DESC)
6  WHERE ROWNUM < 11;

NAME                               MB
-------------------------- ----------
free memory                2126.21647
KGH: NO ACCESS             1406.54184    <<<<<<<< 增长较快
gcs resources              863.664917
gcs shadows                503.804535
SQLA                       468.870087
KGLH0                      286.282593
db_block_hash_buckets      178.003906
gc name table                      72
KGLHD                      66.0846176
ASH buffers                        64

10 rows selected.
复制
检查SGA resize 情况，实例重启后的2.5 小时内，resize 频率达到了60次。
SYS@db1 as AS SYSDBA>select START_TIME, component, oper_type, oper_mode, initial_size/1024/1024 "INITIAL", FINAL_SIZE/1024/1024 "FINAL", END_TIME
2  from v$sga_resize_ops
3  where component in ('DEFAULT buffer cache', 'shared pool') and status = 'COMPLETE'
4  order by start_time, component;
START_TIME COMPONENT OPER_TYPE OPER_MODE INITIAL FINAL END_TIME
------------------- ------------------------- ------------- --------- ---------- ---------- -------------------
06/07/2023 02:17:30 DEFAULT buffer cache INITIALIZING 33024      33024 06/07/2023 02:17:36
06/07/2023 02:17:30 DEFAULT buffer cache STATIC                           0      33024 06/07/2023 02:17:30
06/07/2023 02:17:30 shared pool STATIC                           0       6784 06/07/2023 02:17:30
06/07/2023 02:28:47 DEFAULT buffer cache GROW DEFERRED 33024      33152 06/07/2023 02:28:47
06/07/2023 02:28:47 shared pool SHRINK DEFERRED 6784       6656 06/07/2023 02:28:47
06/07/2023 02:48:30 DEFAULT buffer cache GROW DEFERRED 33152      33280 06/07/2023 02:48:30
06/07/2023 02:48:30 shared pool SHRINK DEFERRED 6656       6528 06/07/2023 02:48:30
06/07/2023 02:53:00 DEFAULT buffer cache GROW DEFERRED 33280      33408 06/07/2023 02:53:00
06/07/2023 02:53:00 shared pool SHRINK DEFERRED 6528       6400 06/07/2023 02:53:00
06/07/2023 02:58:15 DEFAULT buffer cache GROW DEFERRED 33408      33536 06/07/2023 02:58:15
06/07/2023 02:58:15 shared pool SHRINK DEFERRED 6400       6272 06/07/2023 02:58:15
06/07/2023 03:04:46 DEFAULT buffer cache GROW DEFERRED 33536      33664 06/07/2023 03:04:46
06/07/2023 03:04:46 shared pool SHRINK DEFERRED 6272       6144 06/07/2023 03:04:46
06/07/2023 03:18:55 DEFAULT buffer cache GROW DEFERRED 33664      33792 06/07/2023 03:18:55
06/07/2023 03:18:55 shared pool SHRINK DEFERRED 6144       6016 06/07/2023 03:18:55
06/07/2023 03:24:25 DEFAULT buffer cache GROW DEFERRED 33792      33920 06/07/2023 03:24:25
06/07/2023 03:24:25 shared pool SHRINK DEFERRED 6016       5888 06/07/2023 03:24:25
06/07/2023 03:28:56 DEFAULT buffer cache GROW DEFERRED 33920      34048 06/07/2023 03:28:56
06/07/2023 03:28:56 shared pool SHRINK DEFERRED 5888       5760 06/07/2023 03:28:56
06/07/2023 03:33:26 DEFAULT buffer cache GROW DEFERRED 34048      34176 06/07/2023 03:33:26
06/07/2023 03:33:26 shared pool SHRINK DEFERRED 5760       5632 06/07/2023 03:33:26
06/07/2023 03:38:56 DEFAULT buffer cache GROW DEFERRED 34176      34304 06/07/2023 03:38:57
06/07/2023 03:38:56 shared pool SHRINK DEFERRED 5632       5504 06/07/2023 03:38:57
06/07/2023 03:43:24 DEFAULT buffer cache GROW DEFERRED 34304      34432 06/07/2023 03:43:25
06/07/2023 03:43:24 shared pool SHRINK DEFERRED 5504       5376 06/07/2023 03:43:25
06/07/2023 03:43:25 DEFAULT buffer cache SHRINK IMMEDIATE 34432      34304 06/07/2023 03:43:25
06/07/2023 03:43:25 shared pool GROW IMMEDIATE 5376       5504 06/07/2023 03:43:25
06/07/2023 03:48:07 DEFAULT buffer cache GROW DEFERRED 34304      34432 06/07/2023 03:48:08
06/07/2023 03:48:07 shared pool SHRINK DEFERRED 5504       5376 06/07/2023 03:48:08
06/07/2023 03:48:08 DEFAULT buffer cache SHRINK IMMEDIATE 34432      34304 06/07/2023 03:48:08
06/07/2023 03:48:08 shared pool GROW IMMEDIATE 5376       5504 06/07/2023 03:48:08
06/07/2023 03:52:38 DEFAULT buffer cache GROW DEFERRED 34304      34432 06/07/2023 03:52:39
06/07/2023 03:52:38 shared pool SHRINK DEFERRED 5504       5376 06/07/2023 03:52:39
06/07/2023 03:52:39 DEFAULT buffer cache SHRINK IMMEDIATE 34432      34304 06/07/2023 03:52:39
06/07/2023 03:52:39 shared pool GROW IMMEDIATE 5376       5504 06/07/2023 03:52:39
06/07/2023 04:00:57 DEFAULT buffer cache GROW DEFERRED 34304      34432 06/07/2023 04:00:58
06/07/2023 04:00:57 shared pool SHRINK DEFERRED 5504       5376 06/07/2023 04:00:58
06/07/2023 04:00:58 DEFAULT buffer cache SHRINK IMMEDIATE 34432      34304 06/07/2023 04:00:58
06/07/2023 04:00:58 shared pool GROW IMMEDIATE 5376       5504 06/07/2023 04:00:58
06/07/2023 04:07:25 DEFAULT buffer cache GROW DEFERRED 34304      34432 06/07/2023 04:07:25
06/07/2023 04:07:25 DEFAULT buffer cache SHRINK IMMEDIATE 34432      34304 06/07/2023 04:07:26
06/07/2023 04:07:25 shared pool SHRINK DEFERRED 5504       5376 06/07/2023 04:07:25
06/07/2023 04:07:25 shared pool GROW IMMEDIATE 5376       5504 06/07/2023 04:07:26
06/07/2023 04:20:20 DEFAULT buffer cache GROW DEFERRED 34304      34432 06/07/2023 04:20:21
06/07/2023 04:20:20 shared pool SHRINK DEFERRED 5504       5376 06/07/2023 04:20:21
06/07/2023 04:20:21 DEFAULT buffer cache SHRINK IMMEDIATE 34432      34304 06/07/2023 04:20:21
06/07/2023 04:20:21 shared pool GROW IMMEDIATE 5376       5504 06/07/2023 04:20:21
06/07/2023 04:28:24 DEFAULT buffer cache GROW DEFERRED 34304      34432 06/07/2023 04:28:25
06/07/2023 04:28:24 shared pool SHRINK DEFERRED 5504       5376 06/07/2023 04:28:25
06/07/2023 04:28:25 DEFAULT buffer cache SHRINK IMMEDIATE 34432      34304 06/07/2023 04:28:25
06/07/2023 04:28:25 shared pool GROW IMMEDIATE 5376       5504 06/07/2023 04:28:25
06/07/2023 04:38:55 DEFAULT buffer cache SHRINK IMMEDIATE 34432      34304 06/07/2023 04:38:55
06/07/2023 04:38:55 DEFAULT buffer cache GROW DEFERRED 34304      34432 06/07/2023 04:38:55
06/07/2023 04:38:55 shared pool SHRINK DEFERRED 5504       5376 06/07/2023 04:38:55
06/07/2023 04:38:55 shared pool GROW IMMEDIATE 5376       5504 06/07/2023 04:38:55
06/07/2023 04:48:28 DEFAULT buffer cache GROW DEFERRED 34304      34432 06/07/2023 04:48:29
06/07/2023 04:48:28 shared pool SHRINK DEFERRED 5504       5376 06/07/2023 04:48:29
06/07/2023 04:48:29 DEFAULT buffer cache SHRINK IMMEDIATE 34432      34304 06/07/2023 04:48:29
06/07/2023 04:48:29 shared pool GROW IMMEDIATE 5376       5504 06/07/2023 04:48:29
06/07/2023 04:53:29 DEFAULT buffer cache GROW DEFERRED 34304      34432 06/07/2023 04:53:30
06/07/2023 04:53:29 shared pool SHRINK DEFERRED 5504       5376 06/07/2023 04:53:30
06/07/2023 04:53:30 DEFAULT buffer cache SHRINK IMMEDIATE 34432      34304 06/07/2023 04:53:30
06/07/2023 04:53:30 shared pool GROW IMMEDIATE 5376       5504 06/07/2023 04:53:30

63 rows selected.
复制
综上：
  • 实例重启后的2.5小时内，Shared pool 与 Buffer cache resize 频率达到了60次，通过观察，这段时间KGH: NO ACCESS 组件内存使用有较明显增长。

结 论
Ora-4031 报错的主要在SQLA 内存结构上，同时还有 少量 KKTIN,PLMCD, KGLS 等内存结构。
AWR 显示在出现报错前，Shared pool 的组件 KGH: NO ACCESS 就已经达到了31G,  消耗了大部分的SGA(40G) 。根据MOS 解释Shared pool 与 Buffer cache  Reszie过于频繁，导致“KGH:NO ACCESS”内存分配过多，从而消耗SGA内存。
实例重启后的2.5小时内，依然能观察到Shared pool 与 Buffer cache resize 的频繁resize,频率达到了60次，通过观察，这段时间KGH: NO ACCESS 组件内存使用有较明显增长。
综上：
  • 本次故障原因是SGA 过小造成Shared pool 与 Buffer cache resize 频繁resize,  导致KGH:NO ACCESS 内存不断增长，消耗较多的SGA 内存。

建议方案

4.1 调大SGA 并设置shared pool 和 buffer cache 初始值 (推荐)
sga_max_size=70G (重启库生效,支持滚动重启实例)
sga_target=70G
shared_pool_size= 15G
db_cache_size =45G
_PX_use_large_pool=true
large_pool_size=3.5G
streams_pool_size=256M
复制
同时需配合修改主机大页，修改主机大页分为两种方式，均需要停实例和集群后操作：
  • 永久生效，需要重启主机。
    /etc/sysctl.conf 配置 vm.nr_hugepages = 41984
  • 临时生效，不需要重启主机
    （为了保证重启主机后依然生效，建议同时在/etc/sysctl.conf 进行配置。）
4.2 仅设置shared pool 和 buffer cache 初始值(不推荐)
alter system set shared_pool_size=5600M sid='*';
alter system set db_cache_size=33000M sid='*';
复制
注意：
由于当前SGA 整体偏小，仅设置share pool 和 buffer cache 的初始值，对降低resize 频率帮助有限。
如无停机窗口，则需加强share pool 使用情况的监控，必要时flush share pool。该实例的上次启动时间到此次问题发生间隔1年，如仅设置share pool 和  buffer cache 初始值，1年以后该问题仍可能出现，届时需要flush share pool 甚至仍需重启该实例才能恢复。

END


本文作者：李亚明(上海新炬中北团队）

本文来源：“IT那活儿”公众号

oracle数据库大数据oracle数据库
文章转载自IT那活儿，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论

相关阅读

【专家观点】罗敏：从理论到真实SQL，感受DeepSeek如何做性能优化
墨天轮编辑部
1238次阅读
2025-03-06 16:45:38
【专家有话说第五期】在不同年龄段，DBA应该怎样规划自己的职业发展？
墨天轮编辑部
1216次阅读
2025-03-13 11:40:53
2025年2月国产数据库大事记
墨天轮编辑部
955次阅读
2025-03-05 12:27:34
2025年2月国产数据库中标情况一览：GoldenDB 3500+万！达梦近千万！
通讯员
846次阅读
2025-03-06 11:40:20
Oracle RAC ASM 磁盘组满了，无法扩容怎么在线处理？
Lucifer三思而后行
739次阅读
2025-03-17 11:33:53
Oracle+Deepseek+Dify 实现数据库数据实时分析
bicewow
647次阅读
2025-03-06 09:41:49
【ORACLE】ORACLE19C在19.13版本前的一个严重BUG-24761824
DarkAthena
541次阅读
2025-03-04 14:33:31
Oracle避坑指南｜同名表导出难题：如何精准排除指定用户下的表？
szrsu
502次阅读
2025-03-05 00:42:34
Ogg23ai高手必看-MySQL Innodb Cluster跟oracle的亲密接触
曹海峰
437次阅读
2025-03-04 21:56:13
2月“墨力原创作者计划”获奖名单公布
墨天轮编辑部
436次阅读
2025-03-13 14:38:19