DB 디스크 공간 관리

Tibero 데이터베이스 디스크 공간 관리에 대해 설명합니다.

빈번한 Extents 발생

데이터베이스 내에 Extent가 많이 발생한 오브젝트를 확인합니다.

다음은 DB 수행 쿼리 작성 예입니다.

SELECT owner,
       segment_name, 
       segment_type, 
       tablespace_name, 
       count(*) numext,
       round(sum(bytes)/1024/1024, 1) MB 
FROM dba_extents
WHERE owner not in ('SYS','SYSCAT') 
GROUP BY segment_name,
       segment_type ,
       owner, 
       tablespace_name
HAVING COUNT(extent_id) > 40 
ORDER BY segment_type,
       round(sum(bytes)/1024/1024, 1) desc, 
       segment_name;

결과는 아래와 같습니다.

OWNER           SEGMENT_NAME                   SEGMENT_TYPE       TABLESPACE_NAME    NUMEXT         MB
--------------- ------------------------------ ------------------ --------------- ------- --- ----------
TPCC            BMSQL_ORDER_LINE_PKEY          INDEX              USR                   223        160
TPCC            BMSQL_STOCK                    TABLE              USR                   143        360
TPCC            BMSQL_ORDER_LINE               TABLE              USR                    42        350
TPCC            BMSQL_CUSTOMER                 TABLE              USR                   128        210

각 항목에 대한 설명은 다음과 같습니다.

항목	설명
OWNER	소유자 정보
SEGMENT_NAME	세그먼트 정보
SEGMENT_TYPE	세그먼트 타입
TABLESPACE_NAME	세그먼트가 사용하는 Tablespace의 이름
NUMEXT	사용하고 있는 Extent의 수
MB	현재 사용 중인 세그먼트의 사용량

Extent가 40개 이상 발생한 오브젝트를 보여줍니다.

Extent 수가 많은 것이 반드시 심각한 성능 저하로 연결되지 않지만 Extent의 잦은 실시간 할당 때문에 성능상 좋지 못합니다.

Extent가 지나치게 많이 발생한 오브젝트에 대해서는 Next Extents의 크기를 데이터 증가 수준에 맞게 조정하여 Extent가 자주 발생하지 않도록 합니다. 또한 데이터의 양이 많지 않은데 Extent의 크기만 클 경우 Reorg 등의 조치가 필요한지 검토해 봅니다.

---

Tablespace 사용량

Tablespace의 사용 현황과 Fragmentation 을 점검합니다.

다음은 DB 수행 쿼리 작성 예입니다.

SELECT  A.tablespace_name , 
        A.totbytes/1024/1024 "BYTES(MB)", 
        B.freebytes/1024/1024 "FREE(MB)",
        (A.totbytes - B.freebytes)/1024/1024 "USED(MB)",
        A.totblocks "TOTAL BLK" , 
        B.freeblocks "FREE BLK",
        A.totblocks - B.freeblocks "USED BLK"
FROM (SELECT tablespace_name,
                sum(bytes) totbytes, 
                sum(blocks) totblocks
        FROM dba_data_files
        GROUP BY tablespace_name) A, 
        (SELECT tablespace_name,
                sum(bytes) freebytes, 
                sum(blocks) freeblocks
        FROM dba_free_space
        GROUP BY tablespace_name) B
WHERE A.tablespace_name = B.tablespace_name;

결과는 아래와 같습니다.

TABLESPACE_NAME  BYTES(MB)   FREE(MB)   USED(MB) TOTAL BLK   FREE BLK   USED BLK
--------------- ---------- ---------- ---------- ---------- ---------- ----------
UNDO2                 1024        936         88     131072     119808      11264
USR                   1024    871.125    152.875     131072     111504      19568
SYSTEM                1024     920.25     103.75     131072     117792      13280
UNDO1                 1024        892        132     131072     114176      16896
SYSSUB                1024      992.5       31.5     131072     127040       4032

각 항목에 대한 설명은 다음과 같습니다.

항목	설명
TABLESPACE_NAME	Tablespace의 이름
BYTES(MB)	할당된 사이즈
FREE(MB)	사용할 수 있는 사이즈
USED(MB)	사용 중인 사이즈
TOTAL BLK	할당된 Block의 수
FREE BLK	사용할 수 있는 Block의 수
USED BLK	사용 중인 Block의 수

Free Space 공간을 확인하고 공간이 부족하다면 Tablespace의 공간을 재정의합니다.

