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 0TAC 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 1TAC 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;Last updated