CPU 대기 이벤트 분석: CPU 사용량이 높은 SQL을 식별하고 최적화하는 방법

서버 이관 프로젝트를 진행하는 과정에서 데이터베이스 성능을 안정적으로 유지하는 것은 매우 중요한 과제다. 기존 환경과 새로운 서버 환경 간의 차이로 인해 예상치 못한 성능 저하가 발생할 수 있으며, 특히 CPU 사용량이 급증하는 경우 시스템 전체의 성능이 저하될 가능성이 크다.

 

CPU 사용량이 높은 SQL은 데이터베이스에서 과도한 연산을 수행하는 SQL 쿼리나, 실행 계획이 비효율적으로 설계된 SQL이 원인일 수 있다. 이러한 문제를 해결하기 위해 CPU 대기 이벤트(Wait Event) 분석을 수행하여, CPU 사용량이 높은 SQL을 식별하고 최적화하는 과정이 필요하다.

 

본 글에서는 CPU 대기 이벤트의 개념과 주요 원인, SQL을 활용한 CPU 사용량 분석 방법, 그리고 성능 최적화를 위한 구체적인 방안을 설명한다. 이를 통해 서버 이관 후 CPU 사용량 증가로 인해 발생하는 성능 문제를 효과적으로 분석하고 해결할 수 있도록 한다.

---

1. CPU 대기 이벤트란?

1.1 CPU 대기 이벤트의 개념

CPU 대기 이벤트란 SQL이 실행되는 동안 CPU를 사용해야 하지만, 어떤 이유로 인해 실행이 지연되거나 CPU 리소스를 과도하게 점유하는 상황을 의미한다.

일반적으로 SQL이 실행될 때 최적화된 실행 계획을 통해 필요한 만큼의 CPU만 사용하고 빠르게 실행되는 것이 이상적이다. 그러나 일부 SQL은 잘못된 인덱스 사용, 불필요한 연산, 서브쿼리 과다 사용, 비효율적인 조인 등으로 인해 불필요하게 CPU를 점유하면서 성능 저하를 유발할 수 있다.

CPU 대기 이벤트 분석을 통해 이러한 문제를 파악하고, 최적화할 SQL을 정확히 찾아내는 과정이 필수적이다.

---

1.2 CPU 대기 이벤트가 발생하는 주요 원인

CPU 사용량이 높은 SQL이 발생하는 주요 원인은 다음과 같다.

1. 비효율적인 실행 계획:
   •   옵티마이저가 잘못된 실행 계획을 선택하여, 불필요한 연산을 수행하는 경우.
   •   Full Table Scan이 발생하여 대량의 데이터를 조회하는 경우.
2. 인덱스 미사용 또는 잘못된 인덱스 활용:
   •   적절한 인덱스를 사용하지 않고 테이블을 전체 검색하는 경우.
   •   인덱스를 사용할 수 없는 WHERE 조건이 포함된 경우.
3. 과도한 조인 연산:
   •   여러 개의 테이블을 조인할 때 잘못된 조인 방식(Nested Loop, Hash Join 등)을 사용하는 경우.
   •   서브쿼리를 반복적으로 실행하여 불필요한 CPU 사용이 증가하는 경우.
4. 대량의 데이터 처리:
   •   GROUP BY, DISTINCT, ORDER BY 등의 연산이 과도하게 사용되는 경우.
   •   비효율적인 집계 연산(SUM, AVG, COUNT 등)으로 CPU 부하가 증가하는 경우.
5. PL/SQL 또는 함수 사용 문제:
   •   PL/SQL 블록 내부에서 SQL을 반복 실행하여 과도한 CPU 사용이 발생하는 경우.
   •   사용자 정의 함수(UDT)가 SQL 내부에서 반복적으로 호출되는 경우.

---

2. CPU 대기 이벤트 분석 방법

CPU 대기 이벤트를 분석하려면 ASH(Active Session History) 데이터를 활용하여 CPU 사용량이 높은 SQL을 식별해야 한다.

