인덱스 미사용 SQL 분석: ASH에서 특정 SQL이 인덱스를 사용하고 있는지 확인하는 방법.

서버 이관 프로젝트를 수행하는 과정에서 데이터베이스의 성능을 안정적으로 유지하는 것은 필수적인 과제다. 기존 서버와 새로운 환경 간의 차이로 인해 예상치 못한 성능 저하가 발생할 가능성이 있으며, 특히 인덱스를 제대로 사용하지 않는 SQL이 증가하면 CPU 및 디스크 I/O 부하가 급증하고 응답 시간이 길어지는 문제가 발생할 수 있다.

 

인덱스(Index)는 데이터베이스에서 검색 속도를 높이기 위한 필수적인 구조다. 그러나 SQL 실행 계획이 비효율적으로 생성되거나, 옵티마이저(Optimizer)가 인덱스를 사용하지 않도록 결정하는 경우 Full Table Scan이 발생하여 성능 저하로 이어질 수 있다. 이를 방지하려면 인덱스를 미사용하는 SQL을 식별하고, ASH(Active Session History) 데이터를 활용하여 해당 SQL이 인덱스를 올바르게 사용하고 있는지 확인하는 과정이 필요하다.

 

본 글에서는 ASH 데이터를 활용하여 특정 SQL이 인덱스를 사용하고 있는지 분석하는 방법과, 인덱스를 활용하도록 SQL을 최적화하는 방법을 상세히 설명한다. 이를 통해 서버 이관 후 발생할 수 있는 성능 문제를 효과적으로 해결하고 데이터베이스의 효율성을 극대화할 수 있도록 한다.

---

1. 인덱스 미사용 SQL 분석이 필요한 이유

1.1 인덱스 미사용으로 인한 성능 저하

인덱스를 활용하지 않는 SQL이 많아지면 Full Table Scan이 증가하면서 디스크 I/O 부하가 급격히 상승하고, CPU 사용량이 늘어나면서 전체적인 성능 저하를 유발할 수 있다.

인덱스를 사용하지 않는 SQL이 발생하는 주요 원인은 다음과 같다.

1. WHERE 조건이 인덱스를 사용할 수 없는 방식으로 작성된 경우
   • 인덱스 컬럼에 함수가 적용된 경우 (UPPER(column) = 'VALUE')
   • LIKE 검색에서 앞부분이 와일드카드로 시작하는 경우 (LIKE '%keyword')
2. 옵티마이저가 인덱스를 사용하지 않도록 결정한 경우
   • 테이블 크기가 작아서 Full Table Scan이 더 효율적인 경우
   • 인덱스보다 테이블 전체 검색이 더 빠르다고 판단한 경우
3. 잘못된 인덱스 생성 또는 인덱스 누락
   • 적절한 인덱스가 존재하지 않거나, 잘못된 컬럼 조합으로 생성된 경우.
   • 인덱스가 비효율적으로 설계되어 활용되지 않는 경우.

이러한 문제를 해결하려면 ASH 데이터를 활용하여 특정 SQL이 실행될 때 인덱스를 사용하고 있는지 확인하고, 필요한 경우 SQL 튜닝을 수행하는 과정이 필요하다.

---

2. ASH 데이터를 활용한 인덱스 사용 여부 분석 방법

2.1 특정 SQL의 실행 계획(Execution Plan) 조회

SQL 실행 시 인덱스를 사용하고 있는지 확인하는 가장 기본적인 방법은 실행 계획을 조회하는 것이다.

EXPLAIN PLAN FOR  SELECT    *  FROM    employees  WHERE    department = 'Sales'; SELECT    *  FROM    TABLE(DBMS_XPLAN.DISPLAY);

• 실행 계획에서 'TABLE ACCESS FULL'이 나타나면 인덱스가 사용되지 않고 있음을 의미한다.
• 'INDEX RANGE SCAN' 또는 'INDEX UNIQUE SCAN'이 나타나면 인덱스가 정상적으로 사용되고 있음을 의미한다.

그러나 실행 계획만으로는 실제 실행 중인 SQL이 인덱스를 얼마나 자주 사용하고 있는지 분석하기 어렵다. 이를 해결하기 위해 ASH 데이터를 활용해야 한다.

