공간 관리
공간을 효율적으로 관리하는 것은 데이터베이스의 성능에 중요한 일입니다. 이 장에서는 데이터베이스에서 익스텐트와 블록을 관리하는 방법을 다룹니다.

블록
오라클에서, 블록은 데이터 파일 I/O의 가장 작은 단위이자 할당될 수 있는 공간의 가장 작은 단위입니다. 오라클 블록은 하나이상의 연속적인 운영체제 블록으로 이루어져 있습니다.

익스텐트
익스텐트는 여러 연속적인 데이터 블록으로 만들어진 데이터베이스 저장장소 공간 할당의 논리적 단위입니다. 하나 이상의 익스텐트가 모여 세그먼트를 이룹니다. 세그먼트에서 기존 공간이 완전히 사용되면, 오라클은 세그먼트에 대해 새로운 익스텐트를 할당합니다.

세그먼트
세그먼트는 테이블스페이스 내의 특정 논리적 저장구조에 대한 모든 데이터를 포함하는 익스텐트의 집합입니다. 예를 들어, 오라클은 각 테이블에 대해 하나 이상의 익스텐트를 할당하여, 그 테이블에 대한 데이터 세그먼트를 형성합니다. 인덱스에 대해서는 하나 이상의 익스텐트를 할당하여, 인덱스 세그먼트를 형성합니다.

데이터베이스 작업으로 인해 데이터가 증가하여 할당된 공간을 초과할 때, 오라클은 세그먼트를 확장합니다. INSERT나 UPDATE 문을 실행시킬 때 세그먼트를 확장하여 동적으로 확장이 이루어지게 되면 성능은 감소합니다. 서버가 여유 공간을 찾고 데이터 딕셔너리에 익스텐트를 추가하기 위하여 여러 순환적(recursive) SQL문을 실행하기 때문입니다.    

동적인 확장을 피하기 위하여 다음을 수행합니다:

공간을 쉽게 관리하기 위하여, DBA는 적절한 크기의 세그먼트와 익스텐트로 객체를 생성합니다. 일반적으로, 작은 익스텐트 보다는 큰 익스텐트가 선호됩니다.

크기가 큰 익스텐트의 장점

크기가 큰 익스텐트의 단점

큰 익스텐트를 적게 할당할 것인가 아니면 작은 익스텐트를 많이 할당할 것인가를 결정할 때에는, 테이블의 증가와 사용에 대한 계획에 맞추어 각각의 이점과 결함을 고려해야 합니다.

데이터베이스 튜닝 목표 중의 하나는 액세스되는 블록의 수를 최소화하는 것입니다. 이 목표를 달성하기 위하여, 개발자는 애플리케이션과 SQL 문을 튜닝하고, DBA는 다음을 통해 블록 액세스를 감소시킵니다:

불행하게도, DBA에게 마지막 2개의 목표는 서로 상충됩니다. 즉, 많은 데이터를 블록으로 압축시키면, 행 이전이 증가하기 때문입니다

블록 크기의 특성은 다음과 같습니다:

선택된 블록 크기는 성능에 영향을 미칩니다. 일반적인 Guideline은 다음과 같습니다:


작은 오라클 블록

장점

단점

큰 오라클 블록

장점

단점

데이터베이스 블록의 크기를 재조정하는데 추가 메모리가 없을 경우, DB_BLOCK_BUFFERS를 재설정할 필요가 있습니다. 이것은 캐쉬 적중율에 영향을 미칠 것입니다.

기술적 주의사항


2개의 공간 관리 파라미터 PCTFREE와 PCTUSED를 사용하여, 세그먼트의 모든 데이터 블록 내에서 여유 공간의 사용을 제어할 수 있습니다. 테이블 또는 클러스터(고유 데이터 세그먼트를 갖고 있는)를 생성하거나 변경할 때, 이들 파라미터를 지정하십시오. 또한, 인덱스(고유 인덱스 세그먼트를 갖고 있는)를 생성하거나 변경할 때 PCTFREE 공간관리 파라미터를 지정할 수 있습니다.

