문서유형ㅣ기술 정보
분야ㅣ모니터링/점검
적용제품버전ㅣ7FS02PS
문서번호ㅣTMOTI047
개요
TAC 모니터링에 필요한 정보들과 수행방법에 대한 가이드 문서입니다.
방법
제 1장 Tibero 소개
본 장에서는 Tibero 데이터베이스 기본 정보 및 TSM 정보에 대하여 설명합니다.
1.1. Tibero Instance 및 데이터베이스 정보
TAC 의 데이터베이스의 인스턴스 및 데이터베이스의 기본적인 정보를 확인하는 방법에 대한 내용입니다.
SELECT i.version,
i.instance_name ,
d.name "DB_name" ,
i.status,
ch.value "NLS_CHARACTERSET" ,
d.log_mode ,
d.create_date "DB_create_date" ,
d.current_tsn ,
d.ckpt_tsn ,
l.edition,
l.expire_date
FROM gv$instance i,
v$database d,
_vt_license l,
(SELECT value
FROM _vt_version
WHERE name='BUILD_NUMBER') bd ,
(SELECT value
FROM _dd_props
WHERE name='NLS_CHARACTERSET') ch;
VERSION INSTANCE_N DB_name STATUS NLS_CHARACTERSET LOG_MODE DB_create_date CURRENT_TSN CKPT_TSN EDITION EXPIRE_DATE
-------- ---------- --------------- ---------------- ---------------- ------------ --------------- ----------- ---------- ---------------- -----------
7 tibero0 tac NORMAL UTF8 ARCHIVELOG 2026/03/25 12523168 12408253 enterprise 2026/06/16
7 tibero1 tac NORMAL UTF8 ARCHIVELOG 2026/03/25 12523168 12408253 enterprise 2026/06/16
| 항목 | 설명 |
|---|---|
| VERSION | Tibero의 빌드 번호입니다. |
| INSTANCE_NAME | 데이터베이스의 이름입니다. |
| DB name | 데이터베이스를 생성했을 때의 SID 입니다. (컨트롤 파일에 적혀있는 데이터베이스의 이름으로 항상 인스턴스 이름과 동일) |
| STATUS | Tibero 서버의 현재 Boot 모드 상태입니다. (NOMOUNT, MOUNT, NORMAL 등) |
| NLS_CHARACTERSET | 데이터베이스에 자료가 저장될 때 사용되는 Encoding 입니다. Create Database 구문 에서 결정합니다. (Characterset UTF-8, MS949(EUC-KR), ASCII 등을 지원함) |
| LOG_MODE | Redo Log를 Archive 여부를 확인가능한 항목입니다. |
| DB create_date | 데이터베이스의 생성 날짜를 의미 합니다. |
| CURRENT_TSN | 현재의 TSN 값입니다. |
| CKPT_TSN | 마지막으로 체크포인트가 일어난 시간입니다. (=TSN) |
| EDITION | 라이선스 에디션 정보입니다. |
| EXPIRE_DATE | 라이선스의 만료 날짜입니다. |
1.2. TAC DB TSM(Tibero Shared Memory) 정보
SELECT Inst_id,
TSM as "TSM(Tibero Shared Memory)"
FROM (SELECT Inst_id,
'TSM(Tibero Shared Memory) : '||total/1024 || ' K' as TSM
FROM gv$sga
WHERE name = 'SHARED MEMORY'
UNION ALL
SELECT Inst_id,
'Shared Cache Size : '||ROUND(total/1024, 0) || ' K' as TSM
FROM gv$sga
WHERE name = 'SHARED POOL MEMORY'
UNION ALL
SELECT inst_id,
'Database Buffer Size : '||value/1024 || ' K' as TSM
FROM GV$PARAMETERS
WHERE name='DB_CACHE_SIZE'
UNION ALL
SELECT inst_id,
'DB Block Size : '||value/1024 || ' K' as TSM
FROM GV$PARAMETERS
WHERE name = 'DB_BLOCK_SIZE'
UNION ALL
SELECT inst_id,
'Redo Log BUffer Size : '||value/1024 || ' K' as TSM
FROM GV$PARAMETERS
WHERE name = 'LOG_BUFFER'
UNION ALL
SELECT inst_id,
'WPM(Working Process Memory) : ' || ROUND(value/1024, 0) || ' K'
FROM GV$PARAMETERS
WHERE name='EX_MEMORY_HARD_LIMIT')
ORDER BY 1;
해당 쿼리를 수행하면 결과는 아래와 같이 TSM 확인 가능합니다.
INST_ID TSM(Tibero Shared Memory) ------- --------------------------------------------------------------- 1 TSM(Tibero Shared Memory) : 15728640 K 1 Shared Cache Size : 6727944 K 1 Database Buffer Size : 7864320 K 1 DB Block Size : 8 K 1 Redo Log Buffer Size : 10240 K 1 WPM(Working Process Memory) : 0 K 2 TSM(Tibero Shared Memory) : 15728640 K 2 Shared Cache Size : 6727944 K 2 Database Buffer Size : 7864320 K 2 DB Block Size : 8 K 2 Redo Log Buffer Size : 10240 K 2 WPM(Working Process Memory) : 0 K
| 항목 | 설명 |
|---|---|
| TSM(Tibero Shared Memory) | Tibero에서 사용하는 전체 공유 메모리 크기로 Init 파라미터에서 조정합니다. (TOTAL_SHM_SIZE)TSM = 공유 Cache 크기 + 데이터베이스 버퍼 크기 + 고정 메모리 (고정 메모리 : 최초 전역변수나 Thread 개수 등에 따라 사이즈가 결정되며 Runtime에 결정) |
| Shared Cache Size | Tibero에서 사용하는 공유 메모리(공유 Pool : 라이브러리 Cache, DDCache)로 각종 용도에 사용됩니다. 공유 Cache 크기는(TSM - 고정 메모리 - 데이터베이스 버퍼 크기)로 결정됩니다. 공유 Cache와 라이브러리 Cache의 영역 제한은 없습니다. |
| Database Buffer Size | 데이터베이스 버퍼 Cache 크기로 환경 파일에서 DB_CACHE_SIZE로 조정이 가능합니다. |
| DB Block Size | 데이터베이스 Block의 크기로 Init 파라미터에서 조정(DB_BLOCK_SIZE)이 가능하며 Create Database 이후엔 변경이 불가능합니다. |
| Redo Log Buffer Size | Redo Log를 저장하는 메모리 공간의 크기를 지정하며 Init 파라미터에서 조정(LOG_BUFFER)이 가능합니다. |
| WPM(Working Process Memory) | Tibero에서 사용할 수 있는 물리 메모리의 총 크기를 말하며 Init 파라미터에서 조정(EX_MEMORY_HARD_LIMIT)이 가능합니다. |
제 2장 메모리
본 장에서는 Tibero에서 사용되는 메모리 정보에 대해서 설명합니다.
2.1. 데이터베이스 버퍼 Cache 적중률
데이터베이스 버퍼 Cache의 적중률을 점검합니다.
데이터베이스 버퍼의 적중률이란 사용자가 액세스한 메모리 Block 가운데 이미 Cache가 되어있어 물리적 I/O 없이 액세스 할 수 있는 Block의 비율을 나타냅니다.
만약 데이터베이스 버퍼의 적중률이 권장값 미만일 경우에는 할당된 데이터베이스 버퍼의 크기가 너무 적거나 지나치게 많은 I/O를 유발하는 애플리케이션이 존재한다는 것을 의미합니다.
2.1.1 TAC DB
다음은 TAC DB 쿼리 작성 예입니다.
SELECT ins.inst_id,
pr1.value + pr2.value "Physical read",
bg1.value+bg2.value+bg3.value+bg4.value+bg5.value "Logical read" ,
TRUNC((1-((pr1.value+pr2.value)/ (bg1.value+bg2.value+bg3.value+bg4.value+bg5.value)) ) *100, 2)
"hit(MORE THAN 60-70%)"FROM (select distinct inst_id
from gv$sysstat
) ins,
gv$sysstat pr1,
gv$sysstat pr2,
gv$sysstat bg1,
gv$sysstat bg2,
gv$sysstat bg3,
gv$sysstat bg4,
gv$sysstat bg5
WHERE pr1.name = 'block disk read'
nd pr2.name = 'multi block disk read - blocks'
and bg1.name = 'consistent block gets'
and bg2.name = 'consistent multi gets - blocks'
and bg3.name = 'block gets (CRX)'
and bg4.name = 'current block gets'
and bg5.name = 'current block gets - no wait'
and pr1.inst_id = ins.inst_id
and pr2.inst_id = ins.inst_id
and bg1.inst_id = ins.inst_id
and bg2.inst_id = ins.inst_id
and bg3.inst_id = ins.inst_id
and bg4.inst_id = ins.inst_id
and bg5.inst_id = ins.inst_id;결과
INST_ID Physical read Logical read hit(MORE THAN 60-70%) ---------- ------------- ------------ --------------------- 101 556 4799 88.41 102 415 5621 92.61
각 항목에 대한 설명은 아래와 같습니다.
| 항목 | 설명 |
|---|---|
| INST_ID | Tibero 인스턴스의 번호 입니다. - 기동순서에 따라 지정됩니다. - v$instacne 의 instance_number 컬럼 정보와 구분됩니다. |
| Physical read | 데이터 Block을 디스크에서 읽어오는 횟수입니다. |
| Logical read | 데이터 Block을 버퍼 Cache에 요청한 횟수입니다. |
| hit | 버퍼 캐시의 적중률로 계산법은 ( 1 - physical read / logical read) * 100 입니다. |
데이터베이스 버퍼의 적중률은 OLTP, DSS, DW 등 데이터베이스를 사용하는 애플리케이션의 특성에 따라 권장값이 서로 다릅니다.
일반적으로 OLTP 시스템은 90% 이상, DSS나 DW 시스템은 80~85% 이상이면 양호하다고 할 수 있습니다. 다만, 순수하게 Batch 처리 업무 위주의 시스템인 경우에는 적중률이 50% 이하이더라도 문제가 있다고 단정할 수는 없습니다.
데이터베이스 버퍼의 적중률이 기준값보다 낮은 경우에는 O/S의 메모리 여유량을 확인하여 메모리 부족으로 인한 Paging 및 Swapping이 발생하지 않는 범위 내에서 tip 파일의 DB_CACHE_SIZE 값을 늘려 데이터베이스 버퍼의 크기를 증가시킬 수 있습니다.
그러나 현실적으로 디스크상의 모든 데이터 Block을 Cache하는 것은 불가능하므로, 일반적으로 데이터베이스 버퍼의 크기를 증가시키는 것이 최선의 해결책이 될 수는 없음을 고려해야 합니다.
특히, 다량의 디스크 Block을 빈번하게 Full Scan하는 애플리케이션이 존재하는 경우에는 적중률을 높은 수준으로 유지하는 것이 불가능하므로, 액세스 패턴이 비효율적인 애플리케이션의 요청을 추출하여 지속적인 튜닝을 통해 관리해야 합니다.
2.2. SQL Cache 적중률
SQL Cache 적중률을 확인하는 방법에 대한 내용입니다.
2.2.1 TAC DB
아래 쿼리를 통해 TAC 노드별로 SQL Cache 적중률을 확인 할 수 있습니다.
SELECT inst_id,
namespace,
gets,
gethits,
gethitratio,
pins,
pinhits,
pinhitratio
FROM GV$LIBRARYCACHE
WHERE namespace = 'SQL AREA'결과
INST_ID NAMESPACE GETS GETHITS GETHITRATIO PINS PINHITS PINHITRATIO ------- --------- ------- ------- ----------- ------- ------- ----------- 2 SQL AREA 611,279 608,470 99.54 618,230 618,230 100 1 SQL AREA 595,924 595,391 99.91 595,395 595,395 100
각 항목에 대한 설명은 다음과 같습니다.
| 항목 | 설명 |
|---|---|
| INST_ID | Tibero 인스턴스의 번호 입니다. - 기동순서에 따라 지정 - v$instacne 의 instance_number 컬럼 정보와 구분됩니다. |
| NAMESPACE | Library Cache, DD Cache의 항목들입니다. (SQL AREA : Library Cache) |
| GETS | Cache의 총 접근 횟수를 의미합니다. |
| GETHITS | Cache의 적중 횟수를 의미합니다. |
| GETHITRATIO | Cache의 적중률의미 합니다. 계산법은 (PINHITS / PINS) * 100 입니다. |
| PINS | Cache에 있는 객체에 대한 요청을 PIN 한 횟수입니다. |
| PINHITS | Cache에 이미 PIN 되어진 객체의 HIT 횟수입니다. |
| PINHITRATIO | PIN 수로 나눈 PINHITS 수의 비율로 PIN CHACH HIT 비율을 의미합니다. 1에 가까운 결과가 나올수록 |
데이터 Dictionary Cache의 적중률은 90% 이상 유지할 것을 권장합니다.
적중률이 기준값 이하일 때 초기화 파라미터 TOTAL_SIZE와 DB_CACHE_SIZE의 비율을 조정하여 공유 Cache의 크기를 증가시켜 적중률을 높일 수 있습니다.
2.3. TAC DB Dictionary Cache 적중률
Dictionary Cache 적중률을 확인합니다.
다음은 TAC DB 쿼리 작성 예입니다.
SELECT INST_ID,
TO_CHAR(sysdate, 'yyyy/mm/dd hh24:mi:ss') "Current Time",
ROUND( ( sum(hit_cnt) - sum(miss_cnt) ) / sum(hit_cnt) * 100, 1)
"Dictionary Cache Hit Ratio(%)"FROM gv$rowcache
GROUP BY INST_ID;결과는 아래와 같습니다.
INST_ID Current Time Dictionary Cache Hit Ratio(%) ------- ------------------- ----------------------------- 1 2026/04/10 14:53:25 99.9 2 2026/04/10 14:53:25 99.8
각 항목에 대한 설명은 다음과 같습니다.
| 항목 | 설명 |
|---|---|
| INST_ID | Tibero 인스턴스의 번호 입니다. - 기동순서에 따라 지정됩니다. - v$instacne 의 instance_number 컬럼 정보와 구분됩니다. |
| Current Time | 해당 SQL을 수행한 시간을 의미합니다. |
| Hit Ratio | 데이터 Dictionary Cache의 적중률을 의미합니다. |
데이터 Dictionary Cache는 데이터 Dictionary 정보를 Cache하는 영역으로, 공유 Cache에서 할당됩니다. Dictionary Cache의 크기는 사용자가 별도로 지정할 수 없으며, Tibero는 공유 Cache에서 Dictionary Cache를 우선적으로 할당하므로, 라이브러리 Cache의 적중률이 양호하다면 Dictionary Cache의 적중률 또한 양호한 수준을 유지합니다.
Dictionary Cache의 적중률은 인스턴스가 기동된 후 어느 정도 시간이 경과한 이후에야 의미가 있으며, 적중률은 90% 이상 유지하는 것을 권장합니다.
적중률이 기준값 이하인 경우에는 초기화 파라미터인 TOTAL_SHM_SIZE와 DB_CACHE_SIZE의 비율을 조정하여 공유 Cache의 크기를 증가시킴으로써 적중률을 높일 수 있습니다.
2.4. TAC DB 공유 Cache의 Free 메모리
TAC 공유 Cache의 Free 메모리 크기를 확인하는 방법에 대한 내용 입니다.
TAC DB 공유 Cache의 Free 메모리 크기 조회 쿼리 예시 입니다.
SELECT inst_id,
round(total/1024/1024, 1) "Shared Cache Total (MB)",
round(used/1024/1024, 1) "Used (MB)",
round((total - used)/1024/1024, 1) "free (MB)"FROM gv$sga
WHERE name='SHARED POOL MEMORY';결과
INST_ID Shared Cache Total (MB) Used (MB) Free (MB) ---------- ----------------------- ---------- ---------- 101 2704.6 75.2 2629.4 102 2704.6 74.3 2630.3
각 항목에 대한 설명은 다음과 같습니다.
| 항목 | 설명 |
|---|---|
| INST_ID | Tibero 인스턴스의 번호 입니다. - 기동순서에 따라 지정 됩니다. - v$instacne 의 instance_number 컬럼 정보와 구분됩니다. |
| Shared Cache Total | 전체 공유 Cache의 메모리 사이즈를 의미합니다. |
| Used | 현재 사용 중인 메모리의 양입니다. |
| free | 가용한 메모리의 사용량입니다. |
공유 Cache 영역은 라이브러리 Cache와 Dictionary Cache로 구성됩니다.
공유 Cache는 기본적으로 Cache를 위한 영역이므로, 여유 공간이 남아 있는 한 기존 오브젝트를 Cache에 유지하며 계속해서 사용합니다.
따라서 라이브러리 Cache나 Dictionary Cache의 적중률이 양호한 경우(90% 이상)에는 일반적인 운영 시간대에 Free 메모리 크기가 낮은 수치를 보이는 것이 정상입니다.
반대로 Free 메모리가 항상 큰 규모로 남아 있다면 공유 Cache가 불필요하게 크게 설정되어 있음을 의미합니다. 또한 일시적으로 Free 메모리 크기가 갑자기 증가하는 경우에는 Fragmentation이 많이 발생하여 많은 오브젝트가 한꺼번에 Flush된 것을 의미하므로, 시스템에 문제가 발생하고 있음을 나타내는 증상일 수 있습니다.
공유 Cache의 메모리 Fragmentation을 예방하기 위해서는 크기가 큰 PSM Block의 사용을 자제하고, Literal SQL 문의 경우 바인드 변수를 사용하여 Fragmentation을 예방할 수 있습니다.
제3장 Tibero 대기 이벤트
Tibero에서 발생하는 대기 이벤트 정보에 대해서 설명합니다.
3.1. V$SYSTEM_EVENT
Tibero는 서버 프로세스와 백그라운드 프로세스가 작업을 처리하는 과정에서 발생하는 대기 현상을 측정하기 위해 다양한 대기 이벤트를 정의하고, 이를 기반으로 프로세스가 작업을 수행하도록 합니다. 이 과정에서 대기 이벤트가 발생할 때마다 발생 횟수와 대기 시간이 내부에 저장되며, 이를 조회할 수 있습니다.
v$system_event 뷰는 인스턴스가 기동된 이후 현재까지 누적된 이벤트 발생 현황을 시스템 레벨에서 확인할 때 사용할 수 있습니다.
개별 세션별 누적 정보를 확인하려면 v$session_event 뷰를 사용하면 되며, 세션 레벨에서 현재 진행 중이거나 직전에 발생한 대기 이벤트 정보를 확인하기 위해서는 v$session_wait 뷰를 사용하면 됩니다.
3.1.1 TAC DB
다음은 TAC DB 에서 Tibero 이벤트를 조회하는 쿼리 예시입니다.
SELECT inst_id,
name, total_waits,
time_waited/1000000 "time_waited",
average_wait/1000000 "average_wait",
total_timeouts
FROM gv$system_event
WHERE Total_waits>0
ORDER BY inst_id, time_waited DESC ;결과
INST_ID NAME TOTAL_WAITS time_waited average_wait TOTAL_TIMEOUTS ------- ------------------------- ------------- ------------ -------------- ------------- 1 WE_ACF_CMPT 1902 5.854036 .003077832 0 1 WE_CONN_IDLE 522 1.556958 .002982678 0 1 WE_ACF_ATH 720 .499886 .000694286 0 1 WE_ACF_GC 462 .473068 .001023957 462 1 WE_DBWR_IDLE 2497 .46998 .000188218 2477 1 WE_NMON_IDLE 232 .23756 .001023966 232 1 WE_NTF_SNDR_IDLE 232 .237513 .001023763 232 1 WE_TPM_COLLECTOR_IDLE 232 .237505 .001023728 232 1 WE_TASK_MGR_IDLE 234 .236753 .001011765 231 1 WE_ACF_RECONF 49 .235883 .004813939 46 1 WE_ACTM_IDLE 23 .235396 .010234609 23 1 WE_LGWR_IDLE 94 .235207 .002502202 77 1 WE_LGWR_SLAVE 95 .235205 .002475842 0 1 WE_RSRC_IDLE 78 .23469 .003008846 78 1 WE_CKPT_IDLE 231 .234052 .001013212 229 1 WE_TPM_SENDER_IDLE 7 .211622 .030231714 7 1 WE_LARC_IDLE 8 .211508 .0264385 7 1 WE_WTHR_RECV 9 .105829 .011758778 0 1 WE_BG_READY 17 .017796 .001046824 0 1 WE_JOB_SCHED_IDLE 4 .003438 .0008595 3 1 WE_ACF_CRAS 3 .003133 .001044333 0 1 WE_ACF_RCF 16 .002916 .00018225 0 ..... 2 WE_GV_REPLY 5132 .000007 0 0 2 WE_CCC_AST_CR 2631 .000003 0 0 2 WE_SPIN_SHP_ALLOC_LC 10 .000002 0 0 2 WE_WLOCK_SEQ_GET_NEXTVAL 1 .000001 0 0 2 WE_WLOCK_EXPAND_LKBSET 1 .000001 0 0 2 WE_GV_REQ 1277 .000001 0 0 2 WE_CR_BUF_BUSY_LOCAL 1 0 0 0 2 WE_PE_DEQ 411 0 0 0 2 WE_JC_SSGMT_READ_TIME 14 0 0 0 2 WE_ACF_GMC 289 0 0 0 2 WE_LOG_FLUSH_REQ 1 0 0 0 2 WE_SEARCH_SPACE_REPLY 13 0 0 0
각 항목에 대한 설명은 다음과 같습니다.
| 항목 | 설명 |
|---|---|
| INST_ID | Tibero 인스턴스의 번호 입니다. - 기동순서에 따라 지정됩니다. - v$instacne 의 instance_number 컬럼 정보와 구분됩니다. |
| TOTAL_WAITS | 해당 이벤트가 호출된 총 횟수입니다. |
| time_waited | 해당 이벤트에서 대기한 시간입니다. (단위 : 초) |
| Average_wait | 해당 이벤트에서 한번에 대기한 평균 시간입니다. (단위 : 초) |
| Total_Timeout | 해당 이벤트에 Timeout이 설정이 되어 있을 때 Timeout 발생 횟수입니다.. |
대표적인 주요 이벤트는 아래와 같습니다.
| 이벤트 | 설명 |
|---|---|
| WE_SEQ_WRITEBACK | Agent가 Seq를 Writeback할 때까지 기다리는 이벤트 입니다. |
| WE_SEQ_FREESLOT | Seq_Buffer_의 Free Slot이 없을 때까지 기다리는 이벤트 입니다. |
| WE_SEQ_NEXTVAL | Agent가 New Value를 Cache에 올릴 때까지 기다리는 이벤트 입니다. |
| WE_BUF_WAIT | Buf Pin을 위해 기다리는 이벤트 입니다. |
| WE_BUF_WRITE | Buf Write까지 기다리는 이벤트 입니다. |
| WE_BUF_FREE | Free Buf가 생길 때까지 기다리는 이벤트 입니다. |
| WE_MBR_WAIT | MBR이 끝날 때까지 기다리는 이벤트 입니다. |
| WE_SEQ_FLUSH | Seq alba가 Cache Flush를 마칠 때까지 기다리는 이벤트 입니다. |
| WE_WTHR_READY | WTHR Ready까지 기다리는 이벤트 입니다. |
| WE_LGWR_ARCHIVE | LGWR Process가 Archive 완료할 때까지 기다리는 이벤트 입니다. |
| WE_LGWR_LNW | LGWR Process가 네트워크를 통해 로그를 쓸 때까지 기다리는 이벤트 입니다. |
| WE_LOG_FLUSH | Log Flush 할 때까지 기다리는 이벤트입니다. |
| WE_LOG_FLUSH_SPACE | 공간이 부족해서 Log Flush할 때까지 기다리는 이벤트입니다. |
| WE_LOG_FLUSH_REQ | Log Flush를 요청해서 기다리는 이벤트입니다. |
| WE_CKPT_WAIT | Checkpoint를 기다리는 이벤트입니다. |
| WE_PROC_DOWN | Shutdown Finish되는 경우 발생하는 이벤트입니다. |
| WE_WTHR_START | WTHR가 시작되기 위해서 메시지를 받을 때까지 기다리는 이벤트입니다. |
| WE_WTHR_MSG | WTHR가 클라이언트의 MSG를 기다리는 이벤트입니다. |
| WE_ACF_SCVR | SCVR(scavenger)에서 CCC/CWS RSB(Resource Block)를 reclaim 요청 받을 때까지 기다리는 이벤트입니다. 만약 reclaim 요청이 없더라도 주기적 (_ACF_SCVR_TIMEOUT(기본값 : 1초))으로 깨어나서 reclaim을 수행합니다. |
| WE_ACF_AST | WTHR가 AST(asynchronous system trap)인 경우 이벤트입니다. |
Waiting은 실제 작업의 성능을 저하시키므로, 가장 많이 발생하는 대기 이벤트를 우선적으로 분석하고, 각 대기 이벤트의 특성에 따라 Waiting이 많이 발생하는 원인을 해결해야 합니다.
또한 모든 wlock 관련 정보는 대기 이벤트로 나타나므로, wlock Contention을 분석하는 데 활용할 수 있습니다.
이벤트 예시
다음은 여러 세션이 특정 Block에 지속적으로 업데이트를 하면서 동시에 계속 Commit을 수행하고 있을 때 WE_LOG_FLUSH_COMMIT 이벤트가 많이 발생하는 예입니다.
v$session_wait 쿼리를 조회해 보면 다음과 같습니다.
[그림 3.1] V$session_wait 수행 결과
V$system_event 수행결과를 통해 해당 이벤트가 255471번 호출된 것을 아래와 같이 확인할 수 있습니다.
[그림 3.2] V$system_event 수행 결과
Commit 횟수를 줄이면 WE_LOG_FLUSH_COMMIT 이벤트가 호출된 횟수 또는 Waiting한 시간이 크게 개선된 것을 확인할 수 있습니다.
[그림 3.3] WE_LOG_FLUSH_COMMIT 개선 효과
WE_LOG_FLUSH_COMMIT 이벤트가 감소한 만큼 Hot Block 경합으로 인해 WE_BUF_WAIT 이벤트가 발생하는 것을 확인할 수 있습니다.
WE_BUF_WAIT는 Buffer Pinning을 위해 대기하는 이벤트로, 동일한 Block에 대해 동시에 업데이트가 수행되는 경우 발생하는 이벤트입니다.
3.2. V$SYSSTAT
인스턴스가 기동된 이후 현재까지 누적된 수행 통계치를 시스템 레벨에서 확인할 때 사용하는 View는 v$sysstat이며, 개별 세션별 수행 통계치를 확인할 때 사용하는 View는 v$sesstat입니다. 또한 현재 접속 중인 자신의 세션에 대한 수행 통계는 v$mystat을 통해 확인할 수 있습니다.
3.2.1 TAC DB
다음은 TAC DB 쿼리 작성 예입니다.
SELECT * FROM gv$sysstat order by inst_id, value desc;
결과값은 아래와 같습니다.
INST_ID STAT# NAME CLASS VALUE ------- ------ ---------------------------------------------- ----- --------- 1 2527 Total Times sleeps to wait acf task ready 5 21164597 1 7116 INC messages received size 5 4622210 1 7121 INC packets received size 5 4622210 1 6742 parse time elapsed 4 1483226 1 6747 hard parse elapsed time 4 1477528 1 7141 INC packets sent size 5 963718 1 7136 INC messages sent size 5 799709 1 2037 optimizer time 4 797031 1 2472 Total Times sleeps to wait ctx sync 5 713767 1 32 block disk read time 17 605534 1 6847 optimizer loading statistics time 4 595535 1 4842 dd search time 4 574467 1 4847 dd search - load time 4 560479 1 3292 optimizer dynamic sampling time 4 502320 1 8002 current block received RTT 5 486245 1 1632 transactions total time 7 473131 1 3297 optimizer dynamic sampling block read time 4 442447 1 622 redo write time 2 354457 1 2547 Total Round Trip Times to grant lock 5 342502 1 297 dbwr write time 7 324160 1 292 dbwr write time - OS 2 322394 1 9847 dbwr write suspend time 7 319952 1 7552 dbwr aio slave bitq read time 7 312180 1 5827 current block received RTT:data block 5 301297 1 4532 isgmt get cr time 3 255159 1 7742 INC messages passed to CMPT - time 5 252813 1 6892 INC messages sent without block time 5 243157 1 4527 dsgmt get cr time 4 242525 1 4537 isgmt get cr in lvl time 3 240762 1 4217 Total CR blocked time by CUR request 5 234883 1 4507 tdi fetch start time 3 221763 1 4897 Total Round Trip Times to grant wait-lock 5 200031 1 6757 SQL execute elapsed time 4 197059 1 4412 profile check time 7 171434 1 4427 total times to begin tx 7 161552 1 2567 csr fetch select time 4 150829 1 4442 total times to get undo block and apply 7 150210 1 4447 total times to get undo block 7 148789 1 7782 Total Times sleeps to wait WRCF status 5 122150 1 5837 current block received RTT:index leaf block 5 90114 1 7772 Total Times sleeps to wait CRCF status 5 83969 1 617 redo wait time for flush time 1 79085 1 7137 INC messages sent time 5 79046 1 7752 INC messages sent from send queue - time 5 75017 1 8857 INC messages received by batch - time 5 57922 1 9632 PSM SQL processing time for cursor open 7 52662 1 9662 PSM SQL processing time 7 52642 1 1687 req service time 7 51710 1 1692 SQL processing time 4 51695 1 8827 Inner SQL processing time 4 51336 1 9767 stat load query soft parse time elapsed 4 50949 1 9752 stat load query hard parse elapsed time 4 49593 1 7767 Total Times to process CCC message 5 41603 1 596 redo log size 1 39491 1 5822 current block received RTT:segment header 5 36180 1 6762 PSM execution elapsed time 0 35483 1 11097 index scan time 4 35305
각 항목에 대한 설명은 다음과 같습니다.
| 항목 | 설명 |
|---|---|
| INST_ID | Tibero 인스턴스 정보입니다. (TAC DB 질의 쿼리) |
| STAT# | Stat 번호입니다. |
| NAME | Stat 이름입니다. |
| VALUE | Stat 값입니다. |
v$sysstat와 v$sesstat에 나타나는 값은 인스턴스가 기동된 이후 또는 세션이 수립된 이후 현재까지 누적된 값이므로, 단순히 값의 크고 작음만으로는 의미 있는 정보를 얻기 어렵습니다.
이를 효과적으로 활용하기 위해서는 두 시점 간의 변화량을 구하여 SQL 수행 도중 내부적으로 어떤 작업이 발생했는지를 분석해야 합니다.
3.3. TAC DB Spin Lock Contention
TSM(Tibero Shared Memory)의 모든 Spin Lock에 대한 적중률을 확인합니다.
다음은 TAC DB 쿼리 작성 예입니다.
SELECT INST_ID,
name "Name",
SUM(fast_cnt + try_get_cnt) as "Gets",
SUM(spin_cnt + sleep_cnt + try_fail_cnt) as "Misses",
SUM(sleep_cnt) as "Sleeps",
round((1 - (SUM(spin_cnt + sleep_cnt + try_fail_cnt)
/ SUM(fast_cnt + spin_cnt + sleep_cnt + try_get_cnt + try_fail_cnt)))
* 100, 3) as "Hit Ratio"FROM _gvt_spinlock
GROUP BY INST_ID,
type,
spin_level,
name
HAVING SUM(fast_cnt + try_get_cnt) > 0ORDER BY 2,
5 DESC,
3 DESC;결과
INST_ID Name Gets Misses Sleeps Hit Ratio ------- ------------------------------ ------- ---------- -------- ---------- 1 SPIN_SHP_ALLOC_SLAB 3927 49 2 98.768 1 SPIN_BUF_BUCKET 206134 58 1 99.972 1 SPIN_LC_BUCKET 51315 1 1 99.998 1 SPIN_SHP_ALLOC_MISC 9636 60 1 99.381 1 SPIN_ROOT_ALLOC 7419 9 1 99.879 1 SPIN_BUF_WS 189376 0 0 100 1 SPIN_BUF_WS_CKPT 174230 0 0 100 1 SPIN_ALLOC 45179 15 0 99.967 1 SPIN_SQLSTATS 24092 1 0 99.996 1 SPIN_RECR_UNPIN 16527 0 0 100 1 SPIN_BITQ 16014 0 0 100 1 SPIN_L1CL 11200 0 0 100 1 SPIN_PARAM 5012 16 0 99.682 1 SPIN_DD_CACHE_BUCKET 4594 14 0 99.696 1 SPIN_WLOCK 4194 28 0 99.337 1 SPIN_SESS_CSR_POOL 3065 0 0 100 1 SPIN_ENABLED_ROLES 2061 0 0 100 1 SPIN_DBWR_AIOQ 1449 0 0 100 1 SPIN_SLAB_ALLOC 1371 0 0 100 1 SPIN_ALLOC_LRU 1064 0 0 100 1 SPIN_PP_QSTAT 791 1 0 99.874 1 SPIN_FD_BUCKET 704 0 0 100 1 SPIN_GLOBAL_MEM_MGR 551 0 0 100 1 SPIN_SC_ALLOC 547 0 0 100 1 SPIN_FIXED_SYS_MEM_INFO 527 0 0 100 1 SPIN_SHP_ALLOC_DD 405 0 0 100 1 SPIN_SESS_TSAM_HNDL_POOL 405 0 0 100 1 SPIN_SESS_LTIME 384 0 0 100 1 SPIN_CF 368 0 0 100 1 SPIN_REDO_ALLOC 337 0 0 100 1 SPIN_IMCS_PARAM 336 0 0 100 1 SPIN_GV_PARAM 336 0 0 100 1 SPIN_REDO_COPY 264 0 0 100 1 SPIN_WTHR_READY 240 0 0 100 1 SPIN_CKPT_PROGRESS 166 0 0 100 1 SPIN_SHP_ALLOC_LC 142 0 0 100 1 SPIN_CL_CONN 122 0 0 100 1 SPIN_USGMT_HASH 122 0 0 100 1 SPIN_PROC_MEM_MGR 116 0 0 100 1 SPIN_TSGMT_LIST 116 0 0 100 1 SPIN_SESS_SERIAL_NO 114 7 0 94.215 1 RW_SYSCTX 112 0 0 100 1 SPIN_ALERT 112 0 0 100 1 SPIN_PE_SESS 112 0 0 100 1 SPIN_MAIO_POOL 112 0 0 100 1 SPIN_TEMP_TS_ID 110 0 0 100 1 SPIN_LISTENER 107 0 0 100 1 SPIN_SERVER 107 0 0 100 1 SPIN_IMCS_REQ_LIST 96 0 0 100 1 SPIN_TSN_TIME_MAP 95 1 0 98.958 1 SPIN_DBWR_IOSLOT 80 0 0 100 1 SPIN_FDPOOL 53 0 0 100 1 SPIN_ASH_CSR_READ 53 0 0 100 1 SPIN_DBWR_LOW 52 0 0 100 1 SPIN_DBWR_IR_STATUS 50 0 0 100 1 SPIN_PP_EXPCN_LIST 48 0 0 100 1 SPIN_PPID 48 0 0 100 1 SPIN_XA_VTI 39 0 0 100 1 SPIN_SVRMODE_ACK 36 0 0 100 1 SPIN_BUF_GLOBAL_RECOQ 29 0 0 100 1 SPIN_REDO_WRITING 26 0 0 100 1 SPIN_USGMT_CACHE 25 0 0 100 1 SPIN_XQC 20 0 0 100 1 SPIN_LGWR_AIO_REQ 16 0 0 100 1 SPIN_DML_STAT_BUCKET 15 0 0 100 1 SPIN_TXR 14 0 0 100 1 SPIN_BUDDY_ALLOC 13 0 0 100 1 SPIN_ALLOC_POOL 12 0 0 100 1 SPIN_MSG_LIST 10 0 0 100 1 SPIN_SHP_ALLOC_SUPER 9 0 0 100 1 SPIN_TX 9 0 0 100 1 SPIN_SHUTDOWN 8 0 0 100 1 SPIN_PEP 6 0 0 100 1 SPIN_TEMP_UNIT_POOL 4 0 0 100 1 SPIN_BUF_FREE_WAIT 2 0 0 100 1 SPIN_LOGNET_WRITE 1 0 0 100
각 항목에 대한 설명은 다음과 같습니다.
| 항목 | 설명 |
|---|---|
| INST_ID | Tibero 인스턴스 정보입니다. (TAC DB 질의 쿼리) |
| NAME | Spin Lock의 종류입니다. |
| Gets | 최초 시도에서 Spin Lock 획득을 요청한 경우 값을 Gets값을 1씩 증가합니다. |
| Misses | 최초 시도에서 Spin Lock 획득에 실패한 경우 Misses값을 1씩 증가합니다. |
| Sleeps | 슬립 상태에 빠질때마다 1씩 증가합니다. |
| Hit Ratio | 적중률입니다. |
Willing-to-Wait 모드에서의 Latch 획득 성공 및 실패에 대한 통계값은 v$latch View를 통해 조회할 수 있습니다.
Gets(I)와 Misses(I) 항목은 각각 Gets(또는 Immediate gets)와 Misses(또는 Immediate misses) 값을 의미하며, Gets와 Immediate gets 가운데 더 큰 값을 표시합니다.
Gets가 Willing-to-Wait 모드로 요청된 경우에는 Immediate gets가 No-Wait 모드로 요청됩니다.
Willing-to-Wait 모드는 Spin Lock 획득에 실패한 경우 Spin과 Sleep을 반복하면서 획득에 성공할 때까지 재시도하는 방식입니다. 반면 No-Wait 모드는 원하는 Spin Lock 획득에 실패할 경우 해당 Latch를 기다리지 않고 즉시 반환합니다. 따라서 기본적으로 Willing-to-Wait 모드를 사용한다는 것은 Spin Lock을 획득할 때까지 대기함을 의미합니다.
3.4. TAC DB Spin Lock 모니터링
Spin Lock의 Contention에 대한 정보를 확인합니다.
SELECT inst_id,
name,
gets,
misses,
spin_gets,
sleeps,
decode(gets,0,0,trunc((1-misses/(misses+gets))*100,2)) "Hit Ratio",
wait_time,
round(ratio_to_report(wait_time)over()*100,2) "WT(%)"
FROM gv$latch
ORDER BY 1 asc,
8 desc,
4 desc;
결과
INST_ID NAME GETS MISSES SPIN_GETS SLEEPS Hit Ratio WAIT_TIME WT(%) ------- -------------------------- ------- ---------- ---------- ---------- ---------- ---------- ---------- 1 SPIN_CWS_RSB 6091 130 80 50 97.91 110322 65.15 1 SPIN_ALLOC 135601 264 261 3 99.8 12052 7.12 1 SPIN_ROOT_ALLOC 4365 5 0 5 99.88 12026 7.1 1 SPIN_SHP_ALLOC_SLAB 3888 25 23 2 99.36 2319 1.37 1 SPIN_BUF_WS 80234 12 10 2 99.98 786 .46 1 SPIN_BITQ 72616 3 3 0 99.99 532 .31 1 SPIN_SHP_ALLOC_MISC 10902 477 476 1 95.8 439 .26 1 SPIN_BUF_BUCKET 142547 501 501 0 99.64 280 .17 1 SPIN_WLOCK 14999 141 140 1 99.06 140 .08 1 SPIN_LC_BUCKET 212034 1 1 0 99.99 120 .07 1 SPIN_DD_CACHE_BUCKET 7999 109 109 0 98.65 111 .07 1 SPIN_CWS_RSBTBL 4221 112 112 0 97.41 91 .05 1 SPIN_CCC_RSB 5415 11 9 2 99.79 83 .05 1 SPIN_PARAM 8316 243 243 0 97.16 80 .05 1 SPIN_CCC_RSBTBL 113351 11 11 0 99.99 47 .03 1 SPIN_DBWR_AIOQ 5998 0 0 0 100 21 .01 1 SPIN_GMC_HANDLE 228 0 0 0 100 16 .01 1 SPIN_SQLSTATS 13903 0 0 0 100 5 0 1 SPIN_L1CL 47948 0 0 0 100 5 0 1 SPIN_CCC_RECL_WS 3147 6 6 0 99.8 4 0 1 SPIN_SHP_ALLOC_DD 712 3 3 0 99.58 3 0 1 SPIN_GV_PARAM 1428 1 1 0 99.93 2 0 1 SPIN_TSN_TIME_MAP 386 0 0 0 100 2 0 1 SPIN_ACF_MTX_RW 2220 0 0 0 100 2 0 1 SPIN_ACF_RCF 246 0 0 0 100 2 0 1 SPIN_RECR_UNPIN 5795 0 0 0 100 2 0 1 SPIN_CWS_RECL_WS 842 2 2 0 99.76 1 0 1 SPIN_SVRMODE_ACK 39 1 1 0 97.5 1 0 1 SPIN_CF 1080 0 0 0 100 1 0 1 SPIN_CKPT_PROGRESS 699 0 0 0 100 1 0 1 SPIN_ACF_NMGR 188 0 0 0 100 1 0 1 SPIN_SESS_SERIAL_NO 468 89 89 0 84.02 0 0 1 SPIN_CWS_LCB 16880 40 40 0 99.76 0 0 1 SPIN_MAIO 0 0 0 0 0 0 0 1 SPIN_CCC_LKBTBL 2675 0 0 0 100 0 0 1 SPIN_ACF_TIMEOUT 3408 0 0 0 100 0 0 1 SPIN_CWS_LKBTBL 1321 0 0 0 100 0 0 1 SPIN_TXR 55 0 0 0 100 0 0 1 SPIN_TEST3 0 0 0 0 0 0 0 1 SPIN_USGMT_CACHE 107 0 0 0 100 0 0 1 SPIN_PPID 66 0 0 0 100 0 0 1 SPIN_BUF_GLOBAL_RECOQ 116 0 0 0 100 0 0 1 SPIN_CCC_CRAS 0 0 0 0 0 0 0 1 SPIN_REDO_COPY 0 0 0 0 0 0 0 1 SPIN_MAIO_POOL 459 0 0 0 100 0 0 1 SPIN_USGMT_HASH 309 0 0 0 100 0 0 1 SPIN_PP_EXPCN_LIST 66 0 0 0 100 0 0 1 SPIN_PPSTAT 0 0 0 0 0 0 0 1 SPIN_RCMAP_BUCKET 0 0 0 0 0 0 0 1 SPIN_RCACHE 0 0 0 0 0 0 0 1 SPIN_TAS_DS 0 0 0 0 0 0 0 1 SPIN_SM_BUCKET 0 0 0 0 0 0 0 1 SPIN_TEST1 0 0 0 0 0 0 0 1 SPIN_TEST1A 0 0 0 0 0 0 0 1 SPIN_TEST1B 0 0 0 0 0 0 0 1 SPIN_BUF_FREE_WAIT 10 0 0 0 100 0 0 1 SPIN_RCPOOL 0 0 0 0 0 0 0 1 SPIN_ACF_ATH_LOG_FLUSH_WAIT 0 0 0 0 0 0 0 1 SPIN_WTHR_READY 144 0 0 0 100 0 0 1 SPIN_IICD 0 0 0 0 0 0 0 1 SPIN_TAS_NAME_HASH 0 0 0 0 0 0 0 1 SPIN_TAS_IOINFO 0 0 0 0 0 0 0 1 SPIN_RECR_LIST 0 0 0 0 0 0 0 1 SPIN_SLAB_PEND 0 0 0 0 0 0 0 1 SPIN_TEST2 0 0 0 0 0 0 0 1 SPIN_LOGNET_POOL 3 0 0 0 100 0 0 1 SPIN_FB_COPY 0 0 0 0 0 0 0 1 SPIN_LOGNET_WRITE 1 0 0 0 100 0 0 1 SPIN_SHP_ALLOC 0 0 0 0 0 0 0 1 SPIN_SHP_ALLOC_LC 214 0 0 0 100 0 0 1 SPIN_SHP_ALLOC_SUPER 17 0 0 0 100 0 0
각 항목에 대한 설명은 다음과 같습니다.
| 항목 | 설명 |
|---|---|
| INST_ID | Tibero 인스턴스의 번호 입니다. - 기동순서에 따라 지정 - v$instacne 의 instance_number 컬럼 정보와 구분됩니다. |
| NAME | Spin Lock의 종류입니다. |
| GETS | 최초 시도에서 Spin Lock 획득을 요청한 경우 값을 Gets값을 1씩 증가합니다. |
| MISSES | 최초 시도에서 Spin Lock 획득에 실패한 경우 Misses값을 1씩 증가합니다. |
| SPIN_GETS | Spin Lock을 잡을 때까지 실패한 횟수를 의미하며 실패할 경우 1씩 증가합니다. |
| SLEEPS | 슬립 상태에 빠질때마다 1씩 증가합니다. |
| Hit Ratio | 적중률입니다. |
| WAIT_TIME | Spin Lock을 기다린 총 슬립 시간입니다. |
| WT | WAIT_TIME을 백분율로 표현한 항목입니다. |
다음은 Spin Lock Contention을 발생할 수 있는 파라미터에 대한 설명입니다.
Shared Pool
- SPIN_SHP_ALLOC
| 관련 파라미터 | _SHARED_POOL_ALLOC_CNT(기본값 : 1) |
|---|---|
| 설명 | Shared Pool은 여러 개로 분할할 수 있으며, 몇 개의 allocator로 나누어 관리할 것인지를 결정합니다. 최대 15개까지 설정할 수 있으며, 설정값이 클수록 Contention은 감소하지만 메모리 사용 효율은 저하됩니다. |
- SPIN_ALLOC_LRU
| 관련 파라미터 | _SHARED_POOL_LRU_PER_ALLOC(기본값 : 4) |
|---|---|
| 설명 | 하나의 Shared Pool Allocator에서 사용할 LRU List의 개수를 결정합니다. 성능 저하를 최소화하기 위해서는 2ⁿ 값으로 설정하는 것이 권장되며, 설정값이 클수록 Contention은 감소하지만 메모리 사용 효율은 저하됩니다. SHARED_POOL_LRU_PER_ALLOC 설정값을 늘려 LRU의 개수를 증가시켰음에도 Contention이 높은 경우에는 SHARED_POOL_ALLOC_CNT 값을 늘려 조정할 수 있습니다. 다만, SHARED_POOL_ALLOC_CNT의 경우 여유가 있는 Allocator가 존재하더라도 Full 상태인 Allocator를 사용할 수 있다는 문제점이 있습니다. 두 파라미터를 곱한 LRU의 총 개수는 최대 16개까지 가능하며, 최신 버전에서는 최대 15개까지 설정할 수 있습니다. |
- SPIN_RECR_HANDLE_POOL
| 관련 파라미터 | _RECR_HANDLE_FREELIST_CNT(기본값 : 8) |
|---|---|
| 설명 | DD Cache와 라이브러리 Cache에서 사용하는 구조체의 Free List 개수를 설정합니다. DD와 라이브러리는 메모리에서 제거될 수 있으므로 이를 관리하기 위해 Handle이라는 구조체를 사용합니다. Handle을 매번 새로 생성하는 것은 오버헤드가 크기 때문에 Pool 형태로 관리합니다. |
WLOCK
- SPIN_WLOCK
| 관련 파라미터 | _WLOCK_BUCKETSET_CNT(기본값 : 64) _WLOCK_BUCKET_PER_SET(기본값 : 1) |
|---|---|
| 설명 | wlock을 찾을 때 사용하는 Hash Bucket의 Bucketset 개수를 결정합니다. Bucketset마다 Spin Lock이 존재하므로, 개수가 많을수록 Contention은 감소하지만 Hash Table의 크기가 증가하여 메모리 사용량이 늘어납니다. Spin Lock만 증가시키려는 경우에는 해당 파라미터만 조정하면 되며, 전체적인 분산 효과를 높이려는 경우에는 _WLIST_FREELIST_CNT 파라미터 값도 함께 증가시켜야 합니다. |
- SPIN_WLIST_FREELIST
| 관련 파라미터 | _WLIST_FREELIST_CNT(기본값 : 8) |
|---|---|
| 설명 | wlock 또는 Buffer Cache에서 사용하는 구조체 Pool에서 Freelist의 개수를 결정합니다. 설정값은 2 ^ n 값을 권장합니다. |
Buffer Cache
- SPIN_BUF_WS / SPIN_BUF_WS_CKPT
| 관련 파라미터 | _DB_BLOCK_LRU_LATCHES(기본값 : cpu 개수 * 3) |
|---|---|
| 설명 | Buffer Cache에서 사용할 LRU의 개수로 Workingset 값을 결정하는 파라미터 입니다. |
- SPIN_BUF_BUCKET
| 관련 파라미터 | _DB_BLOCK_HASH_BUCKETS(기본값 : 131091) _DB_BLOCK_HASH_LATCHES(기본값 : 131091) |
|---|---|
| 설명 | _DB_BLOCK_HASH_BUCKETS 파라미터 설정으로 Buffer Cache에서 특정 Buffer 를 찾을 때 사용할 Hash Bucket 개수를 결정합니다. _DB_BLOCK_HASH_LATCHES 파라미터 설정으로 Buffer Cache에서 사용하는 Hash에서 사용할 Latch 개수를 결정합니다. Contention이 심하면 BUCKETS와 LATCHES값을 같게 할 수 있으나 Bucket마다 Latch가 있게 되어 메모리 효율은 떨어지게 됩니다. |
Redo
- SPIN_REDO_COPY
| 관련 파라미터 | _LOG_SIMULTANEOUS_COPIES(기본값 : cpu 개수 * 2) |
|---|---|
| 설명 | Redo 버퍼에서 동시에 Copy 하는 트랜잭션의 개수를 설정합니다. |
Undo
- SPIN_USGMT_HASH
| 관련 파라미터 | _USGMT_HASH_CNT(기본값 : 32) |
|---|---|
| 설명 | Undo 세그먼트의 Spin Lock Hash Bucket 수를 설정합니다. Spin Lock Hash Bucket 수를 늘려도 메모리를 많이 사용하지 않으므로 최소 64로 설정하는 것을 권장합니다. |
DD Cache
- SPIN_DD_CACHE_BUCKET
| 관련 파라미터 | _DD_BUCKETSET_CNT(기본값 : 16) _DD_BUCKET_PER_SET(기본값 : 64) |
|---|---|
| 설명 | wlock과 거의 동일하게 Hash Bucket과 Bucketset의 개수를 결정합니다. |
PP Cache
- SPIN_PPC_BUCKET
| 관련 파라미터 | _PPC_BUCKET_CNT(기본값 : 1) |
|---|---|
| 설명 | wlock과 거의 동일하게 Hash Bucket 개수를 결정합니다. |
Temp
- SPIN_TEMP_UNIT_POOL_TF
Temp 파일당 하나의 Spin Lock이 할당되며 여러 세션에서 Temp Tablespace를 사용할 경우 발생합니다.
해당 Spin Lock Contention이 비정상적으로 높을 경우 Temp Tablespace에 Temp 파일을 여러개 추가하여 해결합니다.
제4장 Lock & Current 트랜잭션
본 장에서는 Lock과 트랜잭션에 대해서 설명합니다.
4.1. Current 세션의 수
대상 데이터베이스에 접속되어 있는 모든 세션과 현재 작업 중인 세션의 수, Total WPM(Working Process Memory)을 확인합니다. 세션의 상태는 READY, RUNNING, TX_RECOVERING 등이 있습니다.
4.1.1 TAC DB
다음은 TAC DB 쿼리 작성 예입니다.
SELECT a.INST_ID,
to_char(sysdate, 'yyyy/mm/dd hh24:mi:ss') "Time",
"Work Proc Memory",
a.tots "Tot Session",
b.run "Run Session"FROM (SELECT INST_ID,
SUM(pga_used_mem) "Work Proc Memory",
COUNT(*) tots
FROM gv$session
group by inst_id) a,
(SELECT INST_ID,
COUNT(*) run
FROM gv$session
WHERE status='RUNNING' group by inst_id) b
WHERE a.INST_ID = b.INST_ID(+);결과
INST_ID Time Work Proc Memory Tot Session Run Session ------- ------------------- ---------------- ----------- ----------- 1 2023/07/04 14:24:59 8703984 14 11 2 2023/07/04 14:24:59 653512 5 1 2 rows selected.
각 항목에 대한 설명은 다음과 같습니다.
| 항목 | 설명 |
|---|---|
| INST_ID | Tibero 인스턴스 정보입니다. |
| Time | 쿼리를 날린 현재 시간입니다. |
| Work Proc Memory | 현재 모든 세션이 사용하고 있는 PGA의 전체 크기입니다. |
| Tot Session | 접속 중인 총 세션의 수입니다. |
| Run Session | 실제 작업 중인 세션의 수입니다. |
총 세션의 상당수가 Running 세션이라면 Lock과 Current 트랜잭션 정보를 확인하여 적절한 조치를 합니다.
4.2. TAC DB Current 트랜잭션
현재 진행 중인 트랜잭션들의 목록과 관련 정보를 확인합니다.
SELECT distinct vs.inst_id,
vs.sid,
vs.serial#,
vs.username,
va.object,
vs.status,
vt.used_blk,
vt.usn,
vt.start_time,
floor(mod((sysdate - vt.start_time)*24, 24)) || ':'|| lpad(floor(mod((sysdate - vt.start_time)*1440, 60)), 2, 0) ||':'|| lpad(floor(mod((sysdate - vt.start_time)*86400, 60)), 2, 0) AS "Transaction Time",
vst.sql_text
FROM gv$session vs,
gv$transaction vt,
gv$sqltext vst,
gv$access va
WHERE vt.sess_id = vs.sid
and vt.sess_id = va.sid
and nvl(vs.sql_id, vs.prev_sql_id) = vst.sql_id
ORDER BY 1, 2;결과는 아래와 같습니다.
INST_ID SID SERIAL# USERNAME OBJECT STATUS USED_BLK USN START_TIME Transaction Tim SQL_TEXT
---------- ----- ---------- --------------- --------------- ---------- ---------- ---------- --------------- --------------- ------------------------------ 1 152 10785 TPCC BMSQL_NEW_ORDER RUNNING 1 4 2023/07/04 0:00:16 DELETE FROM bmsql_new_order
2 134 97 TPCC BMSQL_NEW_ORDER READY 1 8 2023/07/04 0:00:02 select * from tab각 항목에 대한 설명은 다음과 같습니다.
| 항목 | 설명 |
|---|---|
| INST_ID | Tibero 인스턴스 정보입니다. (TAC DB 질의 쿼리) |
| SID | 트랜잭션을 수행 중인 세션의 ID입니다. |
| USERNAME | 트랜잭션을 수행 중인 세션의 사용자입니다. |
| OBJECT | 현재 동작 중인 트랜잭션에서 사용 중인 Table 목록입니다. (하나의 트랜잭션에서 여러 Table을 Access할 경우 Table마다 하나의 Row로 나온다) |
| STATUS | 트랜잭션을 수행하고 있는 세션의 상태 정보입니다. |
| USED_BLK | 현재 트랜잭션이 사용 중인 Block의 개수입니다. |
| START_TIME | 트랜잭션이 시작된 시간입니다. |
| Transaction Time | 트랜잭션이 진행된 시간입니다. |
| SQL_TEXT | 현재 수행 중인 SQL 내용입니다. |
현재 트랜잭션의 사용 여부를 확인하고, 장시간 Commit 또는 Rollback 없이 유지되는 트랜잭션이 있는 경우 애플리케이션의 잘못된 코딩에 의한 것인지, 또는 관리자나 개발자의 실수에 의한 것인지를 판단하여 불필요한 트랜잭션에 대해서는 Commit 또는 Rollback을 수행하도록 권고합니다.
4.3. TAC DB Current SQL 정보(For 세션)
현재 세션별 수행되고 있는 SELECT 및 DML 구문을 확인합니다.
다음은 TAC DB 쿼리 작성 예입니다.
SELECT max(vs.inst_id) inst_id,sid,
serial#,
'{' || aggr_concat(sql_text, '' ORDER BY PIECE) || '}' SQL
FROM gv$session vs,
gv$sqltext vst
WHERE vs.sql_id=vst.sql_id
GROUP BY SID, SERIAL#;
결과
INST_ID SID SERIAL# SQL
------- ---- ------- --------------------------------------------------------------------------------------------------
1 134 12535 {DELETE FROM bmsql_new_order
WHERE no_w_id = ? AND no_d_id = ? AND no_o_id = ?}
2 143 12223 {SELECT "INST_ID","HASH_VALUE","PLAN_HASH_VALUE","SQL_ID","CHILD_NUMBER",
"COMMAND_TYPE","PIECE","SQL_TEXT"
FROM (
SELECT instance_id() AS inst_id,
"V"."HASH_VALUE",
"V"."PLAN_HASH_VALUE",
"V"."SQL_ID",
"V"."CHILD_NUMBER",
"V"."COMMAND_TYPE",
"V"."PIECE",
"V"."SQL_TEXT"
FROM v$sqltext v
) /*GV$SQLTEXT*/ QB_006
WHERE ("SQL_ID" IS NOT NULL)}각 항목에 대한 설명은 다음과 같습니다.
| 항목 | 설명 |
|---|---|
| INST_ID | Tibero 인스턴스 정보입니다. (TAC DB 질의 쿼리) |
| SID | 세션 ID입니다. |
| SERIAL# | 세션의 Serial입니다. (같은 세션 ID라도 사용자가 바뀌면 Serial이 바뀐다) |
| SQL | 현재 수행 중인 SQL 내용입니다. |
현재 활성화된 세션의 SQL 구문을 확인하여 불량 SQL문이나 루프문으로 DB에 부하를 주고 있는 세션이 있다면 그 쿼리를 찾아내고 해당 세션에 대한 적절한 조치를 합니다.
4.4. TAC DB WLOCK 정보(For 세션)
현재 실행 중인 세션의 트랜잭션 시간 및 Lock 모드를 확인합니다.
다음은 TAC DB 쿼리 작성 예입니다.
SELECT vt.inst_id,
s.sess_id "SID",
s.serial_no "SERIAL#",
s.status "STATUS",
s.user_name "USER",
o.object_name,
FLOOR((sysdate - vt.start_time)*24) || ':'|| LPAD(FLOOR(MOD((sysdate - vt.start_time)*1440, 60)),2,0) ||':'|| LPAD(FLOOR(MOD((sysdate - vt.start_time)*86400,60)),2,0) AS "LOCK_TIME",
DECODE(lmode, 0, '[0]', 1, '[1]Row-S(RS)', 2, '[2]Row-X(RX)', 3, '[3]Shared(S)', 4, '[4]S/Row-S(SRX)', 5, '[5]Exclusive(X)', 6, '[6]PIN', TO_CHAR (lmode) ) "LOCK MODE",
NVL(s.sql_id, s.prev_sql_id) || '/' || NVL2(s.sql_id, s.sql_child_number, s.prev_child_number) "SQL_ID"FROM vt_wlock l,
vt_session s,
dba_objects o ,
gv$transaction vt
WHERE l.type='WLOCK_DML' AND l.sess_id = s.vtr_tid
AND l.id1 = o.object_id (+)
AND l.sess_id = vt.sess_id
ORDER BY "LOCK_TIME" DESC;
결과는 아래와 같습니다.
INST_ID SID SERIAL# STATUS USER OBJECT_NAME LOCK_TIME LOCK MODE SQL_ID ---------- ----- ---------- -------- --------------- -------------------- --------------- --------------- ------------------------------ 1 136 196 RUNNING TPCC BMSQL_OORDER 0:00:00 [2]Row-X(RX) 8c940cxrfrc8d/78 1 136 196 RUNNING TPCC BMSQL_NEW_ORDER 0:00:00 [2]Row-X(RX) 8c940cxrfrc8d/78 2 137 198 RUNNING TPCC BMSQL_DISTRICT 0:00:00 [1]Row-S(RS) f3sacguuzunmw/57 2 143 210 RUNNING TPCC BMSQL_DISTRICT 0:00:00 [1]Row-S(RS) f3sacguuzunmw/57
DML Lock 관련 정보에 대한 각 항목의 설명은 다음과 같습니다.
| 항목 | 설명 |
|---|---|
| INST_ID | Tibero 인스턴스 정보입니다. (TAC DB 질의 쿼리) |
| SID | 트랜잭션을 수행 중인 세션 ID입니다. |
| SERIAL# | 트랜잭션을 수행 중인 세션 일련번호입니다. |
| STATUS | 트랜잭션을 수행하고 있는 세션의 상태 정보입니다. |
| USER | 트랜잭션을 수행 중인 세션의 사용자입니다. |
| OBJECT_NAME | DML Lock이 걸려있는 오브젝트입니다. |
| LOCK_TIME | DML Lock이 걸려있는 시간입니다. |
| LOCK_MODE | DML Lock 종류입니다. (RS=1, RX=2, S=3, SRX=4, X=5) |
| SQL_ID | 현재 수행 중인 SQL_ID입니다. |
제5장 디스크 I/O 모니터링
본 장에서는 디스크I/O에 대해서 설명합니다.
5.1. TAC DB 데이터 파일 I/O
데이터 파일의 I/O 정도와 시간을 확인합니다.
다음은 TAC DB 쿼리 작성 예입니다.
SELECT rownum num, inst_id
,fl.tablespace_name
,df.name
,fs.phyrds
,fs.phywrts
,round((PHYRDS / (SELECT sum(phyrds)
FROM GV$FILESTAT fs
WHERE inst_id = fs.inst_id)) *100, 1) "P_READ(%)" ,round((PHYWRTS / DECODE((SELECT sum(phywrts)
FROM GV$FILESTAT fs
WHERE inst_id = fs.inst_id), 0, 1,(SELECT sum(phywrts)
FROM GV$FILESTAT fs
WHERE inst_id = fs.inst_id)))*100, 1) "P_WRITE(%)" ,round((phyrds + phywrts) / (SELECT sum(phyrds) + sum(phywrts)
FROM GV$FILESTAT fs
WHERE inst_id = fs.inst_id)*100, 1) "TOTAL IO (%)" ,round(fs.AVGIOTIM/1000,3) "AVG_TIME(msec)"FROM V$DATAFILE df,
GV$FILESTAT fs,
dba_data_files fl
WHERE df.file# = fs.file#
AND df.file# = fl.file_id
ORDER BY 1, phyrds+phywrts DESC;결과는 아래와 같습니다.
NUM INST_ID TABLESPACE_NAME NAME PHYRDS PHYWRTS P_READ(%) P_WRITE(%) TOTAL IO(%) AVG_TIME(msec) --- ------- --------------- ----------------------------------- ------ ------- --------- ---------- ----------- -------------- 1 1 SYSTEM +DS0/tac/datafile/system001.dtf 465 11 47.3 8.9 43.0 .012 2 2 SYSTEM +DS0/tac/datafile/system001.dtf 273 5 27.7 4.0 25.1 .012 3 1 UNDO0 +DS0/tac/datafile/undo001.dtf 24 49 2.4 39.5 6.6 .059 4 2 UNDO0 +DS0/tac/datafile/undo001.dtf 0 4 0.0 3.2 0.4 .024 5 1 USR +DS0/tac/datafile/usr001.dtf 1 0 0.1 0.0 0.1 .001 6 2 USR +DS0/tac/datafile/usr001.dtf 0 0 0.0 0.0 0.0 0 7 1 UNDO1 +DS0/tac/datafile/undo101.dtf 12 1 1.2 0.8 1.2 .031 8 2 UNDO1 +DS0/tac/datafile/undo101.dtf 11 27 1.1 21.8 3.4 .088 9 1 SYSSUB +DS0/tac/datafile/syssub001.dtf 74 11 7.5 8.9 7.7 .018 10 2 SYSSUB +DS0/tac/datafile/syssub001.dtf 123 16 12.5 12.9 12.5 .018 11 1 TIBERO7 +DS0/tac/datafile/tibero7.dtf 1 0 0.1 0.0 0.1 .001 12 2 TIBERO7 +DS0/tac/datafile/tibero7.dtf 0 0 0.0 0.0 0.0 0
각 항목에 대한 설명은 다음과 같습니다.
| 항목 | 설명 |
|---|---|
| INST_ID | Tibero 인스턴스 정보입니다. (TAC DB 질의 쿼리) |
| TABLESPACE_NAME | Tablespace의 이름입니다. |
| NAME | 생성된 데이터 파일 이름입니다. |
| PHYRDS | 디스크에서 Block을 읽은 횟수입니다. |
| PHYWRTS | 디스크에 Block을 쓴 횟수입니다. |
| P_READ(%) | 해당 데이터 파일의 Read 빈도입니다. (전체 Block Read 중에 해당 데이터파일의 비중) |
| P_WRITE(%) | 해당 데이터 파일의 Write 빈도입니다. (전체 Block Write중에 해당 데이터 파일의 비중) |
| TOTAL IO(%) | 해당 데이터 파일의 Read와 Write 빈도입니다. (전체 Block Read와 Write중에 해당 데이터 파일의 비중) |
| AVG_TIME | 평균 I/O 시간입니다. (기본값의 단위가 usec 이므로 적절히 변환하여 사용합니다. 위의 예에서는 msec으로 표기함) |
데이터 파일의 I/O 발생 정도와 소요 시간을 확인합니다. Read(%), Write(%), Total IO(%)는 데이터베이스 내 모든 데이터 파일에 대한 Read, Write, Read+Write 중 해당 데이터 파일이 차지하는 비율을 의미합니다.
평균 I/O 시간이 긴 데이터 파일의 경우에는 해당 데이터 파일이 위치한 디스크의 성능을 고려해야 하며, 많은 데이터 파일이 동일한 위치에 존재하여 I/O 병목 현상이 발생하고 있는지도 함께 확인해야 합니다. 또한 I/O가 많이 발생하는 데이터 파일은 하나의 Device에 집중되지 않도록 분산하여 배치하는 것이 좋습니다.
5.2. TAC DB Online Redo Log Switch 횟수
시간별로 Online Redo Log Switch의 횟수를 확인합니다.
다음은 TAC DB 쿼리 작성 예입니다.
SELECT 'Instance : '|| THREAD# as Inst_id,
TO_CHAR(first_time,'MM/DD') ||' :'|| TO_CHAR(sum(DECODE(TO_CHAR(first_time,'hh24'),'00',1,0)),'99') ||'|'|| TO_CHAR(sum(DECODE(TO_CHAR(first_time,'hh24'),'01',1,0)),'99') ||'|'|| TO_CHAR(sum(DECODE(TO_CHAR(first_time,'hh24'),'02',1,0)),'99') ||'|'|| TO_CHAR(sum(DECODE(TO_CHAR(first_time,'hh24'),'03',1,0)),'99') ||'|'|| TO_CHAR(sum(DECODE(TO_CHAR(first_time,'hh24'),'04',1,0)),'99') ||'|'|| TO_CHAR(sum(DECODE(TO_CHAR(first_time,'hh24'),'05',1,0)),'99') ||'|'|| TO_CHAR(sum(DECODE(TO_CHAR(first_time,'hh24'),'06',1,0)),'99') ||'|'|| TO_CHAR(sum(DECODE(TO_CHAR(first_time,'hh24'),'07',1,0)),'99') ||'|'|| TO_CHAR(sum(DECODE(TO_CHAR(first_time,'hh24'),'08',1,0)),'99') ||'|'|| TO_CHAR(sum(DECODE(TO_CHAR(first_time,'hh24'),'09',1,0)),'99') ||'|'|| TO_CHAR(sum(DECODE(TO_CHAR(first_time,'hh24'),'10',1,0)),'99') ||'|'|| TO_CHAR(sum(DECODE(TO_CHAR(first_time,'hh24'),'11',1,0)),'99') ||'|'|| TO_CHAR(sum(DECODE(TO_CHAR(first_time,'hh24'),'12',1,0)),'99') ||'|'|| TO_CHAR(sum(DECODE(TO_CHAR(first_time,'hh24'),'13',1,0)),'99') ||'|'|| TO_CHAR(sum(DECODE(TO_CHAR(first_time,'hh24'),'14',1,0)),'99') ||'|'|| TO_CHAR(sum(DECODE(TO_CHAR(first_time,'hh24'),'15',1,0)),'99') ||'|'|| TO_CHAR(sum(DECODE(TO_CHAR(first_time,'hh24'),'16',1,0)),'99') ||'|'|| TO_CHAR(sum(DECODE(TO_CHAR(first_time,'hh24'),'17',1,0)),'99') ||'|'|| TO_CHAR(sum(DECODE(TO_CHAR(first_time,'hh24'),'18',1,0)),'99') ||'|'|| TO_CHAR(sum(DECODE(TO_CHAR(first_time,'hh24'),'19',1,0)),'99') ||'|'|| TO_CHAR(sum(DECODE(TO_CHAR(first_time,'hh24'),'20',1,0)),'99') ||'|'|| TO_CHAR(sum(DECODE(TO_CHAR(first_time,'hh24'),'21',1,0)),'99') ||'|'|| TO_CHAR(sum(DECODE(TO_CHAR(first_time,'hh24'),'22',1,0)),'99') ||'|'|| TO_CHAR(sum(DECODE(TO_CHAR(first_time,'hh24'),'23',1,0)),'99') ||'|'as "Online Log_history : 00(h)~"FROM v$log
GROUP BY THREAD#, TO_CHAR(first_time,'MM/DD')
ORDER BY 1;
결과
INST_ID Online Log_history : 00(h)~ --------------- ---------------------------------------------------------------------------------------------------------------- Instance : 0 06/28 : 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 3| 0| 0| 0| 0| 0| 0| 0| 0| Instance : 1 06/28 : 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 3| 0| 0| 0| 0| 0| 0| 0| 0| 2 rows selected.
각 항목에 대한 설명은 다음과 같습니다.
| 항목 | 설명 |
|---|---|
| INST_ID | Tibero 인스턴스 정보입니다.(TAC DB 질의 쿼리) |
| Online Log_history | 시간별 Log Switch 발생 횟수입니다. |
Online Log에 대해 시간대별로 발생한 Log Switch 횟수를 확인합니다.
Online Redo Log의 시간대별 Switch 횟수를 통해 데이터베이스의 트랜잭션 양을 예측할 수 있으며, 현재 시점에서 트랜잭션이 가장 많이 발생하는 시간대를 파악할 수 있습니다. 트랜잭션이 집중되는 시간대에는 대량의 Batch Job을 다른 시간대로 분산하여 수행하는 것이 바람직합니다.
제6장 공간 관리
본 장에서는 디스크 공간 관리에 대해서 설명합니다.
6.1. 빈번한 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) > 40ORDER 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 Extent의 크기를 조정하여 Extent가 자주 발생하지 않도록 합니다. 또한 데이터의 양은 많지 않은데 Extent의 크기만 큰 경우에는 Reorg 등의 조치가 필요한지 검토합니다.
6.2. 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 OFFEXTENT 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 OFFEXTENT 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;
6.3. TAC DB Undo 세그먼트 사용률
Undo Tablespace(Rollback 세그먼트)의 사용률을 확인합니다.
다음은 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 15 1 15 UNDO2 ONLINE 2 8184 0 511 13 1 13 UNDO2 ONLINE 2 8184 0 511 12 1 12 UNDO2 ONLINE 2 8184 0 511 16 1 16 UNDO2 ONLINE 2 8184 0 511 11 1 11 UNDO1 ONLINE 5 20472 4 512 10 1 10 UNDO1 ONLINE 5 20472 4 512 17 1 9 UNDO2 ONLINE 2 8184 0 511 9 1 8 UNDO1 ONLINE 5 20472 4 512 8 1 18 UNDO1 ONLINE 5 20472 4 512 18 1 7 UNDO2 ONLINE 2 8184 0 511 7 1 14 UNDO1 ONLINE 5 20472 4 512 14 1 6 UNDO2 ONLINE 2 8184 0 511 6 ...이하생 INST_ID SEGMENT_ID TABLESPACE_NAME STATUS EXTENTS RSSIZE[K] CUREXT CURSIZE XACTS ------- ---------- --------------- ------ ------- --------- ------ ------- ----- 2 0 SYSTEM ONLINE 2 8184 0 511 10 2 10 UNDO1 ONLINE 5 20472 4 512 9 2 9 UNDO1 ONLINE 5 20472 4 512 8 2 8 UNDO1 ONLINE 5 20472 4 512 7 2 7 UNDO1 ONLINE 5 20472 4 512 6 2 6 UNDO1 ONLINE 5 20472 4 512 5 2 5 UNDO1 ONLINE 5 20472 4 512 4 2 4 UNDO1 ONLINE 5 20472 4 512 3 2 3 UNDO1 ONLINE 5 20472 4 512 2 2 2 UNDO1 ONLINE 5 20472 4 512 1 2 1 UNDO1 ONLINE 5 20472 4 512 11 2 11 UNDO1 ONLINE 5 20472 4 512 ...
각 항목에 대한 설명은 다음과 같습니다.
| 항목 | 설명 |
|---|---|
| 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 세그먼트를 배정받아서 수행 중인 트랜잭션의 수입니다. |
6.3.1. 현재 사용 중인TAC DB Undo 확인
다음은 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를 확장할 때에는 먼저 재사용 가능한 세그먼트가 있는지 확인하며, 재사용 가능한 세그먼트가 없는 경우에만 Extent를 확장합니다.
Undo 세그먼트는 세션에서 사용하는 트랜잭션(TX)이 Commit된 이후부터 재사용이 가능하며, Commit된 정보의 Undo 이미지를 유지하는 시간은 undo_retention 설정값으로 결정됩니다. Snapshot Too Old 오류가 발생하지 않는 범위 내에서 Undo를 최대한 보장할 수 있도록 undo_retention 설정값을 조정하는 것이 좋습니다.
6.3.3 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;
제7장 파라미터 튜닝
7.1 위치지정 파라미터
다음은 위치지정 파라미터의 종류입니다.
| 파라미터 | 설명 |
|---|---|
| CONTROL_FILES | 컨트롤 파일들의 위치를 절대 경로로 지정하고 장애에 대비하여 별도의 디스크로 이중화를 합니다.예 : /data01/Tibero_Data/c1.ctl, /data02/Tibero_Data/c2.ctl |
| DB_CRE ATE_FILE_DEST | 데이터 파일이 저장되는 위치를 절대 경로로 지정하고 I/O 분산을 위해서 데 이터 파일들을 여러 디스크로 분산합니다. |
| LOG_ARCHIVE_DEST | 아카이브 로그가 저장될 위치를 절대 경로로 지정하고 데이터 파일이나 로그 파일의 위치와 분리합니다. |
7.2 Optimizer 설정 파라미터
다음은 Optimizer 설정 파라미터의 종류입니다.
| 파라미터 | 설명 |
|---|---|
| OPTIMIZER_MODE | Optimizer의 모드를 설정하는 파라미터입니다. |
| FIRST_ROWS_n | 최초 n개의 결과를 빨리 응답하기 위해 최적화를 수행합니다. |
| ALL_ROWS | 전체 결과를 응답하는 데 가장 비용이 적게드는 수행 방법을 결정합니다. |
7.3 Execute 메모리 파라미터
다음은 Execute 메모리 파라미터의 종류입니다.
| 파라미터 | 설명 |
|---|---|
| SORT_AREA_SIZE | 정렬 작업을 위한 크기를 설정하며 EX_MEMORY_AUTO_MANAGEMET가 Y이면 무시됩니다. |
| HASH_AREA_SIZE | 해시 작업을 위한 크기를 설정하며 EX_MEMORY_AUTO_MANAGEMET가 Y이면 무시됩니다. EX_MEMORY_AUTO_MANAGEMENT는 Execute 메모리 영역을 메모리 튜 너를 통해 자동으로 설정 또는 sort_area_size, hash_area_size 등을 수동으로 설정할지를 결정합니다. |
| EX_MEMORY_HARD_LIMIT | Execute 메모리의 사이즈를 정의합니다. |
| EX_MEMORY_SOFT_LIMIT_RATIO | Execute 메모리 중 sort, join 등의 작업에서 사용할 수 있는 Execute 메모리 의 비율을 정의합니다. (기본값 : 80%) |
| EX_MEMORY_OPERATION_LIMIT | Sort, Join과 작업 하나에 할당해 줄 수 있는 Execute 메모리의 최대값을 정의 합니다. (기본값 : 0)하나의 작업이 많은 Execute 메모리 사용을 방지합니다. |
| EX_MEMORY_DRIFT_CHECK_IN TERVAL | 급격한 메모리 변화를 감지하기 위하여 0.1초 주기로 검사합니다. (기본값 : 2)Execute 메모리의 변화량이 전체 EX_MEM 양의 10%를 넘는지 검사하고 10%를 넘으면 drift 합니다. |
| EX_MEMORY_COMPEN SATE_INTERVAL | SOFT_LIMIT_RATIO, OPERATION_LIMIT의 재설정 주기를 0.1초 단위로 정의합니다. (기본값 : 30) |
7.4 기타 설정 파라미터
다음은 기타 설정 파라미터의 종류입니다.
| 파라미터 | 설명 |
|---|---|
| CURSOR_SHARING | 유사한 SQL의 경우 SQL 문장의 커서 공유 여부를 설정합니다.
|
| DB_FILE_MULTI BLOCK_READ_COUNT | 전체 Table을 스캔할 때 디스크 상의 데이터 파일에서 여러 Block을 한번에 읽는 Block 수를 정의합니다. 스캔에 필요한 I/O 호출 수를 줄일 수 있어 풀 Table 스캔의 성능을 향상할 수 있습니다. (기본값 : 32)인스턴스 레벨에서 설정 플랫폼별로 해당 파라미터에 대한 제한이 다르므로 확인을 해야 합니다. |
| UNDO_RETENTION | Undo 정보를 재사용하기 전에 디스크에 저장하고 있을 시간을 초 단위로 설정합니다.‘Snapshot too old’ 같은 에러 발생을 줄이는 데 도움이 됩니다. Undo Tablespace에 충분한 공간이 있거나 확장하여 공간을 확보할 수 있으면 Undo 정보는 트랜잭션이 만료되기 전까지 재사용되지 않습니다. 공간이 충분하지 않더라도 트랜잭션은 실패하지 않습니다. Commit 된 트랜잭션이 생성 한 Undo 공간을 재사용할 수 있습니다. 그래도 부족하면 에러가 발생합니다. |
참고
CURSOR_SHARING을 통한 커서의 공유보다는 애플리케이션에서 바인드 변수 처리를 통한 커서 공유를 권장합니다.
7.5 데이터베이스 I/O 튜닝
디스크 I/O를 최소화하기 위해서 파일을 여러 디스크와 컨트롤러로 분산합니다.
다음은 데이터베이스 I/O를 튜닝할 때 고려해야 할 사항입니다.
파일 분산
데이터 파일, Redo Log 파일, Archivelog 파일을 별도의 디스크에 위치시키고 Redo Log 그룹의 멤버들도 별도의 디스크로 분리합니다.
Tablespace
시스템 Tablespace에는 데이터 Dictionary 정보만 저장하고, Table과 인덱스는 별도의 Tablespace로 분리하여 LOB 타입의 데이터나 파티션의 경우 별도의 Tablespace를 사용한다
데이터베이스 I/O 튜닝 모니터링
V$FILESTAT 동적 성능 View를 사용하여 I/O가 많이 발생하는 파일이 하나의 Device에 몰리지 않도록 분산합니다.
디스크 I/O 튜닝
디스크 I/O는 데이터베이스의 성능에 가장 영향을 주는 요인으로 디스크 I/O를 튜닝할 때 고려해야 할 사항은 다음과 같습니다.
- 호스트와 디스크 사이의 인터페이스 : EIDE, SCSI, Ultra SCSI로 구성할 수 있습니다. - RAID 구성 : RAID 0, RAID 1, RAID 5, RAID 0+1, RAID 1+0으로 구성할 수 있습니다. - 파일 시스템 : Read 성능 향상을 고려합니다. - Raw Device : Write 성능 향상을 고려합니다.
튜닝 요소
여러 디스크와 디스크 제어 장치에 로드를 균등하게 분산하여 I/O 성능을 향상시키는 것을 고려해야 합니다. 디스크의 수가 많을수록 디스크 병목 현상이 감소하므로, 다수의 소용량 디스크가 소수의 대용량 디스크보다 더 높은 성능을 발휘할 수 있습니다.