■ Share pool과 성능문제

  xx지원 오라클 장애처리
  ========================
  작성자 :

   1. 장애처리
      장애 시간 : 2006-07-21 11:34
      장애 서버 : xx지원 서버2의 오라클 데이터베이스
      장애 유형 : 오라클 데이터베이스 인스턴스 다운
      장애 원인 : 오라클의 LMD(LOCK MONITOR DEADON) 다운으로 오라클 인스턴스 다운이 발생함
                  (장애분석 참조)
      장애 조치 : 오라클 인스턴스 재시작함
      복구 시간 : 2006-07-21 11:53
      장애 예방방안 : 장애대처 예방 참조

   2. 장애분석
     [ alert! 로그 파일]
       Fri Jul 21 11:34:26 2006                      ==> 장애 시간
       LMD0: terminating instance due to error 4031  ==> 장애 메시지
       Instance terminated by LMD0, pid = 19848      ==> 데이터베이스 다운

     [ trace 파일 분석 ]
      파일명 : llmd0_19848_hiraops2.trc
       *** 2006-07-21 11:34:26.346
       *** SESSION ID:(3.1) 2006-07-21 11:34:26.306
       error 4031 detected in background process
    
     [ 장애원인 ]
       ORA-4031 : shared pool memory 분석으로 LMD 프로세스 다운으로 발생함
       Shared Pool의 정확인 영역은 나타나지 않음

       <참조 : 메시지 >
       04031, 00000, "unable to allocate %s bytes of shared memory (\"%s\",\"%s\",\"%s\",\"%s\")"
       *Cause:  More shared memory is needed than was allocated in the shared pool.
       *Action: If the shared pool is out of memory, either use the dbms_shared_pool package to pin
                large packages,reduce your use of shared memory, or increase the amount of available shared
                memory by increasing the value of the INIT.ORA parameters "shared_pool_reserved_size" and
                "shared_pool_size".
                If the large pool is out of memory, increase the INIT.ORA parameter "large_pool_size".

       < Ora-4031 란 ? >
        이 에러는 사용자가 특정 Object를 사용하고자 할 때 해당 Object를 위해 Shared_Pool에 공간을 할당하려 할 때 충분한
        공간을 얻지 못하는 경우이다
        Shared_Pool 영역에 차지하는 크기가 큰 PL/SQL 사용시 오라클은 공간 확보를 위해 현재 사용되지 않는 Object를 Flush시킨다.
        기존 V7.2. 이하에서는 특정 프로시저를 Shared_Pool에 올리기 위해 연속된 공간을 필요로 하였으나, 이후 버전에
       서는 꼭 연속되지 않아도 사용이 가능하여 ORA-4031 에러가 발생하는 경우가 많이줄어들었다.
        이의 해결을 위해서는 Shared_Pool을 늘려주도록 한다 .
        이 에러를 유발시키는 문제인 메모리의 단편화 (fragmentation)를 줄이기 위해서, 또는 일반적인 경우 자주 사용하는 Object가
        메모리에서 밀려 내려가는 경우를 없애기 위해서 Object를 미리 Shared_Pool에  Keep 한다.

   3.장애 예방 방안
    [ 목적 ]
     - Shared Pool Memory 부족(ORA-4031) 장애발생 예방

    [ 예방 방법 ]
     - shared pool size , shared pool reserved size , large pool size를 증가 한다.
    
    [ Shared Pool Memory 할당 값]
      #shared_pool_size              = 471859200             # 450MB
      #shared_pool_reserved_size     = 47185920              # shared pool * 10%
      #shared_pool_size              = 512000000             # 500MB             2005.07.21
      #shared_pool_reserved_size     = 51200000              # shared pool * 10% 2005.07.21
 
       shared_pool_size               = 629145600              # 600MB             2005.08.08
       shared_pool_reserved_size      = 62914560               # shared pool * 10% 2005.08.08
       java_pool_size                = 2097152
       large_pool_size               = 10M                   # 2004/10/30

    [ 메모리 사용률 ]
      전체 메모리 : 19.9G  
      사용률 : Sys Mem  : 4.68GB   User Mem:  2.71GB   Buf Cache: 1.99GB   Free Mem: 10.5GB

    [ shared pool free memory 확인 QUERY 및 flush 명령어]
  
    SQL> select v$sgastat.pool, to_number(v$parameter.value) value, v$sgastat.bytes,
          (v$sgastat.bytes/v$parameter.value)*100 "Percent Free"
          from v$sgastat , v$parameter
          where v$sgastat.name = 'free memory'
          and v$parameter.name = 'shared_pool_size';

          POOL          VALUE          BYTES   Percent Free
         ---------------------------------------------------------------
         shared pool 629145600 140415416 22.3184293111165   <-- free space 확인
         large pool 629145600 299984         0.0476811726888021
         java pool 629145600 2097152         0.333333333333333

    SQL> alter system flush shared_pool;   <-- 단편화된 조각화를 flush 해주는 방법(메모리 조각모음) (업무외시간에 수행)

    [ Library Cache Fragmentation 확인 Query]

         select
             decode(sign(ksmchsiz - 812), -1, (ksmchsiz - 16) / 4,
               decode(sign(ksmchsiz - 4012), -1, trunc((ksmchsiz + 11924) / 64),
                 decode(sign(ksmchsiz - 65548), -1, trunc(1/log(ksmchsiz - 11, 2)) + 238, 254 )))  bucket,
             sum(ksmchsiz)  free_space,  count(*)  free_chunks,  trunc(avg(ksmchsiz))  average_size,
             max(ksmchsiz)  biggest
           from
             sys.xm$ksmsp
           where
             inst_id = userenv('Instance') and  ksmchcls = 'free'
           group by
             decode(sign(ksmchsiz - 812), -1, (ksmchsiz - 16) / 4,
               decode(sign(ksmchsiz - 4012),-1, trunc((ksmchsiz + 11924) / 64),
                 decode(sign(ksmchsiz - 65548),-1, trunc(1/log(ksmchsiz - 11, 2)) + 238, 254)))

           BUCKET FREE_SPACE FREE_CHUNKS AVERAGE_SIZE    BIGGEST
           ---------- ---------- ----------- ------------ -------------------------------------------------------
                    6       92000         2300         40         40
                    8       1392          29           48         48
                   10       1456          26           56         56
           ……
           위의 결과처럼 shared pool 영역에 작은 chunk 들의 다수 존재하는 경우에는 free list   검색시 shared pool latch를 장시간 holding하게 되며,
           이는 shared pool latch free 대기 현상이 발생할 가능성을 높여주게 된다. 일반적으로 hard parsing에 의해 이러한 현상들이 발생하게 됨.
          
          
           
          
     [ Shared Pool size 적절성 확인 Query ]
         
                                                     
           column kghlurcr heading "RECURRENT|CHUNKS"
           column kghlutrn heading "TRANSIENT|CHUNKS"
           column kghlufsh heading "FLUSHED|CHUNKS"  
           column kghluops heading "PINS AND|RELEASES"
           column kghlunfu heading "ORA-4031|ERRORS" 
           column kghlunfs heading "LAST ERROR|SIZE" 
          
           select
             kghlurcr "RECURRENT|CHUNKS" ,
              kghlurcr *3,
             kghlutrn "TRANSIENT|CHUNKS" ,
             kghlufsh "FLUSHED|CHUNKS"  ,
             kghluops "PINS AND|RELEASES" ,
             (kghlufsh/ kghluops) *100,
             kghlunfu "ORA-4031|ERRORS" ,
             kghlunfs "LAST ERROR|SIZE"
           from
             sys.x_$kghlu
           where
           inst_id = userenv('Instance');

          
           RECURRENT  TRANSIENT    FLUSHED        PINS AND    ORA-4031 LAST ERROR
               CHUNKS     CHUNKS     CHUNKS        RELEASES     ERRORS       SIZE
           ---------- ---------- ---------------  ---------- -----------------------------------------------------------------
                  587       1687          8946       28416904          0          0
                 

--  일반적으로 Transient list가 Recurrent List 의 3배 이상이면 shared pool 이 oversize  된 것이며, chunk flush 수치가
  --         pins and released 수치의 5% 이상이면 shared pool 이 작게 설정된 것이라고 볼 수 있다.
          

shared pool 의 free 공간 확인

select to_number(v$parameter.value) value, v$sgastat.bytes,
(v$sgastat.bytes/v$parameter.value)*100 "Percent Free"
from v$sgastat, v$parameter
where v$sgastat.name = 'free memory'
and v$parameter.name = 'shared_pool_size';

■ Shared Pool 관련 wait

☞   4-6 library cache 알고리즘

라이브러리 캐쉬는 lru 알고리즘에의해 관리가 되며

문장이 이미 캐쉬에 저장되어 있는가를 찾기 위하여, 오라클 서버는 다음을 수행합니다:

1. ASCII 텍스트의 수치로 문장을 줄임
2. 이 수치에 해시 함수 사용

☞   4-7  shared pool latch 