PCTFREE 파라미터
PCTFREE 파라미터는 블록에 이미 존재하는 행을 갱신할 때 사용하기 위한 여유 공간으로 남겨둘 데이터 블록의 최소 퍼센트를 설정합니다.

PCTUSED 파라미터
PCTUSED 파라미터는 새로운 행이 블록에 추가되기 전에 행 데이터와 오버헤드에 대해 사용될 수 있는 블록의 최소 퍼센트를 설정합니다.

PCTFREE와 PCTUSED의 상호 연관 관계
예를 들어, PCTFREE 20으로 CREATE TABLE 문을 실행시킬 경우, 오라클은 각 블록에 존재하는 행에 대해 갱신 작업을 수행하기 위해 이 테이블의 데이터 세그먼트에 있는 각 데이터 블록의 20%를 남겨둡니다. 블록의 사용되는 공간은 (1)행 데이터와 오버헤드의 총합이 총 블록 사이즈의 80%가 될 때까지  증가할 수 있습니다. 그런 다음, 블록은 추가 삽입을 막기 위해 free list로부터 제거됩니다(2).

DELETE 또는 UPDATE 문을 실행시키면, 오라클은 문장을 처리하고, 사용되고 있는 블록 공간이 현재 PCTUSED 보다 작은가를 검사합니다. 작을 경우, 블록은 free list의 처음 부분으로 등록됩니다. 트랜잭션이 커밋될 때, 블록의 여유 공간은 다른 트랜잭션에 이용될 수 있습니다(3).
데이터 블록이 다시 PCTFREE 한계까지 채워지면 (4), 오라클은 그 블록의 퍼센트가 PCTUSED 파라미터 아래로 내려 갈 때까지 새로운 행의 삽입 작업에 블록을 사용하지 않을 것입니다.  

DML과 PCTFREE 및 PCTUSED
2가지 유형의 문장이 데이터 블록의 여유 공간을 증가시킬 수 있습니다. 즉, 기존 값을 더 적은 공간을 사용하는 값으로 갱신하는 UPDATE 문과 DELETE 문 입니다.

블록의 해제된 공간은 연속적이지 않을 수 있습니다. 예를 들어, 블록의 중간에 있는 행이 삭제되었을 때, 오라클은 다음의 경우에만 데이터 블록의 여유 공간을 결합(coalesce)합니다:

  • INSERT 또는 UPDATE문이 새로운 행 부분을 포함하는데 충분한 여유 공간을 갖고있는  블록을 사용하려고 시도할 때
  • 여유 공간이 단편화되어, 행 부분이 블록의 연속 섹션에 삽입될 수 없을 때

데이터 블록의 여유 공간을 연속으로 압축하는 것으로 인해 데이터베이스 시스템의 성능이 감소하기 때문에, 오라클은 위와 같은 경우에만 이러한 압축을 수행합니다.

Guidelines

다음과 같은 두 가지 상황에서는, 테이블 행의 데이터가 너무 크기 때문에 단일 데이터 블록에 다 들어가지 않을 것입니다.

이전 및 연쇄화는 다음과 같이 성능에 부정적인 영향을 미칩니다:

이전 현상은 PCTFREE가 너무 낮게 설정 되었을 때 발생합니다. 갱신 작업을 수행하는데 충분한 여유 메모리가 없기 때문입니다. 이전 현상을 피하기 위해서는, 갱신되는 모든 테이블은 블록 공간이 갱신 작업에 충분하도록 PCTFREE를 설정해야 합니다.  

ANALYZE …COMPUTE STATISTICS
ANALYZE 문의 COMPUTE STATISTICS option을 사용하면 테이블이나 클러스터 내에 이전 및 연쇄화 된 행이 존재하는지 알 수 있습니다.  이 명령어는 이전 및 연쇄화 된 행의 수를 세어 DBA_TABLES의 CHAIN_CNT열에 기록합니다.

