주 콘텐츠로 건너뛰기

검색

Welcome to Tibero GTS!

SQL Plan History (SPH) 기능 사용 안내

문서유형ㅣ기술정보

분야ㅣ튜닝

적용제품버전ㅣTibero7.2.4

문서번호ㅣTTUTI028

개요

본 문서는 SQL Plan History (이하 SPH) 기능 사용을 위한 방법에 대해 안내합니다.

 

방법

실행계획은 V$SQL_PLAN 등의 dynamic view를 통해 조회할 수 있습니다. 

그러나 실행계획이 오랜기간 사용되지 않거나 서버가 재부팅되면 그 실행계획은 메모리에서 삭제되므로 오랜 시간에 걸친 실행계획의 변화를 감지하고 추적하기가 어렵습니다.

이때 SPH는 실행계획을 별도의 테이블에 저장함으로써 시간에 따른 실행계획 변화를 사용자가 쉽게 관리할 수 있도록 도와줍니다. 

주의

단, 이 패키지는 SYS 사용자만 사용 가능합니다.

 

시나리오

SQL> create table tibero.sph_test(c1 number);
SQL> insert into tibero.sph_test select level from dual connect by level < 100000;
SQL> commit;

SQL> set autot on
SQL> select * from tibero.sph_test where c1=8629; -- 4회 수행

/*
SQL ID: 516a56bm7x7d8
Child number: 283
Plan hash value: 808101735
*/

SQL> exec dbms_sph.update_plan_history(2);

SQL> SELECT * FROM SYS._SPH WHERE PLAN_HASH_VALUE='808101735'; -- SPH 테이블에 실렸는지 확인

--SQL_HASH_VALUE : 3867057576
SQL> alter table tibero.sph_test add primary key (c1); -- 플랜 변경

SQL> select * from tibero.sph_test where c1=8629; -- 4회 수행

/*
SQL ID: 516a56bm7x7d8
Child number: 371
Plan hash value: 3929546620
*/

SQL> exec dbms_sph.update_plan_history(2);

SQL> SELECT * FROM SYS._SPH WHERE PLAN_HASH_VALUE='3929546620'; -- SPH 테이블에 실렸는지 확인

--SQL_HASH_VALUE : 3867057576


SQL> set autot off
SQL> set serveroutput on
SQL> exec dbms_sph.report_plans(3867057576, 24, FALSE); --SQL_HASH_VALUE, DURATION, TO_FILE

 

시나리오 결과

Plan의 변화가 시간순으로 출력됩니다.

  1. 테이블 생성 직후 조회에서 table full scan을 하는 실행 계획
  2. 테이블에 primary key 제약조건을 준 뒤, index scan을 하는 실행 계획으로 변경
SQL> set serveroutput on
SQL> exec dbms_sph.report_plans(3867057576, 24, FALSE);
 
                         +-------------------------+
                         | SQL_PLAN_HISTORY REPORT |
                         +-------------------------+
 
FROM: 2025/11/03
TO:   2025/11/04
 
 
================================================================================
SQL Hash Value: 3867057576
 
SQL: 
select * from tibero.sph_test where c1=8629
================================================================================
 
Plan Hash Value :    808101735
User ID         :            0
Schema ID       :            0
 
Modified Parameters : 
    _OPT_ADJUST_TABLE_PRED_SELECTIVITY=100
    _OPT_BIND_PEEKING="NO"
 
Plan Statistics
    Executions       :                   8
    Fetches          :                   8
    Optimizer cost   :                  83
    Buffer gets      :                1328 (AVG:              166.00)
    Rows processed   :                   8 (AVG:                1.00)
    Elapsed time(us) :               30773 (AVG:             3846.63)
                           00:00:00.030773 (AVG:     00:00:00.003846)
    First load       : 2025/11/04
    Last execution   : 2025/11/04
 
SQL Hash Value: 3867057576
Plan Hash Value: 808101735
 
Execution Plan
--------------------------------------------------------------------------------
   1  TABLE ACCESS (FULL): SPH_TEST (Cost:83, %CPU:2, Rows:1) 
 
Predicate Information
--------------------------------------------------------------------------------
   1 - filter: ("SPH_TEST"."C1" = 8629) (0.000)