다음은 Tablespace의 재정의 방법입니다.

• Tablespace 생성

CREATE TABLESPACE my_space
DATAFILE '/usr/tibero/dbf/my_file.tbf' SIZE 32000M AUTOEXTEND OFF 
EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

• 하나의 Tablespace에 여러 개의 데이터 파일 생성

CREATE TABLESPACE my_space2 
DATAFILE
'/usr/tibero/dbf/my_file21.dbf' SIZE 10G AUTOEXTEND OFF, 
'/usr/tibero/dbf/my_file22.dbf' SIZE 10G AUTOEXTEND OFF 
EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

• Tablespace 삭제 Tablespace는 사라지지만 구성 데이터 파일은 삭제되지 않고 남아있습니다.

DROP TABLESPACE my_space;

• Tablespace 안의 모든 데이터 삭제

DROP TABLESPACE my_space INCLUDING CONTENTS AND DATAFILES;

• Tablespace에 데이터 파일 추가

ALTER TABLESPACE my_space ADD DATAFILE 'my_file02.dbf' SIZE 10G;

• 데이터 파일의 크기 변경

ALTER DATABASE DATAFILE 'my_file02.dbf' RESIZE 100M;

---

Undo 세그먼트 사용률

Undo Tablespace(Rollback 세그먼트)의 사용률을 확인합니다.

Undo 세그먼트 조회

Single DB

다음은 Single DB 쿼리 작성 예입니다.

SELECT  dr.segment_ID, 
        dr.tablespace_name, 
        dr.status, 
        vr.extents ,
        (vr.rssize * pt.value)/1024 "rssize[K]" , 
        vr.curext,
        vr.cursize, 
        vr.xacts
FROM    dba_rollback_segs dr,
        v$rollstat vr, 
        (SELECT value 
        FROM v$parameters
        WHERE name='DB_BLOCK_SIZE') pt
        WHERE dr.segment_id=vr.usn;

결과는 아래와 같습니다.

SEGMENT_ID TABLESPACE_NAME STATUS                 EXTENTS  rssize[K]     CUREXT    CURSIZE    XACTS
---------- --------------- ---------------     ---------- ---------- ---------- ----------  ----------
        0     SYSTEM          ONLINE                   2       8184          0        511         0
        1     UNDO1           ONLINE                   5      20472          4        512         0
        2     UNDO1           ONLINE                   5      20472          4        512         0
        3     UNDO1           ONLINE                   5      20472          4        512         0
        4     UNDO1           ONLINE                   5      20472          4        512         0
        5     UNDO1           ONLINE                   5      20472          4        512         0
        6     UNDO1           ONLINE                   5      20472          4        512         0
        7     UNDO1           ONLINE                   5      20472          4        512         0
        8     UNDO1           ONLINE                   5      20472          4        512         0
        9     UNDO1           ONLINE                   5      20472          4        512         0
       10     UNDO1           ONLINE                   5      20472          4        512         0
       11     UNDO1           ONLINE                   5      20472          4        512         0

TAC DB

다음은 TAC DB 쿼리 작성 예입니다.

SELECT     vr.inst_id,
           dr.segment_ID, 
           dr.tablespace_name, 
           dr.status, 
           vr.extents ,
           (vr.rssize * pt.value)/1024 "rssize[K]" , 
           vr.curext,
           vr.cursize,
           vr.xacts
FROM       dba_rollback_segs dr, 
           GV$ROLLSTAT vr,
           (SELECT inst_id, 
                      value
           FROM gv$parameters
           WHERE name='DB_BLOCK_SIZE') pt
WHERE dr.segment_id = vr.usn
           and vr.inst_id = pt.inst_id 
ORDER BY 1;

결과는 아래와 같습니다.

INST_ID SEGMENT_ID     TABLESPACE_NAME STATUS                 EXTENTS      rssize[K]    CUREXT        CURSIZE      XACTS
---------- ---------- --------------- ---------------     ----------     ----------     --------     ---------- ----------
        1         22     UNDO2           ONLINE                   2       8184          0               511          0
        1         15     UNDO2           ONLINE                   2       8184          0               511          0
        1         13     UNDO2           ONLINE                   2       8184          0               511          0
        1         12     UNDO2           ONLINE                   2       8184          0               511          0
        1         16     UNDO2           ONLINE                   2       8184          0               511          0
        1         11     UNDO1           ONLINE                   5      20472          4               512          0
        1         10     UNDO1           ONLINE                   5      20472          4               512          0
        1         17     UNDO2           ONLINE                   2       8184          0               511          0
        1          9     UNDO1           ONLINE                   5      20472          4               512          0
        1          8     UNDO1           ONLINE                   5      20472          4               512          0
        1         18     UNDO2           ONLINE                   2       8184          0               511          0
        1          7     UNDO1           ONLINE                   5      20472          4               512          0
        1         14     UNDO2           ONLINE                   2       8184          0               511          0
