SQL 실행 빈도 분석: 가장 많이 실행되는 SQL을 찾아 성능 최적화를 수행하는 방법

서버 이관 프로젝트를 수행하는 과정에서 데이터베이스 성능을 안정적으로 유지하는 것은 매우 중요한 과제다. 서버 환경이 변경되면 기존과 다른 성능 패턴이 나타날 수 있으며, 특히 자주 실행되는 SQL이 성능 문제를 일으킬 가능성이 크다.

 

SQL 실행 빈도 분석은 가장 많이 실행되는 SQL을 식별하고, 성능 최적화를 통해 전체 시스템의 효율성을 향상시키는 과정이다. 자주 실행되는 SQL이 비효율적일 경우, CPU, 메모리, 디스크 I/O 사용량이 급증하며, 결과적으로 전체적인 시스템 성능 저하를 초래할 수 있다.

 

본 글에서는 SQL 실행 빈도를 분석하는 방법과 주요 SQL 튜닝 기법을 설명한다. 또한, SQL 실행 빈도 데이터를 활용하여 성능 최적화를 수행하는 방법을 구체적인 예제와 함께 소개한다.

---

1. SQL 실행 빈도 분석이 중요한 이유

1.1 SQL 실행 빈도가 성능에 미치는 영향

데이터베이스에서 SQL이 실행될 때, 모든 SQL이 동일한 영향을 미치는 것은 아니다. 일부 SQL은 실행 횟수가 적지만 실행 시간이 길어 성능 저하를 유발할 수 있으며, 반대로 아주 짧은 SQL이라도 실행 빈도가 높다면 시스템에 상당한 부하를 줄 수 있다.

SQL 실행 빈도를 분석하면 다음과 같은 문제를 해결할 수 있다.

1. CPU 사용량이 높은 SQL을 최적화
   • 자주 실행되는 SQL이 CPU 사용량을 과도하게 차지하면 전체 성능이 저하됨.
   • 실행 계획을 분석하여 CPU 부담을 줄이는 것이 필요함.
2. 디스크 I/O 부하가 높은 SQL을 최적화
   • 물리적 읽기(PHYSICAL READS) 또는 논리적 읽기(LOGICAL READS)가 많은 SQL을 찾아 튜닝 가능.
   • Full Table Scan을 최소화하고 적절한 인덱스를 활용해야 함.
3. 대기 이벤트(Wait Event) 감소
   • 자주 실행되는 SQL이 락(Lock)을 과도하게 유발하면 트랜잭션 지연이 발생할 수 있음.
   • 트랜잭션을 최적화하여 락 경쟁을 줄이고, 전체적인 응답 시간을 단축할 수 있음.

---

2. SQL 실행 빈도 분석 방법

SQL 실행 빈도를 분석하는 가장 효과적인 방법은 AWR(Automatic Workload Repository) 및 ASH(Active Session History) 데이터를 활용하는 것이다.

2.1 AWR을 활용한 SQL 실행 빈도 분석

AWR 보고서를 생성하여 자주 실행되는 SQL 목록을 확인할 수 있다.

@$ORACLE_HOME/rdbms/admin/awrrpt.sql

보고서를 생성한 후, "SQL Ordered by Executions" 섹션을 확인하면 가장 많이 실행된 SQL 목록을 볼 수 있다.

이 정보를 활용하면 자주 실행되는 SQL 중 성능 최적화가 필요한 SQL을 선별할 수 있다.

---

2.2 ASH 데이터를 활용한 실시간 SQL 실행 빈도 분석

실시간 데이터를 활용하여 특정 시간 동안 가장 많이 실행된 SQL을 조회할 수도 있다.

SELECT sql_id, COUNT(*) AS execution_count

FROM v$active_session_history

WHERE sample_time BETWEEN TO_DATE('2024-02-07 10:00:00', 'YYYY-MM-DD HH24:MI:SS')

AND TO_DATE('2024-02-07 10:30:00', 'YYYY-MM-DD HH24:MI:SS')

GROUP BY sql_id ORDER BY execution_count DESC;

• sql_id: 실행된 SQL의 고유 ID.
• execution_count: 특정 시간 동안 해당 SQL이 실행된 횟수.

