Oracle database 22c insight:_ kgl_ Large_ heap_ assert_ Threshold automatic and manual adjustment

according to Oracle Annual release plan , There should be one every year Database A big version of , for example Oracle 18c、19c There are two versions .

Because of the impact of the epidemic Oracle 20c Only released on the cloud , Offline release directly jumps to 21c. Into the 2022 year , Although in some release plans ,Oracle 22c In the column , But there is news that ,Oracle 22c Will not release , The next release will be 23c.

But we can still get it from the official website , Found some new signs of a new version .

Case study

stay Oracle In the database , When there are operations with large memory consumption , The database may automatically adjust , To make adaptive configuration .
for example , stay Oracle Text（ Full text search ） In the component , When creating a CTX When indexing , There may be large-scale memory requirements , This leads to the following situation ：

_kgl_large_heap_assert_threshold" changes To 1.5GB Automatically when creating ctx index

The meaning of this parameter is ：

SQL> l  1  select  2  nam.ksppinm NAME,  3  nam.ksppdesc DESCRIPTION,  4  val.KSPPSTVL  5  from  6  x$ksppi nam,  7  x$ksppsv val  8* where nam.indx = val.indx and nam.ksppinm like '%kgl_large_heap_%_threshold%'SQL> /NAME                                          DESCRIPTION                                                     KSPPSTVL--------------------------------------------- --------------------------------------------------------------- --------------------_kgl_large_heap_warning_threshold             maximum heap size before KGL writes warnings to the alert log   52428800_kgl_large_heap_assert_threshold              maximum heap size before KGL raises an internal error           524288000

_kgl_large_heap_warning_threshold Is the threshold for writing early warning information
_kgl_large_heap_assert_threshold Is the largest exception thrown heap Size alarm threshold .

KGL - kernel generic library cache management, Refer to Library Cache Memory management part .

Bug 31585319 And repair

This Bug Appear in the Oracle 12.2.0.1 Later versions , stay Oracle Text When the component is in use .

Bug 31585319 - _KGL_LARGE_HEAP_ASSERT_THRESHOLD MODIFIES TO 1.5G WHEN CREATING CTX INDEX

This BUG The reason and repair is ：

1. stay kcilod() Function , Parameters _kgl_large_heap_assert_threshold The value of is always rewritten to 1.5G. This function creates CTX Called when indexing ;
2. Bug fix for kcilod() It's been modified , Only in parameter _kgl_large_heap_assert_threshold Greater than 0 And less than 1.5G when , To change its value to 1.5G.

The value of parameter _kgl_large_heap_assert_threshold always gets overwritten to 1.5G in kcilod() function. This function gets executed during index creation.
The bug fix modifies kcilod() to only change the value of parameter _kgl_large_heap_assert_threshold to 1.5G when it’s larger than 0 and less than 1.5G.

In this Bug The solution mentioned in , The question is Oracle 22c The Chinese government has amended , Early versions need to see the release of the patch .

This bug is fixed in the 22c Database release. Backports to earlier versions/platforms may be available for download from My Oracle Support.

Bug 31892029 And its avoidance

in addition , I also found another bug, This BUG yes Oracle 19.7 And later versions ： When creating complex materialized views , Probably because of a lot of Shared Pool Memory consumption ORA-04031 error ：

Create Complex MV Fails With Error ORA-04031 (eating All Memory) In The Shared_pool (Doc ID 2741563.1)

The occurrence of this error is similar to ：

CREATE MATERIALIZED VIEW TEST evaluate using current edition ASWITHPOPULATION_1 AS (SELECT case when...............

The information you may see in the system's tracking file is as follows ：

processes = 960shared_pool_size = 24Gmemory_target = 70Gmemory_max_target = 90GIn the trace we have the following:Chunk 11c0dd9d98 sz= 72 freeable "qsmkzbm: qbcinf"Chunk 11c0dd9de0 sz= 72 freeable "qsmkzbm: qbcinf"Chunk 11c0dd9e28 sz= 72 freeable "qsmkzbm: qbcinf"Chunk 11c0dd9e70 sz= 72 freeable "qsmkzbm: qbcinf"Chunk 11c0dd9eb8 sz= 72 freeable "qsmkzbm: qbcinf"Chunk 11c0dd9f00 sz= 72 freeable "qsmkzbm: qbcinf"Chunk 11c0dd9f48 sz= 72 freeable "qsmkzbm: qbcinf"Chunk 11c0dd9f90 sz= 72 freeable "qsmkzbm: qbcinf"Chunk 11c0dda000 sz= 96 freeable "qsmkzbm: qbcinf"Chunk 11c0dda060 sz= 72 freeable "qsmkzbm: qbcinf"Chunk 11c0dda0a8 sz= 72 freeable "qsmkzbm: qbcinf"

This problem is recognized as Bug:

Bug 31892029 - CREATING A COMPLEX VIEW RESULTS IN ORA-04031

A temporary solution is , Expand the scope of error reporting KGL Memory limit ：

If you see any ora-600, then adjust the _kgl_large_heap_assert_threshold,

Execute a command similar to the following ：

alter system set “_kgl_large_heap_assert_threshold”=2147483647;

Reference material

1. _kgl_large_heap_assert_threshold Changes to 1.5GB Automatically When Creating Ctx Index (Doc ID 2736948.1)
2. Create Complex MV Fails With Error ORA-04031 (eating All Memory) In The Shared_pool (Doc ID 2741563.1)