[ Oracle ] Shrink Space 경험

유지보수하고있는 고객사에서 겪었던 Shrink Space에 대해 포스팅할 예정이다. 

같은 Table을 같고있는 운영 유저(Lee) / 개발 유저(Lee2) 의 select count(*) 에서 시간 차이가 발생하고있다는 문의를 받아 진행하게되었다. 

두개의 Table의 Row수는 운영유저(Lee)가 2배정도 많았지만 시간은 26배 더 오래걸렸다. 

 

■ 운영유저(Lee) Count 시간

- 4G Table 34011 rows를 조회하는데 26초의 시간이 걸렸다.

##COUNT(*) 26초 걸림
SQL> SET TIMING ON;
SQL> select count(*) from T_CPN_PTN_SHP;


  COUNT(*)
----------
    34011 

Elapsed: 00:00:26.72

 

■ Table Block 확인

- 운영 유저(LEE)가 ROW에 비해 BLock 갯수가 과도하게 많은 것을 볼수있다.

- 실제 사용하는 Block을 조회하였을떄 201개를 사용하는것을 확인하였다.

SQL> select owner,TABLE_NAME, NUM_ROWS, CHAIN_CNT, BLOCKS, EMPTY_BLOCKS, AVG_SPACE, AVG_ROW_LEN FROM DBA_TABLES where table_name='T_CPN_PTN_SHP';


OWNER                          TABLE_NAME                    NUM_ROWS    CHAIN_CNT    BLOCKS  EMPTY_BLOCKS  AVG_SPACE AVG_ROW_LEN
------------------------------ ------------------------------ ---------- ---------- ---------- ------------ ---------- -----------
LEE2                           T_CPN_PTN_SHP                  16009              0        95            0          0        37
LEE                            T_CPN_PTN_SHP                  34011              0    429818            0          0        37

#실제 사용 ROW
SQL> select count(distinct dbms_rowid.rowid_block_number(rowid)) blocks from  T_CPN_PTN_SHP;

    BLOCKS
----------
      201

 

■ TABLE SHRINK 명령어

* ALTER table table_name SHRINK SPACE [COMPACT] [CASCADE];
-COMPACT 옵션이 지정된 경우 segment shrink는 1단계인 데이터 compaction까지만 수행되게 된다 Lock을 잡지 않음
-CASCADE 옵션이 지정된 경우 segment shrink는 dependent한 오브젝트들에 대해서도 자동으로 수행되게 된다. 예를 들면 테이블을 shrink하면서, 그 테이블에 대해 정의된 인덱스들 또한 자동으로 동시에 shrink할 수 있다.

* Shrink를 적용시킬 수 없는 테이블
UNDO segments
temporary segments
clustered tables
tables with a column datatype LONG
LOB Index
IOT mapping tables and IOT overflow segments
tables with MVIEWS with on commit
tables with MVIEWS which are based on ROWIDs

 

■ Table Shrink 적용

- Shrink 를 하는동안 Row가 이동하기때문에 Table의 Row Movement를 Enable 시켜주어야한다.

- shrink space compact를 하게되면 HWM가 이동하지 않는다. ( HWM가 이동하는동안에 Exclusive TM Lock 이 잡히기 떄문에 compact 부터 적용하였다.)

- 이후 HWM를 이ㅣ동시키기위해 Shrink space 명령어를 사용하였다. ( 3G 4분 소요 ) 

1.Row Movement Enable
SQL> alter table LEE.T_CPN_PTN_SHP enable row movement;
Table altered.

2.Compact
SQL> alter table LEE.T_CPN_PTN_SHP shrink space compact; 

3.Space
SQL>  alter table LEE.T_CPN_PTN_SHP shrink space;

 

■ Shrink 후 Block 확인

- 기존 429818 Block에서 208 Block 까지 줄어든 것을 확인할 수 있다.

select owner,segment_name,blocks from dba_segments where segment_name='T_CPN_PTN_SHP';
OWNER          SEGMENT_NAME         BLOCKS
-------------- -------------------- ----------
LEE            T_CPN_PTN_SHP        208
LEE2           T_CPN_PTN_SHP        96

 

■ Select Count(*)

- 1초로 속도도 준 것을 확인하였다.

SQL> select count(*) from LEE.T_CPN_PTN_SHP;

  COUNT(*)
----------
    34011
    
    
Elapsed: 00:00:1.26