인스턴스와 SGA 구성 요소

#핵심 요약

인스턴스는 서버 메모리에 잡힌 SGA(System Global Area)와 그 메모리를 다루는 백그라운드 프로세스의 묶음임
인스턴스 자체는 파일을 갖지 않음

데이터베이스는 디스크 위의 파일임
인스턴스는 STARTUP할 때마다 새로 만들어지고 내리면 사라짐

SGA는 shared pool, buffer cache, redo log buffer, large pool, java pool, streams pool, fixed SGA로 이루어짐

같은 SGA를 V$SGA, V$SGAINFO, V$SGASTAT 순서로 점점 확대해서 볼 수 있고, 항목을 모두 더하면 총량과 맞아야 함

#개념과 동작 원리

#인스턴스와 데이터베이스

#SGA를 보는 세 가지 배율

실습에서는 같은 SGA를 확대 배율을 높여 가며 세 단계로 확인함

1. V$SGA: 가장 큰 단위인 네 덩어리
   합계가 startup 때 출력되는 Total System Global Area와 같음
2. V$SGAINFO: 구성 요소별 크기와 여유
   RESIZEABLE이 Yes인 것만 서로 크기를 주고받음
   Free SGA Memory Available이 0이면 SGA_TARGET을 남김없이 쓰고 있다는 뜻임
3. V$SGASTAT: 공유 풀 안을 항목 단위로 쪼갠 값
   shared pool의 free memory가 바닥에 가까워지면 ORA-04031이 발생함

세 단계의 항목을 전부 더한 값(V$SGASTAT의 합계)이 1번의 총량과 일치하면 같은 메모리를 배율만 달리 본 것임

#V$SGA의 네 덩어리

Fixed Size: 인스턴스가 반드시 쓰는 고정 영역(내부 제어 구조 등)
거의 변하지 않음

Variable Size: 공유 풀, Large 풀, Java 풀 등 가변 SGA 영역(파싱, 딕셔너리 캐시 등)
설정이나 자동 조정에 따라 변함

Database Buffers: 버퍼 캐시
디스크 데이터 블록을 메모리에 캐싱하는 영역이며 읽기 성능에 핵심임

Redo Buffers: 리두 로그 버퍼
변경 내용을 임시로 쌓아 두면 LGWR가 리두 로그 파일에 기록함

#Large pool, Java pool, Streams pool, Fixed SGA

Large pool은 크게, 잠깐 쓰는 작업을 위한 공간임
두 가지 이유로 씀

1. 메모리를 크게 짧은 시간 필요로 하는 작업을 공유 풀에서 처리하면 LRU 문제가 생김
   그래서 Large pool을 씀
2. 공유 서버를 쓰면 세션별 작업 정보를 모두가 볼 수 있는 SGA로 올려야 함
   이때도 Large pool을 씀

Java pool은 DB 안에서 자바 코드를 실행할 때 쓰는 공간임

Streams pool은 데이터를 다른 곳으로 흘려보내거나 메시지 큐를 다룰 때 쓰는 공간임
Oracle GoldenGate나 Advanced Queuing 환경에서 사용함

Fixed SGA는 DBA가 크기를 정하지 않는 영역임
조회만 할 수 있고 설정할 수 없음

#주요 용어와 구성 요소

#필수 명령어 또는 SQL

#인스턴스 상태와 SGA 총량

select status
  from v$instance;

SELECT *
  FROM v$sga;

col name for a30
select name, round(bytes/1024/1024) MB
  from v$sgainfo
 where name = 'Granule Size';

결과 해석:

NAME                      VALUE
-------------------- ----------
Fixed Size              9140328
Variable Size         603979776
Database Buffers     2701131776
Redo Buffers           24412160

NAME                                     MB RES
-------------------------------- ---------- ---
Maximum SGA Size                       3184 No
Buffer Cache Size                      2576 Yes
Shared Pool Size                        512 Yes
Startup overhead in Shared Pool         216 No
Shared IO Pool Size                     128 Yes
Large Pool Size                          32 Yes
Streams Pool Size                        32 Yes
Redo Buffers                             23 No
Granule Size                             16 No
Fixed SGA Size                            9 No
Data Transfer Cache Size                  0 Yes
Free SGA Memory Available                 0
In-Memory Area Size                       0 No
Java Pool Size                            0 Yes

#구성 요소별 크기

select *
      from v$sgainfo;

SELECT component,current_size/1048576||'M' current_size,
       min_size/1048576||'M' min_size
FROM v$sga_dynamic_components
WHERE component IN ('shared pool','large pool',
                   'java pool','DEFAULT buffer cache');

결과 해석:

같은 조회의 SQL*Plus 스크립트 형태(실습의 SGA.sql)는 열 서식을 지정함

