[오라클] PCTFREE와 PCTUSED

* PCTFREE란?
 - 사용가능한 Block 공간 중에서 데이터 Row의 Update 등 데이터의 변경에 대비해서 확보해 놓은 BLOCK의 %값입니다.
 - PCTFREE의 Default 값은 10%입니다.
 - PCTFREE와 PCTUSED의 합이 100을 초과하지 않는 범위내에서 0~99까지 값을 PCTFREE값을 PCTFREE 값으로 사용할 수 있습니다.

 - 위 그림은 PCTFREE = 20%인 그림입니다.
 - BLOCK의 20%를 사용가능한 빈영역으로 유지하며 빈영역은 각 BLOCK의 ROW의 UPDATE 등 데이터를 갱신하는 데 사용합니다.

* PCTFREE가 적을 경우
 - 기존 테이블 행 갱신에 의한 확장을 위해 적은 공간을 확보합니다.
 - 많은 ROW가 한 BLOCK안에 INSERT 될 수 있습니다.
 - 수정이 적은 SEGMENT에 적합합니다.

* PCTFREE가 클 경우
 - BLOCK당 적은 ROW가 INSERT됩니다. 즉 같은 ROW를 입력하기 위해 많은 BLOCK이 필요합니다.
 - ROW의 조각을 자주 CACHING할 필요가 없으므로 수행속도가 증가합니다.
 - 자주 수정되는 SEGMENT에 적합합니다.


* PCTFREE값을 20% PCTUSED값을 40%으로 설정하는 TABLE 생성

CREATE TABLE PCT_TEST(NO NUMBER, COL1 VARCHAR(2))
PCTFREE 20
PCTUSED 40
TABLESPACE TEST001;

TABLE CREATED.

* BLOCK PARAMETER 변경(PCTFREE & PCTUSED 값 변경)

SQL> select table_name, pct_free, pct_used
       from dba_tables
       where owner = 'TEST001'
       and table_name = 'PCT_TEST';


TABLE_NAME                       PCT_FREE   PCT_USED
------------------------------ ---------- ----------
PCT_TEST                               20                          

* INDEX도 사용가능

SQL> CREATE INDEX PCT_TEST_IDX01 ON PCT_TEST(NO)
  2   PCTFREE 17
  3   PCTUSED 83
  4   TABLESPACE TEST001;
PCTUSED 83
*
ERROR AT LINE 3;
ORA-02158: INVALID CREATE INDEX OPTION

-- 하지만 INDEX에서는 PCTUSED를 사용하지 못한다.

SQL> CREATE INDEX PCT_TEST_IDX01 ON PCT_TEST(NO)
  2   PCTFREE 17
  3   TABLESPACE TEST001;

INDEX CREATED.


-- 확인

SQL> SET LINES 200;
SQL> COL TABLE_OWNER FOR a10;
SQL> COL TABLE_NAME FOR a10;
SQL> col INDEX_NAME FOR a20;
SQL> SELECT TABLE_OWNER, TABLE_NAME, INDEX_NAME, PCT_FREE
  2   FROM DBA_INDEXES
  3   WHERE OWNER = 'TEST001'
  4   AND TABLE_NAME = 'PCT_TEST';


TABLE_OWNE TABLE_NAME INDEX_NAME             PCT_FREE
---------- ---------- -------------------- ----------
TEST001    PCT_TEST   PCT_TEST_IDX01               17

* INDEX PCTFREE 변경

SQL> CONN TEST001/TEST001
CONNECTED.

SQL> ALTER INDEX PCT_TEST_IDX01 REBUILD PCTFREE 20;

INDEX ALTER.

SQL> CONN / AS SYSDBA
CONNECTED.
SQL> SET LINES 200;
SQL> COL TABLE_OWNER FOR a10;
SQL> COL TABLE_NAME FOR a10;
SQL> COL INDEX_NAME FOR a20;
SQL> SELECT TABLE_OWNER, TABLE_NAME, INDEX_NAME, PCT_FREE
  2  FROM DBA_INDEXES
  3  WHERE OWNER = 'TEST001'
  4  AND TABLE_NAME = 'PCT_TEST'


TABLE_OWNE TABLE_NAME INDEX_NAME             PCT_FREE
---------- ---------- -------------------- ----------
TEST001    PCT_TEST   PCT_TEST_IDX01               20


-- INDEX에서 PCTFREE값을 변경할때는 REBUILD를 해줘야 한다.

* PCTUSED란?
 - DATA BLOCK의 FREE SPACE의 %가 PCTFREE에 도달 후, 사용된 공간이 PCTUSED
   이하의 값이 되기 전까지 해당 BLOCK에 새로운 ROW를 추가할 수 없습니다.
 - PCTFREE에 도달하기 전까지는 새로운 행을 INSERT하지 못하고 기존의 ROW를 UPDATE만 할 수 있습니다.


 - 위 그림은 PCTUSED값이 40% BLOCK의 사용영역이 39%보다 적어지지 않으면 새로운 행을 INSERT 할 수 없음을 의미합니다.


* PCTFREE 값을 20% PCTUSED 값을 40%으로 설정하는 TABLE 생성

CREATE TABLE PCT_TEST(NO NUMBER, COL1 VARCHAR(2))
    PCTFREE 20
    PCTUSED 40
    TABLESPACE TEST001;

TABLE CREATED.

* BLOCK PARAMETER 변경 및 조회(PCTFREE & PCTUSED 값 변경 및 조회)

-- PCT USED 및 PCTFREE 값

SQL> SET LINES 200;
SQL> COL OBJECT_NAME FOR a30;
SQL> SELECT t.obj#, o.object_name, t.pctfree$, t.pctused$
  2  FROM tab$ t, dba_objects o
  3  WHERE t.obj# = o.object_id
  4  AND o.owner = 'TEST001';

      OBJ# OBJECT_NAME                      PCTFREE$   PCTUSED$
---------- ------------------------------ ---------- ----------
     77463 PCT_TEST                               13         43
     76987 TEST001                                10         40
     77021 RANGE_PART_TEST002                      0          0
     77033 PART_EXCHANGE_TEST002                  10         40
     77127 HASH_PART_TEST001                       0          0
     77184 COMPOSITE_RANGE_HASH_TEST001            0          0
     77219 COMPOSITE_RANGE_LIST_TEST001            0          0
     77253 PART_IDX_TEST001                        0          0
     77284 CBC_TEST                               10         40


9 rows selected.