파라미터, SGA, PGA, background process

YoonSeo Park ·2023년 12월 11일
PGASGAbackground process파라미터
0

Oracle Architecture

목록 보기
2/11

2023/12/11

■ 초기 파라미터 파일

▶ 1. 텍스트 초기 파라미터 파일(PFILE = init_<SID>.ora)

  • 텍스트 편집기(vi)를 사용하여 수동으로 편집한다.
  • 오라클 DB를 open 한 후에 수동으로 편집하더라도 변경한 값을 인식할 수 없다. -> 재시작해야 적용할 수 있음
  • DB를 처음 open 할때 한번만 access 한다.
  • 기본 이름은 init<SID>.ora 로 만들어 진다.
  • 초기에 emtool을 이용해서 서버를 관리할 떄, 각 DBA마다 init파라미터 파일을 각각 갖고있어야 했고.
    변경된 값을 똑같은 모두 바꿔줘야 하는 번거로움이 있었기 때문에.
    서버에서 한번에 관리할 수 있는 SPFILE이 나오게 되었다.
  • startup nomount 단계에서 $ORACLE_HOME/dbs 디텍토리에서
    spfile<SID>.ora를 먼저 찾고 없으면 init<SID>.ora를 찾는다.
    둘다 없으면 오류 발생한다.

좀더 디테일한 설명
: https://yagi815.tistory.com/341

▶ 2. 서버 파라미터 파일(SPFILE)

  • 위치 확인
    SQL> show parameter spfile
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/product/11.2.0
                                                 .4/db_1/dbs/spfileora11g.ora
  • 일반적으로 사용되는 초기 파라미터 파일 유형
  • 오라클 서버가 읽고 쓸 수 있는 이진파일(binary file)이기 때문에 수동으로 편집기로(vi) 수정하면 안된다.
  • 이 파일은 오라클이 실행되는 서버에 있으며 종료 및 시작과 관계없이 계속 유지된다.
    -> 오라클 서버에 없는 파라미터 파일도 있다.
  • 기본 이름은 spfile<SID>.ora 로 만들어 진다.

■ 파라미터 값 변경

▶ 1. static parameter

  • 초기 파라미터 파일에서만 변경
  • instance 재시작해야 적용된다.
  • 시스템 레벨(alter system set), dba작업
  • issys_modifiable = 'FALSE' 인 경우다.
SELECT *
FROM v$parameter
where issys_modifiable = 'FALES';
  • 변경
alter system set 파라미터 SCOPE = SPFILE;

▶ 2. dynamic parameter

  • 데이터 베이스 온라인 상태인 동안 변경 가능
  • 다음 레벨에서 수정 가능
    • 세션 레벨(alter session set), 일반유저가능
    • 시스템 레벨(alter system set), dba작업
  • 세션 기간 또는 scope 설정에 따른 기간 동안 유효할 수 있도록 설정
  • isses_modifiable = 'TRUE' 이거나 issys_modifiable 이 IMMEDIATE 또는 DEFERRED 경우
SELECT *
FROM v$parameter
where isses_modifiable = 'TRUE'
or issys_modifiable = 'IMMEDIATE';
or issys_modifiable = 'DEFERRED';
  • 변경
ALTER SYSTEM SET 파라미터 SCOPE= SFILE| MEMORY | BOTH;
ALTER SYSTEM SET 파라미터 DEFERRED SCOPE= SFILE| MEMORY | BOTH;

■ SESSION LEVEL, SYSTEM LEVEL 수정할 수 있는 파라미터

SELECT * FROM v$parameter

▶ 1. SESSION LEVEL 에서 수정할 수 있는 파라미터

: 유저 세션에만 영향. 국가별 설정 등. 세션이 끝나면 파라미터도 만료.

  • dynamic 파라미터
  • isses_modifiable = 'TRUE' 인 애들만 바꿀수 있다. = 'FALSE'면 안됨 (세션레벨)
  • 현재 내 session에만 영향을 준다
    SELECT name,value
    FROM v$parameter
    where isses_modifiable = 'TRUE';
SELECT *
FROM v$parameter
where name = 'nls_date_format';

SELECT sysdate FROM dual;
ALTER SESSION SET nls_date_format = 'YYYY-MM-DD';

▶ 2. SYSTEM LEVEL 에서 수정할 수 있는 파라미터

: 전체 데이터베이스 및 모든 세션에 영향.

  • 오라클에서 접속하는 모든 유저에 영향

  • issys_modifiable 은 3가지 설정값이 있다.

    • FALSE
    • IMMEDIATE
    • DEFERRED (연기된) 
SELECT * FROM v$parameter WHERE issys_modifiable ='FALSE';
SELECT * FROM v$parameter WHERE issys_modifiable ='IMMEDIATE';
SELECT * FROM v$parameter WHERE issys_modifiable ='DEFERRED';

ALTER SYSTEM SET 파라미터 = 값 SCOPE =

    1. SCOPE = SPFILE
      : 초기 파라미터 값만 바꿨다.(재시작해야 한다.)
    1. SCOPE = MEMORY
      : 지금 메모리에서 적용한다.
    1. SCOPE = BOTH (SCOPE을 안쓰면 기본값이다.)
      : SPFILE, MEMORY 둘다

▶ 1. SCOPE = SPFILE

SELECT *
FROM v$parameter
where issys_modifiable = 'FALSE';
  • 파라미터 변경작업시 SCOPE = SPFILE
    변경 사항이 서버 파라미터 파일(spfile<SID>.ora)에만 적용되며, 현재 instance에는 변경되지 않습니다.
  • 변경한 값으로 인식되려면 오라클을 재 시작 해야한다(정상종료후 startup).
  • dynamic parameter 와 static parameter 모두 변경사항을 다음 시작시에 영구적으로 적용한다.
  • static parameter는 scope=spfile 만 설정해야한다. 
SELECT *
FROM v$parameter
where name = 'processes';
----  
ALTER SYSTEM SET processes = 200
SCOPE = SPFILE;  
    
SQL> show parameter process

processes                            integer     150

▶ 2. SCOPE = MEMORY

SELECT *
FROM v$parameter
where issys_modifiable = 'IMMEDIATE'
or isses_modifiable = 'TRUE';
  • 변경이 현재 상태에만 영향을 미치며 db가 restartup되면,변경 이전값으로 돌아간다.
  • 변경 사항이 메모리에만 적용한다.
  • 현재 instance 가 변경되고 변경 사항이 즉시 적용된다.
  • 현재만 바꾸고 db 재 시작하면 변경값은 기본값으로 설정된다.
  • 서버 파라미터 파일에는 적용되지 않습니다.
  • dynamic parameter 경우 사용할 수있다.

▶ 3. SCOPE = BOTH

SELECT *
FROM v$parameter
where issys_modifiable = 'DEFERRED';
  • 파라미터의 변경값을 현재 메모리에도 적용되고 서버 파라미터 파일에도 적용된다.
  • dynamic parameter 경우 사용할 수있다. 
SELECT *
FROM v$parameter
where issys_modifiable = 'IMMEDIATE'
or isses_modifiable = 'TRUE';
  • static parameter 는 사용할 수 없다.

issys_modifiable 은 3가지 설정값이 있다.

    1. FALSE
      : 즉시 변경 불가능하다. (static parmeter)이므로 scope=spfile 만 가능하다
    1. IMMEDIATE
      : 즉시 가능하고, scope=spfie(초기), scope=memory(지금 메모리) 둘다 가능하다.
    1. DEFERRED (연기된)
      : 시스템 변경이 현재 SESSION이 아닌 다음 SESSION 부터 허용한다. 지금 세션 부터 적용이 아니라 다음 SESSION부터 적용 , 기본값은 SCOPE=BOTH

      사용법
      : ALTER SYSTEM SET 파라미터 = 값 DEFERRED;

select * from v$parameter where name = 'sort_area_size';
----
- 세션 레벨에서 하는것
alter session set sort_area_size =1048576;
- 시스템 레벨에서 하는것
alter system set sort_area_size =1048576 DEFERRED;

