参数_kgl_large_heap_warning_threshold值影响性

外网数据库alert警告日志报如下提示：

Tue Oct 18 03:03:03 2011
The value (30) of MAXTRANS parameter ignored.
kupprdp: master process DM00 started with pid=122, OS id=30935
to execute - SYS.KUPM$MCP.MAIN('SYS_EXPORT_SCHEMA_01', 'PASSPORT', 'KUPC$C_1_20111018030304', 'KUPC$S_1_20111018030304', 0);
kupprdp: worker process DW01 started with worker id=1, pid=123, OS id=30937
to execute - SYS.KUPW$WORKER.MAIN('SYS_EXPORT_SCHEMA_01', 'PASSPORT');
kupprdp: worker process DW02 started with worker id=2, pid=124, OS id=30939
to execute - SYS.KUPW$WORKER.MAIN('SYS_EXPORT_SCHEMA_01', 'PASSPORT');
kupprdp: worker process DW03 started with worker id=3, pid=125, OS id=30941
to execute - SYS.KUPW$WORKER.MAIN('SYS_EXPORT_SCHEMA_01', 'PASSPORT');
kupprdp: worker process DW04 started with worker id=4, pid=126, OS id=30943
to execute - SYS.KUPW$WORKER.MAIN('SYS_EXPORT_SCHEMA_01', 'PASSPORT');
kupprdp: worker process DW05 started with worker id=5, pid=127, OS id=30945
to execute - SYS.KUPW$WORKER.MAIN('SYS_EXPORT_SCHEMA_01', 'PASSPORT');
kupprdp: worker process DW06 started with worker id=6, pid=128, OS id=30947
to execute - SYS.KUPW$WORKER.MAIN('SYS_EXPORT_SCHEMA_01', 'PASSPORT');
kupprdp: worker process DW07 started with worker id=7, pid=129, OS id=30949
to execute - SYS.KUPW$WORKER.MAIN('SYS_EXPORT_SCHEMA_01', 'PASSPORT');
kupprdp: worker process DW08 started with worker id=8, pid=130, OS id=30951
to execute - SYS.KUPW$WORKER.MAIN('SYS_EXPORT_SCHEMA_01', 'PASSPORT');
Tue Oct 18 03:03:18 2011
Memory Notification: Library Cache Object loaded into SGA
Heap size 8347K exceeds notification threshold (8192K)
KGL object name :SELECT /*+rule*/ SYS_XMLGEN(VALUE(KU$), XMLFORMAT.createFormat2('TABLE_T', '7')), KU$.OBJ_NUM ,KU$.ANC_OBJ.NAME ,KU$.ANC_OBJ.OWNER_NAME ,KU$.ANC_OBJ.TYPE_NAME ,KU$.BASE_OBJ.NAME ,KU$.BASE_OBJ.OWNER_NAME ,KU$.BASE_OBJ.TYPE_NAME ,KU$.SPARE1 ,KU$.XMLSCHEMACOLS ,KU$.SCHEMA_OBJ.NAME ,KU$.SCHEMA_OBJ.NAME ,'TABLE' ,KU$.PROPERTY ,KU$.SCHEMA_OBJ.OWNER_NAME ,KU$.TS_NAME ,KU$.TRIGFLAG FROM SYS.KU$_FHTABLE_VIEW KU$ WHERE NOT (BITAND (KU$.PROPERTY,8192)=8192) AND NOT BITAND(KU$.SCHEMA_OBJ.FLAGS,128)!=0 AND KU$.OBJ_NU
Tue Oct 18 06:14:49 2011
Stopping background process CJQ0
Tue Oct 18 17:15:44 2011

Thread 1 advanced to log sequence 397

原因：

Oracle 10.2.0.1，隐含参数_kgl_large_heap_warning_threshold默认值为2m，过小；在10.2.0.2中默认值修改为50m；

Oracle10g中，在load较大的对象进library cache中时，会记录以上警告。这是一隐含参数_kgl_large_heap_warning_threshold 指定的，

如果系统中需要load很多大对象，又不想在alert中看到太多这类的警告信息，可以修改该参数。

其中Oracle library cache参见我的blog:

Oracle内存结构 share pool library cache

http://blog.csdn.net/xujinyang/article/details/6884995

解决方法：

将参数_kgl_large_heap_warning_threshold值修改为50m（或0以及其他较大值），过程如下

1、登录数据库

sqlplus / a sysdba

2、设置_kgl_large_heap_warning_threshold 参数

SQL>alter system set "_kgl_large_heap_warning_threshold"=52428800 scope=spfile;

3、重启数据库