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

Problem

CentOS 7 환경의 서버에서 daily backup이 정상적으로 완료되지 않았다. 확인해보니 백업을 시도할 때 마다 Alert log에 다음과 같은 메시지가 발생하면서 백업 진행이 중지되었다.

 

Memory Notification: Library Cache Object loaded into SGA
Heap size 20018K exceeds notification threshold (8192K)
Details in trace file /oracle/app/oracle/diag/rdbms/testdb/testdb/trace/testdb_dw03_76080.trc
KGL object name :SELECT /*+rule*/ SYS_XMLGEN(VALUE(KU$), XMLFORMAT.createFormat2('M_VIEW_LOG_T', '7')), 0 ,KU$.MVIEWLOG.MASTER ,KU$.MVIEWLOG.MOWNER ,'TABLE' ,KU$.MVIEWLOG.LOG ,'MATERIALIZED_VIEW_LOG' ,KU$.MVIEWLOG.MOWNER FROM SYS.KU$_M_VIEW_LOG_PFH_VIEW KU$ WHERE  KU$.MVIEWLOG.MOWNER IN (SELECT object_name FROM "SYS"."SYS_EXPORT_SCHEMA_03" WHERE process_order = -55 AND duplicate BETWEEN 1 AND 1) AND  NOT EXISTS (SELECT 1 FROM  SYS.KU$NOEXP_TAB A WHERE A.OBJ_TYPE='TABLE' AND A.NAME=KU$.MVIEWLOG.MASTER AND A.SCHEMA=KU$.MVIEWL
Memory Notification: Library Cache Object loaded into SGA
Heap size 17708K exceeds notification threshold (8192K)
Details in trace file /oracle/app/oracle/diag/rdbms/testdb/testdb/trace/testdb_dw03_76080.trc
KGL object name :SELECT /*+rule*/ SYS_XMLGEN(VALUE(KU$), XMLFORMAT.createFormat2('M_VIEW_LOG_T', '7')), 0 ,KU$.MVIEWLOG.MASTER ,KU$.MVIEWLOG.MOWNER ,'TABLE' ,KU$.MVIEWLOG.LOG ,'MATERIALIZED_VIEW_LOG' ,KU$.MVIEWLOG.MOWNER FROM SYS.KU$_M_VIEW_LOG_FH_VIEW KU$ WHERE  KU$.MVIEWLOG.MOWNER IN (SELECT object_name FROM "SYS"."SYS_EXPORT_SCHEMA_03" WHERE process_order = -55 AND duplicate BETWEEN 1 AND 1) AND  NOT EXISTS (SELECT 1 FROM  SYS.KU$NOEXP_TAB A WHERE A.OBJ_TYPE='TABLE' AND A.NAME=KU$.MVIEWLOG.MASTER AND A.SCHEMA=KU$.MVIEWLO


Memory Notification: Library Cache Object loaded into SGA
Heap size 20018K exceeds notification threshold (8192K)
Details in trace file /oracle/app/oracle/diag/rdbms/testdb/testdb/trace/testdb_dw06_39834.trc
KGL object name :SELECT /*+rule*/ SYS_XMLGEN(VALUE(KU$), XMLFORMAT.createFormat2('M_VIEW_LOG_T', '7')), 0 ,KU$.MVIEWLOG.MASTER ,KU$.MVIEWLOG.MOWNER ,'TABLE' ,KU$.MVIEWLOG.LOG ,'MATERIALIZED_VIEW_LOG' ,KU$.MVIEWLOG.MOWNER FROM SYS.KU$_M_VIEW_LOG_PFH_VIEW KU$ WHERE  KU$.MVIEWLOG.MOWNER IN (SELECT object_name FROM "SYS"."SYS_EXPORT_SCHEMA_04" WHERE process_order = -55 AND duplicate BETWEEN 1 AND 1) AND  NOT EXISTS (SELECT 1 FROM  SYS.KU$NOEXP_TAB A WHERE A.OBJ_TYPE='TABLE' AND A.NAME=KU$.MVIEWLOG.MASTER AND A.SCHEMA=KU$.MVIEWL
Memory Notification: Library Cache Object loaded into SGA
Heap size 17708K exceeds notification threshold (8192K)
Details in trace file /oracle/app/oracle/diag/rdbms/testdb/testdb/trace/testdb_dw06_39834.trc
KGL object name :SELECT /*+rule*/ SYS_XMLGEN(VALUE(KU$), XMLFORMAT.createFormat2('M_VIEW_LOG_T', '7')), 0 ,KU$.MVIEWLOG.MASTER ,KU$.MVIEWLOG.MOWNER ,'TABLE' ,KU$.MVIEWLOG.LOG ,'MATERIALIZED_VIEW_LOG' ,KU$.MVIEWLOG.MOWNER FROM SYS.KU$_M_VIEW_LOG_FH_VIEW KU$ WHERE  KU$.MVIEWLOG.MOWNER IN (SELECT object_name FROM "SYS"."SYS_EXPORT_SCHEMA_04" WHERE process_order = -55 AND duplicate BETWEEN 1 AND 1) AND  NOT EXISTS (SELECT 1 FROM  SYS.KU$NOEXP_TAB A WHERE A.OBJ_TYPE='TABLE' AND A.NAME=KU$.MVIEWLOG.MASTER AND A.SCHEMA=KU$.MVIEWLO

 

검색해보니 해당 오류메시지는 Error 가 아닌 Warning 이며, 
이는 shared pool 영역중 libraray cache 영역의 단편화가 심해 Free Memory를 찾는데 오랜 시간이 걸릴 때 
위와 같은 오류가 발생한다고 한다.

 

---

Solution

DB 서버 restart를 해준다.

 

추가적으로 찾은 해결 방법은 아래와 같은데, 이는 경고 임계치를 수정하는 것으로 실제로 영향을 주는 것이 아니었다.

sqlplus “/as sysdba”
alter system set "_kgl_large_heap_warning_threshold"=8388608 scope=spfile ;
shutdown immediate
startup

 

경고 임계치 수정 없이 DB daemon restart만 해주었지만, 오랜시간 구동되던 DB가 꺼졌다 켜지면서 쓰지 않으면서 붙어있던 세션이나 메모리가 초기화 되어 사용 가능한 메모리가 확보된 것으로 보인다.

 

서버 재기동 후 정상적으로 백업이 완료되는 것을 확인할 수 있었다.

 

 

 

---

참고

Memory Notification: Library Cache Object loaded into SGA