문서유형ㅣ기술정보
분야ㅣ모니터링/점검
문서번호ㅣTMOTI027
개요
본 글에서는 TABLE REORG 대상 테이블을 조회하는 방법과 실제 REORG 작업을 수행하는 예시 쿼리를 함께 설명합니다.
방법
REORG 대상 조회 방법
각 TABLE 이 차지하고 있는 사이즈와 예상되는 테이블 사이즈를 비교해 비율 차이가 많이 나는 테이블을 대상으로 DBMS_SPACE 패키지로 REORG 대상 조회 작업을 합니다.
(row count * row max length) / (table block count * block size)
1. DBMS_SPACE 체크 대상 조회 쿼리
- 테이블의 extents 된 사이즈와 예상 테이블 사이즈의 차이가 100MB 초과와 예상 테이블 사이즈/extents 된 사이즈의 비율이 50% 이하일 경우 조회합니다.
- 조회 쿼리는 통계정보 수집한 테이블을 대상으로 확인합니다.
SET LINES 180;
COL owner FOR A10;
COL table_name FOR A30;
SELECT *
FROM (
SELECT a.owner,
b.table_name,
table_type,
last_analyzed,
num_rows,
ROUND(extents_size / 1024 / 1024, 2) AS "EXTENTS_SIZE(MB)",
ROUND(max_data_size / 1024 / 1024, 2) AS "MAX_DATA_SIZE(MB)",
ROUND((extents_size - max_data_size) / 1024 / 1024, 2) AS "DIFF_SIZE(MB)",
ROUND((max_data_size / extents_size * 100), 2) AS "DATA_RATE(%)"
FROM (
SELECT owner,
segment_name,
SUM(bytes) AS extents_size, -- table extents total size
SUM(blocks) AS extents_block_count -- table extents block total count
FROM dba_extents
WHERE owner NOT IN ('SYS', 'SYSCAT', 'SYSGIS')
AND segment_type IN ('TABLE', 'TABLE PARTITION')
GROUP BY owner, segment_name
) a,
(
SELECT a.owner,
a.table_name,
DECODE(partitioned, 'YES', 'PARTITION TABLE', 'TABLE') AS table_type,
last_analyzed,
ROUND(num_rows) AS num_rows,
blocks,
avg_row_len,
max_row_len,
(ROUND(num_rows) * max_row_len) AS max_data_size
FROM dba_tables a,
(
SELECT owner,
table_name,
SUM(data_length) AS max_row_len -- 1 row max size
FROM dba_tbl_columns
WHERE owner NOT IN ('SYS', 'SYSCAT', 'SYSGIS')
GROUP BY owner, table_name
) b
WHERE a.owner = b.owner
AND a.table_name = b.table_name
AND last_analyzed IS NOT NULL
) b
WHERE a.owner = b.owner
AND a.segment_name = b.table_name
) x
WHERE "DIFF_SIZE(MB)" > 100
AND "DATA_RATE(%)" < 50
ORDER BY "DATA_RATE(%)" DESC,
"DIFF_SIZE(MB)" DESC;2. REORGE 대상 조회 쿼리
- 1 번에서 조회된 테이블을 대상으로 데이터 블록 사용량 확인해서 REORG 필요 여부 확인합니다.
- in 조건안에 조회 대상 테이블 입력후 조회합니다.
- FREE 비율이 50% 이상인 블록이 전체 블록의 20% 초과시 REORG 대상으로 선정합니다.
SET SERVEROUTPUT ON
SET LINESIZE 180
SET PAGESIZE 100
DECLARE
USED_BLOCKS NUMBER;
FRM_BLOCKS NUMBER;
FRM_RATIO NUMBER;
uf NUMBER;
ub NUMBER;
f1 NUMBER;
f1b NUMBER;
f2 NUMBER;
f2b NUMBER;
f3 NUMBER;
f3b NUMBER;
f4 NUMBER;
f4b NUMBER;
fbl NUMBER;
fby NUMBER;
BEGIN
FOR tb_list IN (
SELECT owner, segment_name, partition_name, segment_type
FROM dba_segments
WHERE segment_name IN ('MK_TBH_RF_COV','TEST','ABC') -- 조회 대상 테이블
) LOOP
BEGIN
dbms_space.space_usage(
tb_list.owner,
tb_list.segment_name,
tb_list.segment_type,
uf, ub, f1, f1b, f2, f2b, f3, f3b, f4, f4b, fbl, fby,
tb_list.partition_name
);
USED_BLOCKS := f1 + f2 + f3 + f4 + fbl;
FRM_BLOCKS := f3 + f4;
IF USED_BLOCKS = 0 THEN
dbms_output.put_line('----------------------');
dbms_output.put_line(tb_list.owner || '.' || tb_list.segment_name || '.' || tb_list.partition_name || ' is not used.');
ELSE
FRM_RATIO := ROUND(FRM_BLOCKS / USED_BLOCKS * 100);
IF USED_BLOCKS > 100 THEN
IF FRM_RATIO > 20 THEN
dbms_output.put_line('#######################');
dbms_output.put_line(tb_list.owner || '.' || tb_list.segment_name || '.' || tb_list.partition_name || ' is fragmented ' || FRM_RATIO || '%');
dbms_output.put_line('unformatted blocks : ' || TO_CHAR(uf));
dbms_output.put_line('blocks 0-25% free : ' || TO_CHAR(f1));
dbms_output.put_line('blocks 25-50% free : ' || TO_CHAR(f2));
dbms_output.put_line('blocks 50-75% free : ' || TO_CHAR(f3));
dbms_output.put_line('blocks 75-100% free : ' || TO_CHAR(f4));
dbms_output.put_line('full blocks : ' || TO_CHAR(fbl));
ELSE
dbms_output.put_line('----------------------');
dbms_output.put_line(tb_list.owner || '.' || tb_list.segment_name || '.' || tb_list.partition_name || ' is fragmented [' || FRM_RATIO || '%]');
END IF;
ELSE
dbms_output.put_line('----------------------');
dbms_output.put_line(tb_list.owner || '.' || tb_list.segment_name || '.' || tb_list.partition_name || ' pass [' || USED_BLOCKS || '] Used blocks');
END IF;
END IF;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(TO_CHAR(SQLCODE) || ':' || SQLERRM);
END;
END LOOP;
END;
/
Online REORGE 방법 (Table Shrink)
1. 대상 테이블과 동일한 구조를 가진 임시 테이블 생성
CREATE TABLE temp_table ( column, ... );
2. Redefinition 작업 시작
BEGIN DBMS_REDEFINITION.START_REDEF_TABLE( UNAME => 'owner', ORIG_TABLE => 'target_table', INT_TABLE => 'temp_table', OPTIONS_FLAG => 2); -- 1: PK 정보 사용, 2: ROWID 정보 사용 (Default: 1) END; /
3. Redefinition 완료 후 임시 테이블에 PRIMARY KEY 및 인덱스 추가
1) PRIMARY KEY 추가
ALTER TABLE temp_table ADD CONSTRAINT pk_constraint_name PRIMARY KEY(column);
2) 인덱스 추가
CREATE INDEX temp_index ON temp_table ( column, ... );
4. Redefinition 작업 도중 발생한 변경 데이터 Sync 작업 진행
BEGIN DBMS_REDEFINITION.SYNC_INTERIM_TABLE( UNAME => 'owner', ORIG_TABLE => 'target_table', INT_TABLE => 'temp_table'); END; /
5. 임시 테이블과 대상 테이블의 변경
BEGIN DBMS_REDEFINITION.FINISH_REDEF_TABLE( UNAME => 'owner', ORIG_TABLE => 'target_table', INT_TABLE => 'temp_table'); END; /
6. 제약사항 및 인덱스 이름 변경
1) 인덱스 이름 변경
temp_table 의 인덱스 이름을 변경한 후 target_table 의 인덱스 이름을 변경합니다.
ALTER INDEX temp_index RENAME TO index_name;
2) 제약사항 이름 변경
temp_table 의 PRIMARY KEY 등의 제약사항 이름을 변경한 후 target_table 의 제약사항 이름을 변경합니다.
ALTER TABLE TEST RENAME CONSTRAINT PK_TEST_BAK TO PK_TEST;
7. 임시 테이블 drop
DROP TABLE temp_table;