모니터링 주요항목별 SQL
DB CAFE
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
1 주요항목별 모니터링 조회 SQL[편집]
1.1 대기 이벤트 (Wait Events)[편집]
- 대기 이벤트는 성능 병목을 찾기 위한 중요한 지표
SELECT
event,
total_waits,
time_waited_micro / 1000000 AS time_waited_seconds,
wait_class
FROM
v$system_event
WHERE
wait_class != 'Idle'
ORDER BY
time_waited_seconds DESC;
1.2 CPU 사용량[편집]
- 데이터베이스에서 CPU 자원을 얼마나 사용하는지를 모니터링하는 것은 매우 중요
- CPU가 과부하 상태라면 대기 시간 증가
SELECT
s.sid,
s.serial#,
p.spid AS os_pid,
s.username,
s.status,
t.value/100 AS "CPU Used (Sec)"
FROM
v$session s
JOIN
v$process p ON s.paddr = p.addr
JOIN
v$sesstat t ON s.sid = t.sid
JOIN
v$statname n ON t.statistic# = n.statistic#
WHERE
n.name = 'CPU used by this session'
ORDER BY
"CPU Used (Sec)" DESC;
1.3 디스크 I/O (Disk I/O)[편집]
- 디스크 I/O는 데이터를 읽고 쓰는 작업의 성능을 결정하는 중요한 요소
- 디스크 I/O가 과도할 경우, 성능 저하가 발생
SELECT
s.sid,
s.serial#,
s.username,
s.status,
i.physical_reads,
i.physical_writes
FROM
v$session s
JOIN
v$sess_io i ON s.sid = i.sid
ORDER BY
i.physical_reads DESC;
1.4 메모리 사용량 (Memory Usage)[편집]
- 메모리 사용량은 인스턴스와 세션의 성능에 큰 영향
- Oracle은 SGA(System Global Area) 와 PGA(Program Global Area)를 통해 메모리를 관리
1.4.1 조회 SQL (SGA 메모리)[편집]
SELECT name
, bytes / 1024 / 1024 AS size_mb
FROM v$sga;
1.4.2 조회 SQL (PGA 메모리)[편집]
SELECT round(value / 1024 / 1024, 2) AS pga_size_mb
FROM v$pgastat
WHERE name = 'total PGA allocated';
1.5 성능 Top SQL[편집]
- 성능에 가장 영향을 미치는 SQL 문을 식별하는 것은 매우 중요
SELECT sql_id
, executions
, round(elapsed_time / 1000000, 2) AS elapsed_seconds
, round(cpu_time / 1000000, 2) AS cpu_seconds
, disk_reads
, buffer_gets
FROM v$sql
ORDER BY elapsed_seconds DESC
FETCH FIRST 10 ROWS ONLY;
1.6 락(Lock) 및 대기 (Lock Contention)[편집]
- 락(잠금)으로 인해 자원에 대한 경쟁이 발생하면 성능 저하.
SELECT
l.sid,
s.serial#,
l.type,
l.id1,
l.id2,
l.lmode,
l.request,
l.block
FROM
v$lock l
JOIN
v$session s ON l.sid = s.sid
WHERE
l.block > 0
ORDER BY
l.sid;
1.7 인덱스 효율성 (Index Efficiency)[편집]
- 인덱스가 제대로 사용되고 있는지 모니터링하는 것은 매우 중요
- 인덱스 스캔 대신 전체 테이블 스캔이 자주 발생할 경우 성능 저하
SELECT name,value
FROM v$sysstat
WHERE name IN ('table scans (long tables)', 'index scans');
1.8 Active 세션 (Active Session) 조회[편집]
- Active 세션 세션 수가 많으면 CPU 및 메모리와 같은 자원에 부하
- 현재 실행 중인 세션과 대기 중인 세션을 모니터링하는 것이 중요.
SELECT
s.sid,
s.serial#,
s.username,
s.status,
s.event,
s.wait_time,
s.seconds_in_wait
FROM v$session s
WHERE s.status = 'ACTIVE';
1.9 Redo 로그 생성률[편집]
- `Redo Log`는 데이터베이스 변경사항을 기록
- Redo 로그 생성률이 높으면 디스크 I/O에 부하
SELECT name,value
FROM v$sysstat
WHERE name = 'redo size';
1.10 Tablespace 사용량[편집]
- 데이터베이스의 `Tablespace` 사용량을 모니터링하여 공간 부족 문제 방지
SELECT
tablespace_name,
round(sum(bytes) / 1024 / 1024, 2) AS size_mb,
round(sum(maxbytes) / 1024 / 1024, 2) AS max_size_mb
FROM dba_data_files
GROUP BY tablespace_name;