--------------------------------------------------------------------------------
 
No bind variables captured.
 
Plan Hash Value :   3929546620
User ID         :            0
Schema ID       :            0
 
Modified Parameters : 
    _OPT_ADJUST_TABLE_PRED_SELECTIVITY=100
    _OPT_BIND_PEEKING="NO"
 
Plan Statistics
    Executions       :                   4
    Fetches          :                   4
    Optimizer cost   :                   2
    Buffer gets      :                   8 (AVG:                2.00)
    Rows processed   :                   4 (AVG:                1.00)
    Elapsed time(us) :                 656 (AVG:              164.00)
                           00:00:00.000656 (AVG:     00:00:00.000164)
    First load       : 2025/11/04
    Last execution   : 2025/11/04
 
SQL Hash Value: 3867057576
Plan Hash Value: 3929546620
 
Execution Plan
--------------------------------------------------------------------------------
   1  COLUMN PROJECTION (Cost:2, %CPU:0, Rows:1) 
   2    INDEX (UNIQUE SCAN): _TIBERO_CON56100334 (Cost:2, %CPU:0, Rows:1) 
 
Predicate Information
--------------------------------------------------------------------------------
   2 - access: ("SPH_TEST"."C1" = 8629) (0.000)
--------------------------------------------------------------------------------
 
No bind variables captured.
 
 

PSM completed.

 

수행 방법

1. DBMS_SPH.UPDATE_PLAN_HISTORY

라이브러리 cache의 실행계획을 SPH에 저장합니다.

PROCEDURE UPDATE_PLAN_HISTORY 
(
    MIN_EXEC_COUNT    IN PLS_INTEGER  DEFAULT 1
);

예제

SQL> exec dbms_sph.update_plan_history(2); 
           -- 두 번 이상 실행된 실행계획을 SPH에 저장

 

2. DBMS_SPH.REPORT_PLANS

지정된 SQL의 실행계획 변화 이력을 출력합니다.
PROCEDURE REPORT_PLANS 
(
   SQL_HASH_VALUE  IN NUMBER,  -- 리포트할 SQL의 HASH VALUE이다.
   DURATION          IN PLS_INTEGER    DEFAULT 24*365*1000,    -- SQL의 이력을 출력할 기간이다. 시작 지점을 의미하며 종료 시점은 현재이다. 시간(h) 단위이다.
   TO_FILE           IN BOOLEAN        DEFAULT TRUE  -- 리포트 내용을 파일로 저장할지를 설정한다.
);

예제

SQL> set serveroutput on
SQL> exec dbms_sph.report_plans('HASH_VALUE', 24, FALSE); 
           -- 화면에 하루 동안의 실행계획을 모두 출력

참고

화면에 출력을 원할 때(인자 TO_FILE이 FALSE일 때)는 serveroutput을 켜고 실행해야 합니다.

파일로 출력을 원할 때 (인자 TO_FILE이 TRUE일 때)는 해당 경로에 저장됩니다. $TB_HOME/instance/$TB_SID/dump/report/sph_report.{mthr_pid}.{current_time}

 

3. DBMS_SPH.REPORT_PLANS_BY_DATE

지정된 SQL의 실행계획 변화 이력을 출력합니다. 
REPORT_PLANS와 동작이 같으나 인자의 타입만 다릅니다.

PROCEDURE REPORT_PLANS_BY_DATE 
(
    SQL_HASH_VALUE  IN NUMBER, -- 리포트할 SQL의 HASH VALUE이다.
    START_DATE        IN DATE       DEFAULT SYSDATE - 365*1000,  -- SQL의 이력을 출력할 기간이다. 시작 지점을 의미하며 종료 시점은 현재이다. 일(d) 단위이다.
    TO_FILE           IN BOOLEAN    DEFAULT TRUE -- 리포트 내용을 파일로 저장할지를 설정한다.
);

예제

SQL> set serveroutput on
SQL> exec dbms_sph.report_plans_by_date('HASH_VALUE', sysdate - 1, FALSE); 
            -- 화면에 하루 동안의 실행계획을 모두 출력

 

4. DBMS_SPH.REPORT_PLAN_HISTORY

조건을 만족하는 모든 SQL의 실행계획 변화 이력을 출력합니다.

