ORA-4031 ERROR 실제 사례의 분석(SHARED POOL)

(V7.X ~ V9.2)예제를 통한 ORA-4031 ERROR 실제 사례의 분석(SHARED POOL)
=====================================================================

Purpose
-------

   이 자료는 ORA-4031 에러가 발생하는 여러가지 case 가운데 사이즈가
   큰 PL/SQL Routine 또는 Procedure가 메모리에 로드되기 위하여
   주로 발생하는 ORA-4031 사례에 대한 예제와 분석을 소개하는 자료이다.


Problem Description
-------------------

   Procedure, function, package 등의 library가 shared pool 영역에
   할당되려고 할 때 ORA-4031 에러가 발생하는 경우가 있다. 이 때
   shared memory를 많이 차지하는 query를 어떻게 추적하는지 사례를
   통해 알아보기로 한다.


Workaround
----------
   restart instance or flush shared pool
   keep large objects in memory


Solution Description
--------------------

   SYS.X$KSMLRU 와 SYS.X$KSMSP 는 shared pool memory의 사용 현황을
   보여주는 오라클의 base table들이다.


   1. SYS.X$KSMLRU

   SYS.X$KSMLRU 를 보면
   이 fixed table은 shared pool 영역에 cache되기 위해 다른 object를
   밀어낸(aged out) allocation들에 대한 정보를 담고 있다.

   이 table을 통해 어떤 object가 많은 공간을 메모리에 차지하면서
   할당되었는지 알 수 있는데, 한 번 조회하고 나면 조회된 정보는
   테이블에서 remove된다.

   KSMLRCOM  부분이 'MPCODE'나 'PLSQL%' 로 시작한다면,
   큰 사이즈의 PL/SQL object가 shared pool 영역에 load된 것이므로,
   이 procedure는 memory에 keep되어지면 좋다는 결론이 나오는데,
   SYS.X$KSMLRU 를 조회한 결과를 보아야 한다.


   SYS.X$KSMLRU에 만약 아무것도 조회되지 않는다면,
   그러니까, 큰 object가 memory에 load되기 위해 다른 object가
   aged out된 것은 없다는 것을 의미한다.

   이 fixed table의 column에는 다음과 같은 것이 있다.
   =================================================================
   KSMLRSIZ :  allocate된 연속된 memory size.
               이 크기가 5K가 넘으면 문제될 소지가 있다고 보고,
               10K가 넘으면 심각한 문제가, 20K가 넘으면 매우 심각한
               문제를 야기할 수 있으므로 주의가 필요하다.
   KSMLRNUM :  이 object의 할당으로 인하여 flush되었던 object의 갯수.
   KSMLRHON :  load되고 있는 object의 이름.(PL/SQL or a cursor)
   KSMLROHV :  load되고 있는 object의 hash value.
   KSMLRSES :  이 object를 load한 session의 SADDR 값.
   =================================================================


   2. SYS.X$KSMSP

   SYS.X$KSMSP 뷰를 조회하면 shared pool 영역의 free space와 flush할
   수 있는 freeable space에 대한 조각이 얼마인지 확인할 수 있다.

   ORA-4031 오류가 발생했을 때, V$SGASTAT 뷰를 통해서는 shared
   pool 영역의 전체 free space만 확인 가능하지만, 이 뷰를 조회하면
   memory space 조각에 대한 정보도 볼 수 있다.

   이 fixed table의 column에는 다음과 같은 것이 있다.
   (Allocate된 chunk 하나 당 하나의 row가 생성된다.)
   =================================================================
   KSMCHCLS  :  CHUNK의 CLASS
      (free : free, freeable : freeable, perm : permanent,
       recr : recreatable)
   KSMCHSIZ  :  CHUNK의 사이즈
   KSMCHCOM  :  CHUNK에 대한 속성을 나타내는 간단한 text comment
   KSMCHPTR  :  메모리 상에서 LOCATION에 대한 HEX value
   =================================================================

