SQL Tuning Advisor 작업 생성·실행 및 권장 사항 확인

명령어

-- 1) Create and run a task for a SQL in the cursor cache:
VAR task VARCHAR2(64)
BEGIN
  :task := DBMS_SQLTUNE.CREATE_TUNING_TASK(
             sql_id      => '<sql_id>',
             scope       => DBMS_SQLTUNE.SCOPE_COMPREHENSIVE,
             time_limit  => 300,
             task_name   => 'STA_<sql_id>',
             description => 'Slow report query');
  DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => :task);
END;
/
-- For a historical SQL in AWR: begin_snap => <b>, end_snap => <e>, sql_id => '<sql_id>'

-- 2) Status and report:
SELECT task_name, status FROM dba_advisor_log WHERE task_name = :task;

SET LONG 1000000 LONGCHUNKSIZE 1000000 LINESIZE 200 PAGESIZE 0
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK(:task) FROM dual;

-- 3) Get the recommendations as an executable script:
SELECT DBMS_SQLTUNE.SCRIPT_TUNING_TASK(:task) FROM dual;

-- 4) When done:
EXEC DBMS_SQLTUNE.DROP_TUNING_TASK(task_name => :task);

하는 일

SQL에 대해 옵티마이저를 확장된 "tuning mode"로 실행하여 누락된 통계, 새 인덱스, SQL profile, 대체 계획, SQL 재구성에 대한 권장 사항을 생성한다. profile 권장 사항은 테스트 실행으로 검증한다.

사용 시점

문제가 되는 단일 SQL에 대해 빠른 second opinion이 필요할 때, 또는 코드를 바꾸지 않고 실행 계획 회귀를 SQL profile로 해결하고자 할 때.

주의

Tuning Pack 라이선스가 필요하다. COMPREHENSIVE 범위는 테스트를 위해 SQL을 실행할 수 있다(DML은 쿼리 부분만). 운영 환경에서는 time_limit을 설정한다. 인덱스 권장 사항은 나머지 워크로드를 고려하지 않으므로 DML 비용은 별도로 평가한다. 사용자에게 ADVISOR 권한이 필요하다.