Monday, January 31, 2022

Oracle SQL Monitoring Shared Pool "keomg: entry list " Memory Leak

This Blog will try to demonstrate shared pool "keomg: entry list " memory leak when SQL is monitored.
Note 1: Tested in Oracle 19.11 and 19.13 with following settings:
  -- set 3 subpools in shared_pool
  alter system set "_kghdsidx_count"=3 scope=spfile;
  
  -- disable autosga to generate ORA-04031
  alter system set "_memory_imm_mode_without_autosga"=false;
Note 2: The behaviour was first observed by other people in Oracle applications.


1. "keomg: entry list " Memory Leak Test


"keomg: entry list " is used to store SQL Monitoring data. With following tests, we can observe the continuous increase of memory usage only when SQL is monitored.

--================ Test Setup ================--             
drop table keomg_tab1 purge;
create table keomg_tab1 as select level x, rpad('ABC', 20, 'X') y from dual connect by level < 1e3;
create index keomg_tab1#1 on keomg_tab1(x, y);
 
--================ Test 1. WithOut Hint, DELTA=0 ================--
col bytes new_value bytes_before
 
select name, bytes from v$sgastat s where name like 'keomg: entry list%';
select count(*) from keomg_tab1 where y is not null;
select name, bytes, (bytes - &bytes_before) delta from v$sgastat s where name like 'keomg: entry list%';
 
  NAME                    BYTES      DELTA
  ------------------ ---------- ----------
  keomg: entry list     1044480          0
       
--================ Test 2. With MONITOR Hint, DELTA=3072 ================--
select name, bytes from v$sgastat s where name like 'keomg: entry list%';
select /*+ MONITOR */ count(*) from keomg_tab1 where y is not null;
select name, bytes, (bytes - &bytes_before) delta from v$sgastat s where name like 'keomg: entry list%';
 
  NAME                    BYTES      DELTA
  ------------------ ---------- ----------
  keomg: entry list     1047552       3072
      
--================ Test 3. With Parallel 2 Hint, DELTA=9216 ================--
select name, bytes from v$sgastat s where name like 'keomg: entry list%';
select /*+ parallel(2) */ count(*) from keomg_tab1 where y is not null;
select name, bytes, (bytes - &bytes_before) delta from v$sgastat s where name like 'keomg: entry list%';
 
  NAME                    BYTES      DELTA
  ------------------ ---------- ----------
  keomg: entry list     1056768       9216
      
--================ Test 4. With Parallel 16 Hint, DELTA=52224 ================--
select name, bytes from v$sgastat s where name like 'keomg: entry list%';
select /*+ parallel(16) */ count(*) from keomg_tab1 where y is not null;
select name, bytes, (bytes - &bytes_before) delta from v$sgastat s where name like 'keomg: entry list%';
 
  NAME                    BYTES      DELTA
  ------------------ ---------- ----------
  keomg: entry list     1108992      52224
      
--================ Test 5. Index Rebuild Without Parallel Degree, DELTA=0 ================--
select name, bytes from v$sgastat s where name like 'keomg: entry list%';
alter index keomg_tab1#1 rebuild;
select name, bytes, (bytes - &bytes_before) delta from v$sgastat s where name like 'keomg: entry list%';
 
  NAME                    BYTES      DELTA
  ------------------ ---------- ----------
  keomg: entry list     1108992          0
      
--================ Test 6. Index Rebuild With Parallel 2, DELTA=15360 ================--
select name, bytes from v$sgastat s where name like 'keomg: entry list%';
alter index keomg_tab1#1 rebuild parallel 2;
select name, bytes, (bytes - &bytes_before) delta from v$sgastat s where name like 'keomg: entry list%';
alter index keomg_tab1#1 noparallel;
 
  NAME                    BYTES      DELTA
  ------------------ ---------- ----------
  keomg: entry list     1124352      15360
        
