Heap size 80869 K exceeds notification threshold (51200 K)

A few times ago, the alert Log obtained the prompt that the Heap size exceeds the specified threshold, that is, Heap size 80869 K exceeds notification threshold (51200 K ). The threshold value has increased to 50 MB since Oracle 10.2.0.2. Theoretically, the LRU algorithm should be sufficient. This problem occurs because there are too many fragments in the SGA and the prompt message cannot be found to accommodate the current statement at a time. Note that the database does not provide a ora-04031 error prompt at this time. The following is a detailed description.

 

I. Prompt information for alert Log Extraction

Fri Feb 7 19:47:21 2014 Memory Notification: Library Cache Object loaded into SGAHeap size 80869 K exceeds notification threshold (51200 K) details in trace file/u02/database/UK3200/udump/login object name: insert into partition (ACCOUNT_CODE_ID, ACCOUNT_CODE, ACCOUNT_NAME, VOUCHER_NO, ACC_NUM, CURR_CD, ACCOUNT_PERIOD, VALUE_DATE, TRANS_DATE, ACC_POS_HIST_ID, DEBIT_CREDI T, TRANS_DESC, TRANS_TYPE_CD, AE _ID, AMOUNT, CATEGORY, FX, DESCRIPTION, CUST_SUB_TYPE, VOU_TYPE, DISPLAY_TYPE, struct, TYPE_GRP, struct, ACCOUNT_JV_TYPE_ID, JV_DESC, INPUT_DATE) WITH VOU_TBL AS (select. ACC_NUM,. CURR_CD, SUBSTR (: B2, 1, 6) AS ACCOUNT_PERIOD,. VALUE_DATE,. APPROVAL_DATE,. TRANSACTION_CMon Feb 10 18:22:32 2014 Memory Notification: Library Cache Object loa Ded into SGAHeap size 80865 K exceeds notification threshold (51200 K) KGL object name: insert into partition (ACCOUNT_CODE_ID, ACCOUNT_CODE, ACCOUNT_NAME, region, ACC_NUM, CURR_CD, ACCOUNT_PERIOD, VALUE_DATE, TRANS_DATE, ACC_POS_HIST_ID, DEBIT_CREDIT, TRANS_DESC, TRANS_TYPE_CD, AE _ID, AMOUNT, CATEGORY, FX, DESCRIPTION, CUST_SUB_TYPE, VOU_TYPE, DISPLAY_TYPE, IS_HIDE_WHEN_ZERO, TYPE_G RP, DESC_ACCTING_STD, ACCOUNT_JV_TYPE_ID, JV_DESC, INPUT_DATE) WITH VOU_TBL AS (select. ACC_NUM,. CURR_CD, SUBSTR (: B2, 1, 6) AS ACCOUNT_PERIOD,. VALUE_DATE,. APPROVAL_DATE,. TRANSACTION_C # the log file under udump reaches 724 MB, which is the result of this prompt accumulation oracle @ linux-1234: ~> Ls-hltr/u02/database/UK3200/udump/UK3200_ora_6240.trc-rw-r ----- 1 oracle oinstall 724 M 2014-02-07/u02/database/UK3200/udump/UK3200_ora_6240.trc

Ii. Fault Analysis
For an SQL statement or PL/SQL statement to be executed, the code needs to allocate a continuous free space from the library cache to parse the statement. Oracle first scans the shared pool to find the idle memory. If no suitable chunk is found, it searches for a larger chunk. If it finds a smaller chunk than the requested chunk, then it is split, and the excess parts continue to be put in the idle list. As a result, fragments are generated. After a long period of operation, the system will produce a large number of small memory fragments. When a large memory block is allocated for a request, although the total idle space of the shared pool is large, no separate continuous idle block can meet the requirements, then, the system will eliminate the statements that are no longer used according to the LRU algorithm, which will still be unable to be allocated to the memory space, resulting in a 4031 error.
Generally, if the check finds that shared_pool_size is large enough, the 4031 error is generally caused by too many fragments.
Although fragments cannot be avoided, they should be minimized. The following are potential factors that may cause fragmentation:
Shared SQL is not used;
Too many unnecessary resolution calls (soft resolution );
No bound variable is used.

In this case, we have enough memory and cannot obtain continuous idle memory blocks at the moment. However, the insert statement is indeed long and close to 400 rows.

Shared_pool and SGA Analysis

-- Environment SQL> select * from v $ version where rownum <2; BANNER implements Oracle Database 10g Release 10.2.0.3.0-64bit Production -- The following is the query result about object overloading, despite some INVALIDATIONS, overall good performance SQL> @ brief gets gethits into pins pinhits into reloads invalidations into ---------- ------------ ---------- explain SQL AREA 607614 100098 16.47 268976344 267458010 99.44 TABLE/PROCEDURE 103083 101598 4601210 29426797 28904427 98.22 178625 0 BODY 39663 34617 87.28 2503211 2490492 99.49 0 TRIGGER 6638 72063 70249 97.48 1631610 1628323 99.8 0 INDEX 1470 94655 51973 54.91 469366 380268 81.02 0 CLUSTER 3599 27630 27383 99.11 61368 60883 99.21 238 0 OBJECT 0 0 100 0 100 0 0 0 PIPE 0 0 100 0 100 0 0 java source 160 150 93.75 239 102 42.68 84 0 java resource 81 75 92.59 243 160 65.84 27 0 JAVA DATA 162 159 98.15 1130 1126 0 011 rows selected. -- about the hit rate of library cache SQL> SELECT SUM (pins) "Executions", 2 SUM (reloads) "Cache Misses while Executing", 3 ROUND (SUM (pins) /(SUM (reloads) + SUM (pins) * 100, 2) "Hit Ratio, %" 4 from v $ LIBRARYCACHE; Executions Cache Misses while Executing Hit Ratio, % ---------- -------------------------- ------------ 303544356 294260 99.9 -- available idle memory SQL> SELECT pool, name, bytes/1024/1024 FROM v $ sgastat WHERE name LIKE '% free memory %' AND pool = 'shared pool '; pool name bytes/1024/1024 ------------ BYTES --------------- shared pool free memory 91.7747498 -- advice about shared_pool. From the result, the current shared_pool is 340 mbSQL> SELECT shared_pool_size_for_estimate est_size, 2 BYTES size_factor, 3 estd_lc_size, 4 estd_lc_memory_objects obj_cnt, inclusav_factor 5 FROM v $ partition; EST_SIZE SIZE_FACTOR ESTD_LC_SIZE OBJ_CNT sav_factor ------------ -------------- 196. 5765 36 2822. 803 232. 6824 71 4400. 8567 268. 7882 106 5114. 9078 304. 8941 142 6095. 9532 340 1 177 7757 376 1.1059 212 9903 1.0434 412 1.2118 248 12080 1.0874 448 1.3176 283 14288 1.1382 484 1.4235 317 16277 1.1818 520 1.5294 351 16612 1.2166 556 1.6353 386 18515 1.2442 592 1.7412 421 18663 1.2679 628 1.8471 456 18885 1.2877 664 1.9529 490 19127 1.3033 700 2.0588 525 -- the current sga advice results increase to 19255 mb, ESTD_DB_TIME is 219849, the effect is not ideal -- that is to say, to increase the size of sga does not play a role in the current database -- Author: Leshami -- Blog: http://blog.csdn.net/leshami SQL> select sga_size, sga_size_factor, estd_db_time from v $ sga_target_advice order by 1; SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ---------- ------------- ------------ 286. 5 675749 429. 75 257214 572 1 222542 715 1.25 219849 858 1.5 219048 1001 1.75 219048 2 1144

Iii. solutions proposed by Oracle
Memory Notification: Library Cache Object Loaded Into Sga (Doc ID 330239.1) To Bottom

Applies:
Oracle Database-Enterprise Edition-Version 10.2.0.1 and later
Information in this document applies to any platform.
Oracle Server Enterprise Edition
.
SYMPTOMS
The following messages are reported in alert. log after 10g Release 2 is installed.
Memory Notification: Library Cache Object loaded into SGA
Heap size 2294 K exceeds notification threshold (2048 K)
CHANGES
Installed/Upgraded to 10g Release 2
CAUSE
These are warning messages that shocould not cause the program responsible for these errors to fail. They appear as a result of new event messaging mechanic and memory manager in 10g Release 2.
The meaning is that the process is just spending a lot of time in finding free memory extents during an allocate as the memory may be heavily fragmented. fragmentation in memory is impossible to eliminate completely, however, continued messages of large allocations in memory indicate there are tuning opportunities on the application.
The messages do not imply that an ORA-4031 is about to happen.

 

SOLUTION
In 10g we have a new uninitialized ented parameter that sets the KGL heap size warning threshold. This parameter was not present in 10gR1. Warnings are written if heap size exceeds this threshold.

Set _ kgl_large_heap_warning_threshold to a reasonable high value or zero to prevent these warning messages. Value needs to be set in bytes.

If you want to set this to 8192 (8192*1024) and are using an spfile:

(Logged in as "/as sysdba ")

SQL> alter system set "_ kgl_large_heap_warning_threshold" = 8388608 scope = spfile;

SQL> shutdown immediate
SQL> startup

If using an "old-style" init parameter,

Edit the init parameter file and add

_ Kgl_large_heap_warning_threshold = 8388608
NOTE: The default threshold in 10.2.0.1 is 2 M. So these messages cocould show up frequently in some application environments.
In 10.2.0.2, the threshold was increased to 50 MB after regression tests, so this shoshould be a reasonable and recommended value.

Final Solution:

1. Modify the hidden parameter based on meatlink. You cannot determine whether the setting has any negative impact or google answers to this question. 2. Adjust the code (omitted )) SQL> @ hidden_paraEnter value for para: large_heapKSPPINM ksppstvl describ quota ------------------ quota _ too old 52428800 maximum heap size before KGL writes warnings to the alert logSQL> alter system set "_ quota" = 82809856 scope = spfile;