모니터링 주요항목별 SQL

주요항목별 모니터링 조회 SQL

대기 이벤트 (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;

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;

디스크 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;

메모리 사용량 (Memory Usage)

• 메모리 사용량은 인스턴스와 세션의 성능에 큰 영향
• Oracle은 SGA(System Global Area) 와 PGA(Program Global Area)를 통해 메모리를 관리

조회 SQL (SGA 메모리)

SELECT name
     , bytes / 1024 / 1024 AS size_mb
  FROM v$sga;

조회 SQL (PGA 메모리)

SELECT round(value / 1024 / 1024, 2) AS pga_size_mb
  FROM v$pgastat
 WHERE name = 'total PGA allocated';

성능 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;

락(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;

인덱스 효율성 (Index Efficiency)

• 인덱스가 제대로 사용되고 있는지 모니터링하는 것은 매우 중요
• 인덱스 스캔 대신 전체 테이블 스캔이 자주 발생할 경우 성능 저하

SELECT name,value
  FROM v$sysstat
 WHERE name IN ('table scans (long tables)', 'index scans');

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';

Redo 로그 생성률

• `Redo Log`는 데이터베이스 변경사항을 기록
• Redo 로그 생성률이 높으면 디스크 I/O에 부하

SELECT name,value
  FROM v$sysstat
 WHERE name = 'redo size';

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;