반응형

   SQL> select ksmchcls, MAX(ksmchsiz), SUM(ksmchsiz)
        from sys.x$ksmsp
        group by ksmchcls;

   KSMCHCLS MAX(KSMCHSIZ) SUM(KSMCHSIZ)
   -------- ------------- -------------
   R-free          671600       6044400
   R-freea             40           720
   free          16105472     106453784
   freeabl         124176       5391136
   perm          15650000      31052280
   recr              6496       2052048

   6 rows selected.


Example
-------

   SQL> select ksmchcls, MAX(ksmchsiz), SUM(ksmchsiz) 
        from sys.x$ksmsp
        group by ksmchcls;

   KSMCHCLS MAX(KSMCHSIZ) SUM(KSMCHSIZ)
   -------- ------------- -------------
   R-free       138716800     139574016
   R-freea           8152        236200
   free             21712        987208
   freeabl          50680     234508992
   perm          47020752      53054992
   recr             12168      30352488

   6 rows selected.                                     
 

SQL> select * from X$KSMLRU
     where KSMLRSIZ > 0;

ADDR                  INDX   INST_ID KSMLRCOM              KSMLRSIZ  KSMLRNUM KSMLRHON                          KSMLROHV KSMLRSES                                                
---------------- --------- --------- -------------------- --------- --------- -------------------------------- --------- ----------------                                        
C0000000472FA428         0         1 PAR.C:parchk:page         2120         8 BEGIN         PRD_WS_NEXT2( ...  3.325E+09 C00000002B8DA980                                        
                                                                             
C0000000472FA470         1         1 KQLS MEM BLOCK            2288         8 WGQCT                            4.034E+09 C00000002B867680                                        
                                                                             
C0000000472FA4B8         2         1 seldef : kkmset           2552         8 SELECT MJCD,fun_aa_nm1(mjcd)...  850201559 C00000002B915F80                                        
                                                                             
C0000000472FA500         3         1 lazdef : kkmset           2568         8 SELECT MJCD,fun_aa_nm1(mjcd)...  265721063 C00000002B8DA980                                        
                                                                             
C0000000472FA548         4         1 lazdef : kkmset           2664         8 select mjnm,mjcd,sum(jg1) j...   1.594E+09 C00000002B85FB00                                        
                                                                             
C0000000472FA590         5         1 idndef : prsexl           3112         3 SELECT /*+ rule */ * FROM sy...  4.285E+09 C00000002B8FF680                                        
                                                                             
C0000000472FA5D8         6         1 BAMIMA: Bam Buffer        3896         8 YYCAT_T1                         1.175E+09 C00000002B857600                                        
                                                                             
C0000000472FA620         7         1 state objects             4080         8                                          0 C00000002B8C9600                                        
                                                                             
C0000000472FA668         8         1 BAMIMA: Bam Buffer        4168       168 BEGIN  PRD_WS_NEXT2( ...         4.036E+09 C00000002B8BE180                                        
                                                                             
C0000000472FA6B0         9         1 library cache             4232        40 SELECT /*+NESTED_TABLE_GET_R...  2.607E+09 C00000002B856300                                        
                                                                                                   
                                                              
10 rows selected.


위의 결과에서 주목해야 할 부분은 KSMLRNUM 값이 큰 수치(168, 40)를 보이는
Procedure(168)와 SQL(40)이다.
KSMLRNUM 값이 높다는 것은 이 object의 할당으로 인하여 flush되었던 object
의 갯수가 그 만큼 많다는 것이므로, 이 Procedure 또는 Function은 메모리
에 keep되어질 필요가 있음을 의미한다.
Shared_pool에 Procedure를 Keep하는 방법과 여러 사용자 간에 주로 사용하
는 SQL 문을 공유하기에 관한 자료는 을 참조하도록 한다.

 
Reference Documents
------------------- -------------------
Note:61623.1
Note:146599.1
Note:62143.1