[Oracle] Block 관련 정리 - PCTFREE & PCTUSED

DB 관련 튜닝을 공부하다 보면 쿼리 튜닝이 아닌 성능적으로 튜닝이 필요한 경우가 있다.

그럴때 마다 나오는 것이 PCTFREE / PCTUSED 다.

하지만 Block 관련된 core 부분을 대충 공부하고 넘어가면, 나처럼 정리된 것을 다시 한번 읽어 보는 경우가 생긴다.

그래서 정리한 나의 Text 문서들을 한번에 복사해서 정리해 봤다.

Block

- Block 단위로 작업 (Defualt 8K)

- DB_BLOCK_SIZE 파라미터는 Create Database 하면서 지정이 되며, Database를 재생성하기 전까지는 변경이 불가능(Standard block size)

- 혹시라도 DB_BLOCK_SIZE 외의 size로 지정하기 위해서는 Buffer Cache 에도 해당 Block size 만큼 공간을 미리 할당해 두어야 함(아니면 에러가 발생)

  ex) SQL> create tablespace test_4k datafile 'D:\ora_data\orcl\test_4k.dbf' size 5m blocksize 4k;

ERROR at line 1:

ORA-29339: tablespace block size 4096 does not match configured block sizes

SQL> alter system set db_4k_cache_size=10m; -> DB Cache에 미리 공간을 할당

Data block 구조

-- Block 정보에 대한 size 와 type 확인(Default)

select component, type, description, type_size 

from v$type_size

where component in ('KCB', 'KTB');

- Block Header

1. Transaction layer

A. Transaction layer-fixed

 - Block Type

 - 마지막으로 수행된 Block cleanout 시간

 - ITL Entries 정보

 - Free list link 정보

 - Free space lock 정보

  B. Transaction layer-variable

    - 24byte를 가짐

    - Block 에 있는 Row 를 변경하기를 원하는 실제 ITL 관련 정보 저장

      - ITL (Interested Transaction) 특정 Block 에 데이터를 변경해야 할 경우 해당 Block에 사용자 명단에 자신의 정보(TxID)를 적고 변경

        (사용하는 대기자 명단)

      - 가변성 으로써 기본으로 Table에 대한 1개 / Index에 대한 2개의 ITL 정보

      - 테이블이나 인덱스를 생성할 때 INITRANS 파라미터의 값을 변경하면 그 기본값을 변경할 수 있음

      

2. Cache layer

  - Data Block Address(DBA)

  - Block Type (ex- Table, Index, Undo..)

  - Block Format(v6, v7, v8...)

  - System Change Number(SCN: 복구 작업 등에 사용됨)

PCTFREE 와 PCTUSED

PCTFREE (Block의 Defualt 10%)

  - Block에 입력되어 있는 데이터들이 Update될 경우를 대비해 예약을 해 두는 공간(오직 Update)

  - update 했을 때 행의 크기 증가에 대비해 여유 공간을 만듦

PCTUSED (Block의 Defualt 40%)

- Block에서 row가 삭제되는 경우 Dirty List에 등록된(Free Block 에서 Dirty Block이 된) Block이, 

 Free Block 되는 상태까지 남은 용량이 얼마나 되는 지 결정하는 값

- Dirty Block이(block 들이 모여서) Free Block 의 상태까지 될 수 있는 값

  - PCTUSED 에 설정된 퍼센트 값만큼 남아서 Free space가 확보 되면 이 Block을 Free Block 으로 설정되어 Insert가 가능

※ PCTREE를 많이 주면 Update 가 많이 발생할 경우 좋지만 update가 자주 발생 안되면 비효율적인 공간이 됨

-----------------------------------------------------------------------------------------------

기본 pctfree 10 -> 5로 변경 (update 문이 적을 경우 괜찮을 듯..)

--기존 Table들에 대한 정보

select

   table_name, owner, pct_free, pct_used, num_rows

from

   dba_tables

where 

avg_row_len > 1

and 

avg_row_len < .5*&blksz

and

table_name not in

 (select table_name from dba_tab_columns b

   where

 data_type in ('RAW','LONG RAW','BLOB','CLOB','NCLOB')

 )

and owner not like '%SYS%'

order by 

   owner, 

   table_name

;

rem pctused.sql

set heading off;

set pages 9999;

set feedback off;

--보통 8k 기본(8192)

column db_block_size new_value blksz noprint

select value db_block_size from v$parameter where name='db_block_size';

define spare_rows = 2;

select

   ' alter table '||owner||'.'||table_name||

   ' pctused '||least(round(100-((&spare_rows*avg_row_len)/(&blksz/10))),95)||

   ' '||

   ' pctfree '||greatest(round((&spare_rows*avg_row_len)/(&blksz/10)),5)||

   ';'

from

   dba_tables

where 

avg_row_len > 1

and 

avg_row_len < .5*&blksz

and

table_name not in

 (select table_name from dba_tab_columns b

   where

 data_type in ('RAW','LONG RAW','BLOB','CLOB','NCLOB')

 )

and owner not like '%SYS%'  --SYS 관련 계정 제외

order by 

   owner, 

   table_name

;

Row

  1. Row Header(Overhead)

    - Row에 동시에 Transaction 을 일으키는 것을 막기 위한 Lock 정보 기록

    - Row piece에 들어있는 Column Data의 개수 기록

  2. Column Data

    - 실제로  Data가 저장되는 곳

Row Chaining

  - Block의 Column Data 부분에 모두 기록할 수 없을 경우, Row를 다른 Block 에 연결해서 저장

  - Row chaining 이 많이 일어나는 경우 Block 의 크기를 크게 해 주면 되지만 wait 현상이 많이 발생(Write complete waits, Buffer Busy Waits 등) 성능 저하 가능성

Row migration

  - 특정 Block에 위치하던 Row 가 update 등의 이유로 해당 Block 의 공간이 부족해서 다른 공간으로 이사를 가는 것을 의미

  - 해당 Block 에 빈 공간이 부족해서 일어나는 현상이므로 PCTFREE 값을 많이 주거나 테이블 재생성(Reorg) 작업을 통해 줄일 수 있음 

  - pctfree 가 큰 경우 update 가 많치 않을 경우 공간 비효율성 발생

※ I/O가 더 많이 일어나는 장점

참고 문서

 - "오라클 관리 실무" 책을 공부하면서 정리한 내용

사진 출처

http://hayleyfish.tistory.com/74