SQL 이 공유되지 못하고 파싱해달라고 하는 SQL 이 많아지면 공간을 할당 받아야하는데 이때
공간을 할당 받을때 메모리에 락(latch) 를 건다. latch 를 사용하는 이유는 파싱해달라고 요청하는
sql 에 대하여 순서를 정하는 것인데 먼저 요청한 sql 에 대하여 작업을 원할히 수행하기 위해 메모리에 락을 거는것이다.즉 동시 엑세스를 제어하기 위해 latch 를 사용한다.

그런데 조각화(단편화) 가 심해지면 free list 가 그만큼 길어지므로 공간 할당받을때 시간이 많이 소유될것이고 이로 인해 발생하는 wait event ( latch free(library cache) ) 가 다량으로 발생할것이다.

이를 해결하기 위해서는

1. 바인드변수 사용
2. cursor_sharing 사용
3.  shared pool size 늘린다.
4. large pool 을 구성한다.

☞   4-8  shared pool 에 공유되지 않은 SQL 에 대한 정보 보기

select  *
 from v$sqlarea
 where executions < 5
  order by upper(sql_Text)
 
 
select sql_Text, parse_calls, executions
   from v$sqlarea
     order by parse_calls; 

☞   4-12 ~            실습

v$sqlarea

(라이브러리 캐쉬: 문장저장)

동일한 문장

1. 대소문자
2. 스키마(schema)
3. 공백
4. 주석
5. bind 변수의 type(아래설명)

*****************************************
scott1
실행
 SQL> select empno,ename from emp where deptno=10;

      EMPNO ENAME
 ---------- ----------
       7782 CLARK
       7839 KING
       7934 MILLER

internal에서 실행
 SQL> ed
 Wrote file afiedt.buf

      select sql_text,SHARABLE_MEM,EXECUTIONS
      from v$sqlarea
     where sql_text like 'select empno,ename from emp where%';
  

 SQL_TEXT     SHARABLE_MEM  EXECUTIONS
 -------------------------------------------------------------- ----------
 select empno,ename from emp where deptno=10             5234   1
 
 사용했던 문장이 나옴

scott1
변수 선언
 SQL> variable v1 varchar2(10)
 SQL> declare
   2  begin
   3     :v1 := '10';
   4  end;
   5  /

 PL/SQL procedure successfully completed.

 SQL> select empno, ename from emp where deptno=:v1;

      EMPNO ENAME
 ---------- ----------
       7782 CLARK
       7839 KING
       7934 MILLER

internal
 SQL> /

 SQL_TEXT                                           SHARABLE_MEM EXECUTIONS
 -------------------------------------------------- ------------ ----------
 select empno,ename from emp where deptno=10                5234          1
 select empno,ename from emp where deptno=:v1               5067          1

scott1
변수값을 바꾸어줌
 SQL> declare
   2  begin
   3     :v1 := '20';
   4  end;
   5  /

 PL/SQL procedure successfully completed.

 SQL> select empno,ename from emp where deptno=:v1;

      EMPNO ENAME
 ---------- ----------
       7369 SMITH
       7566 JONES
       7788 SCOTT
       7876 ADAMS
       7902 FORD

internal
scott의 결과는 달라도 새로 파싱을 안함
 SQL> /

 SQL_TEXT                                           SHARABLE_MEM EXECUTIONS
 -------------------------------------------------- ------------ ----------
 select empno,ename from emp where deptno=10                5234          1
 select empno,ename from emp where deptno=:v1               5067          2

문제) select empno,ename from emp where deptno=30 을 하고 출력해보아라 !!!  

   
scott2

변수형을 넘버로 선언
 SQL> variable v1 number
 SQL>  declare
    2  begin
    3  :v1 :=20;
    4  end;
 PL/SQL procedure successfully completed.

 SQL> select empno,ename from emp where deptno=:v1;

      EMPNO ENAME
 ---------- ----------
       7369 SMITH
       7566 JONES
       7788 SCOTT
       7876 ADAMS
       7902 FORD

internal

scott2에 실행한 문장후 크기가 늘어남 (파싱계획변경)
 SQL> /

 SQL_TEXT                                           SHARABLE_MEM EXECUTIONS
 -------------------------------------------------- ------------ ----------
 select empno,ename from emp where deptno=10                5234          1
 select empno,ename from emp where deptno=:v1               8939          3

********** bind type에 따라 파싱이 다르다 ****************

☞   4-13           실습

1) v$librarycache 를 통해 hit율(gethitratio)를 분석

   - gets    : 사용자가 실행한 SQL문이 구문 분석되어 라이브러리 캐시 영역에 로드되려 했던 수.
   - gethits : 그 중 로드되었던 수
   - hit율(gethitratio)이 90% 이상일때 좋은 성능 기대

select namespace , gets , gethits,gethitratio
from v$librarycache
where namespace = 'SQL AREA' ;

NAMESPACE         GETS    GETHITS GETHITRATIO
--------------- ---------- ---------- -----------
SQL AREA            141767     141498     .99810252

  <= GETHITRATIO 컬럼 값이 90%이상이면 라이브러리 캐시 영역이 개발자들의 SQL 파싱정보를
      저장하기에 충분한 메모리 공간을 확보하고 있슴을 의미하며, 만약 90% 이하라면 성능이 저하
      될 수 있다는 것을 의미.
      물론 90% 이하라도 사용자들이 성능에 만족한다면 튜닝 대상이 안될 수도 있으며 반드시 튜닝
      을 해야 할 필요는 없다.

  ▷ 유의사항
     - 히트율 분석은 기업에서 가장 일을 바쁘게 진행하고 있는 시간대의 분석 결과를 기준으로
        하여야 한다.

  ▷ 초치사항
     - HIT율이 90% 이하일 경우 SHARED_POOL_SIZE 파라메터 값을 높게 설정.
    ex) initSID.ora
               ......
          SHARED_POOL_SIZE = 32000000  <= 이전 보다 큰 값으로 변경
              .....
    
2) v$library 자료사전을 통해 RELOAD 비율 분석.

   - RELOADS 비율  =  ( Reloads / Pins ) * 100
   - Library Cache Area 의 크기가 너무 작아서 사용자의 SQL 구문분석 정보가 로드되지 못하고
     가장 오래된 SQL문 정보를 삭제 후 사용자의 SQL 문장이 다시 실행될 때 Reloads 증가.
  - 구분분석된 SQL문에서 사용된 객체가 다른 사용자에 의해 삭제된 상태에서 다시 SQL문이
     재실행될 때에는 RELOADS 컬럼값이 증가.
  - PINS : 구문분석되어 Library Cache Area 에 저장될 수 있었던 SQL 정보.
  - PINS 컬럼에 대한 Realods 컬럼의 백분율이 1% 미만일 때 좋은 성능 기대.

select sum(pins),sum(reloads),sum(reloads)/sum(pins)
from v$librarycache
where namespace = 'SQL AREA' ;

SUM(PINS) SUM(RELOADS) SUM(RELOADS)/SUM(PINS)
----------      ------------     ----------------------
    320823            10                   .00003117
  
<= PINS에 대한 RELOADS의 비율이 1% 미만일 때 라이브러리 캐시 영역의 크기가 AQL 파싱정보
     를 저장하기에 충분하다는 것을 의미. 1% 이상이라면 성능이 저하될 수도 있다는 것을 의미.

3) v$librarycache 를 통해 SQL문에서 사용된 객체가 다른 사용자들에 의해 얼마나 자주
     삭제,변경되었는 지를 분석하는 방법.
    주로 ANALYZE ,ALTER , DROP명령어에 의해 테이블 구조가 변경되는 경우에 발생.

select namespace, invalidations
from v$librarycache
where namespace = 'SQL AREA' ;

NAMESPACE       INVALIDATIONS
--------------- -------------
SQL AREA                    0

  <= INVALIDATIONS 컬럼의 값이 높게 출력 되거나 계속적으로 증가 값을 보인다면 공유 풀 영역
     에 작아서 성능이 저하되도 있음을 의미. 즉, 불필요한 재파싱. 재로딩 작업이 발생할 가능성이 높
     아지는 것이다.

☞   4-14 ~            실습

SQL> alter system set cursor_sharing=force scope=both;

시스템이 변경되었습니다.

SQL>
SQL>
SQL> select sql_text,SHARABLE_MEM,EXECUTIONS
           from v$sqlarea
          where sql_text like 'select empno,ename from emp where%';

SQL_TEXT
--------------------------------------------------------------------------------
SHARABLE_MEM EXECUTIONS
------------ ----------
select empno,ename from emp where deptno=10
        6693          1

select empno,ename from emp where deptno=30
        6725          1

select empno,ename from emp where deptno=:v1
        6546          1

SQL> alter system flush shared_pool;

시스템이 변경되었습니다.

SQL> select sql_text,SHARABLE_MEM,EXECUTIONS
  2        from v$sqlarea
  3       where sql_text like 'select empno,ename from emp where%';

SQL_TEXT
--------------------------------------------------------------------------------
SHARABLE_MEM EXECUTIONS
------------ ----------
select empno,ename from emp where deptno=30
        1461          0

