인덱스 스캔의 효율적인 방식...?

728x90

인덱스는 운영환경에서 DB 를 효율적으로 관리하기 위해 설정하게 되면 효율을 아주 높일 수 있따.

이처럼 인덱스를 설정하게 됬을 때 이것이 효율적인지 아닌지를 어떻게 알수 있을까? 우선 크게 SQL 실행계획 그리고 통계, 그리고 wait event 등을 통해서 알 수가 있다.

. SQL 실행 계획(Execution Plan) 확인

SQL 실행 계획을 확인하는 것은 인덱스 스캔 효율성을 파악하는 첫 번째 단계입니다. 실행 계획은 SQL이 실행될 때 어떻게 처리되는지를 보여주며, 인덱스 사용 여부, 풀 테이블 스캔 여부 등을 확인할 수 있습니다.

1-1. EXPLAIN PLAN 명령어 사용

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

이 명령은 해당 SQL 쿼리가 실행될 때의 실행 계획을 표시합니다. 결과에서 인덱스 스캔 여부와 효율성을 확인할 수 있습니다.

INDEX RANGE SCAN: 특정 범위의 인덱스를 읽는 방식으로, 인덱스의 일부만 읽기 때문에 효율적입니다. ..randomI/O, single block scan
INDEX FULL SCAN: 인덱스의 모든 데이터를 스캔하는 방식으로, 경우에 따라 효율성이 떨어질 수 있습니다.
FULL TABLE SCAN: 인덱스를 사용하지 않고 테이블의 모든 데이터를 읽는 방식으로, 인덱스가 제대로 활용되지 않은 경우에 발생합니다. ... sequential I/O, mutlti blcok io scan

1-2. 실행 계획에서 확인해야 할 항목

Operation: 인덱스 스캔 방식 (예: INDEX RANGE SCAN, INDEX FULL SCAN)
Cost: SQL을 처리하는 데 드는 비용(낮을수록 효율적)
Rows: 예상되는 결과 행 수 (적을수록 성능이 좋음)
Cardinality: 검색 결과의 예상 범위
Predicate Information: 인덱스가 적용되는 필터 조건

2. SQL 실행 통계 확인

실행 계획 외에도 SQL 실행 통계를 통해 인덱스 스캔의 효율성을 측정할 수 있습니다. 특히, 물리적 읽기(Physical Reads)와 논리적 읽기(Logical Reads)를 분석하여 효율성을 평가할 수 있습니다.

2-1. AUTOTRACE 명령어 사용

AUTOTRACE는 SQL 실행 후 실행 통계와 실행 계획을 모두 보여주는 유용한 도구입니다.

SET AUTOTRACE ON EXPLAIN STATISTICS SELECT * FROM employees WHERE department = 'HR';

Consistent Gets: SQL이 인덱스를 사용할 때 발생하는 논리적 읽기 수. 낮을수록 인덱스 스캔이 효율적입니다.
Physical Reads: 디스크에서 데이터를 읽어오는 물리적 읽기 수. 인덱스 스캔 시 이 값이 낮아야 합니다.
Buffer Gets: 데이터 버퍼에서 블록을 읽은 횟수로, 이 값이 낮으면 메모리에서 데이터를 효율적으로 처리하고 있다는 의미입니다.

3. 대기 이벤트(Wait Events) 분석

대기 이벤트는 SQL이 실행될 때 자원을 기다리는 시간에 대한 정보입니다. 인덱스 스캔 효율성을 분석할 때 디스크 I/O 관련 대기 이벤트를 주의 깊게 살펴봐야 합니다.

3-1. V$SESSION_WAIT 뷰 사용

현재 세션에서 대기 중인 이벤트를 확인할 수 있습니다.

SELECT event, wait_time, seconds_in_wait FROM v$session_wait WHERE event LIKE '%db file sequential read%' -- 인덱스 스캔 시 발생하는 대기 이벤트 AND sid = <session_id>;

