SGA: allocation forcing component growth分析

来源：cnblogs　　作者：peiyang　　时间：2021/1/25 11:07:21　　对本文有异议

1.问题现象

20年12月31日，数据库应用人员反映2020-12-31 12:40:10存在告警，过了几分钟之后业务恢复正常。

表现的状态：Connect to database time out, please check db status!

因为业务反馈的内容很有限，所以我们取了12月31日12:00-13:00的AWR进行分析。

可以看到AAS并不是很高，AAS=755.39/32.05=23.57

(备注：AAS是衡量快照时间内数据库负载的重要指标）

通过AWR观察

可以看到有大量的cursor:pin s wait on X和SGA:allocation forceing comonent growth等待事件。

2.问题分析

通过MOS因为ASMM和AMM使用自动调整内存管理方案。启用这些架构中的任何一种，都可以在SGA中的各个组件（例如缓冲区高速缓存和共享池）之间自动移动内存，以便在其中一个组件中填充内存请求导致的。


SQL> show parameter sga
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 206336M
sga_target                           big integer 0
SQL> show parameter target
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
archive_lag_target                   integer     0
db_flashback_retention_target        integer     1440
fast_start_io_target                 integer     0
fast_start_mttr_target               integer     0
memory_max_target                    big integer 206336M
memory_target                        big integer 206336M
parallel_servers_target              integer     32
pga_aggregate_target                 big integer 0
sga_target                           big integer 0
SQL> show parameter pga
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target                 big integer 0
SQL>

通过检查发现数据库使用的AMM的内存管理方式，自动内存管理automatic memory management（以下均称AMM）是oracle 11g新推出的新特性，意在对实例中的PGA和SGA进行自动管理。AMM是自动共享内存管理automatic shared memory management（ASMM）的拓展

ORACLE 11g AMM 的引入，组合出来有 5 种内存管理形式.

自动内存管理（AMM） : memory_target=非0，是自动内存管理，如果初始化参数 LOCK_SGA=TRUE，则 AMM 是不可用的。

自动共享内存管理(ASMM): 在memory_target=0 and sga_target为非0的情形下是自动内存管理

手工共享内存管理 : memory_target=0 and sga_target=0 指定 share_pool_size 、db_cache_size 等 sga 参数

自动 PGA 管理 : memory_target=0 and workarea_size_policy=auto and PGA_AGGREGATE_TARGET=值

手动 PGA 管理 : memory_target=0 and workarea_size_policy=manal 然后指定 SORT_AREA_SIZE 等 PGA 参数，一般不使用手动管理PGA。

但是11g推出了自动内存管理（AMM）新特性，该特性引入后，虽然减轻了DBA手动设置共享内存的负担，shared pool和buffer cache及其它几个component可以根据需要自动调整大小,避免ora-4031的错误，但经常出现在shared pool和buffer cache之间发生频繁shrink/grow操作的现象，在一些高并发环境下，会刷出一批共享池对象，并间歇性持有shared pool latch,library cache lock等共享池latch，从而引发数据库性能问题的风险，极端情况下，会导致数据库性能短时间内极速下降。而且如果一旦刷出共享池对象，就会引起数据库大量游标失效，随后的解析会导致大量library cache及cursor等待事件。这也是为什么在AWR的前台等待事件中伴随着大量的cursor:pin s wait on X等待事件的原因。

SQL> set linesize 600
SQL> col component for a25
SQL> col oper_type for a15
SQL> col oper_mode for a10
SQL> col parameter for a25
SQL> col  initial_size for 999999999999
SQL> col final_size for 99999999999
SQL>  select component,
  2   oper_type,
  3   oper_mode,
  4   parameter,
  5   initial_size,
  6   target_size,
  7   final_size,
  8   status,
  9   start_time,
 10   end_time as changed_time
 11   from V$SGA_RESIZE_OPS
 12   where to_char(end_time,'yyyy-mm-dd hh')='2020-12-31 12'
 
 13   order by end_time;
COMPONENT                 OPER_TYPE       OPER_MODE  PARAMETER                  INITIAL_SIZE TARGET_SIZE   FINAL_SIZE STATUS    START_TIME          CHANGED_TIME
------------------------- --------------- ---------- ------------------------- ------------- ----------- ------------ --------- ------------------- -------------------
shared pool               SHRINK          DEFERRED   shared_pool_size            19327352832  1.8790E+10  18790481920 COMPLETE  2020-12-31 12:38:59 2020-12-31 12:40:42
 
DEFAULT buffer cache      GROW            DEFERRED   db_cache_size               51002736640  5.1540E+10  51539607552 COMPLETE  2020-12-31 12:38:59 2020-12-31 12:40:42
 
DEFAULT buffer cache      SHRINK          IMMEDIATE  db_cache_size               51539607552  5.1003E+10  51002736640 COMPLETE  2020-12-31 12:40:42 2020-12-31 12:40:44
shared pool               GROW            IMMEDIATE  shared_pool_size            18790481920  1.9327E+10  19327352832 COMPLETE  2020-12-31 12:40:42 2020-12-31 12:40:44
 
DEFAULT buffer cache      SHRINK          IMMEDIATE  db_cache_size               51002736640  5.0466E+10  50465865728 COMPLETE  2020-12-31 12:40:44 2020-12-31 12:40:47
shared pool               GROW            IMMEDIATE  shared_pool_size            19327352832  1.9864E+10  19864223744 COMPLETE  2020-12-31 12:40:44 2020-12-31 12:40:47

可以看到在12:38-12:40出现了sharepool增长和buffer cache的shrink，buffer cache会刷出部分对象，会导致一些SQL语句被重新硬解析。

备注：buffercache的大小可以从v$sga_dynamic_components进行查询

然后我们再观察AWR的SGA组件明细

从AWR报告看到，KGH: NO ACCESS 类型内存占用已经接近600M左右。内存参数仅仅配置了memory_target，没有配置SHARED_POOL_SIZE, DB_CACHE_SIZE等。KGH: NO ACCESS 是内存在shared pool和db cache之间调节的一个中间状态，正常情况不超过100-200M，而且很快消失，内存调节过于频繁导致卡死在KGH: NO ACCESS，进而可能导致可用shared pool不足，导致数据库出现性能问题。

3.问题处理

通过以往的经验看，SGA_TARGET的稳定性高于memory_target，可以考虑不使用memory_target，而是用SGA_TARGET和pga_aggregate_target的组合。

所以建议如下：

1.关闭自动内存扩展，采用手工共享内存管理或者自动共享内存的方式，但是需要注意的是Disable AMM/ASMM也可以作为一个方法,但是缺点是: 碰到ora-4031的几率会比自动内存管理大.

2.设置SHARED_POOL_SIZE, DB_CACHE_SIZE，确保这些池有一个最小值，从而减少过度调节。

3.设置alter system set "_memory_broker_stat_interval"=999; 降低调节频率,设置resize的频率不能少于16分钟

4.重启实例，清空当前异常内存分配。

最后，我们采用的方式是，使用ASMM方式，将大页启用

SQL> show parameter target       
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
archive_lag_target                   integer     0
db_flashback_retention_target        integer     1440
fast_start_io_target                 integer     0
fast_start_mttr_target               integer     0
memory_max_target                    big integer 0
memory_target                        big integer 0
parallel_servers_target              integer     32
pga_aggregate_target                 big integer 50G
sga_target                           big integer 280G
SQL> show parameter db_cache_size
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_cache_size                        big integer 100G
SQL> show parameter shared_pool_size
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
shared_pool_size                     big integer 60G

大页使用情况

[oracle@xsdbd31 ~]$ grep -i huge /proc/meminfo
AnonHugePages: 1587200 kB
HugePages_Total: 143380
HugePages_Free: 13567
HugePages_Rsvd: 13548
HugePages_Surp: 0
Hugepagesize: 2048 kB