scott 창에서

SQL> select empno,ename from emp where deptno=10;

SQL> /

SQL_TEXT
--------------------------------------------------------------------------------
SHARABLE_MEM EXECUTIONS
------------ ----------
select empno,ename from emp where deptno=30
        1461          0

select empno,ename from emp where deptno=:"SYS_B_0"
       11873          1

scott 창에서

SQL> select empno,ename from emp where deptno=20;

SQL> /

SQL_TEXT
--------------------------------------------------------
SHARABLE_MEM EXECUTIONS
------------ ----------
select empno,ename from emp where deptno=30
        1461          0

select empno,ename from emp where deptno=:"SYS_B_0"
       11873          1

문제) 다시 exact   로 바꾸고 실습하여라 !!!

SQL> alter system set cursor_Sharing=exact;

시스템이 변경되었습니다.

SQL> select sql_text,SHARABLE_MEM,EXECUTIONS
  2             from v$sqlarea
  3            where sql_text like 'select empno,ename from emp wh

SQL_TEXT
------------------------------------------------------------------
SHARABLE_MEM EXECUTIONS
------------ ----------
select empno,ename from emp where deptno=10
        6705          1

select empno,ename from emp where deptno=30
        1461          0

select empno,ename from emp where deptno=:"SYS_B_0"
       11873          2

SQL>
SQL> /

SQL_TEXT
------------------------------------------------------------------
SHARABLE_MEM EXECUTIONS
------------ ----------
select empno,ename from emp where deptno=10
        6705          1

select empno,ename from emp where deptno=20
        6725          1

select empno,ename from emp where deptno=30
        1461          0

select empno,ename from emp where deptno=:"SYS_B_0"
       11873          2

☞   4-17           실습

****************  테이블과 뷰만들어서 파싱확인하기 ****************

scott1
테이블 생성 및 뷰 생성
 SQL> create table abc
   2  (a1 number, a2 number);

 Table created.

 SQL> insert into abc values(111,111);

 1 row created.

 SQL> commit;

 Commit complete.

 SQL> create view abcvw
   2  as
   3  select a1+a2 summun
   4  from abc;

 View created.

scott2
테이블 뷰 실행
 SQL> select * from abcvw;

     SUMMUN
 ----------
        222

scott1
테이블 드랍
 SQL> drop table abc;

 Table dropped.

internal
 SQL> ed
 Wrote file afiedt.buf

   1  select sql_text,SHARABLE_MEM,EXECUTIONS, INVALIDATIONS
   2  from v$sqlarea
   3* where sql_text like 'select * from abcvw%'
 SQL> /

 SQL_TEXT                            SHARABLE_MEM EXECUTIONS INVALIDATIONS
 ----------------------------------------------- -----------------------
 select * from abcvw   866     1           1

scott2
테이블 뷰 쿼리
 SQL> select * from abcvw;
 select * from abcvw
        *
 ERROR at line 1:
 ORA-04063: view "SCOTT.ABCVW" has errors

scott1
다시 테이블 생성
 SQL> create table abc
   2  (a1 number, a2 number);

 Table created.

 SQL> insert into abc values(111,111);

 1 row created.

 SQL> commit;

 Commit complete.

scott2
뷰 실행(다시 실행이 됨)
 SQL> select * from abcvw;

     SUMMUN
 ----------
        222

internal

 SQL> /

 SQL_TEXT                            SHARABLE_MEM EXECUTIONS INVALIDATIONS
 ----------------------------------- ------------ ---------- -------------
 select * from abcvw                         5718          2             1

☞   4-20            실습

select shared_pool_size_for_estimate as pool_size, estd_lc_size, estd_lc_time_saved
   from v$shared_pool_advice;

SHARED_POOL_SIZE_FOR_ESTIMATE NUMBER :   Shared pool size for the estimate (in megabytes)

ESTD_LC_SIZE NUMBER:        Estimated memory in use by the library cache (in megabytes)

ESTD_LC_TIME_SAVED NUMBER

Estimated elapsed parse time saved (in seconds), owing to library cache memory objects being found in a shared pool of the specified size. This is the time that would have been spent in reloading the required objects in the shared pool had they been aged out due to insufficient amount of available free memory

☞   4-25       실습

■ V$SQL_PLAN

- Oracle9i 부터 새롭게 추가된 기능
- SQL문이 실행되면서 작성된 실행계획이 Shared Pool Area에 저장됨.

SQL>connect system/manager
SQL>grant dba to scott ;
SQL>connect scott/tiger
SQL>select * from dept ;
SQL>select sql_text , hash_value , address
        from v$sqlarea ;

SQL_TEXT            HASH_VALUE ADDRESS
------------------- ---------- ----------
select * from dept     3015709834   04165C60

SQL>select id, lpad(' ',depth)||operation operation , options , object_name,optimizer,cost
        from v$sql_plan
        where hash_value = &1 and address='&2'
        start with id=0
        connect by ( prior id = parent_id and prior hash_value = hash_value
                     and prior_child_number = child_number)
        order  siblings by id, position;

       &1 :  3015709834 (HASH_VALUE )
       &2 :  04165C60   (ADDRESS)

   ID     OPERATION                  OPTIONS   OBJECT_NAME    OPT    COST
  ----  ----------------------  ----------- ---------------   ------   ------
   0      SELECT STATEMENT                                              CHO
   1     TABLE ACCESS              FULL            DEPT


  <=  또는, set autotrace on 사용으로 SQL문 실행시 파싱단계에서 옵티마이저가 작성한 실행계획
        을 참조할 수 있다.

☞   4- 26 실습

SQL> show parameter shared_pool_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
shared_pool_size                     big integer 50331648

SQL> select sum(sharable_mem)
       from  v$db_object_cache;

SUM(SHARABLE_MEM)
-----------------
           331406

☞   4-30 ~ 4-32           실습

 show parameter shared

select p.value/r.value * 100 "reserved radit"
     from v$parameter p, v$parameter r
     where p.name = 'shared_pool_reserved_size'
    and r.name = 'shared_pool_size';

select * from v$db_object_cache
 where sharable_mem  > 10000
 and ( type='PACKAGE' or  type='PACKAGE BODY' or
          type='FUNCTION' or type='PROCEDURE' )
and kept='NO';

@d:\oracle\ora92\rdbms\admin\dbmspool.sql

 desc dbms_shared_pool

 execute dbms_shared_pool.keep('dbms_sql');

select * from v$db_object_cache
 where sharable_mem  > 10000
 and ( type='PACKAGE' or  type='PACKAGE BODY' or
          type='FUNCTION' or type='PROCEDURE' )

☞   4-30 ~ 4-32           실습

SQL> declare x number;
      begin x :=5;
     end;
     /

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL>
SQL> declare /* keep_me */ x number;
     begin x := 5;
     end;
     /

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> select address, hash_value
      from v$sqlarea
      where command_type=47
      and sql_Text like '%keep_me%';

ADDRESS  HASH_VALUE
-------- ----------
669841E4 4215866029

 execute dbms_shared_pool.keep('669841E4,4215866029','c');

select distinct name, sharable_mem, loads
     from v$db_object_cache
   where name like '%keep_me%';

DBMS_SHARED_POOL을 이용한 KEEP

Shared Poll에 크기가 큰 프로그램을 KEEP하기 위해서는 아래에 있는 것처럼 DBMS_SHARED_POOL Package를 이용 할 수 있습니다.

SQL> @C:\oracle\ora92\rdbms\admin\dbmspool.sql

패키지가 생성되었습니다.

권한이 부여되었습니다.

뷰가 생성되었습니다.

패키지 본문이 생성되었습니다.

SQL> @C:\oracle\ora92\rdbms\admin\prvtpool.plb

뷰가 생성되었습니다.

패키지 본문이 생성되었습니다.

SQL> grant execute on dbms_shared_pool to scott;

권한이 부여되었습니다.

Object를 KEEP하는 방법은 다음과 같습니다.

Procedure,Function,Package : exec dbms_shared_pool.keep(‘pname’,’p’)
Trigger : exec dbms_shared_pool.keep(‘tr_emp’,’r’)
Sequence : exec dbms_shared_pool.keep(‘seq_empno,’q’)

SQL문은 아래와 같은 방법으로 KEEP 합니다.

예를들어 select empno, ename, sal from emp where deptno = ‘20’ 라는 SQL문장을 Library Cache안의 Shared Cursor 부분에 KEEP하기 위해서는 아래처럼 하면 됩니다…

SQL> conn scott/tiger

연결되었습니다.

SQL> select empno, ename, sal from emp where deptno = 20;

     EMPNO ENAME             SAL
---------- ---------- ----------
      7369 SMITH             800
      7566 JONES            2975
      7788 SCOTT            3000
      7876 ADAMS            1100
      7902 FORD             3000

SQL> conn / as sysdba

연결되었습니다.

SQL> select address, hash_value from v$sqlarea
  2  where sql_text = 'select empno, ename, sal from emp where deptno = 20';