PROCEDURE REPORT_PLAN_HISTORY 
(
    DURATION          IN PLS_INTEGER  DEFAULT 24*365*1000, -- SQL의 이력을 출력할 기간이다. 시작 지점을 의미하며 종료 시점은 현재이다. 시간(h) 단위이다.
    MIN_PLAN_COUNT    IN PLS_INTEGER  DEFAULT 1, -- 출력할 SQL의 최소 실행계획 수이다. 예를 들어 2로 지정하면 둘 이상의 실행계획을 가지는 SQL만 출력한다.
    TO_FILE           IN BOOLEAN      DEFAULT TRUE -- 리포트 내용을 파일로 저장할지를 설정한다.
);

예제

SQL> set serveroutput on
SQL> exec dbms_sph.report_plan_history(24, 1, FALSE);
                -- 화면에 하루 동안 실행된 모든 SQL의 실행계획을 모두 출력

 

5. DBMS_SPH.REPORT_PLAN_HISTORY_BY_DATE

조건을 만족하는 모든 SQL의 실행계획 변화 이력을 출력합니다. 
REPORT_PLAN_HISTORY와 동작이 같으나 인자의 타입만 다릅니다.
 
PROCEDURE REPORT_PLAN_HISTORY_BY_DATE 
(
    START_DATE        IN DATE         DEFAULT SYSDATE - 365*1000, -- SQL의 이력을 출력할 기간이다. 시작 지점을 의미하며 종료 시점은 현재이다. 일(d) 단위이다.
    MIN_PLAN_COUNT    IN PLS_INTEGER  DEFAULT 1, -- 출력할 SQL의 최소 실행계획 수이다. 예를 들어 2로 지정하면 둘 이상의 실행계획을 가지는 SQL만 출력한다.
    TO_FILE           IN BOOLEAN      DEFAULT TRUE -- 리포트 내용을 파일로 저장할지를 설정한다.
);

예제

SQL> set serveroutput on
SQL> exec dbms_sph.report_plan_history_by_date(sysdate - 1, 1, FALSE); 
                 -- 화면에 하루 동안 실행된 모든 SQL의 실행계획을 모두 출력

 

6. DBMS_SPH.TRUNCATE_PLAN_HISTORY

SPH에 저장된 실행계획 이력을 삭제합니다.
PROCEDURE TRUNCATE_PLAN_HISTORY 
(
    RETENTION_PERIOD  IN PLS_INTEGER  DEFAULT 24*365*1000 -- SQL 이력을 삭제할 기간을 지정한다. 이 기간동안 실행되지 않은 실행계획은 모두 삭제된다. 시간(h) 단위이다.
    MAX_COUNT         IN PLS_INTEGER  DEFAULT 1000000 -- 실행계획을 삭제하지 않고 유지할 SQL의 수이다.
);

예제

SQL> exec dbms_sph.truncate_plan_history(24); 
            -- 하루 동안 실행되지 않은 실행계획을 모두 삭제

참고

RETENTION_PERIOD와 MAX_COUNT를 모두 지정하면 두 조건을 모두 만족시키는 SQL만이 유지됩니다.

 

7. DBMS_SPH.TRUNCATE_PLAN_HISTORY_BY_DATE

SPH에 저장된 실행계획 이력을 삭제합니다. 
TRUNCATE_PLAN_HISTORY와 동작은 같으며 인자만 다릅니다.

PROCEDURE TRUNCATE_PLAN_HISTORY_BY_DATE 
(
    START_DATE        IN DATE         DEFAULT SYSDATE - 365*1000 -- SQL 이력을 삭제할 기준 시점이다. 이 시점 이전의 모든 실행계획 정보가 삭제된다. 시간(h) 단위이다.
    MAX_COUNT         IN PLS_INTEGER  DEFAULT 1000000 -- 실행계획을 삭제하지 않고 유지할 SQL의 수이다.
);

예제

SQL> exec dbms_sph.truncate_plan_history_by_date(sysdate - 1); 
            -- 하루 동안 실행되지 않은 실행계획을 모두 삭제

참고

START_DATE와 MAX_COUNT를 모두 지정하면 두 조건을 모두 만족시키는 SQL만이 유지됩니다.

 

 

 

관련 대상