▶ spfile 이용해서 pfile 생성

pfile (init<SID>.or)

SQL> create pfile from spfile;
File created.
SQL> !
[oracle@oracle ~]$ cd $ORACLE_HOME/dbs
[oracle@oracle dbs]$ ls
hc_ora11g.dat  initora11g.ora  orapwora11g                spfileora11g.ora
init.ora       lkORA11G        spfileora11g_20231208.bak
[oracle@oracle dbs]$ cat initora11g.org
-- initora11g.ora -> 파일이 만들어 졌다

▶ spfile을 지우고 init 시작한 db

SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string

▶ pfile 이용해서 spfile 생성

SQL> create pfile from spfile; 

File created.

DBCA를 이용하지 않고 db 생성시에는 pfile을 만들고 db생성해야 한다.

DBCA란?
(database configuration assistant)

spfile을 만들면 자동으로 pfile이 만들어진다.

SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/product/11.2.0
                                                 .4/db_1/dbs/spfileora11g.ora

■ INSTANCE = SGA + Background Process

▶ 용어정리

  • user process
    : 사용자가 작성한 SQL문을 server process 로 전달해주고 결과를 가지고 오는 프로세스
    (ex) sqldeveloper)
  • server process
    : sql문을 수행하는 프로세스
  • connection
    : user process - server process 간의 통신경로
  • session
    : 데이터베이스 instance 에 대한 현재 유저가 로그인 상태를 나타낸다. (sqldeveloper에서 세션을 열면, sqldeveloper가 세션이 된다.)

■ SGA(System Global Area)

: 모든 오라클 프로세스가 엑세스 하는 공유 메모리 shared memory

  • 데이터 베이스의 Instance가 시작되면 시스템으로부터 자원을 할당 받고, 데이터베이스 Instance가 종료되면 할당 받은 자원을 시스템에 반환한다. 즉 Instance가 시작된다는 것은 메모리의 일부를 SGA의 영역으로 할당 받고, CPU로부터 PROCESS 자원도 할당 받았음을 의미한다.

  • SGA 영역에 있는 구성 요소들을 DB 운영중에 동적으로 설정할 수 있다.

  • SGA 메모리는 SGA_MAX_SIZE 로 설정한다.

  • sga 정보 보기
    (필수 : Shared Pool, DB buffer cache, Redo log buffer)
    SELECT *FROM v$sgainfo;

RESIZEABLE = 'NO' 이면 운영중에 변경이 불가능하다.

SELECT *
FROM v$parameter
where name = 'sga_max_size';

-> ISSYS_MODIFIABLE = 'FALSE' 이므로 static 파라미터이다. 따라서 SCOPE=SPFILE 로 해야한다.

alter system set sga_max_size = 1GB SCOPE = SPFILE

Total System Global Area 531476480 bytes
Fixed Size 1365796 bytes
Variable Size 146802908 bytes
Database Buffers 377487360 bytes
Redo Buffers 5820416 bytes

Fixed Size : 오라클이 instance 생성시 사용되는 알고리즘들이 이 메모리에서 수행

Variable Size =
Shared Pool Size + Large Pool Size +
Java Pool Size + Streams Pool Size

  • SGA 구성 요소들은 granule(증감단위)에 따라 할당하자.
    • granule : 연속적인 가상 메모리 할당
    • 예상된 총 SGA_MAX_SIZE 기준으로 granule size가 결정된다.
      • SGA_MAX_SIZE가 1GB 작거나 같으면 4mbyte , 1 GB 보다 크면 16mbyre 단위로 결정된다.

▶ 1. Database Buffer Cache, Data Buffer Cache

: 데이터 파일에서 읽은 데이터 블록의 복사본을 저장하는 메모리 영역 , 메모리 영역의 집합

  • 오라클은 물리적인 I/O 를 최소화하기 위해 최근에 사용된 블록(BLOCK)을 저장하는 메모리 영역

  • 모든 유저는 data buffer cache에 대한 엑세스를 공유

  • 기본적으로 48mbyre 또는 4mbyre * CPU 수 중 큰값

  • data buffer cache 내부 구성요소 파라미터
    • db_cache_size (필수 구성)
      : db_block_size 기본 블록 크기에 배수 단위 설정하거나 granule 에 따라 할당하자.
    • db_keep_cache_size (옵션 구성)
    • db_recycle_cache_size (옵션 구성)
    • db_nk_cache_size (n: 2,4,8,16,32)
      : 기본은 8k 이지만 만약 16k 테이블스페이스가 들어오면 , 확장 data buffer cache에 db_16k_cache_size 로 설정해준다.
  • 동적으로 크기를 조정 할 수 있다.
alter system set db_cache_size = 96m (SCOPE=BOTH);
  • LRU(Least Recently Used) 알고리즘을 통해 관리한다.
    : Oracle데이터 베이스 서버는 자주 사용하는 데이터를 메모리에 오래 저장하여 I/O 효율을 높이고, 자주 사용하지 않는 데이터는 데이터 파일에 물리적으로 저장하여 SGA영역을 효율적으로 관리한다

▶ 2. shared pool

: 사용자가 작성한 SQL문이 저장되어 관리 되는 곳

  • shared_pool_size 파라미터를 이용해서 크기를 지정한다.
  • 동적으로 크기를 조정할 수 있다.
ALTER SYSTEM SET shared_pool_size=128m;
  • library cache : SQL문, 실행계획을 가지고 있는 메모리
  • data dictionary cache : data dictionary 테이블의 정보를 가지고 있는 메모리
  • oracle shared server 환경일때 USA(User Global Area)가 생성된다.

SQL> show parameter shared_pool_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
shared_pool_size                     big integer 0

select *
from v$parameter
where name ='shared_pool_size';


select *
from v$sgastat
where pool='shared pool';

▶ 3. Large pool

: SGA의 내부의 대규모 메모리 할당을 제공하기 위해 선택적인 공간

  • large_pool_size 파라미터로 크기 조정
  • 동적인 파라미터 이다
    alter system set large_pool_size = 10m SCOPE =BOTH;
  • 다음 용도의 데이터를 메모리에 저장하는데 사용
    • oracle shared server 환경일때 USA(User Global Area)가 생성된다.
      shared server 환경일때, shared pool 메모리 공간의 부담을 줄이기 위해서 large pool 사용하자
    • RMAN 을 이용해서 백업 및 복원 작업 수행 할때
    • parallel query(병렬쿼리) 작업 수행 할때
SQL> show parameter shared_pool_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
shared_pool_size                     big integer 0
SQL> show parameter large_pool_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
large_pool_size                      big integer 0


select *
from v$parameter
where name ='large_pool_size';

▶ 4. Java pool

: 자바 명령을 구문 분석할 경우 사용하는 메모리 공간이며 자바를 설치하고 사용할 경우 지정해 주어야 한다.

  • java_pool_size 파라미터로 크기조정

  • 동적 파라미터
    alter system set java_pool_size=24m;

  • JVM 내의 모든 세션별 JAVA 코드 및 데이터를 메모리에 저장하는데 사용한다.

SQL> show parameter java_pool_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
java_pool_size                       big integer 0

select *
from v$parameter
where name ='java_pool_size';

▶ 5. Steam pool

: 버퍼링된 큐 메시지를 저장하고 Oracle Streams 캡처 프로세스 및 적용 프로세스에 대해 메모리를 제공합니다.

  • 하나의 데이터베이스에서 다른 데이터베이스로 데이터를 이동, 복제하는 stream process 에 필요한 buffer queue message 사용
  • 동적 파라미터
    alter system set streams_pool_size = 10m;
select *
from v$parameter
where name ='java_pool_size';

SQL> show parameter streams_pool_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
streams_pool_size                    big integer 0

▶ 6. Redo log buffer

