조인 최적화와 ASH 분석: 조인이 많은 SQL의 실행 패턴을 분석하여 최적화하는 방법.

서버 이관 프로젝트를 수행하는 과정에서 가장 중요한 목표 중 하나는 데이터베이스 성능을 안정적으로 유지하는 것이다. 새로운 서버 환경에서는 기존과 다르게 조인(Join)이 포함된 SQL의 실행 속도가 달라질 수 있으며, 예상치 못한 성능 저하가 발생할 가능성이 높다.

 

조인은 여러 테이블의 데이터를 결합하여 원하는 정보를 가져오는 중요한 연산이다. 그러나 비효율적인 조인 방식이 적용되거나, 인덱스를 적절히 활용하지 않는 경우, SQL 실행 시간이 길어지고 시스템 리소스 사용량이 증가할 수 있다. 조인 최적화를 수행하지 않으면 CPU 부하 증가, 디스크 I/O 과부하, 트랜잭션 처리 지연 등의 문제가 발생할 수 있다.

 

이를 해결하기 위해 ASH(Active Session History) 데이터를 활용하여 조인이 포함된 SQL의 실행 패턴을 분석하고, 실행 계획을 최적화하는 과정이 필요하다. 본 글에서는 조인 최적화를 위한 주요 기법과 ASH 데이터를 활용한 성능 분석 방법을 상세히 설명한다. 이를 통해 서버 이관 후 조인 성능 문제를 신속하게 해결하고 데이터베이스 성능을 최적화할 수 있도록 한다.

---

1. 조인 최적화가 중요한 이유

1.1 조인이 성능에 미치는 영향

조인은 테이블 간의 관계를 맺어 원하는 데이터를 가져오는 데 필수적인 연산이지만, 잘못된 방식으로 수행되면 성능 저하를 초래할 수 있다.

조인이 성능에 미치는 주요 영향은 다음과 같다.

1. 조인 방식에 따른 성능 차이
   • Nested Loop Join: 작은 데이터셋을 조인할 때 효율적이지만, 한쪽 테이블이 크면 성능 저하 발생 가능.
   • Hash Join: 대량의 데이터를 조인할 때 성능이 좋지만, 메모리를 많이 사용함.
   • Merge Join: 정렬된 데이터셋을 조인할 때 유리하지만, 정렬 비용이 발생할 수 있음.
2. 잘못된 인덱스 사용
   • 조인 키에 적절한 인덱스가 없으면 Full Table Scan이 발생하여 성능 저하가 발생할 수 있음.
3. 조인 순서 문제
   • 옵티마이저가 잘못된 조인 순서를 선택하면 불필요한 연산이 증가하면서 응답 시간이 길어질 수 있음.
4. 디스크 I/O 및 메모리 사용량 증가
   • 조인 과정에서 불필요한 데이터 블록을 읽거나 정렬하는 과정이 많아지면, 시스템 리소스 사용량이 증가할 수 있음.

이러한 문제를 해결하기 위해서는 조인의 실행 패턴을 분석하고, 비효율적인 실행 계획을 최적화하는 과정이 필수적이다.

---

2. ASH 데이터를 활용한 조인 SQL 분석 방법

2.1 특정 SQL의 조인 실행 패턴 분석

SQL이 실행될 때 어떤 조인 방식이 사용되었는지 확인하려면 ASH 데이터를 활용하여 분석할 수 있다.

SELECT    sql_id,    COUNT(*) AS execution_count,    event  FROM    v$active_session_history  WHERE    sql_id = 'SQL_ID'    AND wait_class IN (     'User I/O', 'Concurrency', 'Application'   )  GROUP BY    sql_id,    event  ORDER BY    execution_count DESC;

 

SELECT sql_id, COUNT(*) AS execution_count, event FROM v$active_session_history WHERE sql_id = 'SQL_ID' AND wait_class IN ('User I/O', 'Concurrency', 'Application') GROUP BY sql_id, event ORDER BY execution_count DESC;

