16:25:06  > !cat sqlbind.sql

set lines 150 pages 50 verif off trimspool on
col VAR_NAME form A18
col TYPE_VALUE form A30
col sess_param  form A80

-- 20080506  jailee

accept sql_id      prompt 'Please enter the value for Sql_id : '

select *
from (select decode(no,1,'VAR ','exec :')||replace(name,':','') VAR_NAME,decode(no,1,datatype_string, ' := '||''''||value_string||''';') TYPE_VALUE
      from v$sql_bind_capture b
          ,(select 1 no from dual union all select 2 from dual) t
      where sql_id = '&sql_id'
      order by 1,2
     )
union all
select '--DBA_HIST_SQLBIND','' from dual
union all
select *
from (select decode(no,1,'VAR ','exec :')||replace(name,':',''),decode(no,1,datatype_string, ' := '||''''||value_string||''';')
      from DBA_HIST_SQLBIND b
          ,(select 1 no from dual union all select 2 from dual) t
      where  dbid = (select dbid from v$database)
      and sql_id = '&&sql_id'
      and (snap_id , dbid) = (select max(snap_id) , max(b.dbid)
                              from DBA_HIST_SQLBIND, (select dbid from v$database )  b
                              where sql_id =  '&&sql_id')
      order by b.snap_id,1,2
     )
;

select --SQL_ID,
       CHILD_ADDRESS,isdefault,'"'||NAME||'" = '||value||' ;' sess_param
from v$sql_optimizer_env b
where sql_id = '&&sql_id'
--and isdefault = 'NO'
order by isdefault,name
;

16:26:10 > @sqlbind
Please enter the value for Sql_id : 55ynxfrzdhzhw

VAR_NAME           TYPE_VALUE
------------------ ------------------------------
VAR B1             CHAR(32)
VAR B2             CHAR(32)
VAR B3             CHAR(32)
VAR B4             CHAR(32)
exec :B1            := '001';
exec :B2            := '2008-05-01';
exec :B3            := '2008-05-02';
exec :B4            := '107983481   ';
--DBA_HIST_SQLBIND
VAR B1             CHAR(32)
VAR B2             CHAR(32)
VAR B3             CHAR(32)
VAR B4             CHAR(32)
exec :B1            := '001';
exec :B2            := '2008-05-01';
exec :B3            := '2008-05-02';
exec :B4            := '107981134   ';

19 rows selected.

Elapsed: 00:00:00.55

CHILD_ADDRESS    ISD SESS_PARAM
---------------- --- --------------------------------------------------------------------------------
C00000076A201F58 NO  "_b_tree_bitmap_plans" = false ;
C00000076A201F58 NO  "_bloom_filter_enabled" = false ;
C00000076A201F58 NO  "_db_file_optimizer_read_count" = 128 ;
C00000076A201F58 NO  "_gby_hash_aggregation_enabled" = false ;
C00000076A201F58 NO  "_index_join_enabled" = false ;
C00000076A201F58 NO  "_optimizer_compute_index_stats" = false ;
C00000076A201F58 NO  "_optimizer_sortmerge_join_enabled" = false ;
C00000076A201F58 NO  "_pga_max_size" = 2097152 KB ;
C00000076A201F58 NO  "optimizer_secure_view_merging" = false ;
C00000076A201F58 NO  "sort_area_size" = 2147483647 ;
C00000076A201F58 YES "active_instance_count" = 3 ;
C00000076A201F58 YES "bitmap_merge_area_size" = 1048576 ;
C00000076A201F58 YES "cpu_count" = 14 ;
C00000076A201F58 YES "cursor_sharing" = exact ;
C00000076A201F58 YES "hash_area_size" = -2 ;
C00000076A201F58 YES "optimizer_dynamic_sampling" = 2 ;
C00000076A201F58 YES "optimizer_features_enable" = 10.2.0.3 ;
C00000076A201F58 YES "optimizer_index_caching" = 0 ;
C00000076A201F58 YES "optimizer_index_cost_adj" = 100 ;
C00000076A201F58 YES "optimizer_mode" = all_rows ;
C00000076A201F58 YES "parallel_ddl_mode" = enabled ;
C00000076A201F58 YES "parallel_dml_mode" = disabled ;
C00000076A201F58 YES "parallel_execution_enabled" = true ;
C00000076A201F58 YES "parallel_query_mode" = enabled ;
C00000076A201F58 YES "parallel_threads_per_cpu" = 2 ;
C00000076A201F58 YES "pga_aggregate_target" = 51200000 KB ;
C00000076A201F58 YES "query_rewrite_enabled" = true ;
C00000076A201F58 YES "query_rewrite_integrity" = enforced ;
C00000076A201F58 YES "skip_unusable_indexes" = true ;
C00000076A201F58 YES "sort_area_retained_size" = 0 ;
C00000076A201F58 YES "star_transformation_enabled" = false ;
C00000076A201F58 YES "statistics_level" = typical ;
C00000076A201F58 YES "workarea_size_policy" = auto ;

select * from table(dbms_xplan.display_awr('803b7z0t84sp7', NULL, NULL, 'basic rows bytes cost'));

V$SQL BIND CAPTURE

SQL> CREATE TABLE bind_test(id INT);
SQL> var x number;
SQL> EXEC :x := 1;
SQL> select * from bind_test where id = :x';

SQL> SELECT name, position, was_captured, 
	to_char(last_captured,'yyyy/mm/dd hh24:mi:ss'), value_string
   FROM v$sql_bind_capture
   WHERE sql_id = (SELECT sql_id FROM v$sql WHERE sql_text =
   ('select * from bind_test where id = :x')
   
NAME	POSITION	WAS_CAPTURED	LAST_CAPTURED		VALUE_STRING
----	--------	----------	--------------		------------
:X	1		YES		2007/10/13 00:16:19	1

SELECT a.ksppinm "Parameter", b.ksppstvl "Session Value",
  2         c.ksppstvl "Instance Value"
  3    FROM x$ksppi a, x$ksppcv b, x$ksppsv c
  4   WHERE a.indx = b.indx AND a.indx = c.indx
  5         AND a.ksppinm LIKE '%kgl_large_heap%';

Parameter
--------------------------------------------------------------------------------

Session Value
--------------------------------------------------------------------------------
--------------------
Instance Value
--------------------------------------------------------------------------------
--------------------
_kgl_large_heap_warning_threshold
52428800
52428800

출처 : http://gampol.tistory.com/entry/Memory-Notification-Library-Cache-Object-loaded-into-SGA

조치방법: (다음과 같이 조치후 서버 restart)
sqlplus “/as sysdba”
alter system set "_kgl_large_heap_warning_threshold"=8388608 scope=spfile ;
shutdown immediate
startup

 
원인: (Cause 항목 참조)
해당 오류메시지는 Error 가 아닌 Warning 입니다.
이는 shared pool 영역중 libraray cache 영역의 단편화가 심해 Free Memory를 찾는데 오랜 시간이 걸릴 때 
위와 같은 오류가 발생합니다.
새로운 트랜잭션이 발생시 SQL의 파싱결과 등이 저장되는 library cache영역에 단편화로 인한 메시지로 
10g R2의 메모리 관리 메커니즘에 따른 메시지로 보시면됩니다.
 
 
=======================================================================
Applies to:
Oracle Server - Enterprise Edition - Version: 
This problem can occur on any platform.
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.
Real memory messages should be ORA-403x when  a real memory allocation problem 
occurs.
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
In 10.2.0.2,  the threshold is increased to 50MB after regression tests, so this should be a reasonable and recommended value.
References

http://surachartopun.com/2008/07/memory-notification-library-cache.html

10.2.0.1 Annoying message "Memory Notification: Library Cache Object loaded into SGA"
Posted: March 5, 2006 by Frits Hoogland in Oracle EE, Oracle XE 
9In the 10.2.0.1 version of the oracle database a new heap checking mechanism, together with a new messaging system is introduced. 
This new mechanism reports memory allocations above a threshold in the alert.log, together with a tracefile in the udump directory.

In certain situations it can be handy to know if large allocations are being done in the sga heap (shared pool), 
but only to troubleshoot memory allocation problems (which eventually will appear as the infamous ORA-4031). 
For normal day to day work, I doubt the effectivity of these messages.

The default limit is set at 2048K. It appears normal usage can result in messages from the memory manager. 
During my last installation (with all options set default, all options installed), 
this resulted in 125 messages from the memory manager.

Oracle gives a solution on metalink in note 330239.1:
Set _kgl_large_heap_warning_threshold to a “reasonable high” value. This parameter is set in bytes, 
and oracle recommends the value 8388608 (8192K).

Wednesday, July 30, 2008
Memory Notification: Library Cache Object loaded into SGA
 
Today, I checked alert.log file. I found:

Memory Notification: Library Cache Object loaded into SGA
Heap size 9426K exceeds notification threshold (8192K)

When I found out on metalink:
These messages are report in alert.log after 10g Release 2 is installed.

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"=10485760 scope=spfile ;

SQL> shutdown immediate

SQL> startup

SQL> show parameter _kgl_large_heap_warning_threshold

NAME TYPE VALUE

--------------------------------- ------- ------------------------------
_kgl_large_heap_warning_threshold integer 10485760

If using an "old-style" init parameter,

Edit the init parameter file and add

_kgl_large_heap_warning_threshold=10485760