: 데이터베이스 데이터 블록의 모든 변경 사항을 기록한다

  • 기본 목적은 복구(recovery) 입니다.
  • 내부의 기록된 변경사항을 redo entry(리두 항목) 라고 합니다.
  • redo entry(리두항목)에는 변경 사항을 재구성 하거나 재실행할 정보가 포함되어 있다.
  • redo entry가 생성되는 SQL문
    : DML(insert, update, delete, merge) , create, alter, drop, select * for update
  • 크기는 : log_buffer
  • static 파라미터이다.
  • log_buffer : os block size 배수 단위로 설정한다 (512 byte)
    alter system set log_buffer = 5m SCOPE=SPFILE;
select *
from v$parameter
where name ='log_buffer';

■ 구조

▶ 1. parse (필수)

  1. syntax, semantic, 권한 확인
    • shared pool : data dictionary cache
  2. 동일한 sql문. (soft parsing)
    • shared pool : library cache -> latch
  3. hard parsing
    • 메모리 할당 -> latch
    • 실행계획
  • 실행계획
      1. data 처리방법
      • rowid (redo entry에 rowid만 있으면 제일빠르다.)
        : #object#file#block#rowslot
        • user by rowid
        • index by rowid
      • full table scan
      1. join 순서 결정
      1. join 방법 결정

▶ 2 bind (선택)

: 변수에 실제값이 입력되는 단계
표준편차 생각

▶ 3. execute

  1. undo segment 할당

    • undo segment header, transaction table slot, latch

  2. undo segment 를 undo block 할당한다.

  3. 수정하려는 row가 존재하는 block이 data buffer cache에 있는지 조회

    • 있으면 끝
    • 없으면 논리적 IO를 일으켜
      user tbs 에서 논리적 IO를 일으켜
      가장 처음 들어간 undo block 을 빼고 넣는다.

  1. redo log buffer entry 작성한다.

    • rowid (rowid만 있으면
      가장 빠르게 찾을수있다)

  2. 수정 대상 row가 있는 block header transaction slot 획득해야한다.

  3. row level lock 건다.

  4. 이전값을 undo block 작성하고 새로운 값으로 변경한 후 blcok header에는 CR block으로 설정

    • 읽기일관성

예시)

update hr.emp
set sal=2000
where id=100;

를 하고 commit을 하지않으면
data buffer cache에
블락 헤더에 데이터를 변경하고
header에 CR로 설정한다.

undo block에 이전값을 저장하고
복사해놓는다.

SELECT sal FROM hr.emp
where id = 100;

을 입력하면
header에 CR 은 select 때문에
access시에 복사본이 생성하게 된다.

undo block에 이전값이 있는데도
굳이 새로운 블락에 복사해서 만드는 이유는
undo block에는 이전값만 넣고
select * 하면 새로운블락에 전부를 복사하는 것이 효율이 더좋다.

그래서 commit을 안하고 계속 access하게 되면
disk memory 에 block이 너무 많아지면
데이터 버퍼 캐시 공간이 부족해질수 있다.

commit을 하면 복사 block 은 free memory 상태가 된다.

2023/12/12

자세히
https://velog.io/@khyup0629/Oracle-Oracle-%EB%A9%94%EB%AA%A8%EB%A6%AC-%EA%B4%80%EB%A6%AC-%EA%B8%B0%EB%B2%95%EB%93%A4

■ ASMM (Automatic Shared Memory Management) (10g)

: 10g 부터 SGA 구성 요소 ( Buffer Pool , Shared Pool , Large Pool , Java Pool) 메모리를 동적으로 조정가능할 수 있게되었으며 이를 ASMM 기능이라 한다
원래 Buffer Cache는 db_cache_size , Shared Pool 은 shared_pool_size , Large Pool은 large_pool_size 파라미터를 통해 변경되며 이들 파라미터를 변경하면 Oracle Instance는 재 기동이 되어야 함. ASMM은 재 기동없이 이들 메모리 값을 동적으로 변경가능하게 한다.  이를 위해 SGA_TARGET , SGA_MAX_TARGET 이라는 신 파라미터가 도입됨. 

  • SGA_TARGET 값을 설정하면 SGA 영역을 자동 관리한다.
  • SGA_TARGET 값은 SGA_MAX_SIZE 값 보다는 작거나 같게 설정하면 된다.
  • SGA_TARGET 값은 DB 운영중에 조정을 할 수 있다.
  • MMAN이 Memory Advisor 사용하여 작동한다.
  • spfile을 사용 권한
SELECT *
FROM v$parameter
WHERE name in ('sga_max_size','sga_target');

SELECT *
FROM v$sga_dynamic_components;

SGA_TARGET 설정되어 있더라도 수동으로 관리해야하는 SGA 영역

  • DB_KEEP_CACHE_SIZE
  • DB_RECYCLE_CACHE_SIZE
  • DB_nk_CACHE_SIZE (n=2,4,8,16,32)
  • LOG_BUFFER

SGA_TARGET 설정되어 있더라도 수동으로 동적 SGA 파라미터의 크기 조정

  • 새로운 값이 현재 크기 보다 큰 경우 즉시 구성 요소 크기로 조정된다.
    ex) 만약
    자동관리 120M < 수동관리 150M
    자동관리 150M 로 변경된다.
  • 새로운 값이 현재 크기 보다 작은 경우 최소 크기가 변경된다.
    ex) 만약
    자동관리 120M > 수동관리 50M
    자동관리 최소크기 50M

[oracle@oracle ~]$ ps -ef | grep ora_mman

oracle     330     1  0 Dec07 ?        00:00:00 ora_mman_ora11g
oracle   17404 17345  0 00:00 pts/3    00:00:00 grep --color=auto ora_mman

■ PGA (Program Global Area)

: 서버프로세스 또는 백그라운드 프로세스의 데이터 및 제어 정보를 포함하는 메모리 영역입니다.

  • PGA는 서버프로세스 또는 백그라운드 프로세스를 시작할때 오라클 DB에서 생성되는 독립적으로 사용하는 비 공유 메모리 (non shared memory, 자기자신만 쓰는 메모리)

PGA 영역에 있는 SQL 작업영역 관리는 8i 수동관리

  • sort_area_size
  • hash_area_size
  • bitmap_merge_area_size
  • create_bitmap_area_size
SELECT *
FROM v$parameter
Where name in ('sort_area_size',
                'hash_area_size',
                'bitmap_merge_area_size',
                'create_bitmap_area_size');

▶ 1. 자동 PGA 메모리관리(9i)

  • pga_aggregate_target 파라미터 기반으로 SQL 작업 영역에 할당되는 PGA 메모리 양이 동적으로 조정된다.

SQL 작업 영역에서 관리하는것

  • sort_area_size

  • hash_area_size

  • bitmap_merge_area_size

  • create_bitmap_area_size

  • workarea_size_policy = auto

SQL> show parameter workarea_size_policy

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
workarea_size_policy                 string      AUTO

SQL> show parameter pga_aggregate_target

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target                 big integer 169M
  • PGA 수

▶ 2. 수동 PGA 메모리관리(9i)

  • PGA 수동관리
ALTER SYSTEM SET WORKAREA_SIZE_POLICY = MANUAL;
ALTER SYSTEM SET pga_aggregate_target=0; (8i 예전버전)
  • PGA 세션 레벨에서 수동관리
ALTER SYSTEM SET WORKAREA_SIZE_POLICY = MANUAL;
ALTER SYSTEM SET SORT_AREA_SIZE = 1ML;

■ AMM (Auto Memory Management) (11g)

: SGA+PGA를 자동으로 관리하는 방식

  • AMM 기능은 PGA 및 SGA 크기 조정을 작업 로드에 따라 자동으로 수행한다.
  • AMM 기능을 사용하려면 MEMORY_TARGET 값을 설정해야한다.
  • 오라클이 사용할 수 있는 최대 메모리 설정은 MEMORY_MAX_TARGET

