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
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
-- 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;
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
