개요
SQL Plan History (이하 SPH) 기능 사용을 위한 방법을 안내합니다.
SPH는 쿼리 수행에 필요한 PLAN을 저장하고 이력을 관리하는 기능입니다.
( 오라클의 SQL Plan Baseline 기능과 유사하다. )
SPH 필요성은 아래와 같습니다.
•
최적의 플랜으로 동작하는 쿼리가 다양한 이유로 인해 (파라미터 변경 부작용, 패치 후 영향, 통계정보 갱신 후 Optimizer의 오판 등) 플랜이 변경되어 쿼리의 성능 저하를 일으킬 수 있습니다.
•
캐시에 저장되는 플랜은 재기동, LRU Algorithm 등의 이유로 캐시에서 밀려날 수 있기 때문에, 이 전의 플랜을 다시 불러올 수 없는 경우가 발생합니다.
방법
SPH 사용 방법
DBMS_SPH 패키지를 사용하여 플랜을 저장하고 조회합니다.
•
UPDATE_PLAN_HISTORY(N) : 현재 Library cache의 plan 중 N번 이상 실행된 plan을 저장
•
REPORT_PLANS(), REPORT_PLAN_HISTORY().. : PLAN 변경 내역을 조회
•
SYS._SPH, SYS._SPH_TEXT, SYS._PLAN, SYS._SPH_VARIABLES, SYS._SPH_IPARAMS : 관련 테이블
SPH 사용 예시
시나리오는 다음과 같습니다.
create table tibero.sph_test(c1 number);
insert into tibero.sph_test select level from dual connect by level < 100000;
commit;
select * from tibero.sph_test where c1=8629; -- 4회 수행
/*
set autot on
SQL ID: 516a56bm7x7d8
Child number: 82
Plan hash value: 1698064395
*/
exec dbms_sph.update_plan_history(2);
SELECT * FROM SYS._SPH WHERE PLAN_HASH_VALUE='1698064395'; -- SPH 테이블에 실렸는지 확인
alter table tibero.sph_test add primary key (c1); -- 플랜 변경
select * from tibero.sph_test where c1=8629; -- 4회 수행
/*
set autot on
SQL ID: 516a56bm7x7d8
Child number: 111
Plan hash value: 854204648
*/
exec dbms_sph.update_plan_history(2);
SELECT * FROM SYS._SPH WHERE PLAN_HASH_VALUE='854204648'; -- SPH 테이블에 실렸는지 확인
select SQL_HASH_VALUE from sys._sph where PLAN_HASH_VALUE='1384442108';
--SQL_HASH_VALUE : 3867057576
select SQL_HASH_VALUE from sys._sph where PLAN_HASH_VALUE='423184405';
--SQL_HASH_VALUE : 3867057576
set serveroutput on
exec dbms_sph.report_plans(3867057576, 24, FALSE); --SQL_HASH_VALUE, DURATION, TO_FILE
SQL
복사
출력되는 결과는 아래와 같습니다.
•
Plan의 변화가 시간 역순으로 출력
•
테이블에 primary key 제약조건을 준 뒤,
index scan을 하는 실행 계획으로 변경
•
테이블 생성 직후 조회에서 table full scan을 하는 실행 계획