AMM 관리 기법이 등장하면서 아래 2가지 Parameter가 생겼습니다.

  • MEMORY_TARGET
    : AMM 기능 사용시 사용할 수 있는 Memory의 총 량을 정할 수 있습니다.
    이 Parameter가 0이면 AMM을 사용하지 않는다는 뜻이며, 최대 설정 값은 MEMORY_MAX_TARGET의 값 까지 지정할 수 있습니다.
  • MEMORY_MAX_TARGET
    : MEMORY_TARGET 값이 최대로 증가될 값을 지정합니다.

AMM 기능을 사용하게 된다면 SGA_TARGET, PGA_AGGREGATE_TARGET의 값을 0으로 지정하는 것이 좋습니다.
ASMM과 마찬가지로 0이 아닌 경우 그 값을 최소값으로 인식하기 때문입니다

SQL> show parameter memory_target

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
memory_target                        big integer 0

  • memory_target 값이 설정되어 있더라도 수동으로 sga_target, pga_aggregate_target 설정할 수 있다.

  • MMAN이 Memory Advisor 사용하여 작동한다.

  • 현재 메모리 구성요소

SELECT *
FROM v$memory_dynamic_components;
  • 지금까지 발생한 resize 작업에 대한 정보
SELECT *
FROM v$memory_resize_ops;
  • 현재 메모리 크기 조정 작업 진행 상황
SELECT *
FROM v$memory_current_resize_ops;
  • memory_target 값을 설정하면 기본적으로 SGA(60%), PGA(40%)

■ Background Process

자세히
https://yunhyeonglee.tistory.com/19

▶ 1. DBWn (Database Writer)

  • data buffer cache 에 내용을 데이터 파일에 기록한다.

  • data buffer cache 에 있는 수정된 (dirty) buffer 를 데이터 파일에 기록한다.

dbwr 작동되 시점

  • free buffer를 찾지 못했을 경우 (free buffer wait event) : data buffer cache에 free buffer가 없을 경우 , LRU 알고리즘을 이용하여 DISK로 내려보낸다.
  • checkpoint event 발생할때

SQL> show parameter db_writer_process

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_writer_processes                  integer     1

SQL> show parameter memory_target

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
memory_target                        big integer 0
SQL> ! ps -ef | grep ora_dbw
oracle     332     1  0 Dec07 ?        00:00:03 ora_dbw0_ora11g

SELECT *
FROM v$parameter
where name = 'db_writer_processes';
  • 변경
ALTER SYSTEM SET db_writer_processes = 2 SCOPE = SPFILE;

▶ 2. LGWR (Log Writer)

  • Redo log buffer 에 있는 redo entry 를 redo log file에 기록한다.

LGWR 작동 시점

  • commit 수행할 경우
  • redo log buffer 1/3 찼을 경우
  • 1MB 이상의 redo entry 가 들어오면
  • 3초마다
  • DBWr가 기록하기 전에

SQL> ! ps -ef | grep lgwr
oracle 9539 1 0 03:23 ? 00:00:00 ora_lgwr_ora11g

  1. undo segment 할당
  2. undo block
  3. 수정 대상 row가 존재하는 block이 data buffer cache 에 있는지 조회
  4. redo log buffer에 redo entry 작성
  5. 기존 값을 새로운 값으로 변경한후
    2행 Lock
    CR

COMMIT

    1. SCN 할당
      SCN(System Commit Number) -> backup recovery

      controlfile scn갱신
      blcok header scn 갱신
      data file header block scm 갱신

    1. undo segment header commit 완료
    1. 수정된 행이 있는 block header
    1. LGWR 작동 -

      Redo log buffer에 있는 Redo entry를
      redo log file에 있는
      current 그룹에 write한다.

▶ 3. CKPT (Checkpoint process)

  • checkpoint event 발생시점에 DBWR 에 알려준다.
  • checkpoint 정보를 데이터 파일 헤더를 갱신
  • checkpoint 정보를 컨트롤 파일 갱신

용어 정리

  • block = buffer 같은건데
    disk에서는 block이라고 사용하고
    memory에서는 buffer 이라고 사용한다.

checkpoint

: data buffer캐시의 dirty buffer를 디스크로 내리는 작업
: databuffer cache에 있는 dirty buffer 을 정기적으로 디스크에 기록하므로 시스템이나 데이터베이스에 failure가 발생한 경우 데이터가 손실되지 않도록 합니다.

  • instance recovery에 필요한 시간을 줄인다. 즉 마지막 체크 포이트 다음에 나오는 리두 로그 파일에 redo entry에 대해서 recovery 를 수행하면 된다.
  • 체크포인트 정보에는 체크포인트 시간, SCN,recovery 를 시작할 리두로그 파일의 위치, 로그에 정보를 가지고 있다.

체크포인트 발생하는 경우

  • shutdown normal | transactional | immediate
  • ALTER SYSTEM CHECKPOINT;
  • ALTER TABLESPACE users OFFLINE NORMAL;
  • ALTER TABLESPACE users READ ONLY;
  • ALTER TABLESPACE users BEGIN BACKUP;
  • ALTER TABLESPACE users END BACKUP;
  • DROP TABLE hr.emp;
  • TRUNCATE TABLE hr.emp;
  • paralle query (select /+ full(e) paralle(e,2) / from hr.emp e;)
  • Log switch 발생시 (ALTER SYSTEM SWITCH LOGFILE;)
  • fast_start_mttr_target 을 설정한 경우
    : 강제로 checkpoint 주기를 설정하는것이다.

SQL> show parameter fast_start_mttr_target

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
fast_start_mttr_target               integer     0
  • log_checkpoints_to_alert = TRUE 설정을 하면 체크 포이튼 정보를 alert_SID.log 에 기록한다.
    SQL> show parameter log_checkpoints_to_alert
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_checkpoints_to_alert             boolean     FALSE
--
-- 체크포인트변경시 alert log 파일에 보이기
ALTER SYSTEM SET log_checkpoints_to_alert = TRUE SCOPE=SPFILE;

▶ 4. SMON (System Monitor)

  • Instance recovery (Instance fail 시 인스턴스 복구하는 역할)
    instance fail : 디스크가 끊킬때, 강제종료

  • coalesces free space (디스크 조각 모음)

  • 임시 블록 세그먼트들을 재사용할 수 있도록 하는 역할

SQL> ! ps -ef | grep smon

oracle   22302     1  0 05:06 ?        00:00:00 ora_smon_ora11g

▶ 5. PMON (Process Monitor)

  • user process 가 실패할 경우 프로세스 recovery 수행
  • 데이터버퍼 캐시 정리
  • 사용하고 있는 리소스 해제
  • 트랜잭션에 대해서 자동 rollback
  • lock 해제
  • listner 에게 db 정보를 등록시키는 작업을 수행

SQL> ! ps -ef | grep pmon

oracle   22275     1  0 05:06 ?        00:00:00 ora_pmon_ora11g

▶ 6. ARCH(Archiver)

:
Online redo log 파일을 archived redo log 파일로 보관

■ Control file

  • db 구조를 포함하는 작은 binary file

  • database 이름, 식별자 , 생성시간

  • data file, redo log file 이름, 위치정보

  • 현재 online redo log file의 sequence 번호

  • checkpoint 정보, scn 정보

  • backup

  • archivelog mode, noarchivelog mode

  • control file은 다중화 하자
    : Control File에 문제가 발생하면 심각한 장애가 발생할 수 있기 때문에 평소에 파일이 삭제되지 않도록 주의해야 합니다. 혹여나 삭제되더라도 복구할 수 있도록

SELECT * FROM v$database;

SELECT dbid, name, checkpoint_change#, current_scn FROM v$database;

SELECT name, checkpoint_change# FROM v$datafile;

SELECT * FROM v$log;

SQL> show parameter control_files

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      /u01/app/oracle/oradata/ora11g
                                                 /control01.ctl, /u01/app/oracl
                                                 e/fast_recovery_area/ora11g/co
                                                 ntrol02.ctl

