Portfolio of Experience in Oracle SQL Tuning and Performance Monitoring

아래 쿼리들은 아주 간단한 소개 입니다.

---

▶ 개요

• 역할: SQL 튜닝 및 데이터 아키텍처 설계

• 환경: Oracle 11g/12c, AWR, gv$SQLAREA, DBA_HIST_SQL_PLAN 등

• 경험:

  • 오픈 마켓에서 주기적인 AWR 기반 SQL 튜닝 업무 수행
  • 자체 실시간 모니터링 스크립트를 활용하여 성능 저하 SQL 탐지 및 개선

---

▶ 주기적 AWR 분석 & TOP SQL 튜닝

• 주간 AWR 분석 프로세스

  • AWR 리포트를 통해 CPU 사용량 및 I/O 급증 구간 확인
  • TOP SQL 추출 및 plan hash value 분석
  • 실행 계획 불안정(SQL Plan Drift) 확인 및 고정
  • 통계정보 갱신, 인덱스 최적화 수행

• 주요 활용 뷰:

  • dba_hist_sqlstat, dba_hist_sql_plan, v$sql, gv$sql_plan_statistics_all

• 사용한 주요 지표:

  • elapsed_time, executions, buffer_gets, disk_reads, rows_processed, plan_hash_value

---

▶ 실시간 SQL 튜닝 스크립트 구성

1. → 순간 성능 저하 SQL 탐지

SELECT INST_ID, SQL_ID, EXECUTIONS, ELAPSED_TIME,
       ROUND((ELAPSED_TIME/EXECUTIONS)/1000000, 2) AS elapsed_per_exec,
       SUBSTR(SQL_TEXT, 1, 500) AS sql_text
FROM gv$sqlarea
WHERE INST_ID = 3
  AND (MODULE NOT LIKE '%Orange%' OR MODULE IS NULL)
ORDER BY elapsed_per_exec DESC;

2. → 바인드 변수 누락 SQL 식별

SELECT INST_ID, SQL_ID, EXECUTIONS, BUFFER_GETS, BIND_DATA
FROM gv$sqlarea
WHERE PARSING_SCHEMA_NAME = ' ' AND BIND_DATA IS NULL;

3. → 실행 계획 변경 여부 확인

SELECT DISTINCT SQL_ID
FROM DBA_HIST_SQL_PLAN
GROUP BY SQL_ID, PLAN_HASH_VALUE
HAVING COUNT(*) > 1;

4. → SQL Plan 단계별 I/O / 시간 분석

SELECT SQL_ID, PLAN_HASH_VALUE, OPERATION, OPTIONS, OBJECT_NAME,
       EXECUTIONS, ROUND(ELAPSED_TIME/EXECUTIONS) AS elapsed_per_exec
FROM gv$sql_plan_statistics_all
WHERE SQL_ID = :sql_id AND INST_ID = 3
ORDER BY TIMESTAMP;

---

▶ 개선 사례

• 사례 1: 5분 이상 소요되던 배치 SQL → AWR 분석 후 Nested Loop → Hash Join 변경, 실행시간 30초로 감소
• 사례 2: Bind Variable 누락으로 Child Cursor 100개 이상 생성 → 바인드 적용 유도 후 1건으로 안정화
• 사례 3: 동일 SQL의 Plan Drift 이슈 확인 후 SPM(SQL Plan Management)으로 Plan 고정

구조 개선 등 다수의 사례 보유

---

▶ 마무리

• SQL 튜닝은 단순히 실행계획만이 아니라, 실시간 트렌드와 실행환경 이해가 필요함
• AWR + 실시간 뷰 + 튜닝 스크립트를 조합한 종합적 접근 방식을 통해 성능 안정화에 기여

---