NUM_ROWS열은 분석된 테이블 또는 클러스터에 저장된 총 행수를 가집니다.이전된 행을 제거해야 할 지를 결정하기 위해서 전체 행 수에 대한 이전 및 연쇄화
된 행의 비율을 계산하십시오.

Table Fetch Continued Row Statistic
또한 V$SYSSTAT이나 report.txt의 Table Fetch Continued Row 통계정보를 확인하여 이전 및 연결 된 행이 있는지를 알 수 있습니다.

Guidelines
PCTFREE를 증가시켜, 이전되는 행을 피하십시오. 블록에 이용할 수 있는 더 많은 여유 공간이 있다면, 행은 증가하는데 필요한 공간을 갖게 될 것입니다.  또한 많은 삭제가 있는 테이블이나 인덱스를 재구성(재생성) 할 수 있습니다. 

ANALYZE ... LIST CHAINED ROW
LIST CHAINED ROWS 옵션과 함께 ANALYZE 명령을 사용하여, 테이블 또는 클러스터에서 이전되었거나 연결된 행을 식별할 수 있습니다. 이 명령은 이전되었거나 연결된 각 행에 대한 정보를 수집하여 특정 출력 테이블에 놓습니다. 연결된 행을 포함하는 테이블을 생성하기 위해서는, UTLCHAIN.SQL 스크립트를 실행시키십시오.

  create table CHAINED_ROWS(
     owner_name         varchar2(30),
     table_name         varchar2(30),
     cluster_name       varchar2(30),
     partition_name     varchar2(30),
     head_rowid         rowid,
     analyze_timestamp  date);

이 테이블을 수동으로 생성할 경우, 테이블의 열 이름, 데이터 유형, 크기는 CHAINED_ROWS 테이블의 것과 같아야 합니다.

다음의 SQL*Plus 스크립트를 사용하여 이전된 행을 삭제할 수 있습니다:
 
   /* Get the name of the table with migrated rows */
   accept table_name prompt ‘Enter the name of the table with migrated rows:’
   /* Clean up from last execution */
   set echo off
   drop table migrated_rows;
   drop table chained_rows;

   /* Create the CHAINED_ROWS table */
   @?/rdbms/admin/utlchain
   set echo on
   spool fix_mig
   /* List the chained & migrated rows */
   analyze table &table_name list chained rows;
   /* Copy the chained/migrated rows to another table */
   create table migrated_rows as
       select   orig.*  
           from   &table_name orig, chained_rows  cr
           where    orig.rowid = cr.head_rowid;
           and     cr.table_name = upper (‘&table_name’);

   /* Delete the chained/migrated rows from the orignial table */
   delete from &table_name
       where rowid in ( select head_rowid  from  chained_rows);

   /*  Copy the chained/migrated rows back into the original table */
   insert into &table_name select * from  migrated_rows;

   spool off

이 스크립트를 사용할 때에는, 행이 삭제 시 위반하게 될 외부키(foreign key) 제약조건을 출력할 필요가 있습니다.

최고 수위 표시 위의 공간은 다음 명령을 사용하여 테이블 레벨에서 이용할 수 있습니다:

   ALTER TABLE <table_name> DEALLOCATE UNUSED…

전체 테이블 스캔 시, 오라클은 최고 수위 표시 아래의 모든 블록을 읽습니다. 최고 수위 표시 위의 빈 블록은 공간을 낭비할 수는 있지만, 성능을 저하시키지는 않습니다. 그러나, 최고 수위 표시 아래의 사용된 블록 이하는 성능을 저하시킬 것입니다.

통계를 수집하고 데이터 딕셔너리 저장장소에 저장하기 위하여, 테이블, 인덱스, 클러스터의 저장장소 특성을 분석할 수 있습니다. 이들 통계를 사용하여 테이블이나 인덱스가 사용되지 않은 공간을 갖고 있는지의 여부를 결정할 수 있습니다.