--================ Test 7. Index Rebuild With Parallel 16, DELTA=101376 ================--
select name, bytes from v$sgastat s where name like 'keomg: entry list%';
alter index keomg_tab1#1 rebuild parallel 16;
select name, bytes, (bytes - &bytes_before) delta from v$sgastat s where name like 'keomg: entry list%';
alter index keomg_tab1#1 noparallel;
 
  NAME                    BYTES      DELTA
  ------------------ ---------- ----------
  keomg: entry list     1225728     101376
From above tests, we can see memory increase when using MONITOR hint, Parallel hint, and index rebuild with Parallel Degree. The magnitude of increase is proportional to Parallel Degree.

With following queries, we can look SQL statements whose execution have been (or are being) monitored.

-- X$KESWXMON
select * from v$sql_monitor order by last_refresh_time desc;
select * from v$sql_monitor_statname 

-- X$ALL_KESWXMON
select * from gv$all_sql_monitor order by last_refresh_time desc;
select * from gv$sql_plan_monitor order by last_refresh_time desc;
select * from gv$all_sql_plan_monitor order by last_refresh_time desc;
select * from gv$sql_monitor_statname;


2. Oracle Patches


In Oracle 19.13, two Patches are included to fix the problem:
  Bug 32465193 - ORA-4031 Due to high SQL Monitoring allocations in Shared Pool
  Bug 32645139 - ORA-4031 Top 10 Memory Uses for SGA Heap Shows Keomg Allocations
      (Patch 33523677: MERGE ON DATABASE RU 19.12.0.0.0 OF 32465193 32645139)
However, the test on Oracle 19.13 with both patches installed shows the same result as Oracle 19.11. Both above patches have no effect on "keomg: entry list " memory leak.


3. Workaround


Oracle has a few hidden parameters to control the SQL Monitoring:

  Name                         Description                                                                   Default
  ---------------------------  ----------------------------------------------------------------------------  -------
  _dbop_enabled                Any positive number enables automatic DBOP monitoring. 0 is disabled          1     
  _sqlmon_threshold            CPU/IO time threshold before a statement is monitored. 0 is disabled          5     
  _sqlmon_max_plan             Maximum number of plans entry that can be monitored. Defaults to 20 per CPU   120   
  _sqlmon_max_planlines        Number of plan lines beyond which a plan cannot be monitored                  300   
  _sqlmon_binds_xml_format     format of column binds_xml in [G]V$SQL_MONITOR                                default
  _px_load_monitor_threshold   threshold for pushing information to load slave workload monitor              10000 
  _merge_monitor_threshold     threshold for pushing information to MERGE monitoring                         10000 
  _monitor_workload_interval   workload monitoring interval in hours                                         24    
  _sqlmon_recycle_time        Minimum time (in s) to wait before a plan entry can be recycled                      5
One quick workaround is to disable SQL Monitoring (disable/enable are immediate, not need DB restart):

  -- disable
  alter system set "_sqlmon_threshold"=0 scope=both sid='*';
               
  -- enable to default
  alter system set "_sqlmon_threshold"=5 scope=both sid='*';
               
  -- reset also disable
  alter system reset "_sqlmon_threshold" scope=both;
For further information about SQL Monitoring, see Oracle MOS: Monitoring SQL Statements with Real-Time SQL Monitoring (Doc ID 1380492.1)


4. "keomg: entry list " Component Single Subpool Allocation in Shared Pool


In our test DB, we set "_kghdsidx_count"=3 to configure 3 subpools in shared_pool. With following query, we can see "keomg: entry list " is only allocated in one single subpool: Subpool 3. Eventually we will experience the ORA-04031 error due to single subpool memory pressure, similar to "SO private sga" discussed in Blog: Oracle 19c new shared pool "SO private sga" and "SO private so latch" Performance Impacts

-- x$ksmsp lists each memomy chunk (ksmchptr, minimum unit) in each area (ksmchpar) for each component (ksmchcom) in subpool (ksmchidx)
-- x$ksmsp does not contain reserved extents
select ksmchidx subpool, ksmchcom, count(*) row_cnt, sum(ksmchsiz) siz
  from x$ksmsp
 where ksmchcom like 'keomg: entry%'
 group by ksmchidx, ksmchcom;
 
  SUBPOOL  KSMCHCOM         ROW_CNT     SIZ
  -------  ---------------- -------  ------
        3  keomg: entry li      274  848304
       
        