[백그라운드 프로세스 간단정리]

  • DBWn (Database Writer):
    용도: 디스크에 변경된 데이터 블록을 쓰기 위한 프로세스. 버퍼 캐시의 변경된 블록을 디스크에 기록.
    예시: Dirty 버퍼를 디스크로 쓰는 작업.
  • LGWR (Log Writer):
    용도: 로그 버퍼의 내용을 리두 로그 파일에 기록하여 트랜잭션의 변경 사항을 지속적으로 저장.
    예시: 트랜잭션의 로깅, 리커버리 프로세스에서 사용.
  • CKPT (Checkpoint):
    용도: 데이터베이스의 일관성을 유지하기 위해 주기적으로 체크포인트를 수행. 변경된 데이터를 물리적인 데이터 파일에 기록.
    예시: 체크포인트 발생 시 DBWn에게 변경된 데이터를 디스크에 쓰도록 지시.
  • SMON (System Monitor):
    용도: 비정상 종료 시 데이터베이스의 상태를 복구하고, 트랜잭션 롤백, 블록 정리 등을 수행.
    예시: 오라클 인스턴스 복구, 트랜잭션 롤백 등.
  • PMON (Process Monitor):
    용도: 클라이언트와의 연결을 관리하고, 비정상 종료한 클라이언트 프로세스와 관련된 리소스를 해제.
    예시: 잠긴 리소스 해제, 클라이언트 연결 관리.
  • ARCn (Archiver):
    용도: 아카이브 로그 파일을 생성하여 데이터베이스의 로그 파일을 백업. 옵션에 따라 데이터 보관 및 복구에 활용.
    예시: 로그 아카이브, 백업 및 리커버리 지원.
  • DBRM (Database Resource Manager):
    용도: 리소스 할당 및 사용을 관리하여 성능 최적화. 우선순위에 따라 리소스 할당.
    예시: 리소스 관리, 성능 조절.
  • MMAN (Memory Manager):
    용도: 메모리 할당 및 해제를 관리하여 최적의 성능과 메모리 사용량을 유지.
    예시: 공유 풀 및 버퍼 캐시의 메모리 관리.
  • DIAx (Dispatcher):
    용도: 멀티쓰레드 처리 및 고객 요청을 적절한 백그라운드 프로세스에 할당.
    예시: 고객 요청 처리, 멀티쓰레드 처리.

▶ 1. Control file 다중화

  1. 새로운 디렌토리 만들고, 컨트롤파일 system set 3중화로 변경
[oracle@oracle ~]$ mkdir backup
[oracle@oracle backup]$ pwd
/home/oracle/backup
[sql developer]
ALTER SYSTEM SET control_files = '/u01/app/oracle/oradata/ora11g/control01.ctl',
				 '/u01/app/oracle/fast_recovery_area/ora11g/control02.ctl',
				 '/home/oracle/backup/control03.ctl' SCOPE=SPFILE;
  1. 데이터베이스를 정상적인 종료
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
  1. 기존 control file을 다른 위치 복사
cp -v /u01/app/oracle/oradata/ora11g/control01.ctl /home/oracle/backup/control03.ctl
  1. 오라클로 접속한후 db 재시작
SQL> startup
ORACLE instance started.
Total System Global Area  531476480 bytes
Fixed Size                  1365796 bytes
Variable Size             222300380 bytes
Database Buffers          301989888 bytes
Redo Buffers                5820416 bytes
Database mounted.
Database opened.
  • 확인하기 (control file 3중화)
SQL> show parameter control_files
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      /u01/app/oracle/oradata/ora11g
                                                 /control01.ctl, /u01/app/oracl
                                                 e/fast_recovery_area/ora11g/co
                                                 ntrol02.ctl, /home/oracle/back
                                                 up/control03.ctl
  • 2중화로 복구
  1. 2중화로 변경
ALTER SYSTEM SET control_files = '/u01/app/oracle/oradata/ora11g/control01.ctl',
				 '/u01/app/oracle/fast_recovery_area/ora11g/control02.ctl' SCOPE=SPFILE;
  1. 데이터베이스를 정상적인 종료
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
  1. 오라클로 접속한후 db 재시작
SQL> startup
SQL> show parameter control_files

2023/12/13

자세히
https://m.blog.naver.com/sungs6031/40090739267

■ Redo log file

  • 복구 방식 제공 (only recovery 목적)
  • 순환형식이기 때문에 최소 2개 이상의 그룹이 필요
  • 데이터의 모든 변경 사항 기록
  • 그룹으로 구성될 수 있음

select * from v$log;

select * from v$logfile;

  • Redo log file 주기 보기
select sequence#, first_change#, next_change#,
    to_char(first_time,'yyyy/mm/dd hh24:mi:ss') from v$log_history;
  • 로그 스위치
    로그 스위치는 데이터베이스의 지속성을 확보하고, 데이터 손실을 최소화하기 위해 사용됩니다. redo 로그에 저장된 트랜잭션 변경 내용은 데이터베이스를 장애 상황에서 복구하는 데 사용되며, 로그 스위치는 이러한 로그 정보를 일관된 방식으로 유지하고 관리합니다.
  • 발생이유
  1. 로그 파일 크기 제한
  2. 로그 파일 그룹이 디스크에 가득 찼을 때
  3. 로그 파일 교체 강제 수행

▶ 1. Redo log file group,member 추가

1. Redo log file group 추가

  • ALTER DATABASE ADD LOGFILE GROUP 4 ('멤버','멤버')
ALTER DATABASE ADD LOGFILE GROUP 4
('/u01/app/oracle/oradata/ora11g/redo04.log'
,'/u01/app/oracle/fast_recovery_area/ora11g/redo04.log') size 100m;
ALTER DATABASE ADD LOGFILE GROUP 5
('/u01/app/oracle/oradata/ora11g/redo05.log'
,'/u01/app/oracle/fast_recovery_area/ora11g/redo05.log') size 100m;
==
select * from v$log;

2. Redo log file member 추가

  • ALTER DATABASE ADD LOGFILE MEMBER '' TO GROUP 1;
ALTER DATABASE ADD LOGFILE MEMBER
'/u01/app/oracle/fast_recovery_area/ora11g/redo01.log' TO GROUP 1,
'/u01/app/oracle/fast_recovery_area/ora11g/redo02.log' TO GROUP 2,
'/u01/app/oracle/fast_recovery_area/ora11g/redo03.log' TO GROUP 3;

select * from v$logfile;

  • 물리적으로 다른 위치에다 멤버를 추가해준다.
    같은 곳에 member가 있으면 동시에 제거 될수도 있으면 만든 이유가 없으므로
  • INVALID 상태는 한번도 사용하지 않았다는것이다.
  • 수동으로 LOG SWITCH 발생
ALTER SYSTEM SWITCH LOGFILE;
  • 수동으로 CHECK POINT 발생
ALTER SYSTEM CHECKPOINT;

▶ 2. Redo log file group,member 삭제

  • redo log group 상태가 INACTIVE 상태에서만 삭제가능 (ACTIVE, CURRENT 삭제불가)

1. Redo log file group 삭제

ALTER DATABASE DROP LOGFILE GROUP 그룹number;
ALTER DATABASE DROP LOGFILE GROUP 2;

[oracle@oracle ora11g]$ ls
control02.ctl redo01.log redo02.log redo03.log redo04.log redo05.log

  • OS에서만 삭제한것이다.
  • 따라서 그룹을 삭제한후 물리적인 디렉토리에 가서도 꼭 그룹에 해당하는 파일삭제
[oracle@oracle ora11g]$ pwd
/u01/app/oracle/fast_recovery_area/ora11g
[oracle@oracle ora11g]$ ls
control02.ctl  redo01.log  redo02.log  redo03.log  redo04.log  redo05.log
[oracle@oracle ora11g]$ rm -i redo02.log
rm: remove regular file ‘redo02.log’? y
[oracle@oracle ora11g]$ cd /u01/app/oracle/oradata/ora11g/
[oracle@oracle ora11g]$ ls
control01.ctl  redo02.log  redo05.log    temp01.dbf
example01.dbf  redo03.log  sysaux01.dbf  undotbs01.dbf
redo01.log     redo04.log  system01.dbf  users01.dbf
[oracle@oracle ora11g]$ rm -i redo02.log
rm: remove regular file ‘redo02.log’? y
  • 삭제후 다시 그룹 추가