DBA_TABLES 뷰를 질의하여 결과 통계 자료를 보십시오.

열

설명

NUM_ROWS

테이블의 행 수

BLOCKS 

최고 수위 표시 아래의 블록 수

EMPTY_BLOCKS 

최고 수위 표시 위의 블록 수

AVG_SPACE

최고 수위 표시 아래 블록의 평균 여유공간

AVG_ROW_LEN 

행 오버헤드를 포함한, 평균 행 길이

CHAIN_CNT    

테이블의 이전된 또는 연결된 행 수

EMPTY_BLOCKS는 이전에는 완전 사용되었지만  현재는 비워있는 블록을 나타내는 것이 아니라, 아직 사용되지 않은 블록을 나타냅니다.

기술적 주의사항
오라클은 SAMPLE 절이 생략될 경우에는 1064 행을 샘플로 하고, 데이터의 반 이상이 SAMPLE 수치에 의해 지정되었을 경우에는 ESTIMATE가 아니라 COMPUTE를 사용합니다.

또한 지원되는 위의 패키지를 사용하여 세그먼트의 공간 사용에 관한 정보를 얻을 수 있습니다. 이 패키지에는 2개의 프로시저가 포함되어 있습니다:

이들 프로시저는 catproc.sql에 의해 실행되는 dbmsutil.sql 스크립트에 의해 생성되어 문서화됩니다. 이 패키지를 실행시킬 때, FREE_LIST_GROUP_ID 값을 제공해야 합니다. Oracle Parallel Server를 사용하지 않을 경우, 1을 사용하십시오.

이 스크립트는 사용자에게 테이블 소유자와 테이블명에 관한 정보를 제공하고, DBMS_SPACE.UNUSED_SPACE를 실행시키며, 공간 관련 통계를 출력합니다.

 Declare
   owner       varchar2(30);  
   name        varchar2(30)
   seq_type    varchar2(30)
   tblock      number;
   uBlock      number;
   ubyte       number;
   lue_fid     number;
   lue_bid     number;
   lublock     number;
BEGIN
   dbms_space.unused_space (‘&owner’, ‘&table_name’, ‘TABLE’,
        tblock, tbyte, ublock, ubyte, lue_fid, lue_bid, lublock);
   dbms_output.put_line (‘Total blocks allocated to table = ‘
        || to_char(tblock));
   dbms_output.put_line (‘Total blocks allocated to table = ‘
        || to_char(tbyte));
   dbms_output.put_line (‘Unused blocks(above HWM)        = ‘
        || to_char(ublock));
   dbms_output.put_line (‘Unused blocks(above HWM)        = ‘
        || to_char(ubyte));
   dbms_output.put_line (‘Last extent used file id        = ‘
        || to_char(lue_fid));
   dbms_output.put_line (‘Last extent used beginning block id = ‘
        || to_char(lue_bid));
   dbms_output.put_line (‘Last extent block in last extent    = ‘
        || to_char(lublock));
END;

휘발성 테이블에 대한 인덱스 또한 성능 문제를 나타낼 수 있습니다.  

데이터 블록에서, 오라클은 삭제된 행을 삽입된 행으로 교체합니다. 그러나, 인덱스 블록에서는, 엔트리들을 순서대로 정열합니다. 값은 동일한 범위의 다른 값들과 함께 적당한 블록으로 갑니다.

많은 애플리케이션은 오름차순 인덱스 순서로 삽입하고 이전 값을 삭제합니다. 그러나, 블록이 한 엔트리만을 포함하더라도, 이 블록은 유지되어야 합니다. 이러한 경우에, 인덱스를 규칙적으로 재구축해야 할 필요가 있을 것입니다.

인덱스 블록의 모든 엔트리를 삭제할 경우, 오라클은 이 블록을 free list에 다시 놓습니다.

다음 명령을 사용하여 인덱스에 의해 사용되는 공간을 감시할 수 있습니다.

  SQL> ANALYZE INDEX index_name VALIDATE STRUCTURE;

