오라클 19c에서 제공하는 자동 인덱싱(Auto Indexing) 및 자동 SQL 튜닝(Auto SQL Tuning) 기능은 데이터베이스의 성능을 최적화하는 핵심 기술입니다.
자동 인덱싱(Auto Indexing)
(1) 자동 인덱싱 개요
- 오라클 19c의 자동 인덱싱(Auto Indexing) 기능은 실제 쿼리 실행 패턴을 분석하여 인덱스를 자동 생성, 모니터링, 유지보수하는 기능입니다.
- 이를 통해 DBA의 개입 없이도 쿼리 성능을 최적화할 수 있습니다.
(2) 특징
- 쿼리 실행 계획을 지속적으로 분석하여 필요한 인덱스 자동 생성
- 기존 인덱스와 비교하여 불필요한 인덱스는 자동 삭제 또는 비활성화
- 인덱스 사용 패턴을 학습하여 자주 사용되는 경우만 유지
- 자동으로 생성된 인덱스는 초기에는 가상 인덱스 형태로 적용 후 성능 검증 후 영구 저장
(3) 자동 인덱싱 활성화 및 설정
자동 인덱싱 상태 확인
SELECT parameter_name, parameter_value FROM dba_auto_index_config;
자동 인덱싱 활성화
ALTER SYSTEM SET AUTO_INDEX_MODE = IMPLEMENT;
- IMPLEMENT : 자동 생성된 인덱스를 활성화하여 실제 쿼리에서 사용.
- REPORT ONLY : 인덱스를 생성하되, 실제 사용은 하지 않음 (성능 테스트 용도).
- OFF : 자동 인덱싱 기능 비활성화.
자동 생성된 인덱스 목록 확인
SELECT table_name, index_name, auto, status
FROM dba_indexes
WHERE auto = 'YES';- AUTO = YES : 오라클이 자동으로 생성한 인덱스.
- STATUS = VALID : 현재 활성화된 인덱스.
특정 테이블에 대한 자동 인덱싱 비활성화
BEGIN
DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_TABLE', 'SCHEMA.TABLE_NAME', 'OFF');
END;
/
자동 인덱스 리포트 확인
SELECT DBMS_AUTO_INDEX.REPORT_ACTIVITY(SYSDATE - 7, SYSDATE, 'TEXT')
FROM dual;- 최근 7일 동안 생성된 자동 인덱스 활동 내역 조회.
(4) 자동 인덱싱 작동 방식
- 쿼리 실행 패턴 분석 : 오라클 옵티마이저는 실행되는 SQL 문을 분석하고, 적절한 인덱스가 없는 경우를 감지
- 가상 인덱스 생성 : 먼저, 실제 인덱스를 생성하지 않고 가상 인덱스(Virtual Index) 형태로 적용하여 성능 평가 수행.
- 성능 테스트 및 검증 : 일정 시간 동안 SQL 실행 패턴을 모니터링하여 실제로 성능이 개선되는지 평가.
- 영구 인덱스 적용 : 성능이 향상된 것으로 검증되면 실제 인덱스로 변환하여 적용. 만약 쿼리에서 자주 사용되지 않으면 자동으로 제거됨.
(5) 자동 인덱싱의 장점
- DBA의 개입 없이 자동 최적화
- 불필요한 인덱스 최소화 (사용되지 않는 인덱스는 제거)
- 워크로드 변화에 대응 (동적 SQL 및 실행 패턴이 바뀌어도 자동 최적화)
- 스토리지 절약 (필요한 경우에만 인덱스 유지
자동 SQL 튜닝(Auto SQL Tuning)
(1) 자동 SQL 튜닝 개요
- 오라클 19c의 자동 SQL 튜닝(Auto SQL Tuning) 기능은 SQL 실행 계획을 분석하여 최적의 실행 계획을 자동 적용하는 기능입니다
- SQL 튜닝 어드바이저(SQL Tuning Advisor)를 활용하여 자동으로 문제점을 찾아 수정합니다.
(2) 특징
- 매일 자동으로 실행되며, 성능이 저하된 SQL을 탐지 후 튜닝 권장 사항을 제공
- 튜닝 결과를 바탕으로 SQL Profile을 생성하여 자동 적용 가능.
- Adaptive Execution Plans(적응형 실행 계획)을 활용하여 실행 중에도 최적화 수행.
(3) 자동 SQL 튜닝 활성화 및 설정
자동 SQL 튜닝 실행 상태 확인
SELECT status FROM dba_advisor_tasks WHERE task_name = 'SYS_AUTO_SQL_TUNING_TASK';
자동 SQL 튜닝 활성화
BEGIN
DBMS_AUTO_TASK_ADMIN.ENABLE(
client_name => 'sql tuning advisor',
operation => NULL,
window_name => NULL
);
END;
/
✅ 특정 SQL 문에 대한 SQL 튜닝 실행
DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text => 'SELECT * FROM employees WHERE department_id = 10',
user_name => 'HR',
scope => 'COMPREHENSIVE',
time_limit => 60,
task_name => 'SQL_TUNE_TASK1'
);
DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => 'SQL_TUNE_TASK1');
END;
/- sql_text : 튜닝할 SQL 문 지정.
- scope = COMPREHENSIVE : 전체적인 튜닝 분석 수행.
- time_limit : 튜닝 수행 시간 제한 (초 단위).
튜닝 결과 확인
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_TUNE_TASK1') FROM dual;
자동 SQL Profile 적용
EXEC DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(task_name => 'SQL_TUNE_TASK1', name => 'SQL_PROFILE1');
(4) 자동 SQL 튜닝 작동 방식
- 1. 실행 계획 분석 : 오라클은 성능이 저하된 SQL 문을 찾아 실행 계획을 평가.
- 2. SQL Profile 생성 : 실행 계획을 개선할 수 있는 힌트 및 통계를 적용하여 SQL Profile을 생성.
- 3. SQL Profile 적용 : 최적화된 SQL Profile을 적용하여 쿼리 성능을 자동 개선.
- 4. Adaptive Execution Plan 활용 : 실행 중에도 실시간으로 실행 계획을 조정하여 튜닝 수행.
(4) 자동 SQL 튜닝의 장점
- 자동 분석 및 최적화 (DBA 개입 최소화)
- 쿼리 성능 개선 (적절한 실행 계획 적용)
- 적응형 실행 계획 지원 (실시간 최적화)
- 자동 SQL Profile 적용 가능
자동 인덱싱 vs. 자동 SQL 튜닝 비교
| 기능 | 자동 인덱싱 (Auto Indexing) | 자동 SQL 튜닝 (Auto SQL Tuning) |
| 목적 | 자동으로 인덱스 생성 및 최적화 | 실행 계획 분석 및 SQL Profile 적용 |
| 동작 방식 | SQL 실행 패턴 분석 후 자동 인덱스 생성 | SQL 실행 계획을 분석 후 최적화 적용 |
| 핵심 기능 | 필요한 인덱스 자동 생성 및 삭제 성능 분석 후 유지 여부 결정 | SQL 실행 계획 평가 및 변경 SQL Profile 적용 |
| 적용 대상 | 인덱스가 필요한 테이블 실행 속도가 느린 SQL 문 | DBA 개입 최소화 필요 시 튜닝 작업 가능 |
결론
- 자동 인덱싱은 자주 실행되는 쿼리에 최적화된 인덱스를 자동 생성하여 성능을 개선.
- 자동 SQL 튜닝은 SQL 실행 계획을 분석하여 실행 속도를 자동 최적화.
- 두 기능을 함께 사용하면 쿼리 성능을 자동으로 관리하고 최적화하는 강력한 성능 튜닝 환경을 구축할 수 있음.