2.1 ASH 데이터를 활용한 CPU 사용량 분석

다음 SQL을 실행하면 특정 시간 동안 CPU 사용량이 높은 SQL 목록을 조회할 수 있다.

 

SELECT sql_id, COUNT(*) AS cpu_usage

FROM v$active_session_history

WHERE session_state = 'ON CPU'

AND 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 cpu_usage DESC;

•   session_state = 'ON CPU': 현재 CPU를 사용 중인 SQL만 필터링.
•   COUNT(*) AS cpu_usage: SQL별 CPU 사용 횟수를 계산하여 정렬.

이 데이터를 활용하면 CPU 사용량이 높은 SQL을 우선적으로 분석할 수 있다.

---

2.2 AWR 보고서를 활용한 CPU 사용량 분석

AWR(Automatic Workload Repository) 보고서를 활용하면 CPU 사용량이 높은 SQL을 상세히 확인할 수 있다.

AWR 리포트를 생성하려면 다음 명령을 실행한다.

 

@$ORACLE_HOME/rdbms/admin/awrrpt.sql

실행 후 분석할 시간 범위와 리포트 형식을 선택하면, SQL별 CPU 사용량을 포함한 AWR 보고서가 생성된다.

AWR 보고서의 Top SQL by CPU Usage 섹션을 확인하면, CPU 사용량이 높은 SQL을 찾을 수 있다.

---

3. CPU 사용량이 높은 SQL 최적화 방법

CPU 사용량이 높은 SQL을 최적화하려면 다음과 같은 방법을 적용할 수 있다.

3.1 실행 계획 분석 및 최적화

SQL 실행 계획을 확인하여 비효율적인 연산이 포함되었는지 분석한다.

 

EXPLAIN PLAN FOR

SELECT * FROM employees WHERE salary > 5000;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

•   Full Table Scan이 발생하면 적절한 인덱스를 적용하여 성능을 개선한다.
•   조인 순서를 변경하거나 힌트(Hint)를 적용하여 실행 계획을 최적화할 수 있다.

---

3.2 인덱스 최적화

인덱스를 활용하여 불필요한 연산을 줄이고 CPU 사용량을 감소시킬 수 있다.

 

CREATE INDEX emp_salary_idx ON employees(salary);

•   인덱스가 필요한 경우 적절한 인덱스를 추가하여 Full Table Scan을 방지한다.

---

3.3 불필요한 연산 제거

CPU 사용량이 높은 SQL에서는 DISTINCT, GROUP BY, ORDER BY와 같은 연산을 최소화해야 한다.

 

-- 비효율적인 SQL

SELECT DISTINCT emp_id

FROM employees

WHERE department = 'Sales';

 

-- 최적화된 SQL

SELECT emp_id

FROM employees

WHERE department = 'Sales'

GROUP BY emp_id;

•   DISTINCT 대신 GROUP BY를 활용하여 중복 제거 비용을 최소화할 수 있다.

---

3.4 PL/SQL 블록 최적화

반복적인 SQL 실행을 줄이기 위해 BULK COLLECT 및 FORALL을 활용하면 CPU 사용량을 줄일 수 있다.

 

DECLARE

TYPE emp_tab IS TABLE OF employees%ROWTYPE;

v_emp emp_tab;

 

BEGIN

SELECT * BULK COLLECT INTO v_emp FROM employees WHERE department = 'Sales';

END;

•   BULK COLLECT를 사용하면 반복적인 SQL 실행을 최소화할 수 있다.

---

 

CPU 대기 이벤트 분석은 데이터베이스 성능을 저하시킬 수 있는 주요 SQL을 식별하고 최적화하는 필수 과정이다. ASH 데이터와 AWR 보고서를 활용하여 CPU 사용량이 높은 SQL을 분석하고, 실행 계획 최적화, 인덱스 적용, 불필요한 연산 제거 등의 방법을 적용하면 성능을 크게 개선할 수 있다.