동적 성능 뷰들

V$SYSSTAT

• 인스턴스 시작 이후에 수집된 인스턴스 레벨의 통계 정보 누적치

SELECT NAME, VALUE FROM V$SYSSTAT WHERE NAME LIKE '%memory%';

/*
NAME                                                             VALUE
---------------------------------------------------------------- -----
session uga memory                                               7190041485288 
session uga memory max                                           8386087328 
session pga memory                                               3712816832 
session pga memory max                                           3983169888 
redo k-bytes read (memory)                                           0 
redo k-bytes read (memory) by LNS                                    0 
workarea memory allocated                                            0 
sorts (memory)                                                   303825 
*/

V$SESSTAT

• 세션 시작 이후에 수집된 세션 레벨의 통계 정보 누적치

DECLARE
  TYPE  VARCHAR2_ARRAY IS TABLE OF VARCHAR2(30000) INDEX BY PLS_INTEGER;
  VA    VARCHAR2_ARRAY;
  V_PGA NUMBER;
BEGIN
  FOR IDX IN 1 .. 10000 LOOP
    -- ALLOCATE MEMORY
    VA(IDX) := RPAD('X', 30000);
    
    IF MOD(IDX, 1000) = 0 THEN
      SELECT S.VALUE INTO V_PGA
        FROM V$SESSTAT S, V$STATNAME N
       WHERE N.NAME = 'session pga memory'
         AND N.STATISTIC# = S.STATISTIC#
         AND S.SID = USERENV('SID');
         
      DBMS_OUTPUT.PUT_LINE('CURRENT PGA = ' || TRUNC(V_PGA/1024/1024) || 'MB');
    END IF;
  END LOOP;
END;
/
         
/*
CURRENT PGA = 41MB
CURRENT PGA = 72MB
CURRENT PGA = 103MB
CURRENT PGA = 134MB
CURRENT PGA = 166MB
CURRENT PGA = 197MB
CURRENT PGA = 228MB
CURRENT PGA = 259MB
CURRENT PGA = 291MB
CURRENT PGA = 322MB
*/ 

-- 위 데모와 같이 V$SESSTAT 뷰를 통해서 PGA 크기 변화 추적 가능.
-- 하지만 변화가 어떤 오브젝트에 왜 생겼는지는 알 수 없으며, 알기 위해서는 HEAP DUMP

V$SGASTAT

SELECT POOL, ROUND(SUM(BYTES)/1024/1024) AS MB 
  FROM V$SGASTAT 
GROUP BY POOL 
ORDER BY SUM(BYTES) DESC;
  
/*
POOL         MB
------------ --
shared pool  284 
             157 
large pool    4 
java pool     4 
streams pool  4 
*/

SELECT * FROM V$SGASTAT ORDER BY POOL ASC, BYTES DESC;
/*
POOL         NAME                       BYTES
------------ -------------------------- -----
java pool    free memory                4194304 
large pool   free memory                3702784 
large pool   PX msg pool                491520 
shared pool  free memory                122057000 
shared pool  SQLA                       41663536 
shared pool  KGLS                       11681736 
shared pool  CCUR                       7804392 
shared pool  PLMCD                      7744088 
shared pool  row cache                  7584808 
shared pool  KGLSG                      5264904 
shared pool  KGLHD                      5097048 
shared pool  KGLH0                      5087648 
shared pool  PCUR                       4708568 
shared pool  kglsim hash table bkts     4194304 
shared pool  KCB Table Scan Buffer      3981120 
...
*/

-- 특정 풀에서 메모리 크기 문제가 발생 했을 때, 어떤 종류의 오브젝트가 원인인지 분석 가능 [shared pool / large pool / java pool / streams pool]
-- 오라클은 shared pool latch 경합 문제 해소를 위해 shared pool 을  가능한 여러 개의 서브 풀로 나눠서 관리 하나 V$SGASTAT 에는 서브 풀 정보가 없음


-- 서브 풀 수 : _KGHDSIDX_COUNT 파라미터 (CPU 4개 이상, shared pool 250 MB 이상 일때 나뉘어짐)
SELECT A.KSPPINM, B.KSPPSTVL 
  FROM X$KSPPI A, X$KSPPSV B
 WHERE A.INDX=B.INDX
   AND A.KSPPINM = '_kghdsidx_count';


/*
KSPPINM         KSPPSTVL
--------------- --------
_kghdsidx_count 3

-- 서브 풀을 관찰하기 위해서 베이스 X$KSMSS 테이블 활용
-- 공유 풀 > 힙 > 서브 힙 > 오브젝트 와 같은 계층 관계를 관찰하기 위해서  X$KSMSP 테이블 혹은 SGA 힙 덤프 이용

V$SHARED_POOL_RESERVED

-- 공유 풀(Shared Pool)은 ORA=4031 에러와 같은 치명적인 에러를 예방하기 위해 예비 풀(Reserved Pool)을 보유

SELECT A.KSPPINM, B.KSPPSTVL 
  FROM X$KSPPI A, X$KSPPSV B
 WHERE A.INDX=B.INDX
   AND A.KSPPINM LIKE '%shared_pool_reserved%';
/*
KSPPINM                            KSPPSTVL
--------------------------------   -----------------
shared_pool_reserved_size          15518924
_shared_pool_reserved_pct          5
_shared_pool_reserved_min_alloc    4400
*/

-- 전체 공유 풀 크기의 5 퍼센트(_shared_pool_reserved_pct)를 예비 풀로 사용 하며, 그 크기(shared_pool_reserved_size)는 15518924 바이트 임.
-- 공유 풀에서 필요한 크기의 여유 공간을 찾지 못하고, 그 크기가 4400 바이트(_shared_pool_reserved_min_alloc) 이상이면 예비 풀 사용

-- ORA-4031: unable to allocate 4200 bytes of shared memory ("shared pool", "unknown object", "sga heap", "state objects") 에러가 발생 한 경우, _shared_pool_reserved_min_alloc 파라미터를 4200 이하로 낮추면 해결 됨 (예비 풀 적용)
-- 위와 같이 _shared_pool_reserved_min_alloc 를 지나치게 작게 설정 할 경우, 예비 풀 자체가 단편화 됨, 근본적인 해결을 위해서 메모리의 재사용성을 높여야 함 (Literal SQL 대신 Bind SQL 활용 등)

EXEC PRINT_TABLE('SELECT * FROM V$SHARED_POOL_RESERVED');
/*
FREE_SPACE                    : 10882080
AVG_FREE_SIZE                 : 197856
FREE_COUNT                    : 52
MAX_FREE_SIZE                 : 217008
USED_SPACE                    : 12127872
AVG_USED_SIZE                 : 220506.763636363636363636363636363636364
USED_COUNT                    : 3
MAX_USED_SIZE                 : 4172016
REQUESTS                      : 0
REQUEST_MISSES                : 0
LAST_MISS_SIZE                : 0
MAX_MISS_SIZE                 : 0
REQUEST_FAILURES              : 0 <-- ORA-4031 발생 시 예비 풀 상황 추적 가능
LAST_FAILURE_SIZE             : 0 <-- ORA-4031 발생 시 예비 풀 상황 추적 가능
ABORTED_REQUEST_THRESHOLD     : 2147483647
ABORTED_REQUESTS              : 0
LAST_ABORTED_SIZE             : 0
*/