ALTER DATABASE ADD LOGFILE GROUP 2
('/u01/app/oracle/oradata/ora11g/redo02.log'
,'/u01/app/oracle/fast_recovery_area/ora11g/redo02.log') size 100m;

2. Redo log file member 삭제

ALTER DATABASE DROP LOGFILE MEMBER '멤버';

ALTER DATABASE DROP LOGFILE MEMBER '/home/oracle/backup/redo06.log';
  • 물리적인 디렉토리에서도 삭제해줘야한다.
[oracle@oracle backup]$ pwd
/home/oracle/backup
[oracle@oracle backup]$ ls
control03.ctl  redo06.log
[oracle@oracle backup]$ rm -i redo06.log
rm: remove regular file ‘redo06.log’? y
  • 삭제후 다시 멤버 추가
    ALTER DATABASE ADD LOGFILE MEMBER '/home/oracle/backup/redo06.log' TO GROUP 6;
  • 수동으로 LOG SWITCH 발생
ALTER SYSTEM SWITCH LOGFILE;

■ Tablespace

  • 오라클 데이터베이스는 데이터 table space 저장
  • tablespace 하나 이상의 data file로 구성한다.
  • tablespace는 하나 이상의 segment로 구성한다.
 DB(논리적)		  OS(물리적)	
  ↑
tablesapce	←	datafile
  ↑
segment
  ↑					↑
extent
  ↑
block		←	os block

■ Data file

  • data file은 oracle server 에 실행중인 운영체제를 따르는 물리적인 구조이다.
  • tablespace 는 data file 이라고 하는 하나 이상의 파일로 구성되어 있다.

■ segment

  • segment는 tablespace 안의 특정 논리적 저장 영역 구조에 할당된 영역이다.
  • 테이블, 인덱스, 언두, LOB 등 오라클이 제공하는 논리적 공간을 의미한다.
  • segment는 tablespace에 속한 여러 data file로 확장 할 수 있다.
  • 생성시 저장공간이 필요한 object를 segment라고 한다.

■ extent

  • 하나 이상의 extent 로 segment 를 구성한다.
  • extent는 연속된 oracle block 집합

■ block

  • 오라클 데이터베이스의 data(row) 는 block에 저장된다.
  • 오라클의 최소 I/O 단위
  • 오라클이 사용할 수 있는 block 크기는 2k,4k,8k,16k,32k
  • db_block_size 파라미터로 기본 크기를 결정한다.
  • 오라클 블록은 하나 이상의 OS 블록을 가지고 생성한다.

■ 테이블 스페이스 생성

1. ▶ 로컬로 관리되는 테이블 스페이스

: 사용 가능한 extent 에 대해서 테이블 스페이스(자기 스스로) 에서 관리 하는 방식

  • 비트맵은 사용 가능한 확장영역(extent)을 기록하는데 사용한다.
CREATE TABLESPACE userdata
DATAFILE '/u01/app/oracle/oradata/ora11g/userdata01.dbf' size 100m
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1m;

2. ▶ 딕셔너리 관리 테이블스페이스 (8i, 지금쓰지않는다)

: 사용 가능한 extent에 대해서 딕셔너리에서 관리한다.

  • fet$ : free extent 정보
  • uet$ : use(사용한) extent 정보
  • extent 할당하거나 할당이 해제 될때 딕셔너리 테이블에 대해서 갱신이 발생한다.
  • enq : ST-contention : wait event
CREATE TABLESPACE dict_tab
DATAFILE '/u01/app/oracle/oradata/ora11g/dict_tab01.dbf' size 100m
EXTENT MANAGEMENT DICTIONARY DEFAULT STORAGE(inital 1m next 1m pctincrease 0);
  • 로컬방식, 딕셔너리 방식
    로컬 관리 방식은 비트맵과 페이지 할당 측면에서 효율적이며, 특히 대용량 테이블스페이스의 성능을 향상시킬 수 있습니다. 반면에 딕셔너리 관리 방식은 일부 상황에서 성능이 저하될 수 있지만,
  • 로컬로 관리되는 테이블 스페이스 생성
CREATE TABLESPACE flm_tab
DATAFILE '/u01/app/oracle/oradata/ora11g/flm_tab01.dbf' size 5m
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1m
SEGMENT SPACE MANAGEMENT MANUAL;

CREATE TABLE hr.emp
TABLESPACE flm_tab
AS SELECT * FROM hr.employees;

SELECT * FROM dba_tables WHERE owner = 'HR' AND table_name = 'EMP';
SELECT * FROM dba_segments WHERE owner = 'HR' AND segment_name = 'EMP';
SELECT * FROM dba_extents WHERE owner = 'HR' AND segment_name = 'EMP';

■ segment의 MANUAL 방식

1. ▶ pct_free

BLOCK에는 header 공간과, data저장 공간으로 나눠지는데
이때 data저장공간에는 pctfree 를 만들어줘야한다.
ex) pctfree:10 -> 10% 공간을 남겨준다.

  • pctfree 가 만들어진 목적
    1. 기존 row들의 증가분(update) 때문에 남겨놓은 공간
    2. transaction slot 생성할수 있는 공간 확보 (23byte)

2. ▶ row_chain

  • row chain
    : 1개의 row가 여러 block에 걸쳐 입력된다.
  • 남아있는 데이터 저장공간보다 table의 크기가 큰경우 1개의 row가 block에 걸쳐 입력 되게된다.
    발생 이유
    1. block 크기를 잘못 지정할때
    2. table 디자인을 잘못 지정할때

3. ▶ pct_used

  • pct_used
    : insert 시에 기존 block 을 재사용 할 수 있는지를 판단하는 기준
  • data 저장 공간에 데이터가 줄어들었을때
    data free 공간이 생기는데
    pct used 보다 수위가 넘으면 재사용은 불가능하다.
    안넘으면 재사용 가능

4. ▶ free lists

: FREELISTS는 한 데이터 블록 내에서 여러 사용자 세션에 의해 동시에 업데이트되는 경우의 성능을 향상시키기 위한 메커니즘 중 하나입니다.

extent 관리를 한다.

  • 각 Free List는 동일한 데이터 블록 내에서 다수의 행을 관리하고 행을 삽입할 때 사용됩니다.

  • Free Lists는 행이 어떤 블록에 할당될지 결정하는 데 도움을 줍니다. (INSERT 작업시)

  • 테이블스페이스를 MANUAL 로 적용하면
    동시 INSERT 성능이 저하 됩니다.

buffer busy wait
: free lists 가 찾을때 , 다른 insert가 들어오면 발생한다.
가 발생하면
free lists 를 늘려준다.

5. ▶ PCTFREE, PCTUSED, FREELISTS 설정

DROP TABLE hr.emp PURGE;

CREATE TABLE hr.emp
PCTFREE 20
PCTUSED 30
STORAGE (FREELISTS 2)
TABLESPACE flm_tab
AS SELECT * FROM hr.employees;


SELECT * FROM dba_tables WHERE owner = 'HR'
AND table_name in ('EMP','EMPLOYEES');


ALTER TABLE hr.emp
    PCTFREE 30
    PCTUSED 60
    STORAGE (FREELISTS 10);
    
SELECT * FROM dba_tables WHERE owner = 'HR'
AND table_name in ('EMP','EMPLOYEES');

SELECT * FROM dba_segments WHERE owner = 'HR' AND segment_name = 'EMP';
SELECT * FROM dba_extents WHERE owner = 'HR' AND segment_name = 'EMP';

select * from hr.emp where employee_id = 100;

2023/12/14

저번 내용 요약

 DB(논리적)		 OS(물리적)	
  ↑
tablesapce	←	datafile
  ↑
segment
  ↑					↑
extent
  ↑
block		←	os block