~~~~
이하  생략...
~~~
INST_ID         SEGMENT_ID     TABLESPACE_NAME     STATUS                EXTENTS  rssize[K]     CUREXT  CURSIZE      XACTS
----------     ----------      ---------------     ---------------    ---------- ---------- ---------- -- -------- ----------
        2          0             SYSTEM              ONLINE                   2       8184          0       511          0
        2         10             UNDO1               ONLINE                   5      20472          4       512          0
        2          9             UNDO1               ONLINE                   5      20472          4       512          0
        2          8             UNDO1               ONLINE                   5      20472          4       512          0
        2          7             UNDO1               ONLINE                   5      20472          4       512          0
        2          6             UNDO1               ONLINE                   5      20472          4       512          0
        2          5             UNDO1               ONLINE                   5      20472          4       512          0
        2          4             UNDO1               ONLINE                   5      20472          4       512          0
        2          3             UNDO1               ONLINE                   5      20472          4       512          0
        2          2             UNDO1               ONLINE                   5      20472          4       512          0
        2          1             UNDO1               ONLINE                   5      20472          4       512          0
        2         11             UNDO1               ONLINE                   5      20472          4       512          0

각 항목에 대한 설명은 다음과 같습니다.

항목	설명
INST_ID	Tibero 인스턴스 정보 (TAC DB 질의 쿼리)
SEGMENT_ID	세그먼트 ID
TABLESPACE_NAME	Rollback(Undo) Tablespace의 이름
STATUS	세그먼트 상태 (ONLINE, OFFLINE, UNDEFINED)
EXTENTS	할당된 Extent의 수
RSSIZE(K)	세그먼트 사용량
CUREXT	현재 사용 중인 Extent
CURSIZE	현재 사용 중인 Extent의 크기
XACTS	해당 Undo 세그먼트를 배정받아서 수행 중인 트랜잭션의 수

현재 사용 중인 Undo 확인

Single DB

다음은 Single DB 쿼리 작성 예입니다.

SELECT  distinct vs.sid, 
        vs.serial#, 
        dr.segment_ID,
        DECODE(vst.command_type, 1, 'SELECT'
                                , 2, 'INSERT'
                                , 3, 'UPDATE'
                                , 4, 'DELETE'
                                , 5, 'CALL', 0) "SQL Type",
        dr.tablespace_name, 
        vt.used_blk, 
        vr.curext, 
        vr.cursize, 
        vr.xacts
FROM    dba_rollback_segs dr, 
        v$rollstat vr, 
        v$transaction vt, 
        v$session vs,
        _vt_sqltext vst
WHERE (dr.segment_id=vr.usn
             and vt.usn= vr.usn
             and dr.segment_id= vt.usn
             and vt.sess_id=vs.sid)
       and (vs.prev_sql_id=vst.sql_id
            or vs.sql_id=vst.sql_id);

결과값은 아래와 같습니다.

SID    SERIAL# SEGMENT_ID SQL Type        TABLESPACE_NAME   USED_BLK     CUREXT      CURSIZE      XACTS
-------- ---------- ---------- --------------- --------------- ---------- ---------- ---------- ----------
    134     719509          4 SELECT          UNDO1                    1          2       512          1
    147     719495         10 UPDATE          UNDO1                    1          4       512          1
    151     719503          5 SELECT          UNDO1                    1          2       512          1
    149     719499          2 UPDATE          UNDO1                    1          4       512          1
    145     719491          3 SELECT          UNDO1                    1          4       512          1
    147     719495         10 SELECT          UNDO1                    1          4       512          1
    148     719497          7 SELECT          UNDO1                    1          2       512          1
    149     719499          2 SELECT          UNDO1                    1          4       512          1
    152     719505          9 SELECT          UNDO1                    1          4       512          1
    150     719501          1 SELECT          UNDO1                    1          2       512          1
    133     719507          8 SELECT          UNDO1                    2          4       512          1
    146     719493          6 SELECT          UNDO1                    1          4       512          1

