[BUG]Memory Notification: Library Cache Object loaded into SGA

╰半橙微兮° 2022-06-18 00:15 76阅读 0赞

> 今天再厦门的测试库ALERT日志中发现了好多如下错误信息：
> 
> **运行环境：**REDHAT 4.0 + ORACLE 10.2.0.1
> 
> **错误信息：**
> 
> Mon Aug 15 01:06:00 2011  
> Errors in file /opt/ora10g/admin/gis/udump/gis\_ora\_3177.trc:  
> Mon Aug 15 09:02:19 2011  
> Memory Notification: Library Cache Object loaded into SGA  
> Heap size 2597K exceeds notification threshold (2048K)  
> KGL object name :select  
>    t.owner,  
>    t.table\_name,  
>    r.comments,  
>    decode(t.temporary, 'Y', 'global temporary', ''),  
>    t.table\_type\_owner,  
>    t.table\_type,  
>    decode (t.table\_type, 'XMLTYPE', '%SqlXMLTable.'||t.owner||t.table\_name||'1%', ''),  
>    decode (t.table\_type, 'XMLTYPE', '%SqlXMLTable.'||t.owner||t.table\_name||'2%', '')  
> from  
>    sys.all\_tab\_comments r,  
>    sys.all\_all\_tables t  
> where  
>    not exists (select 1 from sys.all\_mviews s where s.owner = t.owner and t.table\_name in (s.mview\_name, s.update\_log))  
> Mon Aug 15 09:02:19 2011  
> Memory Notification: Library Cache Object loaded into SGA  
> Heap size 2597K exceeds notification threshold (2048K)  
> Details in trace file /opt/ora10g/admin/gis/udump/gis\_ora\_3799.trc  
> KGL object name :……省略……
> 
>  
> 
> 查看：/opt/ora10g/admin/gis/udump/gis\_ora\_3177.trc 
> 
> \[oracle@WinBosTest ~\]$ cat /opt/ora10g/admin/gis/udump/gis\_ora\_3177.trc  
> /opt/ora10g/admin/gis/udump/gis\_ora\_3177.trc  
> Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production  
> With the Partitioning, OLAP and Data Mining options  
> ORACLE\_HOME = /opt/ora10g/product/10.2.0/db\_1  
> System name:    Linux  
> Node name:      WinBosTest  
> Release:        2.6.9-5.ELsmp  
> Version:        \#1 SMP Wed Jan 5 19:30:39 EST 2005  
> Machine:        i686  
> Instance name: gis  
> Redo thread mounted by this instance: 1  
> Oracle process number: 20  
> Unix process pid: 3177, image: [oracle@WinBosTest][oracle_WinBosTest] (TNS V1-V3)
> 
> \*\*\* 2011-08-15 01:06:00.129  
> \*\*\* ACTION NAME:(0000054 STARTED16) 2011-08-15 01:06:00.099  
> \*\*\* MODULE NAME:(backup full datafile) 2011-08-15 01:06:00.099  
> \*\*\* SERVICE NAME:(SYS$USERS) 2011-08-15 01:06:00.099  
> \*\*\* SESSION ID:(83.10969) 2011-08-15 01:06:00.099
> 
>  
> 
> 查看：/opt/ora10g/admin/gis/udump/gis\_ora\_3799.trc
> 
> \[oracle@WinBosTest ~\]$ cat /opt/ora10g/admin/gis/udump/gis\_ora\_3799.trc  
> /opt/ora10g/admin/gis/udump/gis\_ora\_3799.trc  
> Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production  
> With the Partitioning, OLAP and Data Mining options  
> ORACLE\_HOME = /opt/ora10g/product/10.2.0/db\_1  
> System name:    Linux  
> Node name:      WinBosTest  
> Release:        2.6.9-5.ELsmp  
> Version:        \#1 SMP Wed Jan 5 19:30:39 EST 2005  
> Machine:        i686  
> Instance name: gis  
> Redo thread mounted by this instance: 1  
> Oracle process number: 42  
> Unix process pid: 3799, image: [oraclegis@WinBosTest][oraclegis_WinBosTest]
> 
> \*\*\* 2011-08-15 09:02:19.036  
> \*\*\* SERVICE NAME:(gis) 2011-08-15 09:02:19.009  
> \*\*\* SESSION ID:(156.12609) 2011-08-15 09:02:19.009  
> Memory Notification: Library Cache Object loaded into SGA  
> Heap size 2597K exceeds notification threshold (2048K)  
> LIBRARY OBJECT HANDLE: handle=9d782418 mutex=0x9d7824cc(0)
> 
> .....省略部分.....
> 
>   DATA BLOCKS:  
>   data\#     heap  pointer    status pins change whr alloc(K)  size(K)  
>   ----- -------- -------- --------- ---- ------ --- -------- --------  
>       0 9d8fda54 97234704 I/P/A/-/-    0 NONE   00      4.80     5.12  
>       6 97b5ff14 86b4e204 I/P/A/-/-    1 NONE   00   2764.55  2767.28
> 
>  
> 
> **错误信息分析：**
> 
> 看到这个信息Heap size 2597K exceeds notification threshold (2048K)，查询资料和分析
> 
> **1）确认数据库版本**
> 
> SQL> select \* from v$version;
> 
> BANNER  
> \----------------------------------------------------------------  
> Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod  
> PL/SQL Release 10.2.0.1.0 - Production  
> CORE    10.2.0.1.0      Production  
> TNS for Linux: Version 10.2.0.1.0 - Production  
> NLSRTL Version 10.2.0.1.0 - Production
> 
>  
> 
> **2）确认隐含参数\_kgl\_large\_heap\_warning\_threshold值**  
> SQL> SELECT A.KSPPINM NAME, B.KSPPSTVL VALUE, A.KSPPDESC DESCRIPTION  
>   2    FROM X$KSPPI A, X$KSPPCV B  
>   3   WHERE A.INDX = B.INDX  
>   4     AND A.KSPPINM = '\_kgl\_large\_heap\_warning\_threshold';
> 
> NAME  
> \--------------------------------------------------------------------------------  
> VALUE  
> \--------------------------------------------------------------------------------  
> DESCRIPTION  
> \--------------------------------------------------------------------------------  
> \_kgl\_large\_heap\_warning\_threshold  
> 2097152  
> maximum heap size before KGL writes warnings to the alert log
> 
>  
> 
> **错误原因：**
> 
> 参考**MOS中的参考信息，**关于这个问题Oracle的MOS中\[ID 330239.1\]文章有专门的描述，引用在此，供参考。  
> Memory Notification: Library Cache Object Loaded Into Sga \[ID 330239.1\]  
>   
> Applies to:  
> Oracle **Server** \- Enterprise Edition  
> This problem can occur on 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 2294K exceeds notification threshold (2048K)  
> Changes  
>   
> Installed / Upgraded to 10g Release 2  
> Cause  
>   
> These are warning messages that should not cause the program responsible for these errors to fail.  They appear as a result of new event messaging mechanism 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 undocumented 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  
>   
> SQL> show parameter \_kgl\_large\_heap\_warning\_threshold  
> NAME TYPE VALUE  
> \------------------------------------ ----------- ------------------------------  
> \_kgl\_large\_heap\_warning\_threshold integer 8388608  
>   
> 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 2M.   So these messages could show up frequently in some application environments.  
>   
> In 10.2.0.2,  the threshold was increased to 50MB after regression tests, so this should be a reasonable and recommended value
> 
>  
> 
> **解决办法：**
> 
> 既然知道了问题原因，处理起来就很简单了。如果不希望在alert文件中看到这些报错，可以适当调大隐含参数“\_kgl\_large\_heap\_warning\_threshold”的值，或将其设置为“0”。  
> 1）将\_kgl\_large\_heap\_warning\_threshold参数大小调整为50M  
> sys@orcl> alter system set "\_kgl\_large\_heap\_warning\_threshold"=52428800 scope=spfile;  
>   
> System altered.  
>   
> 2）重启数据库（慎重执行）  
>        由于我这里是数据库，因此可以选择合适时机重新启动，如果是生产库，必须慎重考虑。因此推荐一定要尽量升级到ORACLE 10.2.0.4,避免各种不可预知的BUG。

[oracle_WinBosTest]: mailto:oracle@WinBosTest
[oraclegis_WinBosTest]: mailto:oraclegis@WinBosTest