서버 이관 프로젝트를 수행하는 과정에서 가장 중요한 목표 중 하나는 데이터베이스 성능을 안정적으로 유지하고 최적화하는 것이다. 새로운 서버 환경에서는 기존과 다르게 SQL 실행 속도, 인덱스 사용 방식, 옵티마이저의 실행 계획 등이 변경될 수 있으며, 예상치 못한 성능 저하가 발생할 가능성이 높다.
그중에서도 **Full Table Scan(풀 스캔)**은 데이터베이스 성능을 저하시킬 수 있는 대표적인 문제 요소다. Full Table Scan이 불필요하게 발생하면 CPU 사용량이 증가하고, 디스크 I/O 부하가 급격히 늘어나며, 쿼리 응답 시간이 길어질 수 있다. 이러한 문제를 해결하려면 불필요한 Full Table Scan을 감지하고 적절한 튜닝 기법을 적용하는 과정이 필수적이다.
Oracle에서는 ASH(Active Session History) 데이터를 활용하여 특정 SQL이 Full Table Scan을 수행하는지를 실시간으로 감지할 수 있다. 이를 분석하면 불필요한 Full Table Scan을 찾아내고, 적절한 인덱스 적용, 실행 계획 최적화, SQL 구조 변경 등의 방법을 통해 성능을 개선할 수 있다.
본 글에서는 ASH 데이터를 활용하여 Full Table Scan을 감지하는 방법과, 이를 최적화하는 다양한 기법을 설명한다. 이를 통해 서버 이관 후 성능 저하를 방지하고, 데이터베이스의 효율성을 극대화할 수 있도록 한다.
1. Full Table Scan이 발생하는 주요 원인
1.1 인덱스가 없거나 적절하지 않은 경우
테이블에 적절한 인덱스가 없거나, 존재하는 인덱스가 SQL의 조건절을 충족하지 않는 경우, 옵티마이저는 Full Table Scan을 선택할 가능성이 높아진다.
1.2 WHERE 절에서 인덱스를 사용할 수 없는 방식으로 작성된 경우
- 인덱스 컬럼에 함수가 적용된 경우 (UPPER(column) = 'VALUE')
- 와일드카드 검색에서 앞부분이 '%'로 시작하는 경우 (LIKE '%keyword')
- 데이터 타입이 일치하지 않아 암시적 형 변환(Implicit Conversion)이 발생한 경우 (column = TO_CHAR(123))
1.3 옵티마이저가 Full Table Scan을 더 효율적이라고 판단한 경우
- 테이블의 크기가 작아 인덱스를 사용하는 것이 오히려 비효율적인 경우
- SELECT 절에서 많은 데이터를 가져오는 경우
- 테이블 통계 정보가 오래되어 옵티마이저가 잘못된 실행 계획을 선택한 경우
이러한 문제를 해결하려면 불필요한 Full Table Scan을 감지하고 최적화하는 과정이 필요하다.
2. ASH 데이터를 활용한 Full Table Scan 감지 방법
2.1 특정 SQL이 Full Table Scan을 수행하는지 확인하기
ASH 데이터를 활용하면 특정 SQL이 실행될 때 Full Table Scan이 발생했는지 확인할 수 있다.
| SELECT sql_id, COUNT(*) AS full_scan_count FROM v$active_session_history WHERE event = 'db file scattered read' 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 full_scan_count DESC; |
- event = 'db file scattered read': Full Table Scan이 발생한 SQL만 조회.
- full_scan_count: 특정 SQL에서 Full Table Scan이 발생한 횟수.
이 데이터를 활용하면 어떤 SQL이 Full Table Scan을 자주 수행하는지 분석할 수 있다.
2.2 특정 테이블에서 Full Table Scan이 많이 발생하는지 확인하기
특정 테이블이 Full Table Scan을 많이 수행하는지 확인하려면 다음 SQL을 실행한다.
| SELECT object_name, COUNT(*) AS full_scan_count FROM v$active_session_history a JOIN dba_objects o ON a.current_obj # = o.object_id WHERE event = 'db file scattered read' 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 object_name ORDER BY full_scan_count DESC; |
- object_name: Full Table Scan이 발생한 테이블명.
- full_scan_count: 특정 테이블에서 Full Table Scan이 발생한 횟수.
이 데이터를 활용하면 특정 테이블에서 반복적으로 Full Table Scan이 발생하는지를 감지할 수 있다.
3. Full Table Scan을 최적화하는 방법
3.1 적절한 인덱스 추가
인덱스가 존재하지 않거나 적절하지 않으면 Full Table Scan이 발생할 가능성이 높다. 실행 계획을 확인하고 필요한 경우 인덱스를 추가해야 한다.
| EXPLAIN PLAN FOR SELECT * FROM employees WHERE department = 'Sales'; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); |
- 'TABLE ACCESS FULL'이 나오면 인덱스를 활용하지 못하고 있다는 의미.
적절한 인덱스를 추가하여 실행 계획을 최적화할 수 있다.
| CREATE INDEX emp_dept_upper_idx ON employees(UPPER(department)); |
- 결과: Full Table Scan이 제거되고 Index Scan으로 변경됨.
3.2 WHERE 절 최적화 – 인덱스 활용 가능하도록 수정
인덱스가 있는 경우에도 WHERE 절이 적절하게 작성되지 않으면 인덱스를 활용하지 못할 수 있다.
문제: 인덱스를 활용하지 못하는 SQL
| SELECT * FROM employees WHERE UPPER(department) = 'SALES'; |
- UPPER() 함수가 적용되면 인덱스를 사용할 수 없음.
해결 방법: 함수 기반 인덱스(Function-Based Index) 적용
| SELECT /*+ INDEX(e emp_dept_idx) */ * FROM employees e WHERE department = 'Sales'; CREATE INDEX emp_dept_upper_idx ON employees(UPPER(department)); |
3.3 옵티마이저의 실행 계획 강제 변경
때로는 옵티마이저가 Full Table Scan을 선택하는 경우가 있는데, 힌트를 활용하여 이를 강제 변경할 수 있다.
| SELECT /*+ INDEX(e emp_dept_idx) */ * FROM employees e WHERE department = 'Sales'; |
- INDEX 힌트를 사용하여 인덱스 활용을 강제할 수 있음.
3.4 테이블 통계 갱신
테이블의 통계 정보가 오래되면 옵티마이저가 잘못된 실행 계획을 선택할 수 있다. 이를 방지하려면 통계 정보를 갱신해야 한다.
| EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA_NAME', 'TABLE_NAME'); |
- 결과: 옵티마이저가 최신 데이터를 기준으로 실행 계획을 다시 생성하여 성능을 최적화함.
ASH 데이터를 활용하면 불필요한 Full Table Scan을 감지하고, 이를 최적화할 수 있는 다양한 방법을 적용할 수 있다.
특히, WHERE 절 최적화, 적절한 인덱스 추가, 옵티마이저 실행 계획 조정, 통계 정보 갱신 등의 기법을 활용하면 SQL 성능을 크게 개선할 수 있다.