[오라클] 오라클 스토리지 개념 이해하기
구조
운영 체제 블록: 오라클 스토리지에서 가장 작은 단위이자 운영 체제의 I/O 단위이다. 운영 체제 블록이 모여 하나의 데이터 블록을 구성한다.
데이터 파일: 디스크에 존재하는 실제 파일. 데이터 파일이 모여 테이블스페이스를 구성한다.
데이터 블록: 여러 개의 운영 체제 블록으로 구성되고 오라클 데이터베이스의 I/O 단위이다.
데이터 블록에는 여러 로우 저장된다. 여기서 단위라는 말의 의미는 원하는 로우를 추출하기 위해 동일 데이터 블록 내 다른 로우도 메모리에 적재되는 구조인 것이다. 블록의 크기는 원하는만큼 설정할 수 있는데 크기에 따라 장단점이 존재한다.
데이터 블록이 클수록 블록 사용률이 높고, 한번의 I/O로 많은 데이터를 추출할 수 있는 장점이 있다. 반면 블록에 대한 경합 가능성도 높다.
데이터 블록에 설정할 수 있는 옵션은 INITRANS, MAXTRANS, PCTFREE, PCTUSED가 있다.
INITRANS: 데이터 블록 생성 시 초기 트랜잭션 슬롯 개수, 테이블에 대한 데이터 블록 1, 인덱스에 대한 데이터 블록 2를 기본값으로 한다.
MAXTRANS: 최대 트랜잭션 슬롯 개수
PCTFREE: 여유 공간 크기, 기본값은 블록 크기의 10%. LOW MIGRATION 방지한다.
행 이전: 하나의 데이터 추출 시 많은 데이터 블록을 액세스하므로 성능 저하 발생할 수 있다. 해당 문제 해결을 위해 테이블 재구성 작업을 수행해야 한다.
테이블 재구성: 테이블 재생성한 후 전체 데이터를 옮기는 것이다.
PCTUSED: 수동 세그먼트 공간 관리 방식 사용 시, 데이터 블록 사용률값이다. 기본값은 데이터 블록 크기의 40%이다.
데이터 블록에 설정된 옵션들은 USER_TABLES나 DBA_TABLES를 검색해서 값을 확인할 수 있다. 자동 세그먼트 공간 관리 방식의 테이블스페이스에 'EMP' 테이블이 존재할 경우, DBA_INDEXES에는 PCT_USED 컬럼이 추출되지 않는다.
SQL> SELECT PCT_FREE, PCT_USED, INI_TRANS, MAX_TRANS FROM USER_TABLES WHERE TABLE_NAME = 'EMP';
PCT_FREE PCT_USED INI_TRANS MAX_TRANS
---------- ---------- ---------- ----------
10 40 1 255
SQL> SELECT PCT_FREE, PCT_USED, INI_TRANS, MAX_TRANS FROM DBA_TABLES WHERE TABLE_NAME = 'EMP';
PCT_FREE PCT_USED INI_TRANS MAX_TRANS
---------- ---------- ---------- ----------
10 40 1 255
SQL> SELECT PCT_FREE, PCT_USED, INI_TRANS, MAX_TRANS FROM DBA_INDEXES WHERE TABLE_NAME = 'EMP';
SELECT PCT_FREE, PCT_USED, INI_TRANS, MAX_TRANS FROM DBA_INDEXES WHERE TABLE_NAME = 'EMP'
*
ERROR at line 1:
ORA-00904: "PCT_USED": invalid identifier
행 이전, 행 연결 확인:
SQL> SELECT NUM_ROWS, CHAIN_CNT
2 FROM DBA_TABLES
3 WHERE TABLE_NAME='EMP';
NUM_ROWS CHAIN_CNT
---------- ----------
SQL> SELECT * FROM USER_USERS;
USERNAME USER_ID ACCOUNT_STATUS LOCK_DAT EXPIRY_D DEFAULT_TABLESPACE TEMPORARY_TABLESPACE LOCAL_TEMP_TABLESPACE CREATED INITIAL_RS EXTERNAL_N P COM O INH DEFAULT_CO IMP ALL PASSWORD
---------- ---------- -------------------------------- -------- -------- ------------------------------ ------------------------------ ------------------------------ -------- ---------- ---------- - --- - --- ---------- --- --- --------
SYS 0 OPEN 22/05/17 SYSTEM TEMP TEMP 21/11/18 SYS_GROUP N YES Y YES USING_NLS_ NO NO 21/11/18
BEGIN
DBMS_STATS.GATHER_TABLE_STATS (
OWNNAME=>'SCOTT',
TABNAME=>'EMP',
ESTIMATE_PERCENT=>10,
METHOD_OPT=>'AUTO',
DEGREE=>4,
CASCADE=>TRUE);
END;
/
익스텐트: 다수의 연속 데이터 블록으로 구성되며 데이터베이스 스토리지 공간 할당의 논리적 단위이다. 하나 이상의 익스텐트가 세그먼트를 구성한다.
DELETE VS. TRUNCATE: A 테이블의 MINEXTENTS가 2, 익스텐트 하나의 크기는 1MB라고 가정하자. DELETE. TRUNCATE 명령 모두 데이터는 전부 삭제된다. 그러나 DELETE 명령 후에 테이블 크기는 변하지 않는다. 즉 익스텐트가 할당 해제되지 않는다. TRUNCATE는 MINEXTENTS 옵션 설정값 만큼의 익스텐트만을 남기고 나머지는 할당 해제된다.
익스텐트 정보 검색 예:
SQL> COLUMN FILE_NAME FORMAT A10
SELECT A.FILE_NAME, B.EXTENT_ID, B.BLOCKS, B.BYTES/102
FROM DBA_DATA_FILES A, DBA_EXTENTS B
WHERE A.FILE_ID = B.FILE_ID
4 AND B.SEGMENT_NAME='EMP';
FILE_NAME EXTENT_ID BLOCKS B.BYTES/102
---------- ---------- ---------- -----------
/u01/app/o 0 8 642.509804
racle/orad
ata/ORCL/s
ystem01.db
f
테이블스페이스가 가지고 있는 사용 가능한 연속된 공간 정보 확인:
SQL> SELECT * FROM DBA_FREE_SPACE;
TABLESPACE FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
---------- ---------- ---------- ---------- ---------- ------------
SYSTEM 1 112752 131072 16 1
SYSTEM 1 113536 13631488 1664 1
SYSAUX 2 68272 38404096 4688 2
UNDOTBS1 3 288 196608 24 3
UNDOTBS1 3 320 983040 120 3
세그먼트: 세그먼트는 테이블스페이스 내의 특정 논리적 스토리지 구조에 대한 모든 데이터를 포함하는 익스텐트 세트입니다. 예를 들어, 각 '테이블'에 대해 Oracle은 해당 테이블의 데이터 세그먼트를 형성하기 위해 하나 이상의 익스텐트를 할당하고 각 '인덱스'에 대해 Oracle은 인덱스 세그먼트를 형성하기 위해 하나 이상의 익스텐트를 할당한다.
세그먼트 공간 관리 방식: 수동 - 프리리스트 이용 / 자동(ASSM) - 비트맵 이용
오브젝트 종류:
실제 물리적 공간을 가지는 오브젝트(세그먼트): 테이블, 인덱스, 클러스터, 파티션, 파티션 인덱스
시스템 테이블스페이스에 정의만 저장되는 오브젝트: 뷰, 시퀀스
세그먼트 관리
세그먼트의 데이터 블록 단편화: 세그먼트 생성 후 DML 많이 발생하면 세그먼트 중간 중간에 미사용 공간이 발생하는 현상.
문제점:
1. 테이블 전체 조회 소요 시간 증가
2. 인덱스 레벨 깊어짐
3. 하나의 데이터 블록 조회 시 적은 로우 추출, 디스크 I/O 증가
4. 빈 데이터 블록 증가로 인해 디스크 공간 낭비
테이블 재생성, 인덱스 리빌드, 테이블 MOVE 시 단편화 해결할 수 있지만, 재구성을 위한 추가공간이 필요하고, 서비스 정지 시간이 증가한다.
테이블 재구성 절차:
1. 대상 테이블인 A 테이블과 구조가 동일한 A_임시 테이블을 생성한다.
2. 대상 테이블 A 테이블의 데이터를 A_임시 테이블로 저장한다. 다이렉트 로딩 방식 또는 일단 데이터 로딩 방식 사용 가능.
3. 대상 테이블 이름을 A_백업으로 변경, A_임시 테이블 이름을 A 테이블로 변경한다.
이 외에 테이블 MOVE, 온라인 세그먼트 축소는 단순 명령어로 수행할 수 있다.
| 테이블 재구성 | 테이블 MOVE | 온라인 세그먼트 축소 | |
| 절차 | 복잡함 | 단순함 | 단순함 |
| 작업 중 서비스 | 불가능 | 가능 | 가능 |
| 수행 시간 | 빠름 | 빠름 | 보통 |
| 추가 필요 공간 | 대상 테이블 크기만큼 | 대상 테이블 크기만큼 | 불필요 |
| 인덱스 재구성 | 재구성 필요 | 불필요 | 불필요 |
| 행 이전 | 해결 | 해결 | 부분 해결 |
온라인 세그먼트 축소:
- 블록 단편화 문제에 대해 위 방법을 적용하면, 미사용 데이터 블록을 할당 해제하여 동일 테이블스페이스내의 다른 세그먼트가 사용할 수 있다.
- 온라인으로 세그먼트를 축소하더라도 세그먼트의 단편화가 심한 경우 많은 트랜잭션이 발생하므로 작업 시간을 정해야 서비스 영향을 감소시킬 수 있다.
- 온라인 세그먼트 축소는 인덱스 깊이를 감소시키지 않으므로 인덱스 성능 향상에는 효과가 없다. 인덱스 성능의 향상을 위해서는 인덱스 재구성을 수행한다.