-- x$ksmss (v$sgastat) is about stats of SGA component (ksmssnam) in each subpool (ksmdsidx)
-- ksmdsidx = 0 is for reserved extents
 
select ksmdsidx subpool, ksmssnam, count(*) row_cnt, sum(ksmsslen) siz
  from x$ksmss
 where ksmssnam like 'keomg: entry%'
  group by ksmdsidx, ksmssnam;
 
  SUBPOOL  KSMSSNAM           ROW_CNT     SIZ
  -------  -----------------  -------  ------
        3  keomg: entry list        1  841728


5. ORA-04031 Dump


Following dump is from an Oracle 19.11 hitting ORA-04031 due to "keomg: entry list " single subpool allocation

In this case, "keomg: entry list " is only allocated into "SUB POOL 3", and occupies 50% of "SUB POOL 3" with 2304 MB, hence positioned as TOP consumer.

By the way, we can also see ""SO private sga" (711 MB 14%) is only allocated into "SUB POOL 4".

Subroutine "keswxCurEndPlanMonitoringCb" in ORA-4031 Error Stack points out that xplan SQL Monitor signals no space available in the heap (kghnospc) during memory allocation (kghalo).
       
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.11.0.0.0

Begin 4031 Diagnostic Information
 
Allocation request for: keomg: entry list
Requested from sga heap(3,0), Heap: 0x700000000169c48, size: 3096
******************************************************
HEAP DUMP heap name="sga heap(3,0)" desc=700000000169c48
 
==============================================
TOP 10 MEMORY USES FOR SGA HEAP SUB POOL 3
----------------------------------------------
"keomg: entry list             "  2304 MB 50%
"free memory                   "   752 MB 16%
"db_block_hash_buckets         "   295 MB  6%
"KTC latch subh                "   251 MB  5%
"file queue buckets            "   145 MB  3%
"object queue header free      "   109 MB  2%
"KGLH0                         "   107 MB  2%
"object queue hash buckets     "    74 MB  2%
"KKSSP                         "    66 MB  1%
"kglsim object batch           "    54 MB  1%
     -----------------------------------------
free memory                        750 MB
memory alloc.                     3858 MB
Sub total                         4609 MB
==============================================
 
==============================================
TOP 10 MEMORY USES FOR SGA HEAP SUB POOL 4
----------------------------------------------
"KGLH0                         "   923 MB 18%
"SQLA                          "   902 MB 18%
"SO private sga                "   711 MB 14%
"free memory                   "   570 MB 11%
"db_block_hash_buckets         "   289 MB  6%
"KTC latch subh                "   261 MB  5%
"SQLP                          "   254 MB  5%
"file queue buckets            "   147 MB  3%
"KGLHD                         "   125 MB  2%
"object queue header free      "   108 MB  2%
     -----------------------------------------
free memory                        572 MB
memory alloc.                     4548 MB
Sub total                         5122 MB
==============================================
 
Error Stack: ORA-4031
ksm_ 4031_dump <- ksmasg <- kghallocpdb_swcb <- kgh_invoke_alloc_cb <- kghnospc
<- kghalo <- kghxal <- keomgVarAddBytes <- keswxWriteEndinfoToStream
<- keswxCurEndPlanMonitoringCb <- kxsffir <- kxsFreeWorkArea
<- kxsFreeExecutionHeap <- kksumc <- opiexe <- kpoal8 <- opiodr <- kpoodr <- upirtrc <- kpurcsc
<- kpuexec <- OCIStmtExecute <- jslvec_execcb <- jslvswu <- jslvCDBSwitchUsr
<- jslve_execute0 <- jslve_execute <- jslve_cdb_execute <- rpiswu2 <- kkjexle_cdb
<- kkjsexe <- kkjrdp <- opirip <- opidrv <- sou2o <- opimai_real <- ssthrdmain <- main