모니터링 주요항목별 SQL
DB CAFE
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
Oracle 데이터베이스에서 성능을 모니터링할 때 중점적으로 관리해야 할 항목들은 다음과 같습니다. 각 항목에 대해 간단한 설명과 함께 해당 항목을 조회할 수 있는 SQL도 포함되어 있습니다.
- 1. **대기 이벤트 (Wait Events)**
대기 이벤트는 성능 병목을 찾기 위한 중요한 지표입니다. 데이터베이스가 작업을 완료하지 못하고 기다리는 시간이 얼마나 되는지를 보여줍니다.
- 조회 SQL:
```sql 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;
```
- 2. **CPU 사용량**
데이터베이스에서 CPU 자원을 얼마나 사용하는지를 모니터링하는 것은 매우 중요합니다. CPU가 과부하 상태라면 대기 시간이 증가할 수 있습니다.
- 조회 SQL:
```sql 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;
```
- 3. **디스크 I/O (Disk I/O)**
디스크 I/O는 데이터를 읽고 쓰는 작업의 성능을 결정하는 중요한 요소입니다. 디스크 I/O가 과도할 경우, 성능 저하가 발생할 수 있습니다.
- 조회 SQL:
```sql 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;
```
- 4. **메모리 사용량 (Memory Usage)**
메모리 사용량은 인스턴스와 세션의 성능에 큰 영향을 미칩니다. Oracle은 `SGA`(System Global Area)와 `PGA`(Program Global Area)를 통해 메모리를 관리합니다.
- 조회 SQL (SGA 메모리):
```sql SELECT
name, bytes / 1024 / 1024 AS size_mb
FROM
v$sga;
```
- 조회 SQL (PGA 메모리):
```sql SELECT
round(value / 1024 / 1024, 2) AS pga_size_mb
FROM
v$pgastat
WHERE
name = 'total PGA allocated';
```
- 5. **SQL 성능 (Top SQL Statements)**
성능에 가장 영향을 미치는 SQL 문을 식별하는 것은 매우 중요합니다. 자원을 많이 사용하는 SQL을 최적화하면 성능을 크게 개선할 수 있습니다.
- 조회 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; ```
- 6. **락(Lock) 및 대기 (Lock Contention)**
락(잠금)으로 인해 자원에 대한 경쟁이 발생하면 성능 저하로 이어질 수 있습니다. 데이터베이스의 락 현황을 확인하여 잠금을 모니터링할 수 있습니다.
- 조회 SQL:
```sql 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;
```
- 7. **인덱스 효율성 (Index Efficiency)**
인덱스가 제대로 사용되고 있는지 모니터링하는 것은 매우 중요합니다. 인덱스 스캔 대신 전체 테이블 스캔이 자주 발생할 경우 성능 저하가 생길 수 있습니다.
- 조회 SQL:
```sql SELECT
name, value
FROM
v$sysstat
WHERE
name IN ('table scans (long tables)', 'index scans');
```
- 8. **세션 활동 (Active Sessions)**
활동 중인 세션 수가 많으면 CPU 및 메모리와 같은 자원에 부하가 걸릴 수 있습니다. 현재 실행 중인 세션과 대기 중인 세션을 모니터링하는 것이 중요합니다.
- 조회 SQL:
```sql 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';
```
- 9. **Redo 로그 생성률 (Redo Log Generation Rate)**
`Redo Log`는 데이터베이스 변경사항을 기록합니다. Redo 로그 생성률이 높으면 디스크 I/O에 부하를 줄 수 있습니다.
- 조회 SQL:
```sql SELECT
name, value
FROM
v$sysstat
WHERE
name = 'redo size';
```
- 10. **Tablespace 사용량**
데이터베이스의 `Tablespace` 사용량을 모니터링하여 공간 부족 문제를 방지해야 합니다.
- 조회 SQL:
```sql 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;
```
---
위의 항목들은 Oracle 데이터베이스의 성능을 모니터링하는 데 중요한 요소들입니다. 각 항목을 주기적으로 모니터링하여 성능 저하의 원인을 파악하고 적절한 대응을 할 수 있습니다.