오라클 19c - 자동 인덱싱(Auto Indexing) 및 자동 SQL 튜닝(Auto SQL Tuning) 기능(ㅇ)

오라클 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 실행 계획을 분석하여 실행 속도를 자동 최적화.
• 두 기능을 함께 사용하면 쿼리 성능을 자동으로 관리하고 최적화하는 강력한 성능 튜닝 환경을 구축할 수 있음.