그런 다음 INDEX_STATS 뷰를 질의하십시오:

열

 설명

LF_ROWS 

 현재 인텍스에 있는 값의 수

LF_ROWS_LEN

 모든 값의 길이의 바이트 합

DEL_LF_ROWS 

 인덱스에서 삭제된 값의 수

DEL_LF_ROWS_LEN

 모든 삭제된 값의 길이

        
주의: INDEX_STATS 뷰는 가장 마지막에 분석된 인덱스를 보여줍니다. 만인 동일 사용자로 다른 세션으로 접속하였다면 뷰는 그 뷰를 질의하는 세션에서 분석된 인덱스를 보여 줍니다.

인덱스 재구성(REBUILD)
애플리케이션과 우선순위에 따라 다르겠지만, 삭제된 엔트리가 현재 엔트리의 20% 또는 그 이상을 나타낼 경우 재구축할 것을 결정할 것입니다. 위의 질의를 사용하여 비율을 찾을 수 있습니다. ALTER INDEX REBUILD 문을 사용하여, 기존 인덱스를 재구성하거나 압축하든지, 또는 저장장소 특성을 변경하십시오. REBUILD 문은 기존 인덱스를 사용하여 새로운 인덱스를 구축합니다. STORAGE(익스텐트 할당에 대해), TABLESPACE(인덱스를 새로운 테이블스페이스로 이동시키기 위해), INITRANS(초기 엔트리의 수를 변경하기 위해)와 같은 모든 인덱스 저장장소 명령어가 지원됩니다.또한, 인덱스를 구축할 때, 다음 키워드를 사용하여 재구축하는데 소요되는 시간을 감소시킬 수 있습니다:

UNRECOVERATBLE과 NOLOGGING은 동일하지 않습니다.

주의: Oracle8의 초기버전에서는 UNRECOVERABLE 옵션을 사용할 수 있었지만, 그것은  NOLOGGING 옵션으로 대체되었습니다.

UNRECOVERABLE 절처럼 사용하기 위하여, NOLOGGING 옵션으로 객체를 생성한 후 ALTER 명령으로 LOGGING으로 변경하십시오. RECOVERABLE 절처럼 사용하기 위하여, LOGGING 옵션으로 객체를 생성하십시오.

ALTER INDEX REBUILD는 일반적으로 빠른 전체 스캔 기능을 이용하기 때문에 인덱스를 해제하고 재생성하는 것보다 훨씬 더 빠릅니다. 따라서, ALTER INDEX REBUILD는 복수 블록 I/O를 사용하여 모든 인덱스 블록을 읽은 다음 분기 블록은 삭제합니다. 이러한 접근법의 더 나은 장점은 재구축 작업이 진행 되는 동안에도 이전의 인덱스를 질의(DML에 대해서는 안됨)에 여전히 사용할 수 있다는 것입니다.

기술적 주의사항
ANALYZE 명령의 변형으로 인덱스가 손상될 경우, 인덱스를 재구축해야 합니다.


 문맥

 참조

 초기화 파라메터 

 None

 동적인 성능 뷰 

 V$SYSSTAT
 V$SESSTAT
 V$MYSTAT

 데이터 딕셔너리 뷰

 USER_, ALL_, DBA_CLUSTERS
 USER_, ALL_, DBA_INDEXES
 USER_, ALL_, DBA_TABLES
 INDEX_STATS

 명령어

 ALTER/CREATE INDEX/TABLE/CLUSTER
 ALTER INDEX … REBUILD
 TRUNCATE
 ANALYZE … COMPUTE STATISTICS
 ANALYZE … LIST CHAINED ROWS

 패키지된 프로시저 및 함수

 DBMS_SPACE

 스크립트 

 dbmsutil.sql, utlchain.sql

 진단팩 애플리케이션

 Performance Manager 

X 정답:C


X 정답:A


X 정답:D


O


X 정답:DACB


O


O