SET LINESIZE 120
SET PAGESIZE 100

COLUMN component    FORMAT A25
COLUMN current_size FORMAT A15
COLUMN min_size     FORMAT A15

SELECT component,
       ROUND(current_size / 1048576, 2) || 'M' AS current_size,
       ROUND(min_size / 1048576, 2) || 'M'     AS min_size
FROM   v$sga_dynamic_components
WHERE  component IN (
           'shared pool',
           'large pool',
           'java pool',
           'DEFAULT buffer cache'
       );

결과 해석: 개별 실행 출력은 기재 없음

#버퍼 캐시와 리두 로그 버퍼

select name, block_size, round(current_size/1024/1024) MB, buffers
       from v$buffer_pool
       order by block_size;

show parameter log_buffer

결과 해석: 개별 실행 출력은 기재 없음

#공유 풀 안을 항목 단위로 보기

SELECT pool, name, ROUND(bytes/1024/1024, 1) mb
  FROM v$sgastat
 WHERE pool = 'shared pool'
 ORDER BY bytes DESC
 FETCH FIRST 5 ROWS ONLY;

SELECT ROUND(SUM(bytes)/1024/1024) total_mb FROM v$sgastat;

결과 해석: 개별 실행 출력은 기재 없음

#Large pool, Java pool, Streams pool, Fixed SGA

SHOW PARAMETER large_pool_size

SELECT name, bytes/1024/1024 MB
  FROM v$sgainfo
 WHERE name LIKE '%Large%';

SELECT component, current_size/1024/1024 MB
  FROM v$sga_dynamic_components
 WHERE component LIKE '%large%';

SHOW PARAMETER java_pool_size

SELECT comp_id, version, status
  FROM dba_registry
 WHERE comp_id='JAVAVM';

SHOW PARAMETER streams_pool_size

SELECT pool, ROUND(SUM(bytes)/1024/1024, 1) MB
  FROM v$sgastat
 WHERE pool IN ('java pool', 'streams pool')
 GROUP BY pool;

SELECT name, bytes FROM v$sgainfo WHERE name='Fixed SGA Size';

결과 해석: 개별 실행 출력은 기재 없음

#인스턴스와 데이터베이스 파일의 위치

인스턴스(메모리)와 달리 데이터베이스는 파일이므로, 파일 위치는 다음 뷰로 확인함

SELECT *
  FROM v$bgprocess
  where paddr <> '00';

select file#, name, status
               from v$datafile;

select name from v$controlfile;

show parameter spfile

select *
  from v$passwordfile_info;

select name
  from v$archived_log;

결과 해석: 개별 실행 출력은 기재 없음

#구문과 옵션 설명

Oracle Database 19c의 V$BGPROCESS.PADDR는 프로세스 상태 객체 주소이며 타입은 RAW(4 또는 8)임
이 열 정의만으로 실습의 paddr <> '00' 조건이 모든 플랫폼에서 실행 중인 프로세스만 정확히 고른다고 판정할 수 없음
해당 비교값의 적용 조건과 당시 조회 결과는 확인 불가

#어떤 뷰를 볼 것인가

#사용 조건과 주의사항

NAME                                     MB RES
-------------------------------- ---------- ---
Maximum SGA Size                       3184 No
Shared Pool Size                       2048 Yes    ← 512에서 4배로 불어남
Buffer Cache Size                      1040 Yes    ← 2576에서 반토막 밑으로
Startup overhead in Shared Pool         216 No
Large Pool Size                          32 Yes
Streams Pool Size                        32 Yes
Redo Buffers                             23 No
Granule Size                             16 No
Fixed SGA Size                            9 No
Shared IO Pool Size                       0 Yes    ← 자동 조정에 밀려 소멸
Data Transfer Cache Size                  0 Yes
Free SGA Memory Available                 0
In-Memory Area Size                       0 No
Java Pool Size                            0 Yes

Fixed SGA는 설정할 수 없고 조회만 가능함

서로 크기를 주고받는 것은 RESIZEABLE이 Yes인 구성 요소뿐임

ORA-04031은 shared pool의 free memory가 바닥에 가까워질 때 발생한다는 것이 실습의 설명임
원인 조사에서는 V$SGASTAT을 컨테이너별로 구분해 보는 사례가 수업 자료에 있음(CON_ID 열이 있는 출력 예시)
단, 이 예시 출력의 질의문은 기재 없음

#보충 설명: SQL*Plus 명령 구분

SHOW PARAMETER, COLUMN(COL), SET LINESIZE, SET PAGESIZE는 SQL*Plus 명령임
적용 버전은 19c이며 다른 버전의 동작은 확인 불가