Severe Performance Problems Due to Sudden drop/resize/shrink of buffer cache
(Doc ID 1344228.1)
Last updated on FEBRUARY 01, 2022
Applies to:
Oracle Database - Enterprise Edition - Version 11.2.0.2 and laterOracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A 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
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
| Symptoms |
| Changes |
| Cause |
| Solution |
| Impact of setting _enable_shared_pool_durations = false |
| References |