이 데이터를 활용하면, 단기간에 과도하게 실행된 SQL을 찾아 성능 문제를 진단할 수 있다.

---

2.3 V$SQL을 활용한 SQL 실행 빈도 분석

V$SQL 뷰를 활용하면 데이터베이스 전체에서 실행된 SQL의 누적 실행 횟수를 확인할 수 있다.

 

SELECT sql_id, executions, disk_reads, buffer_gets, cpu_time

FROM v$sql

ORDER BY executions DESC

FETCH FIRST 10 ROWS ONLY;

• executions: 해당 SQL이 실행된 총 횟수.
• disk_reads: SQL 실행 시 디스크에서 읽은 블록 수.
• buffer_gets: 메모리에서 읽은 블록 수.
• cpu_time: SQL 실행에 소요된 CPU 시간.

이 정보를 활용하면, 전체적으로 가장 많이 실행된 SQL을 분석하고, 성능 개선이 필요한 SQL을 식별할 수 있다.

---

3. SQL 실행 빈도 최적화 방법

SQL 실행 빈도를 분석한 후, 자주 실행되는 SQL을 최적화하는 방법을 적용하면 시스템 성능을 향상시킬 수 있다.

3.1 실행 계획 분석 및 인덱스 활용

자주 실행되는 SQL 중 Full Table Scan이 발생하는 경우 실행 속도가 느려질 수 있다.

 

EXPLAIN PLAN FOR

SELECT *

FROM employees

WHERE department = 'Sales';

 

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

 

실행 계획을 분석한 후, 적절한 인덱스를 적용하면 성능이 개선될 수 있다.

CREATE INDEX emp_dept_idx ON employees(department);

이제 같은 SQL을 실행해도 Full Table Scan이 아닌 Index Scan을 사용하여 성능이 개선된다.

---

3.2 바인드 변수(Bind Variable) 사용

SQL이 반복적으로 실행될 때 바인드 변수를 사용하면 SQL이 공유 메모리에 캐싱되어 성능이 향상된다.

 

-- 비효율적인 SQL (매번 다른 리터럴 값을 사용하여 실행됨)

SELECT * FROM employees WHERE department = 'Sales';

 

-- 최적화된 SQL (바인드 변수 활용)

SELECT * FROM employees WHERE department = :dept_name;

• 바인드 변수를 사용하면 SQL 실행 계획이 공유되어 불필요한 컴파일 비용을 줄일 수 있다.

---

3.3 비효율적인 SELECT * 제거

많이 실행되는 SQL에서 불필요한 컬럼을 조회하는 것은 성능 저하의 원인이 될 수 있다.

 

-- 비효율적인 SQL (모든 컬럼 조회)

SELECT * FROM employees WHERE department = 'Sales';

 

-- 최적화된 SQL (필요한 컬럼만 조회)

SELECT emp_id, emp_name FROM employees WHERE department = 'Sales';

• 불필요한 데이터 조회를 줄이면 메모리 사용량이 감소하고 SQL 실행 속도가 향상된다.

---

3.4 SQL 실행 빈도 자체를 줄이는 최적화

자주 실행되는 SQL이 동일한 데이터를 반복 조회하는 경우, 캐싱(Cache) 또는 Materialized View를 활용하여 실행 횟수를 줄일 수 있다.

 

-- Materialized View 생성하여 빈번한 조회 최적화

CREATE MATERIALIZED VIEW emp_sales_mv AS

SELECT emp_id, emp_name FROM employees WHERE department = 'Sales';

 

-- 캐싱된 데이터를 활용하여 성능 개선

SELECT * FROM emp_sales_mv;

• 캐싱을 활용하면 SQL 실행 빈도를 줄이고, 데이터베이스 부하를 감소시킬 수 있다.

---

 

SQL 실행 빈도 분석은 가장 많이 실행되는 SQL을 식별하고 성능 최적화를 수행하는 핵심 과정이다.

AWR, ASH, V$SQL 데이터를 활용하여 실행 빈도가 높은 SQL을 식별한 후, 적절한 인덱스를 활용하고, 바인드 변수를 적용하며, 불필요한 데이터 조회를 줄이는 등의 최적화 기법을 적용하면 성능을 크게 개선할 수 있다.