각 항목에 대한 설명은 아래의 ‘TAC DB’ 내용을 참고합니다.

TAC DB

다음은 TAC DB 쿼리 작성 예이다.

SELECT vr.inst_id, 
       vs.sid, 
       vs.serial#, 
       dr.segment_ID,
       DECODE(vst.command_type, 1, 'SELECT'
                              , 2, 'INSERT'
                              , 3, 'UPDATE'
                              , 4, 'DELETE'
                              , 5, 'CALL', 0) "SQL Type" ,
       dr.tablespace_name, 
       vt.used_blk, 
       vr.curext, 
       vr.cursize, 
       vr.xacts
FROM   dba_rollback_segs dr , 
       gv$rollstat vr , 
       gv$transaction vt , 
       gv$session vs , 
       gv$sqltext vst
WHERE ( dr.segment_id = vr.usn
           and vr.inst_id = vt.inst_id 
           and vr.usn = vt.usn
           and dr.segment_id = vt.usn 
           and vs.sid = vt.sess_id)
        and ( vs.prev_sql_id = vst.sql_id 
            or vs.sql_id = vst.sql_id);

결과값은 아래와 같습니다.

 INST_ID     SID     SERIAL#   SEGMENT_ID   SQL Type        TABLESPACE_NAME   USED_BLK    CUREXT    CURSIZE     XACTS
---------- -------- ---------- ----------   -------------   ---------------   ---------- ---------- ---------- ----------
        1      133     719507          4    SELECT          UNDO1                    1         3        512          1
        1      133     719507          4    SELECT          UNDO1                    1         3        512          1
        1      146     719493          6    UPDATE          UNDO1                    1         3        512          1
        1      146     719493          6    UPDATE          UNDO1                    1         3        512          1
        1      146     719493          6    INSERT          UNDO1                    1         3        512          1
        1      146     719493          6    INSERT          UNDO1                    1         3        512          1
        1      146     719493          6    INSERT          UNDO1                    1         3        512          1
        1      147     719495          7    UPDATE          UNDO1                    1         3        512          1
        1      147     719495          7    UPDATE          UNDO1                    1         3        512          1
        1      152     719505         10    SELECT          UNDO1                    2         0        511          0
        1      152     719505         10    SELECT          UNDO1                    2         0        511          0
        1      134     719509         11    UPDATE          UNDO1                    1         3        512          0
        1      134     719509         11    UPDATE          UNDO1                    1         3        512          0

각 항목에 대한 설명은 다음과 같습니다.

항목	설명
INST_ID	Tibero 인스턴스 정보 (TAC DB 질의 쿼리)
SID	세션 ID
SERIAL#	세션 일련번호
SEGMENT_ID	세그먼트 ID
SQL Type	사용한 SQL 타입 (SELECT, INSERT, UPDATE, DELETE, CALL)
TABLESPACE_NAME	Rollback(Undo) Tablespace 이름
USED_BLK	사용 중인 Block의 개수
CUREXT	현재 사용 중인 Extent
CURSIZE	현재 사용 중인 Extent의 크기
XACTS	해당 Undo 세그먼트를 배정받아서 수행 중인 트랜잭션의 수

Undo Tablespace가 가득 찬 경우 재사용 가능한 Extent가 있다면 재사용(steal)합니다.

Extent를 확장할 경우 확장전 재사용 할 세그먼트가 있는지 확인하고 해당하는 경우가 없다면 확장을 합니다.

Undo 세그먼트의 재사용 가능 시점은 세션에서 사용하는 TX가 Commit되면 가능하고 Commit된 정보에 대해서 Undo 이미지를 유지하기 위한 시간 설정값으로 undo_retention이 있습니다. 최대한 Undo를 보장해서 좀 더 Snapshot too old가 나지 않는 선에서 undo_retention 설정 값을 변경합니다.

Undo 세그먼트의 수동 감소

아래와 같이 USN NUMBER를 획득하여 Shrink 명령을 통해 Undo 세그먼트 감소가 가능합니다.

SELECT usn, extents, rssize, seqno, xacts, shrinks, shrink_size, incno 
FROM v$rollstat;

ALTER ROLLBACK SEGMENT <USN NUMBER> SHIRINK;

간단히 아래의 SQL문을 이용해 수행 결과를 그대로 실행해도 결과는 같습니다.

SELECT 'ALTER ROLLBACK SEGMENT' || USN || ' SHRINK;' FROM v$rollstat;