row의 크기가 block 크기보다 크면 row chain이 발생한다.
따라서 block의 크기를 늘려주거나
테이블 디자인을 oracle의 block의 크기를 보고 다시 설정해야한다.

■ 1. extent 관리가 요건이다

  • 딕셔너리 관리
    uet$(user extent) - 갱신
    fet$(free extent) - 조회
    : 관리할때 항상 갱신하고 조회하는 과정이 성능을 잡아먹는다. 따라서 로컬관리 방식으로 바꼇다.

  • 로컬관리
    로컬 관리 방식은 비트맵과 페이지 할당 측면에서 효율적이며

■ 2. segment 관리가 요건이다
: segment의 block 관리가 요건이다.

row migration
: block(블락: header + data저장공간) 의 data저장공간이 꽉찼을때
업데이트문이 증가분이면 다른 블록으로 넘어간다

  • 문제
    : select 하였을때 업데이터 전 블락을 먼저 조회하고
    migration된 블락을 또 조회하게 된다.

따라서 data 저장공간에 pctfree를 만들어줘야한다.
일반적으로 10프로

▶ pctfree (기본)

  • 기존행의 update(증가분) 때문에 남겨놓은 공간
    왜? row migration을 덜 발생하기 위해서
  • transaction slot 정보를 만들기 위해서

▶ pctused (옵션), segment의 block 관리

  • 기존 block을 재사용(insert) 할수 있는지를 판단

  • pctfree가 있어도 기존행의 증가분(update) 이 넘어서 row migration 방지를 위해 사용한다.

  • 데이터 블록의 상태
    : 행을 추가하거나 업데이트할 때, PCTUSED 값과 블록 내의 여유 공간에 따라 데이터 블록의 상태가 변합니다.
    PCTUSED 값보다 높은 비율로 블록이 사용되면 블록은 "full" 또는 "used" 상태로 표시됩니다.

  • 재사용
    : 블록이 PCTUSED 값을 초과하여 사용된 경우, 해당 블록은 재사용될 수 있습니다.
    PCTUSED 값을 초과하지 않는 경우 블록은 계속해서 사용 중으로 표시되며, 새로운 행을 수용하기 어려워집니다.

▶ freelists

  • 어떤블록이 pctused 만큼 떨어져잇는지 정보를 가지고있다. (기본값 1)

  • insert->emp

  1. undo segment (transactio slot table)
    , undo block
  2. insert 저장해야할 block - emp segment

  • undo blcok은 rollback 을 위해 있는데 insert의 이전값은 없으므로 undo block에는 어떤 주소에 insert할건지 블락 주소를 가지고 있다.

  • freelist를 이용해서 pctused를 넘지않는 block주소를 찾는다음 넣어주고 꽉차면 다음 pctused를 넘지않는 block 을 찾아준다.

  • 동시 insert작업이 발생시 freelists를 못잡아서
    buffer busy wait가 발생한다.

■ segment의 AUTO 방식

1. ▶ AUTO 방식

총 6가지 block의 free상태를 bitmap 방식으로 관리

  • full
  • unformated
  • 0% ~ 25% free
  • 25% ~ 50% free
  • 50% ~ 75% free
  • 75% ~ 100% free
EXTENT #10

#100		#101	#102	#103	#104
-----------------------------------------
ExtenMap |-------------------------------pctfree 
		 |
bitmap	 |


- bitmap
--------------------------
|#100	|#101	|#102	|
--------------------------
|#103	|#104	|   	|
--------------------------

블락의 free상태를 6가지 방식으로 관리

2. ▶ FLM(FreeList Management)

  • PCTUSED, FREELISTS, FREELISTS GROUPS 사용하는 방식

3. ▶ ASSM(Automatic Segment Space Mangement), 자동 세스먼트 공간 관리

  • 각 블록의 상태를 비트맵 값으로 관리하는 방식

  • 공간관리가 자동화 된다.

  • ASSM 은 공간관리를 오라클이 담당하기 때문에 튜닝을 위해 속성을 지정하거나 히든 파라미터값을 변경할 필요가 없다.

데이터 블록의 여유 공간을 총 6단계로 관리

  • full
  • unformated
  • 0% ~ 25% free
  • 25% ~ 50% free
  • 50% ~ 75% free
  • 75% ~ 100% free

CREATE TABLESPACE assm_tab
DATAFILE '/u01/app/oracle/oradata/ora11g/flm_tab01.dbf' size 5m
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1m
SEGMENT SPACE MANAGEMENT AUTO;

■ 테이블스페이스 크기 조정

▶ 1. 테이블 스페이스 생성시에 데이터 파일 자동으로 확장 기능 설정

SQLDEVLOPER 접속시 중간 에러
업체코드 17002 - 리스너를 연결안했다.
[oracle@oracle ~]$ lsnrctl start

CREATE TABLESPACE insa
DATAFILE '/u01/app/oracle/oradata/ora11g/insa01.dbf' SIZE 5M
AUTOEXTEND ON NEXT 2M MAXSIZE 200M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
SEGMENT SPACE MANAGEMENT AUTO;

CREATE TABLESPACE customer
DATAFILE '/u01/app/oracle/oradata/ora11g/customer01.dbf' SIZE 5M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
SEGMENT SPACE MANAGEMENT AUTO;



select * from dba_tablespaces;
select * from dba_data_files;
select * from dba_free_space;

▶ 2. 테이블 스페이스 생성후에 AUTOEXTEND 를 지정하여 자동으로 확장 기능 설정

ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/ora11g/customer01.dbf'
AUTOEXTEND ON NEXT 2M;

select * from dba_data_files;
- AUTROEXTENSIBLE 변경

▶ 3. 데이터 파일 수동으로 RESIZE 조정

ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/ora11g/customer01.dbf'
RESIZE 10M;

select * from dba_data_files;
- BYRES 컬럼 변경

▶ 4. 데이블스페이스 데이터 파일 추가

ALTER TABLESPACE customer ADD DATAFILE 
'/u01/app/oracle/oradata/ora11g/customer02.dbf'
SIZE 10M;

select * from dba_data_files;
  • 자동 확장
ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/ora11g/customer02.dbf'
AUTOEXTEND ON NEXT 2M;

▶ 5. 데이블스페이스 삭제

DROP TABLESPACE customer INCLUDING CONTENTS -- 테이블스페이스에 세그먼트까지 삭제
AND DATAFILES; -- datafile 삭제 (물리적 구조까지 삭제)

DROP TABLESPACE insa INCLUDING CONTENTS
AND DATAFILES;
DROP TABLESPACE flm_tab INCLUDING CONTENTS
AND DATAFILES;
DROP TABLESPACE userdata INCLUDING CONTENTS
AND DATAFILES;
CREATE TABLESPACE insa_tab
DATAFILE '/u01/app/oracle/oradata/ora11g/insa_tab01.dbf' SIZE 5M
AUTOEXTEND ON NEXT 2M MAXSIZE 50M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
SEGMENT SPACE MANAGEMENT AUTO;

CREATE TABLE hr.emp
TABLESPACE insa_tab --  TABLESPACE 를 지정하지 않으면 defalut 값은 USERS임
AS
SELECT * FROM hr.employees;

■ 테이블스페이스

▶ 1. 테이블스페이스 READ ONLY

  • 데이를 읽을 수만 있다.(SELECT문 만 수행가능)

  • DML 불허

  • 부분 CHECKPOINT 발생
    : 부분인것만 디스크에 내림
    싱크가 안맞아서
    백업 recovery 효율이 좋지않다.

  • 테이블스페이스에 객체 삭제 가능

  • READ ONLY로 변경
    ALTER TABLESPACE insa_tab READ ONLY;
  • READ WRITE로 변경
    ALTER TABLESPACE insa_tab READ WRITE;
  • FULL CHECKPOINT
    ALTER SYSTEM CHECKPOINT;

불완전한 복구를 할려면 READ ONLY -> READ WRITE로
바꾸고 DB를 shutdown(FULL CHECKPOINT) 하고 해줘야한다.