db file sequential read: 인덱스 스캔 중에 발생하는 이벤트로, 순차적으로 인덱스를 읽는 작업에 대한 대기 시간을 나타냅니다. 이 값이 높으면 디스크 I/O 병목 현상이 발생할 수 있습니다.

4. SQL 프로파일링

SQL 프로파일링은 인덱스 스캔의 효율성을 확인하기 위해 SQL 실행 시 수행된 작업을 상세히 추적하는 방법입니다.

4-1. DBMS_SQLTUNE 패키지 사용

Oracle의 DBMS_SQLTUNE 패키지를 사용하면 SQL 튜닝 작업을 수행할 수 있습니다. SQL 프로파일링을 통해 인덱스 사용을 최적화할 수 있습니다.

BEGIN DBMS_SQLTUNE.CREATE_TUNING_TASK ( sql_id => 'sql_id', -- 특정 SQL 문에 대한 프로파일링 scope => DBMS_SQLTUNE.SCOPE_COMPREHENSIVE, time_limit => 60 ); END;

5. 통계 정보 갱신

인덱스 스캔의 효율성을 높이기 위해, 테이블 및 인덱스의 통계 정보를 최신 상태로 유지하는 것이 중요합니다. 오래된 통계는 잘못된 실행 계획을 초래할 수 있습니다.

sql

-- 테이블 및 인덱스의 통계 정보 갱신 
EXEC DBMS_STATS.GATHER_TABLE_STATS('schema_name', 'table_name'); 
-- 인덱스만 갱신 
EXEC DBMS_STATS.GATHER_INDEX_STATS('schema_name', 'index_name');

이 명령어는 최신 통계 정보를 수집하여 옵티마이저가 보다 효율적인 실행 계획을 생성할 수 있도록 돕습니다.

6. 인덱스 재구성

만약 인덱스의 비효율적인 스캔이 자주 발생한다면, 인덱스가 단편화되었을 가능성이 있습니다. 이런 경우 인덱스 재구성을 통해 성능을 향상시킬 수 있습니다.

-- 인덱스 재구성

ALTER INDEX index_name REBUILD;

인덱스가 재구성되면, 단편화된 블록들이 정리되고, 인덱스 스캔 성능이 향상될 수 있습니다.

7. SQL 힌트 사용

인덱스 스캔이 효율적이지 않거나 옵티마이저가 인덱스를 사용하지 않을 때, SQL **힌트(Hint)**를 사용하여 강제로 인덱스를 사용하도록 유도할 수 있습니다.

SELECT /*+ INDEX(employees emp_dept_idx) */ * FROM employees WHERE department = 'HR';
이 힌트는 employees 테이블에서 emp_dept_idx 인덱스를 강제로 사용하도록 지시합니다.

요약

인덱스 스캔의 효율성을 측정하고 최적화하기 위한 주요 방법은 다음과 같습니다.

SQL 실행 계획 확인: EXPLAIN PLAN과 DBMS_XPLAN.DISPLAY를 사용하여 인덱스 사용 여부와 실행 비용을 확인.
SQL 실행 통계 확인: AUTOTRACE로 실행 통계와 물리적/논리적 읽기 수 확인.
대기 이벤트 분석: V$SESSION_WAIT을 통해 인덱스 스캔 시 발생하는 대기 시간을 분석.
SQL 프로파일링: DBMS_SQLTUNE을 사용하여 SQL 튜닝 작업 수행.
통계 정보 갱신: 테이블 및 인덱스의 통계 정보를 주기적으로 갱신.
인덱스 재구성: 인덱스가 단편화되었을 때 재구성하여 성능을 향상.
SQL 힌트 사용: 옵티마이저가 인덱스를 사용하지 않을 때 힌트를 사용하여 인덱스 강제 사용.

이 방법들을 통해 인덱스 스캔의 효율성을 분석하고 최적화할 수 있습니다.

인덱스의 핵심은 어떻게 하면 random I/O 를 줄이는 것이 핵심으로 적절한 인덱스를 선택해서 진행하는 것이 매우 중요!