• sql_id: 특정 SQL의 ID.
• execution_count: SQL 실행 횟수.
• event: SQL 실행 중 발생한 주요 대기 이벤트.

이 데이터를 활용하면 조인 SQL이 실행될 때 주로 어떤 대기 이벤트가 발생하는지 파악할 수 있다.

---

2.2 조인이 포함된 SQL의 실행 계획 조회

SQL 실행 계획을 확인하면 조인이 어떤 방식으로 실행되고 있는지, 옵티마이저가 어떤 접근 방식을 선택했는지 분석할 수 있다.

EXPLAIN PLAN FOR  SELECT e.emp_id, e.emp_name, d.dept_name FROM employees e JOIN departments d ON e.dept_id = d.dept_id; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

 

실행 계획에서 Nested Loop Join, Hash Join, Merge Join 중 어떤 방식이 사용되었는지 확인해야 한다.

실행 계획 예시

단계연산(Operation)대상 테이블접근 방식(Access Method)예상 비용(Cost)

1	SELECT STATEMENT	-	-	120
2	HASH JOIN	employees, departments	Full Table Scan	110
3	TABLE ACCESS FULL	employees	Full Table Scan	60
4	TABLE ACCESS FULL	departments	Full Table Scan	50

위 실행 계획을 보면 두 개의 테이블이 Full Table Scan을 수행하면서 Hash Join이 사용되고 있다.
이는 적절한 인덱스를 추가하거나, 조인 방식을 최적화하면 성능을 개선할 수 있는 상황을 의미한다.

---

3. 조인 SQL 최적화 방법

3.1 Nested Loop Join 최적화 – 인덱스 활용

작은 데이터셋을 조인할 때 Nested Loop Join을 효율적으로 사용하려면, 조인 컬럼에 인덱스를 추가하는 것이 필요하다.

CREATE INDEX emp_dept_idx ON employees(dept_id);

 

이제 실행 계획을 다시 확인하여 Nested Loop Join이 인덱스를 사용하도록 유도되었는지 확인해야 한다.

---

3.2 Hash Join 최적화 – 해시 버퍼 크기 조정

대량의 데이터를 조인할 때 Hash Join을 최적화하려면 메모리 버퍼 크기를 조정하는 것이 필요하다.

ALTER SESSION SET hash_area_size = 10485760;

 

이렇게 하면 Hash Join 연산이 메모리에서 수행되도록 유도하여 디스크 I/O를 최소화할 수 있다.

---

3.3 Merge Join 최적화 – 정렬 비용 감소

Merge Join을 사용할 경우 양쪽 테이블이 정렬된 상태여야 하므로, 사전에 정렬을 최소화하는 것이 필요하다.

CREATE INDEX emp_dept_idx ON employees(dept_id);  CREATE INDEX dept_id_idx ON departments(dept_id);

 

이렇게 하면 정렬 과정 없이 Merge Join을 수행할 수 있어 성능이 개선될 수 있다.

---

3.4 조인 순서 강제 변경 – 힌트(Hint) 활용

조인 순서를 강제 변경하여 실행 계획을 최적화할 수도 있다.

SELECT   /*+ LEADING(e d) */   e.emp_id,    e.emp_name,    d.dept_name  FROM    employees e    JOIN departments d ON e.dept_id = d.dept_id;

 

• LEADING 힌트를 사용하면 옵티마이저가 employees 테이블을 먼저 읽도록 강제할 수 있다.

---

 

조인이 많은 SQL을 최적화하려면 ASH 데이터를 활용하여 실행 패턴을 분석하고, 실행 계획을 최적화하는 것이 필수적이다.

특히, Full Table Scan을 줄이고 적절한 인덱스를 적용하는 것, Nested Loop Join, Hash Join, Merge Join의 특성을 고려하여 최적화하는 것, 조인 순서를 조정하는 것 등을 통해 성능을 크게 개선할 수 있다.

 

서버 이관 후에는 조인의 실행 패턴이 달라질 가능성이 높으므로, 지속적으로 모니터링하고 튜닝하는 것을 권장한다.