---

2.2 ASH 데이터를 활용한 SQL 실행 패턴 분석

SQL이 실행되는 동안 인덱스를 사용했는지 여부를 실시간으로 분석할 수 있는 방법은 ASH 데이터를 활용하는 것이다.

SELECT    sql_id,    event,    COUNT(*) AS wait_count  FROM    v$active_session_history  WHERE    sql_id = 'SQL_ID'    AND wait_class = 'User I/O'    AND event IN (     'db file scattered read', 'db file sequential read'   )  GROUP BY    sql_id,    event  ORDER BY    wait_count DESC;

 

• 'db file scattered read': Full Table Scan이 발생한 경우.
• 'db file sequential read': 인덱스 스캔(Index Scan)이 발생한 경우.
• wait_count: 해당 이벤트가 발생한 횟수.

이 데이터를 분석하면, 특정 SQL이 실행될 때 Full Table Scan이 많은지, Index Scan이 많은지 확인할 수 있다.

분석 결과 예시

SQL_IDEVENTWAIT_COUNT

abc123	db file scattered read	1,200
xyz456	db file sequential read	50

위 데이터를 보면, SQL 'abc123'은 Full Table Scan이 발생하여 성능이 저하될 가능성이 높으며, SQL 'xyz456'은 Index Scan을 활용하고 있어 성능이 비교적 안정적임을 알 수 있다.

---

3. 인덱스를 활용하도록 SQL 최적화 방법

3.1 WHERE 절 최적화 – 함수 제거 및 컬럼 변형 방지

문제: 인덱스를 사용할 수 없는 SQL

SELECT * FROM employees WHERE UPPER(department) = 'SALES';

 

• 인덱스 컬럼에 UPPER() 함수가 적용되면 옵티마이저가 인덱스를 사용할 수 없음.

해결 방법: 함수 기반 인덱스(Function-Based Index) 활용

CREATE INDEX emp_dept_upper_idx ON employees(UPPER(department));

 

• 함수 기반 인덱스를 활용하면 SQL 실행 시 인덱스를 사용할 수 있도록 최적화 가능.

---

3.2 LIKE 검색 최적화 – 앞부분 와일드카드 제거

문제: 인덱스를 사용할 수 없는 LIKE 검색

SELECT * FROM employees WHERE department LIKE '%Sales';

 

• 와일드카드(%)가 앞에 오면 인덱스를 사용할 수 없음.

해결 방법: FULL TEXT SEARCH 또는 앞부분 패턴 검색으로 변경

SELECT * FROM employees WHERE department LIKE 'Sales%';

 

• 앞부분 패턴을 지정하면 Index Range Scan을 사용할 가능성이 높아짐.

---

3.3 인덱스 생성 및 실행 계획 확인

인덱스를 추가하여 SQL이 인덱스를 활용하도록 유도할 수 있다.

CREATE INDEX emp_dept_idx ON employees(department);

 

인덱스를 추가한 후, 실행 계획을 다시 확인하여 인덱스가 제대로 적용되었는지 분석해야 한다.

EXPLAIN PLAN FOR  SELECT * FROM employees WHERE department = 'Sales'; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

 

• 'INDEX RANGE SCAN'이 나타나면 인덱스가 정상적으로 적용된 것.
• 'TABLE ACCESS FULL'이 그대로 유지되면 인덱스를 활용하지 못하고 있는 것.

---

 

ASH 데이터를 활용하면 특정 SQL이 실행될 때 인덱스를 사용하고 있는지, Full Table Scan이 발생하고 있는지 실시간으로 분석할 수 있다.

 

SQL 실행 계획만 확인하는 것이 아니라, 실제 실행 중인 SQL의 대기 이벤트를 분석하여 Full Table Scan이 많은 SQL을 식별하고 최적화하는 것이 중요하다.

 

이를 해결하기 위해 WHERE 절을 최적화하고, LIKE 검색을 조정하며, 적절한 인덱스를 생성하면 성능을 크게 개선할 수 있다. 특히 서버 이관 후에는 기존과 다른 옵티마이저 동작이 나타날 가능성이 높으므로, SQL 실행 패턴을 지속적으로 모니터링하고 최적화하는 것이 권장한다.