ADDRESS  HASH_VALUE
-------- ----------
7856AC4C 1137127237   <- 원하는 SQL문장에 대한 주소와 해시 값

아래 명령으로  KEEP 합니다.

SQL> exec dbms_shared_pool.keep('7856AC4C, 1137127237','c');

PL/SQL 처리가 정상적으로 완료되었습니다.

Object의 KEEP 상태는 다음으로 체크 가능 합니다.

SQL> select distinct name, sharable_mem, loads
     from v$db_object_cache
     where name like '%emp%'
     and kept = 'YES';

NAME                                      SHARABLE_MEM      LOADS
------------ ----------------------------------------------
select empno, ename, sal from emp where deptno = 20   1469          1

또는 exec dbms_shared_pool.sizes(0)로 확인 가능 합니다. 이 sizes라는 procedure는 제한된 사이크 이상의 keep된 Object를 나타내 줍니다.

SQL> set serveroutput on size 2000

SQL> exec dbms_shared_pool.sizes(0)  -> buffer overflow가 나더라도 pin시킬(KEEP할) SQL문장을 찾을 수는 있습니다.

각 Object를 Shared Pool에 유지하던 것을 해제 할 때는 아래의 unkeep 프로시저를 이용 합니다.

SQL> exec dbms_shared_pool.unkeep('7856AC4C, 1137127237','c');

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> select distinct name, sharable_mem, loads
  2  from v$db_object_cache
  3  where name like '%emp%'
4  and kept = 'YES';

결과가 없겠죠…

☞  자주 사용되는 PL/SQL 블록을 캐싱한다.

- PL/SQL(프로시저,함수,패키지,트리거) 블록들은 너무 커서 실행시 마다 라이브러리 캐시영역
   에 로드되었다가 다시 제거되는 현상이 반복 --> 라이브러리 캐시영역의 단편화 현상 발생
- PL/SQL 블록 , SEQUENCE 등을 라이브러리 영역에 캐싱할 수 있도록 DBMS_SHARED_POOL
  패키지 제공.

SQL> select name,type,kept
         from v$db_object_cache
        where type in ('PACKAGE','PROCEDURE','TRIGGER','PACKAGEBODY');

NAME                      TYPE                 KEPT
------------------- ---------            ------
                                                             <= 캐싱된 PL/SQL 블록 정보가 없다
SQL>connect scott/tiger
SQL>create or replace procedure check_swan   <= 샘플 Procedure 생성
         (  v_emp_no in emp.empno%type )
          is
          begin
             delete from emp where empno = v_emp_no ;
        end check_swan ;
       /
  
SQL>execute DBMS_SHARED_POOL.KEEP('CHECK_SWAN')
                                <= 해당 프로시저를 공유 풀 영역에 상주.

SQL> select name,type,kept
         from v$db_object_cache
        where type in ('PACKAGE','PROCEDURE','TRIGGER','PACKAGEBODY');

NAME                      TYPE                 KEPT
------------------- -------------       ------
CHECK_SWAN          PROCEDURE          Y   <= 해당 프로지셔 캐싱

SQL>execute DBMS_SHARED_POOL.UNKEEP('CHECK_SWAN')


SQL> select name,type,kept
         from v$db_object_cache
        where type in ('PACKAGE','PROCEDURE','TRIGGER','PACKAGEBODY');

NAME                      TYPE                 KEPT
------------------- -------------       ------
CHECK_SWAN          PROCEDURE          N   <= 해당 프로지셔 캐싱 해제

☞  4-41 실습

아래의 쿼리를 이용하여 가장 빈번하게 사용되는 오브젝트를 찾을 수 있다.

select cache#, type, parameter, gets, getmisses, modifications mod 

from   v$rowcache 

where  gets > 0 

order by gets;





CACHE# TYPE        PARAMETER                GETS  GETMISSES    MOD


------ ----------- ------------------ ---------- ---------- ------


     7 SUBORDINATE dc_user_grants        1615488         75      0


     2 PARENT      dc_sequences          2119254     189754    100


    15 PARENT      dc_database_links     2268663          2      0


    10 PARENT      dc_usernames          7702353         46      0


     8 PARENT      dc_objects           11280602      12719    400


     7 PARENT      dc_users             81128420         78      0


    16 PARENT      dc_histogram_defs   182648396      51537      0


    11 PARENT      dc_object_ids       250841842       3939     75

row cache 튜닝은 매우 제한적이다. 최상의 솔루션은 V$ROWCACHE 의 결과를 기반으로 딕셔너리 접근을 줄이는 것이다. 예를 들어, 만일 시퀀스가 문제라면, 시퀀스를 캐슁하는 것을 고려할 수 있다. 여러 개의 테이블 조인을 포함하는 뷰 및 중첩된 뷰는 래치 경합을 증가시키게 된다. 일반적인 대안은 단순히 SHARED_POOL_SIZE를 증가하는 것이다.

기타>

LIBRARY CACHE LOCK WAIT EVENT가 나타날 때의 해결방법
====================================================

PURPOSE
-------
v$session_wait event 에 'library cache lock'이 발생하면서 session이
waiting 되는 경우가 있다. 오랫동안 이 현상이 지속될 경우 어떤 session이
이 library cache lock 을 갖고 있는지 확인해 볼 수 있다.

Explanation
-----------
v$session_wait view에 'library cache lock'이 나타날 수 있는 경우가
어떠한 것이 있는 지 알아보자.

예를 들어 다음과 같은 alter table 문장의 경우를 살펴보자.
 
 ALTER TABLE x MODIFY (col1 CHAR(200));

X 라는 Table의 row가 많다면 위의 문장으로 인해서 모든 row의 값이
200 bytes로 update되어야 하므로 굉장히 오랜 시간이 걸리게 된다.
이 작업 중에는 Table에 dml이 실행되어도 waiting되는 데, 이런 때에
'library cache lock' 이라고 나오게 된다.

또 package가 compile 되는 중에는 다른 user가 같은 package 내의
procedure나 function 등을 실행시켜도 waiting이 걸리면서 library cache
lock 이나 library cache pin event가 나타난다.

이 library cache lock 을 잡고 있는 session을 확인해 보자.

1. waiting session의 session address 확인

 library cache lock으로 waiting하고 있는 session의 sid를 찾아서
그 session address - v$session의 saddr column 을 확인한다.

2. 다음과 같은 sql로 해당 library cache lock을 잡고 있는 session을
확인할 수 있다.

 아래의 SQL 중에서 'saddr_from_v$session' 부분에 위의 1번에서 찾은
Waiting session의 saddr 값을 입력한다.
단, 아래의 sql은 반드시 sys 또는 internal user에서 실행해야 한다.

 SELECT SID,SERIAL#,USERNAME,TERMINAL,PROGRAM
 FROM V$SESSION
 WHERE SADDR in
  (SELECT KGLLKSES FROM X$KGLLK LOCK_A
   WHERE KGLLKREQ = 0
     AND EXISTS (SELECT LOCK_B.KGLLKHDL FROM X$KGLLK LOCK_B
                 WHERE KGLLKSES = 'saddr_from_v$session' /* BLOCKED SESSION */
                 AND LOCK_A.KGLLKHDL = LOCK_B.KGLLKHDL
                 AND KGLLKREQ > 0)
  );

3. 2번에서 확인한 blocking session에 의해 waiting하고 있는 session들도
확인할 수 있다. 2번에서 찾은 session의 saddr 값을 다음 sql에 대입하여
찾을 수 있다.

 SELECT SID,USERNAME,TERMINAL,PROGRAM
 FROM V$SESSION
 WHERE SADDR in
  (SELECT KGLLKSES FROM X$KGLLK LOCK_A
   WHERE KGLLKREQ > 0
     AND EXISTS (SELECT LOCK_B.KGLLKHDL FROM X$KGLLK LOCK_B
                 WHERE KGLLKSES = 'saddr_from_v$session' /* BLOCKING SESSION */
                 AND LOCK_A.KGLLKHDL = LOCK_B.KGLLKHDL
                 AND KGLLKREQ = 0)
  );

4. blocking session이 왜 오래 걸리는 것인지, v$session_wait 를 다시
확인하거나 실행하고 있는 sql 문장이나 object 등을 v$sql view 등을 확인
해 보아야 한다. 작업이라면 종료될 때까지 기다릴 수 있겠지만, 비정상적인
경우 또는 waiting session을 위해서는 다음과 같이 kill 할 수도 있다.

 alter system kill session 'SID, SERIAL#';

ORA-04031: unable to allocate … shared memory

--------------------------------------------------------------------------------

Symptom:
The Oracle error:

ORA-04031: unable to allocate nnn bytes of shared memory

Cause:
More shared memory is needed than was allocated. SGA private memory has been exhausted.

Fragmentation of shared pool memory is a common problem and ORA-04031 is commonly a result of such fragmentation. Application programmers usually get this error while attempting to load a big package or while executing a very large procedure and there is not sufficient contiguous free memory available in the shared pool. This may be due to fragmentation of the shared pool memory or insufficient memory in the shared pool.

