문서유형ㅣ기술정보
분야ㅣ튜닝
적용제품버전ㅣ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의 변화가 시간순으로 출력됩니다.
- 테이블 생성 직후 조회에서 table full scan을 하는 실행 계획
- 테이블에 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와 동작이 같으나 인자의 타입만 다릅니다.
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만이 유지됩니다.