▶ 2. 테이블스페이스 OFFLINE

  • 부분 CHECKPOINT 발생

  • 테이블스페이스에 속한 객체들을 사용할 수 없다.

OFFLINE 으로 설정할 수 없는 테이블스페이스는

  • SYSTEM
  • 활성화 되어 있는 UNDO
  • 기본 TEMP TABLESPACE
  • 테이블스페이스 오프라인으로 변경
    ALTER TABLESPACE insa_tab OFFLINE;
  • 테이블스페이스 온라인으로 변경
    ALTER TABLESPACE insa_tab ONLINE;

OFFLINE 옵션

  • NORMAL : 부분 CHECKPOINT 발생
  • TEMPORARY : 가능한 데이터파일에 속한 DIRTY BUFFER 만 디스크로 쓰는 작업 수행
  • IMMEDIATE : CHECKPOINT 발생하지 않고 OFFLINE으로 수행된다. 후에 복구 작업을 수행해야 한다. ARCHIVELOG 모드에서 수행하는 옵션

ONLINE BACKUP(ARCHIVELOG mode)

ALTER TABLESPACE insa_tab BEGIN BACKUP;
ALTER TABLESPACE insa_tab END BACKUP;

▶ 3. BIGFILE 테이블스페이스

  • bigfile 테이블스페이스에 단일 파일이 포함한다.
  • 최대 파일의 크기 8TB ~ 128TB
CREATE BIGFILE TABLESPACE big_tab
DATAFILE '/u01/app/oracle/oradata/ora11g/big_tab01.dbf' SIZE 1G
AUTOEXTEND ON NEXT 2M MAXSIZE 2G
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
SEGMENT SPACE MANAGEMENT AUTO;

■ 데이터파일 이관(migration)작업

DROP TABLESPACE insa_tab INCLUDING CONTENTS
AND DATAFILES; 

CREATE TABLESPACE insa_tab
DATAFILE '/u01/app/oracle/oradata/insa_tab01.dbf' SIZE 5M
AUTOEXTEND ON NEXT 2M MAXSIZE 50M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
SEGMENT SPACE MANAGEMENT AUTO;


CREATE TABLE hr.emp
TABLESPACE insa_tab
AS
SELECT * FROM hr.employees;

SELECT * FROM hr.emp;

■ 시나리오 (무조건 순서대로)

▶ 1. 테이블스페이스 오프라인상태로 변경

ALTER TABLESPACE insa_tab OFFLINE;

▶ 2. 물리적으로 데이터 파일을 이동

mv -v /u01/app/oracle/oradata/insa_tab01.dbf /u01/app/oracle/oradata/ora11g/insa_tab01.dbf

▶ 3. 기존 파일을 새로운 파일 위치로 수정

ALTER TABLESPACE insa_tab RENAME DATAFILE '전에 파일경로' TO
'변경후 파일경로';

ALTER TABLESPACE insa_tab RENAME DATAFILE
'/u01/app/oracle/oradata/insa_tab01.dbf' TO
'/u01/app/oracle/oradata/ora11g/insa_tab01.dbf';

▶ 4. 테이블스페이스 온라인으로 변경

ALTER TABLESPACE insa_tab ONLINE;

▶ 5. 확인

SELECT * FROM v$datafile;
SELECT * FROM dba_data_files;

■ UNDO 테이블스페이스

  • DML 작업시에 이전값을 저장하는 공간
  • 트랜잭션이 종료(commit, rollback) 될때 까지는 이전값을 보존해야 한다.

목적

  • rollback
  • read consistent(읽기일관성)
  • flashback query
  • 실패한 트랜잭션 recovery
  • 읽기 일관성
    : select 조회중에 DML 작업이 들어와도
    select 작업을 실행하면 control file에 있는
    SCN 번호를 보고 마지막 SCN번호보다 작거나 같은것만 읽는다.

    DML 작업을한 block 은 SCN번호가 크므로
    UNDO에 가서 찾아보고 복사를해서 그것을 조회한다.
    따라서 UNDO에는 COMMIT 전에 값을 최대한 오래있을려고한다.

▶ 1. undo monitoring

undo space가 부족한 경우 에러

  • ORA-01650: unable to extend rollback segment

읽기 일관성이 어긋난 경우 long query 문에서

  • ORA-01555 : snapshot too old

enq ( undo관리를 잘못해서 )

  • US - contention wait event

SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1

undo_management
: UNDO 영역을 관리하는 방식

undo_retention : 읽기 일관성을 900초 유지해준다.
: 트랜잭션이 종료가 되었더라도 이전값을 undo_retention 파라미터에 설정되어 있는 초 시간까지는 보존하자.

  • read consistent (읽기일관성)
  • flashback query

ALTER SYSTEM SET undo_retention = 1800;

  • 트랜잭션이 종료 되었더라도 이전값을 undo_retention 꼭 보장하자
  • undo_retention 보장하기 위해서 새로운 트랜잭션이 실패할 수도 있다. -> undo space 관리를 잘하자.
    ALTER TABLESPACE UNDOTBS1 RETENTION GUARANTEE;
  • 보장하지 않겠다.
    ALTER TABLESPACE UNDOTBS1 RETENTION NOGUARANTEE;
  • 어떤곳에 트랜잭션이 걸려있는지 본다 (xacts 컬럼으로)
SELECT n.name, s.extents, s.rssize, s.xacts, s.status
FROM v$rollname n, v$rollstat s
WHERE n.usn = s.usn;
SELECT s.username, t.xidusn, t.ubafil , t.ubablk, t.used_ublk
FROM v$session s, v$transaction t
WHERE s.saddr = t.ses_addr;

username: 현재 세션의 사용자 이름
xidusn: 트랜잭션의 undo 세그먼트 번호
ubafil: 트랜잭션의 undo 파일 번호
ubablk: 트랜잭션의 undo 블록 번호
used_ublk: 트랜잭션에서 사용 중인 undo 블록의 수

used_ublk
: 사용하고 있는 user block의 수

SELECT * FROM dba_rollback_segs;
  • 다른 세션에서 트랜잭션 작업을 동시에 하면 undo 공간이 부족해질수 있다. 따라서 undo 공간을 자동으로 늘려줘야한다.
ALTER TABLESPACE UNDOTBS1 ADD DATAFILE '/u01/app/oracle/oradata/ora11g/undotbs02.dbf'
SIZE 10M AUTOEXTEND ON;

▶ 2. 새로운 undo tablespace 생성한 후 지정

■ 시나리오

▶ 1. 새로운 undo tablespace 생성

CREATE UNDO TABLESPACE undo1
DATAFILE '/u01/app/oracle/oradata/ora11g/undo1.dbf' SIZE 5M AUTOEXTEND ON;
  • UNDO 테이블스페이스는 SEGMENT 관리는 무조건 MANUAL 이다.

▶ 2. 확인

SQL> show parameter undo_tablespace

NAME TYPE VALUE

undo_tablespace string UNDOTBS1

▶ 3. 새로운 undo table space 지정

ALTER SYSTEM SET undo_tablespace = undo1;

  • 기존의 undo segment들은 사용하지 못하도록 offline으로 바뀐다.

▶ 4. 확인

SQL> show parameter undo_tablespace

NAME TYPE VALUE

undo_tablespace string UNDO1

▶ 5. 기존 undo 트랜잭션 작업 확인

select * from dba_rollback_segs;

기존 undo 안에 트랜잭션이 지연되고 있다고
status 컬럼에 pending offline이 나온다. 그러면 트랜잭션을 끝내주고 삭제해야한다.

▶ 6. 기존 테이블스페이스 삭제

DROP TABLESPACE undotbs1 including contents
and datafiles;

profile
YoonSeo Park
DB 공부를 하고 있는 사람입니다. 글을 읽어주셔서 감사하고 더 좋은 글을 쓰기 위해 노력하겠습니다 :)
이전 포스트

Listener, alertlog, startup, shutdown

다음 포스트

undo, redo, controlfile

0개의 댓글