Possible remedies:
Use the dbms_shared_pool package to pin large packages.

Attempt to reduce the use of shared memory.

Increase the initialisation parameter ‘SHARED_POOL_SIZE’.

--------------------------------------------------------------------------------

Alternate symptom
An error of the form (Oracle 8.1.5):

ORA-04031: unable to allocate NNNNN bytes of shared memory ("large pool","unknown object","large pool hea","PX msg pool")

Cause:
This error indicates that Oracle is unable to allocate memory from the ‘large’ pool.

To determine the number of free bytes in the ‘large’ pool execute the following SQL:

SELECT NAME, SUM(BYTES) FROM V$SGASTAT WHERE POOL='LARGE POOL' GROUP BY ROLLUP (NAME);

Possible Remedy:
To resolve the problem, consider increasing the value for ‘LARGE_POOL_SIZE’.

ERROR:
ORA-00604: error occurred at recursive SQL level 2
ORA-04031: unable to allocate 4048 bytes of shared memory ("shared
pool","TRIGGER$SYS","sga heap","state objects")
ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable to allocate 4048 bytes of shared memory ("shared
pool","unknown object","sga heap","state objects")

DB를 관리하면서 다음과 같은 에러를 직면할때가 있느데 이럴 경우에는 신속하게
대처할 수 있는 방법
  1. 오래된 세션을 끊어서 shared pool 에 잡고 있는 메모리를 해제 시켜준다.
  2. alter system flush shared_pool; 명령어를 통해 메모리 조각모임을 해준다.
장기적인 대책은
  단편화 시키는 SQL를 찾아내고 바인딩 변수화 시킨다.

ORA-04031 의 경우 shared pool 내에 메모리 조각화에 따라서 연속된 parsing 공간을 제공하지 못하기 때문에 발생하는 에러입니다.
다시 말해서 parsing 에러가 발생하는 것입니다.

이를 해소 해주기 위한 방법으로
개체가 큰 자주 사용되는 프로시져등을 메모리에 pined 해주면 되며,
shared pool 사이즈를 늘려 주는것이 가장 좋으며,
상황이 여의치 않는 경우 단편화된 shared pool 의 조각화을 다시 flush 해주는 방법
등이 있습니다.

SQL> alter system flush shared_pool;

shared pool 의 hit 율을 잘 분석해 보시고 오라클의 권장사항에 따라서 튜닝 가이드 라인을 정하는게 중요합니다.

너무 크게 줘서 free size 가 너무 많이 남는 경우 즉 hit 율은 좋은데, free size 가 너무 큰 경우는 메모리 낭비를 하게 되며, 각각의 o/s 에 따라서  paging 이나 swap 이 발생할 가능성이 있으니, 튜닝후 적적할 모니터를 통해서 사이즈를 잡아 가는 것이 좋습니다.

SQL> select name, bytes/1024/1024 "Size in MB" from v$sgastat where name='free memory';

NAME                       Size in MB
-------------------------- ----------
free memory                451.496498
free memory                  .5859375
free memory                    .03125

즉 오라클의 동적 뷰들의 대부분은 current 한 내용이 아닌 축적용으로 평균치를 나타 내기 때문에 의미가 없습니다.

======================================================================================================================

Ora-00604 또는 Ora-04031 에러 메세지 발생했을 때 아래 내용에 따라 오류를 조치하기 바랍니다.
 
1. 발생 원인
  Shared Pool의 사용 가능한 Memory 가 시간이 흐름에 따라 작은 조각으로 분할되어 진다는 것이다.
  그래서 큰 부분의 Memory 를 할당하려 한다면 Shared Memory가 부족하다는 ORA-4031 Error가 발생한다.
  즉, 전체적으로는 많은 양의 사용 가능한 Space가 있다 하더라도 충분한 양의 연속적인 공간이 없으면 이 Error가 발생한다.
 
2. 조치 방법
   DB를 관리하면서 다음과 같은 에러를 직면할때가 있느데 이럴 경우에는 신속하게 대처할 수 있는 방법
 
  1. 오래된 세션을 끊어서 shared pool 에 잡고 있는 메모리를 해제 시켜준다.
  2. alter system flush shared_pool; 명령어를 통해 메모리 조각모임을 해준다.
  3. Shared_Pool에 크기가 큰 프로그램을 Keep 을 시켜준다.
 
장기적인 대책은 단편화 시키는 SQL를 찾아내고 바인딩 변수화 시킨다.
   이를 해소 해주기 위한 방법으로 개체가 큰 자주 사용되는 프로시져등을 메모리에 pined 해주면 되며,
shared pool 사이즈를 늘려 주는것이 가장 좋으며, 상황이 여의치 않는 경우 단편화된 shared pool 의 조각화을
다시 flush 해주는 방법과 인스턴스를 내렸다 올리는 방법도 있습니다.

  SQL> alter system flush shared_pool;

======================================================================================================================

ORA-4031의 솔루션은 그 원인에 따라 다양한 방법이 있습니다.

먼저 ORA-4031가 발생하는 원인은, SHARED_POOL을 관리하는 과정에, 많은 조각화(Fragment)가 발생하고 Free Memory가 아주 적은 상태에서, 커다란 SQL(PL/SQL)이 Memory로 Load 될 때 공간이 부족해서 발생할 수 있습니다.

이 ora-4031 Error가 발생하게 되면, Shared pool의 관리가 원활히 되지 않아, 이후에 수행되는 모든 SQL이 error가 발생합니다. 그러므로 이는
반드시 예방되어야 합니다.

 이러한 Memory관리상의 문제를 해결하기 위해 조치 할 수 있는 것은 아래의 것들이 있습니다.

1.      v$sql 내의 Literal SQL이 많은지 확인한다.

많은 경우 Literal SQL을 사용하는 SQL을    찾아서 공유 할수 있도록 Bind Variable을 사용토록 하면 됩니다.

   => Literal SQL을 찾는 방법.

select substr(sql_text, 1, 40) "SQL",
       count(*) cnt,
       sum(executions) "TotExecs",
       sum(sharable_mem) mem,
       min(first_load_time) start_time,
       max(first_load_time) end_time,
       max(hash_value) hash        
from v$sqlarea
where executions < 5    --> 수행 횟수가 5번 이하인 것.
group by substr(sql_text, 1, 40)
having count(*) > 30    --> 비슷한 문장이 30개 이상.
order by 2 desc;

2. v$sql 내의 sharable Memory가 큰것들을 확인 한다.

   1M byte이상의 SQL이 있다면 확인 후 SQL의 복잡도를 줄인다(recursive call을 많이 한다든지..). 대부분의 경우 크기가 큰 것들은 일반 SQL이 아니라 PL/SQL이므로 이러한 것들은 Memory에서 내려오지 않도록 Pin을 시키는 방법도 있습니다. (그렇다고 memory에서 완전히 안내려 오는 것은 아닙니다.)

   => PL/SQL을 Memory에 Pin시키는 방법.

   execute dbms_shared_pool.keep('SCOTT.HELLO_WORLD'); 

3. SHARED_POOL_SIZE와 SHARED_POOL_RESERVED_SIZE의 크기를 늘린다.

   항상 Shared pool의 Free가 여유가 있도록 shared_pool_size를 크기를 좀 늘리시고

   특히 Shared_pool_reserved_size의 크기를 100M정도 되도록 지정하세요. 경험적으로 shared_pool_reserved_size가 100M정도 지정하면 ora-4031가 많이 발생하지는 않더군요.

   Free공간 확인 .

   SELECT free_space, avg_free_size, used_space,
          avg_used_size, request_failures, last_failure_size
   FROM    v$shared_pool_reserved;

4. 이것이 진짜 마약처럼 잘 듯는 방법인데, 9i부터는 Shared_pool의 관리를 좀더 효율적으로 하고 System의    CPU를 효과적으로 사용하기 위해 하나의 heap memory를 사용하던 것을 subheap으로 나누어 관리를 하고 있지요. 이렇게 sub-heap으로 나누어 관리하다 보니 작은 공간이 sub heap에 동시에 있더라도 이를 잘 활용하지 못해서 발생하는 경우가 있습니다. 이러한 이유로 ORA-4031 Error의 원인이 되는 경우가 종종 있습니다.

   현재 시스템이 Multi CPU인 경우에는 아마도 1보다 큰 값으로 되어 있을 겁니다.

   그래서 아래의 Query로 조회해 본 후 그 값이 1보다 큰 값이라면 init.ora에서

   _kghdsidx_count=1로 지정한 후 restart해서 사용해 보세요. 어지간해서 ORA-4031가발생 하지 않을 겁니다.

          select x.ksppinm, y.ksppstvl
            from x$ksppi x , x$ksppcv y
             where x.indx = y.indx
             and x.ksppinm like '_kghdsidx_count%' escape ''
             order by x.ksppinm;
 

==========================================
 결과 수치 값 예
==========================================
  select x.ksppinm, y.ksppstvl
                from x$ksppi x , x$ksppcv y
                where x.indx = y.indx
               and x.ksppinm like '_kghdsidx_count%'
               order by x.ksppinm

KSPPINM   KSPPSTVL   _kghdsidx_count
--------------------------------------------------------------------------------
4

shared pool flush 시키기

SQL> alter system flush shared_pool;

출처 : OTN - Technical Bulletins

No. 10095

ORA-4031 조치 방법 과 DBMS_SHARED_POOL STORED PROCEDURE 사용법
==============================================================

Purpose
-------

  다음과 같은 작업 수행 시 Oracle 이 Shared pool 에서 연속적인
  메모리 부분을 찾지 못해 ORA-4031 에러를 발생시키는 것을 볼 수 있다.

   .  PL/SQL Routine
   .  Procedure 수행 시  
   .  Compile 시
   .  Forms Generate 또는 Running 시
   .  Object 생성하기 위해 Installer 사용 시

  본 자료에서는 이러한 에러에 대한 대처 방안을 설명 하고자 한다.

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

  Error 발생의 주된 원인은 Shared Pool의 사용 가능한 Memory 가 시간이
 흐름에 따라 작은 조각으로 분할되어 진다는 것이다. 그래서 큰 부분의
 Memory 를 할당하려 한다면 Shared Memory가 부족하다는 ORA-4031 Error가
 발생한다. 즉, 전체적으로는 많은 양의 사용 가능한 Space가 있다 하더라도
 충분한 양의 연속적인 공간이 없으면 이 Error가 발생한다.

1. Shared Pool과 관련된 인스턴스 파라미터
  다음 3가지 파라미터는 본 자료를 이해 하는데 매우 중요하다.

 * SHARED_POOL_SIZE - Shared Pool 의 크기를 지정 한다. 정수를 사용하며
    "K" 나 "M" 을 덧붙일 수 있다. 
           
 * SHARED_POOL_RESERVED_SIZE - 공유 풀 메모리에 대한 대량의 연속 공간
    요청에 대비해서 예약하는 영역의 크기를 지정한다. 이 영역을 사용하기
    위해서는 SHARED_POOL_RESERVED_MIN_ALLOC 보다 큰 영역 할당 요청이어야
    한다. 일반적으로 SHARED_POOL_SIZE 의 10% 정도를 지정한다.
           
 * SHARED_POOL_RESERVED_MIN_ALLOC - 예약 메모리 영역의 할당을 통제한다.
  이 값보다 큰 메모리 값이 할당 요청되었을 때 공유 풀의 free list 에
  적합한 메모리 공간이 없으면 예약된 메모리 공간의 리스트에서 메모리를
  할당해 준다. 이 값은 8i부터는 내부적으로만 사용된다.

Workaround
-----------
Re-start the instance 

Solution Description:
---------------------
이 Error 해결방안을 살펴 보면 다음과 같다.

 1. 혹시 알려진 제품 문제에 해당 되지 않는지 확인 한다.
  
 * BUG 1397603: ORA-4031 / SGA memory leak of PERMANENT memory occurs
 for buffer handles. (Workaround: _db_handles_cached=0, Fixed: 8172,
 901 )
 * BUG 1640583: ORA-4031 due to leak / cache buffer chain contention
from AND-EQUAL access. (Fixed: 8171,901 )
 * BUG 1318267: INSERT AS SELECT statements may not be shared when they
 should be if TIMED_STATISTICS. It can lead to ORA-4031. (Workaround:
_SQLEXEC_PROGRESSION_COST=0, Fixed: 8171, 8200)
 * BUG 1193003: Cursors may not be shared in 8.1 when they should be
   (Fixed: 8162, 8170, 901)

 2. Object를 Shared Pool에 맞추어 Fragmentation을 줄인다.
    (Dbms_Shared_Pool Procedure 이용) 
    
     다음은 크기가 크고 빈번히 access되는 package들임.

 standard packages
 dbms_standard
 diutil
 diana
 dbms_sys_sql
 dbms_sql
 dbms_utility
 dbms_describe
 pidl
 dbms_output
 dbms_job

 3. Shared Pool 을 효율적으로 사용하도록 Application Program을 조절한다.

 4. 메모리 할당을 조정한다.

 우선 다음 쿼리로 library cache 문제인지 shared pool reserved space  문제인지 진단한다.

          SELECT free_space, avg_free_size, used_space,
          avg_used_size, request_failures, last_failure_size
          FROM    v$shared_pool_reserved;

  만일    REQUEST_FAILURES is > 0 and LAST_FAILURE_SIZE is >
          SHARED_POOL_RESERVED_MIN_ALLOC
  이면 ORA-4031 은 Shared Pool 의 연속 공간 부족의 결과이다. 

  해결책: SHARED_POOL_RESERVED_MIN_ALLOC 값을 증가 시켜서 shared pool
          reserved space 에 올라가는 오브젝트의 수를 줄인다. 그리고
          SHARED_POOL_RESERVED_SIZE 와 SHARED_POOL_SIZE 를 충분히 확보
          해 준다.

  만일    REQUEST_FAILURES is > 0 and LAST_FAILURE_SIZE is <
          SHARED_POOL_RESERVED_MIN_ALLOC
  이거나
          REQUEST_FAILURES is 0 and LAST_FAILURE_SIZE is <
          SHARED_POOL_RESERVED_MIN_ALLOC
  이면 ORA-4031 은 library cache 내의 연속된 공간 부족의 결과 이다.

  해결책: SHARED_POOL_RESERVED_MIN_ALLOC 을 줄여서 shared pool reserved
          space 를 보다 쉽게 사용할 수 있도록 해준다. 그리고 가능하면
          SHARED_POOL_SIZE 를 증가시킨다.
   

  5. DBMS_SHARED_POOL STORED PROCEDURE 사용법

이 stored package는 dbmspool.sql을 포함하며 7.0.13 이상 version에서 사용
 가능하다. 이는 다음과 같이 3가지 부분으로 나누어 진다.
 
 Procedure sizes(minsize number):
 -> Shared_Pool_size 안에서 정해진 Size 보다 큰 Object를 보여준다.

 Procedure keep(name varchar2, flag char Default  'P'):
 -> Object (Only Package)를 Shared Pool 에 유지한다. 또한 일단 Keep한
      Object는 LRU Algorithm에 영향을 받지 않으며
   "Alter System Flush Shared_Pool" Command 에 의해 Package 의 Compiled
      Version 이 Shared Pool에서 Clear되지 않는다.

 Procedure unkeep(name varchar2):
   -> keep() 의 반대 기능이다

  이 Procedure들과 사용법에 대해 보다 더 자세한 정보를 위해서는  $ORACLE_HOME/rdbms/admin/dbmspool.sql script 또는 오라클 레퍼런스매뉴얼을 참조하기 바람.

Reference Documents
-------------------
<NOTE:146599.1> Diagnosing and Resolving Error ORA-04031.

No. 19876

(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 문을 공유하기에 관한 자료는 <Bulletin:11776>을 참조하도록 한다.

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

No. 19876

(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 문을 공유하기에 관한 자료는 <Bulletin:11776>을 참조하도록 한다.

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

출처 : Tong - exospace님의 Oracle통

- shared pool size를 늘리기 전에 다음과 같은 사항을 먼저 체크한다

  ○ shared sql을 이용하는 application 튜닝
  ○ shared_pool_size, shared_pool_reserved_size 튜닝
  ○ shared_pool_reserved_min_alloc 튜닝

1. ora-4031 에러가 library cache나 shared_pool reserved space내의 fragmentation으로 발생했는지 다음 명령어로 확인한다.

select free_space,avg_free_size,used_space,avg_used_size,request_failures,last_failure_size
from v$shared_pool_reserved;

=> shred pool reserved space 내에 연속된 공간이 부족해서 ora-4031 에러가 발생했다면 위 쿼리의 결과 값은 다음과 같은 조건을 만족해야 한다

request_failures > 0
and
last_failure_size > shared_pool_reserved_min_alloc

shared pool reserved space 내에 연속된 공간이 부족한 문제를 해결하려면, shared pool reserved space 내에 캐시될 objects의 개수를 맞추기 위해서 shared_ool_reserved_min_alloc parameter 값을 늘려준다.  또한 shard pool reserved space 내에서 이용 가능한 메모리를 증가시키려면 shared_pool_reserved_size와 shared_pool_size parameter 값을 증가시킨다.

2. library cache 내에 존재하는 sapce 중에서 연속된 space의 부족으로 ora-4031 에러가 발생했다면, 앞의 sql 문의 결과 값은 다음과 같은 조건을 만족해야 한다.

request_failures > 0
and
last_failure_size < shared_pool_reserved_min_alloc

- or -

request_failures = 0
and
last_failure_size < shared_pool_reserved_min_alloc

library cache 내에 존재하는 space 중에서 연속된 space의 부족으로 발생한 문제를 제거하려면 shared pool reserved space 내에 더 많은 objects 들을 두기 위해 shared_pool_reserved_min_alloc parameter 값을 낮춰주고, shared_pool_size parameter 값을 증가 시킨다.

3. 진단 후의 솔루션

- 최신 patchset 적용
- shared pool 이 조각나면 실제 운용 환경에서는 사용자들이 느낄 수 있을 정도의 성능 저하 현상이 발생한다.
  wait_event 상으로는 shard pool latch가 발생한다. 그리고 연속된 조각을 발견할수 없다는 ora-04031 에러를 발생시킨다.

-literal values 와 bind variable을 포함하는 candidates들을 볼 수 있는 쿼리

select substr(sql_text,1,40) "SQL",count(*),sum(executions) "TotExecs"
from v$sqlarea
where executions < 5
group by substr(sql_text,1,40)
having count(*) > 30
order by 2;

=> having 부분의 30이라는 숫자는 사용자 환경에 맞춰 변경해 주면 된다.

- X$KSMLRU view
: shared pool 내에 있는 다른 objects들을 내보낸(age out)것을 추적하는데 이용된다. 이것은 large allocation을 유발시킨 것을 찾아낼 수 있게 해준다. 많은 object들이 지속적으로 shared pool 에서 빠져나간다면 응답 시간이 느려지는 문제가 발생할 수 있고, flush된 objects들이 shared pool 내로 재진입 할때는 library cache latch 이벤트를 유발하는 원인이 될 수도 있다.

select * from X$KSMLRU where ksmlrsiz > 0;

- miss ratio 결과 값이 1% 이상이라면 shared pool size를 증가시켜 library cache miss ratio를 줄이도록 하자.

4. 최후 수단

event = "4031 trace name errorstack level 3"

9i부터는 Shared_pool의 관리를 좀더 효율적으로 하고 System의 CPU를 효과적으로 사용하기 위해
하나의 heap memory를 사용하던 것을 subheap으로 나누어 관리를 하고 있다.
이렇게 sub-heap으로 나누어 관리하다 보니 작은 공간이 sub heap에 동시에 있더라도  이를 잘 활용하지 못해서 발생하는 경우가 발행할 수 있다.
이러한 이유로 ORA-4031 Error의 원인이 되는 경우가 종종 발생할 수 잇다.

현재 시스템이 Multi CPU인 경우에는 아마도 이 값이 1보다 큰 값으로 정의되어 있을 것이다..

그래서 아래의 Query로 조회해 본 후 그 값이 1보다 큰 값이라면 init.ora에서  _kghdsidx_count=1로 지정한다.

select x.ksppinm, y.ksppstvl from x$ksppi x , x$ksppcv y
where x.indx = y.indx and x.ksppinm like '_kghdsidx_count%' escape ''
order by x.ksppinm;

SELECT * FROM v$sqltext;
SELECT substr(sql_text, 1, 40) AS “SQL”,
count(*) AS cnt,
sum(executions) AS “TotExecs”,
sum(sharable_mem) AS mem,
min(first_load_time) AS start_time,
max(first_load_time) AS end_time,
max(hash_value) AS hash
from v$sqlarea
where executions < 5 --> 수행 횟수가 5번 이하인 것.
group by substr(sql_text, 1, 40)
having count(*) > 30 –> 비슷한 문장이 30개 이상.
order by 2 desc;
프로그램 시작 (세션 시작시)
alter session set cursor_sharing=force;
…
프로그램 작성
…
프로그램 종료 (세션 종료시)
alter session set cursor_sharing=exact;
강제로 메모리 정리
alter system flush shared_pool;
..

=======================================================================================
-----에러내용
ORA-04031: 4064 바이트의 공유 메모리를 할당할 수 없습니다 ("shared pool","select * from fm_org_chg_inf...","sga heap(2,0)","kglsim heap")

-----원인

Duplicate entries for SHARED_POOL_SIZE and JAVA_POOL_SIZE.

SGA_TARGET is set and also duplicate entries in parameter file found as below

"shared_pool_size" and "<SID>.__shared_pool_size"
    "java_pool_size" and "<SID>.__java_pool_size"
Upgrade fails with ORA-04031, When both SGA_TARGET and  Manual shared Memory Parameters

-----해결방안

1- Remove either of the entry of SGA_TARGET and Manual shared Memory Parameters from pfile
2- If SGA_TARGET is removed, then make sure <SID>.__parameters also removed.
3- Make sure SHARED_POOL_SIZE and JAVA_POOL_SIZE is more than 200 MB as mentioned in Note 376612.1 "ORA-04031: unable to allocate xxxx bytes of shared memory" during upgrade to 10gR2

---사견

SHARED_POOL_SIZE를 늘리라는 얘기를 하는데.

SGA_TARGET잡은 상태에서는 SHARED POOL, DATA CACHE는 바꿀수가 없습니다.

그렇게 할려면 SGA_TARGET라는 파라메터를 쓰지 말라는 말씀입니다..^^

다시말하면 오라클 버그라는 말이지요..망할 오라클..

아래 쿼리를 수행해서 DB_CACHE_SIZE,SHARED_POOL_SIZE가 SGA_TARGET 잡힌 상태에서

얼마나오는지 보고 수동으로 잡는게 좋을거 같습니다.

alter system set sga_target = 0 ;
ALTER SYSTEM SET DB_CACHE_SIZE=3791650816 ;
ALTER SYSTEM SET SHARED_POOL_SIZE=3892314112;

col NAME for a50
col VALUE for a50
set linesize 120
set pagesize 100
select nam.KSPPINM name,val.ksppstvl value
from x$ksppi nam, x$ksppsv val where
nam.indx=val.indx and nam.ksppinm like '%shared%' order by 1;

col NAME for a50
col VALUE for a50
set linesize 120
set pagesize 100
select nam.KSPPINM name,val.ksppstvl value
from x$ksppi nam, x$ksppsv val where
nam.indx=val.indx and nam.ksppinm like '%cache%' order by 1;

[출처] ORA-04031|작성자 5racle

이 에러의 원인은 크게 3가지로 나뉘어 진다.

1. 다수의 사용자로 인한 SharedPoolSize부족문제

2. 구동중인 App에 비해 현저히 부족한 SharedPool사용으로 인한 문제

3. 덩치 큰 SQL 구동을 위한 연속된 SharedPool할당 불가로 인한 문제

이중 1,2는 같은 맥락에서 접근할 수 있으므로 크게 2가지라고 볼 수도 있다.

이 문제는 OTN 에서 꽤나 유명한 에러로서 아래와 TechBulletin에는 아래와 같이 언급되어 있다.

No. 10095

ORA-4031 조치 방법 과 DBMS_SHARED_POOL STORED PROCEDURE 사용법
==============================================================

Purpose
-------

다음과 같은 작업 수행 시 Oracle 이 Shared pool 에서 연속적인 메모리 부분을 찾지 못해 ORA-4031 에러를 발생시키는 것을 볼 수 있다.

. PL/SQL Routine
. Procedure 수행 시
. Compile 시
. Forms Generate 또는 Running 시
. Object 생성하기 위해 Installer 사용 시

본 자료에서는 이러한 에러에 대한 대처 방안을 설명 하고자 한다.

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

Error 발생의 주된 원인은 Shared Pool의 사용 가능한 Memory 가 시간이 흐름에 따라 작은 조각으로 분할되어 진다는 것이다. 그래서 큰 부분의
Memory 를 할당하려 한다면 Shared Memory가 부족하다는 ORA-4031 Error가 발생한다. 즉, 전체적으로는 많은 양의 사용 가능한 Space가 있다 하더라도
충분한 양의 연속적인 공간이 없으면 이 Error가 발생한다.

1. Shared Pool과 관련된 인스턴스 파라미터
다음 3가지 파라미터는 본 자료를 이해 하는데 매우 중요하다.

* SHARED_POOL_SIZE - Shared Pool 의 크기를 지정 한다. 정수를 사용하며 "K" 나 "M" 을 덧붙일 수 있다.

* SHARED_POOL_RESERVED_SIZE - 공유 풀 메모리에 대한 대량의 연속 공간 요청에 대비해서 예약하는 영역의 크기를 지정한다. 이 영역을 사용하기
위해서는 SHARED_POOL_RESERVED_MIN_ALLOC 보다 큰 영역 할당 요청이어야 한다. 일반적으로 SHARED_POOL_SIZE 의 10% 정도를 지정한다.

* SHARED_POOL_RESERVED_MIN_ALLOC - 예약 메모리 영역의 할당을 통제한다.
이 값보다 큰 메모리 값이 할당 요청되었을 때 공유 풀의 free list 에 합한 메모리 공간이 없으면 예약된 메모리 공간의 리스트에서 메모리를 할당해 준다.
이 값은 8i부터는 내부적으로만 사용된다.

Workaround
-----------
Re-start the instance

Solution Description:
---------------------
이 Error 해결방안을 살펴 보면 다음과 같다.

1. 혹시 알려진 제품 문제에 해당 되지 않는지 확인 한다.

* BUG 1397603: ORA-4031 / SGA memory leak of PERMANENT memory occurs for buffer handles. (Workaround: _db_handles_cached=0, Fixed: 8172,901 )
* BUG 1640583: ORA-4031 due to leak / cache buffer chain contentionfrom AND-EQUAL access. (Fixed: 8171,901 )
* BUG 1318267: INSERT AS SELECT statements may not be shared when they should be if TIMED_STATISTICS. It can lead to ORA-4031. (Workaround: _SQLEXEC_PROGRESSION_COST=0, Fixed: 8171, 8200)
* BUG 1193003: Cursors may not be shared in 8.1 when they should be (Fixed: 8162, 8170, 901)

2. Object를 Shared Pool에 맞추어 Fragmentation을 줄인다.
(Dbms_Shared_Pool Procedure 이용)

다음은 크기가 크고 빈번히 access되는 package들임.

standard packages
dbms_standard
diutil
diana
dbms_sys_sql
dbms_sql
dbms_utility
dbms_describe
pidl
dbms_output
dbms_job

3. Shared Pool 을 효율적으로 사용하도록 Application Program을 조절한다.

4. 메모리 할당을 조정한다.

우선 다음 쿼리로 library cache 문제인지 shared pool reserved space 문제인지 진단한다.

SELECT free_space, avg_free_size, used_space, avg_used_size, request_failures, last_failure_size FROM v$shared_pool_reserved;

만일 REQUEST_FAILURES is > 0 and LAST_FAILURE_SIZE is > SHARED_POOL_RESERVED_MIN_ALLOC
이면 ORA-4031 은 Shared Pool 의 연속 공간 부족의 결과이다.

해결책: SHARED_POOL_RESERVED_MIN_ALLOC 값을 증가 시켜서 shared pool reserved space 에 올라가는 오브젝트의 수를 줄인다. 그리고
SHARED_POOL_RESERVED_SIZE 와 SHARED_POOL_SIZE 를 충분히 확보해 준다.

만일 REQUEST_FAILURES is > 0 and LAST_FAILURE_SIZE is < SHARED_POOL_RESERVED_MIN_ALLOC
이거나
REQUEST_FAILURES is 0 and LAST_FAILURE_SIZE is < SHARED_POOL_RESERVED_MIN_ALLOC
이면 ORA-4031 은 library cache 내의 연속된 공간 부족의 결과 이다.

해결책: SHARED_POOL_RESERVED_MIN_ALLOC 을 줄여서 shared pool reserved space 를 보다 쉽게 사용할 수 있도록 해준다. 그리고 가능하면 SHARED_POOL_SIZE 를 증가시킨다.

5. DBMS_SHARED_POOL STORED PROCEDURE 사용법

이 stored package는 dbmspool.sql을 포함하며 7.0.13 이상 version에서 사용가능하다. 이는 다음과 같이 3가지 부분으로 나누어 진다.

Procedure sizes(minsize number):-> Shared_Pool_size 안에서 정해진 Size 보다 큰 Object를 보여준다.

Procedure keep(name varchar2, flag char Default 'P'):
-> Object (Only Package)를 Shared Pool 에 유지한다. 또한 일단 Keep한 Object는 LRU Algorithm에 영향을 받지 않으며 "Alter System Flush Shared_Pool" Command 에 의해 Package 의 Compiled
Version 이 Shared Pool에서 Clear되지 않는다.

Procedure unkeep(name varchar2):-> keep() 의 반대 기능이다

이 Procedure들과 사용법에 대해 보다 더 자세한 정보를 위해서는 $ORACLE_HOME/rdbms/admin/dbmspool.sql script 또는 오라클 레퍼런스 매뉴얼을 참조하기 바람.

Reference Documents
-------------------
Diagnosing and Resolving Error ORA-04031.

위의 내용과 관련하여 SharedPoolSize 조절 방법은 아래와 같다.

이미 공지의 사실이지만 서두로 언급하면 Oracle은 Background Process와 SGA영역으로 구분된다.

그중 SGA는 SharedPool과 RedoLogBuffer, BufferCache로 이루어져 있다.

이중 SharedPool은 SQL Area와 Data Structure로 이루어져 있다.

SharedPool Size를 산정하는 방법은 아래와 같다.

계산 공식　　　　　　　　

Session당 최대메모리사용량(Max Session Memory) * 동시 접속하는 User의 수　
+　 Shared SQL 영역으로 사용되는 메모리양　　　　　　　　　
+　 Shared PLSQL을 위해 사용하는 메모리 영역　　　　　　　　　　
+　 최소 30%의 여유 공간　

계산 예제　　　　　　　　　

　 (1) 적당한 user session에 대한 session id를 찾는다.　　　　　　　　
　　　　　　　　　
　　　　　　　 SQLDBA>　 select sid from v$process p, v$session s　　　　　　　　　　
　　　　　　　　　　　　 where p.addr=s.paddr and s.username='SCOTT';　　　　　　　　　

　　　　　　　　　　　　　 SID　　　　　　　　　
　　　　　　　　　　 ----------　　　　　　　　　
　　　　　　　　　　　　　　 29　　　　　　　　　
　　　　　　　 1 rows selected.　　　　　　　　　

　 (2) 이 session id에 대한 maximum session memory를 찾는다.　　　　　　　　
　　　　　　　　　
　　　　　　　 SQLDBA> select value from v$sesstat s, v$statname n　　　　　　　　　　
　　　　　　　　　　　 where s.statistic# = n.statistic#　　　　　　　　　　
　　　　　　　　　　　 and n.name = 'session uga memory max' and sid=29;　　　　　　　　　

　　　　　　　　　　 VALUE　　　　　　　　　　　　　　
　　　　　　　　　　 -----------　　　　　　　　　
　　　　　　　　　　　 273877　　　　　　　　　　　　　　　　　
　　　　　　　 1 rows selected.　　　　　　　　　
　　　　　　　　　
　 (3) Total shared SQL area를 구한다.　　　　　　　　
　　　　　　　　　
　　　　　　　 SQLDBA>　 select sum(sharable_mem) from v$sqlarea;　　　　　　　　　

　　　　　　　 SUM(SHARAB　　　　　　　　　
　　　　　　　 ---------------------　　　　　　　　　
　　　　　　　　　　　　　　 8936625　　　　　　　　　
　　　　　　　 1 row selected.　　　　　　　　　
　　　　　　　　　
　 (4) PLSQL sharable memory area를 구한다.　　　　　　　　　
　　　　　　　　　
　　　　　　　 SQLDBA>　 select sum(sharable_mem) from v$db_object_cache;　　　　　　　　　

　　　　　　　 SUM(SHARAB　　　　　　　　　
　　　　　　　 ------------------　　　　　　　　　
　　　　　　　　　　　 4823537　　　　　　　　　
　　　　　　　 1 row selected.　　　　　　　　　
　　　　　　　　　
　　　　　　　　　
　 (5) Shared pool size를 계산한다.　　　　　　　　　
　　　　　　　　　　　　　　　　　
　　　　　　　　　　　　 274K shared memory　 *　 400 users　　　　　　　　　
　　　　　　　 +　　　　　 9M Shared SQL Area　　　　　　　　　　　　　　
　　　　　　　 +　　　　　 5M PLSQL Sharable Memory　　　　　　　　　　
　　　　　　　 +　　　　　 60M Free Space (30%)　　　　　　　　　　　　　　

　　　　　　　 =　　　 184M Shared Pool　　　　　　　　　　　　
　　　　　　　　　　　　　　　　　
　　　　　　　　　　
　　 이 예제에서는 Shared pool의 size는 184M가 적당하다고 할 수 있다. 이때 Free Space(60M) 계산 방법은 전제 184 M 에 대한 30 % 정도의 추정치 이다.　　　　　　　　
　　　　　　　　　
Shared Memory부족 (ORA-4031)에 대한 대처

　다음과 같은 방법으로 에러를 피해 갈 수 있다.- "Sys.dbms_shared_pool.keep" procedure사용.　　　　　　　　

[참고] 위 package를 사용하려면 ?/rdbms/admin/dbmspool.sql,prvtpool.sql를 수행시켜 package를 create시킨 후 사용한다.　　　　　　　　
　　　 (자세한 사항은 bulletin 10095,Oracle7 Server Tuning 4장12를 참조한다.)　　　　　　　　　
　　　　　　　　　

ORACLE_HOME/dbs/initSID.ora에 보시면 Processes=???값과 sessions=?????라는
값에 좌우가 되는데 시스템에서 허락되는
User별 process값이 있고 이 범위내에서 허용이 된다.
SQL> show parameter process;

NAME TYPE VALUE
------------------------------------ ------- ------------------------------
aq_tm_processes integer 0
db_writer_processes integer 1
job_queue_processes integer 0
log_archive_max_processes integer 1
processes integer 300
SQL> show parameter session;

NAME TYPE VALUE
------------------------------------ ------- ------------------------------
java_max_sessionspace_size integer 0
java_soft_sessionspace_limit integer 0
license_max_sessions integer 0
license_sessions_warning integer 0
mts_sessions integer 330
session_cached_cursors integer 0
session_max_open_files integer 10
sessions integer 335
SQL>

[출처] ORA-04031|작성자 메치니