DBAtlas · 데이터베이스 운영 아틀라스

느린 쿼리: 심층 진단

쿼리 하나 또는 시스템 전체가 느리다. 단계적으로 범위를 좁힌다: 각 노드에서 점검을 실행하고 결과에 따라 분기를 선택한다. 끝에 근본 원인 + 해결책에 도달한다.

무엇이 느린가?

특정 쿼리 하나 또는 몇 개가 느린가, 아니면 시스템 전체인가? 이에 따라 조사가 '쿼리 중심' 또는 '리소스/병목 중심' 중 하나로 나뉜다.

  • 특정 쿼리 하나 또는 몇 개가 느리다
  • 시스템 전체가 느리다

쿼리가 지금 실행 중인가?

지금 실행 중/멈춘 쿼리인가, 아니면 '대체로 느린' 쿼리인가? 활성 세션을 본다.

SELECT sid, serial#, sql_id, last_call_et AS running_sec, event, blocking_session
FROM v$session WHERE status='ACTIVE' AND type='USER' ORDER BY last_call_et DESC;
  • 지금 실행 중 / 멈춤
  • 대체로 느림. 실행 계획을 보고 싶다

쿼리가 무언가를 기다리고 있는가?

활성 상태인데 진행되지 않는다면 대기 중일 가능성이 높다. blocking_session / wait_event가 채워져 있는가?

SELECT sid, blocking_session, event, seconds_in_wait
FROM v$session WHERE sid=&target_sid;
  • 다른 세션에 의해 블로킹됨
  • 리소스 대기 (IO / 로그 / latch)
  • 대기 없음. 활발히 CPU를 소비 중

어떤 대기가 지배적인가?

지배적인 대기 이벤트가 병목의 유형을 알려준다.

SELECT event, total_waits, time_waited FROM v$session_event WHERE sid=&target_sid ORDER BY time_waited DESC FETCH FIRST 5 ROWS ONLY;
  • IO 대기 (db file read / PAGEIOLATCH)
  • 로그 / 커밋 대기 (log file sync / WRITELOG)
  • 락 / enqueue (enq: TX / LCK)

실행 계획 확인 (EXPLAIN)

실행 계획을 가져온다. full table scan, 예상치 못한 조인 순서, 추정 행 수와 실제 행 수의 차이가 있는가?

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id', NULL, 'ALLSTATS LAST'));
  • full scan / 인덱스 누락
  • 어제는 빨랐다. 실행 계획이 갑자기 바뀌었다
  • 너무 많은 행을 처리 / N+1
  • 실행 계획은 좋아 보이지만 느리다

통계가 최신인가?

추정 행 수와 실제 행 수(E-rows vs A-rows)의 큰 차이는 오래된 통계의 신호다.

SELECT table_name, num_rows, last_analyzed FROM dba_tables WHERE table_name='&TABLE_NAME';
  • 통계가 오래되었다
  • 통계는 최신이지만 여전히 느리다

시스템 전체: 병목은 어디인가?

쿼리 하나가 아니라 모든 것이 느리다면 리소스 병목을 찾는다: CPU, IO, 메모리, 또는 광범위한 블로킹인가?

SELECT metric_name, value FROM v$sysmetric WHERE metric_name IN ('Host CPU Utilization (%)','Average Active Sessions') AND intsize_csec=(SELECT MAX(intsize_csec) FROM v$sysmetric);
  • CPU가 100%에 가까움
  • 높은 IO 대기
  • 메모리 압박 / 낮은 캐시 적중률
  • 블로킹이 많음

근본 원인: 블로킹

쿼리가 다른 세션이 보유한 락을 기다리고 있다. 체인의 맨 앞 세션(루트 블로커)을 찾아 해결한다.

근본 원인: IO 병목

쿼리가 디스크에서 많이 읽는다. 보통 full scan(인덱스 누락)이나 메모리 부족(buffer cache) 때문이다.

근본 원인: 커밋 / 로그 병목

'log file sync' / 'WRITELOG'가 지배적이면 커밋이 redo/WAL 쓰기를 기다리고 있는 것이다.

근본 원인: 인덱스 누락

실행 계획이 full scan을 한다. 필터/조인 컬럼에 적합한 인덱스를 두면 크게 빨라진다.

근본 원인: 실행 계획 회귀

쿼리가 어제는 빨랐지만 오늘은 아니다. 실행 계획이 바뀌었다. 보통 오래된 통계나 파라미터 변경 때문이다.

근본 원인: 쿼리 설계

실행 계획은 합리적이지만 쿼리 자체가 너무 많은 일을 한다(너무 많은 행, N+1, 불필요한 조인).

근본 원인: 오래된 통계

통계가 최신이 아니어서 옵티마이저가 잘못 추정한다.

심층 분석

실행 계획과 통계에 문제가 없는데도 느리다면 더 깊이 파고든다.

근본 원인: CPU 병목

시스템 전체가 CPU에 묶여 있다. 보통 실행 계획이 나쁜 소수의 쿼리가 CPU를 소비한다.

근본 원인: 메모리 압박

낮은 캐시 적중률 및/또는 스와핑: 메모리가 부족하거나 잘못 할당되어 있다.

DBAtlas에서 열기 →