Severe Performance Problems Due to Sudden drop/resize/shrink of buffer cache (Doc ID 1344228.1)

Last updated on NOVEMBER 28, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.2 and later
Information in this document applies to any platform.

Symptoms

Automatic memory management or Automatic shared memory management is being used on the instance.
SGA resize operations , as shown in AWR report or V$SGA_RESIZE_OPS, shows the buffer cache size suddenly drops by a huge amount ( like approx 2400M in the example below ) which led to performance issue due to decreased cache size ( i/o bottleneck )

The AWR reports during that time period shows


Cache Sizes

                     Begin       End
Buffer Cache :      2,624M      640M   <==decrease in size during this period
Shared Pool Size:   4,512M    6,752M   <=== Sudden jump in the shared pool
.

Memory Resize Operations Summary



SGA breakdown difference

Pool Name                      Begin MB      End MB   % Diff
shared KGH: NO ACCESS          2,387.88              -100.00   <===this became free in shared pool
shared KGLH0                   1,103.38    1,171.28     6.15
shared free memory             2,310.09    4,401.56    90.54   <==see increase in free
buffer_cache                   2,624.00      640.00   -75.61   <=== Memory stole from buffer pool


Following this other re size operation seems to error out, which seems like AMM is broken


Memory Resize Ops

                                                      Delta
Start Ela (s)    omponent  Oper Typ/Mod  Init Size(M) Target Delta Final (M) Sta
06/20 01:45:58 0 bufcache      GRO/DEF            608    32     96       640 ERR <==trying to grow buffer cache and get ERR
06/20 01:45:58 0 shared        SHR/DEF          6,784   -32    -96     6,752 ERR
06/20 01:41:28 0 bufcache      GRO/DEF            512    96              608 COM
06/20 01:41:28 0 shared        SHR/DEF          6,880   -96            6,784 COM
06/20 01:36:58 0 bufcache      GRO/DEF            416    96              512 COM <== suddenly at 416 and shows grow op to 512
06/20 01:36:58 0 shared        SHR/DEF          6,976   -96            6,880 COM <== shared pool and buf cache shrinking for pga growth
06/20 01:32:53 0 pga           GRO/MAN          3,680   160            3,840 COM <== pga grow by 160
06/20 01:32:25 0 bufcache      SHR/IMM          2,848   -32            2,816 COM <=== shrunk to 2816
06/20 01:32:25 0 shared        GRO/IMM          4,544    32            4,576 COM
06/20 01:32:24 0 bufcache      SHR/IMM          2,912   -32            2,880 COM

.................
.................

Changes

++ Database migrated to 11gR2.
++ Using AMM  feature ( i.e Memory_target & Memory_Max_Size ) .

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms