DBMS_SPH

DBMS_SPH 패키지의 기본 개념과 패키지 내의 프러시저를 사용하는 방법을 설명합니다.

개요

DBMS_SPH 패키지는 SQL Plan History(이하 SPH) 기능을 사용하는데 필요로 하는 기능을 제공하기 위 한 패키지입니다. SPH는 서버가 실행한 SQL의 이력을 저장 및 관리해주는 기능입니다.

Tibero는 SQL로부터 실행계획을 만들어 실행합니다. 이 실행계획은 V$SQL_PLAN 등의 dynamic view를 통해 조회할 수 있습니다. 그러나 실행계획이 오래동안 사용되지 않거나 서버가 재부팅되면 그 실행계획은 메 모리에서 삭제됩니다. 이 때문에 오랜 시간에 걸친 실행계획의 변화를 감지하고 추적하기가 어렵습니다.

SPH는 실행계획을 별도의 테이블에 저장함으로써 시간에 따른 실행계획 변화를 사용자가 쉽게 관리할 수 있게 도와줍니다. 단, 이 패키지는 SYS 사용자만 사용 가능합니다.

프러시저

본 절에서는 DBMS_SPH 패키지에서 제공하는 프러시저를 설명합니다.

REPORT_PLANS

지정된 SQL의 실행계획 변화 이력을 출력합니다. REPORT_PLANS_BY_DATE와 동작이 같으나 인자의 타입만 다릅니다. 결과는 화면에 출력할 수 있고 파일로 저장할 수도 있습니다. 화면에 출력을 원할 때(인자 TO_FILE 이 FALSE일 때)는 serveroutput을 켜고 실행해야 합니다.

다음은 결과가 파일로 저장되는 경로입니다.

$TB_HOME/instance/$TB_SID/dump/report/sph_report.{mthr_pid}.{current_time}

REPORT_PLANS 프러시저의 세부 내용은 다음과 같습니다.

• 프로토타입

PROCEDURE REPORT_PLANS
(
    SQL_HASH_VALUE     IN NUMBER,       
    DURATION             IN PLS_INTEGER        DEFAULT 24*365*1000,
    TO_FILE             IN BOOLEAN             DEFAULT TRUE
);

• 파라미터

파라미터	설명
SQL_HASH_VALUE	리포트할 SQL의 HASH VALUE
DURATION	SQL의 이력을 출력할 기간 인자는 시작 시점을 의미하며 종료 시점은 항상 현재 기준 : 실행계획의 마지막 실행시간 단위 : 시간(hour) – 범위 : 0~243651000 – 기본값 : 243651000시간(약 1000년)
TO_FILE	리포트 내용을 파일로 저장할지를 설정 TRUE : 내용이 파일에 저장 (기본값) FALSE : 내용이 화면에 출력

• 예외 상황

예외 상황	설명
PARAMETER_OUT_OF_RANGE	파라미터가 지정된 범위를 벗어난 경우

• 예제

set serveroutput on
select hash_value from v$sqlarea where sql_text like 'select hash_value%';

HASH_VALUE
-------------
3870695416

1 row selected.

exec dbms_sph.report_plans(3870695416, 24, FALSE);
-- 화면에 하루 동안의 실행계획을 모두 출력

+-------------------------+
| SQL_PLAN_HISTORY REPORT |
+-------------------------+

FROM: 1997/05/29 17:29:41
TO: 1997/05/30 17:29:41

... 생략 ...

REPORT_PLANS_BY_DATE

지정된 SQL의 실행계획 변화 이력을 출력합니다. REPORT_PLANS와 동작이 같으나 인자의 타입만 다릅니다. 결과는 화면에 출력할 수 있고 파일로 저장할 수도 있습니다. 화면에 출력을 원할 때(인자 TO_FILE이 FALSE 일 때)는 serveroutput을 켜고 실행해야 합니다.

다음은 결과가 파일로 저장되는 경로입니다.

$TB_HOME/instance/$TB_SID/dump/report/sph_report.{mthr_pid}.{current_time}

REPORT_PLANS_BY_DATE 프러시저의 세부 내용은 다음과 같습니다.

• 프로토타입

PROCEDURE REPORT_PLANS_BY_DATE
(
    SQL_HASH_VALUE     IN NUMBER,
    START_DATE          IN DATE        DEFAULT SYSDATE - 365*1000,
    TO_FILE             IN BOOLEAN     DEFAULT TRUE
);

• 파라미터

파라미터	설명
SQL_HASH_VALUE	리포트할 SQL의 HASH VALUE
START_DATE	SQL의 이력을 출력할 기간의 시작 시점. 종료 시점은 항상 현재. 기준 : 실행계획의 마지막 실행 시간 단위 : 일(day) 범위 : SYSDATE - 3651000 ~ SYSDATE 기본값 : SYSDATE - 3651000(SYSDATE로부터 약 1000년)
TO_FILE	리포트 내용을 파일로 저장할지를 설정 TRUE : 내용이 파일에 저장 (기본값) FALSE : 내용이 화면에 출력.

• 예외 상황

예외 상황	설명
PARAMETER_OUT_OF_RANGE	파라미터가 지정된 범위를 벗어난 경우

• 예제

set serveroutput on
select hash_value from v$sqlarea where sql_text like 'select hash_value%'; 

HASH_VALUE
-------------
3870695416

1 row selected.

-- 화면에 하루 동안의 실행계획을 모두 출력
exec dbms_sph.report_plans_by_date(3870695416, sysdate - 1, FALSE);

+-------------------------+
| SQL_PLAN_HISTORY REPORT |
+-------------------------+

FROM: 1997/05/29 17:49:11
TO: 1997/05/30 17:49:11

... 생략 ...

REPORT_PLAN_HISTORY

조건을 만족하는 모든 SQL의 실행계획 변화 이력을 출력합니다. REPORT_PLAN_HISTORY_BY_DATE와 동작이 같으나 인자의 타입만 다릅니다. 결과는 화면에 출력할 수 있고 파일로 저장할 수도 있습니다. 화면에 출력을 원할 때(인자 TO_FILE이 FALSE일 때)는 serveroutput을 켜고 실행해야 합니다.

다음은 결과가 파일로 저장되는 경로입니다.

$TB_HOME/instance/$TB_SID/dump/report/sph_report.{mthr_pid}.{current_time}

REPORT_PLAN_HISTORY 프러시저의 세부 내용은 다음과 같습니다.

• 프로토타입

PROCEDURE REPORT_PLAN_HISTORY
(
    DURATION            IN PLS_INTEGER    DEFAULT 24*365*1000,
    MIN_PLAN_COUNT      IN PLS_INTEGER    DEFAULT 1,
    TO_FILE             IN BOOLEAN        DEFAULT TRUE
);

• 파라미터

파라미터	설명
DURATION	SQL의 이력을 출력할 기간. 인자는 시작 시점을 의미하며 종료 시점은 항상 현재. 기준 : 실행계획의 마지막 실행 시간 단위 : 시간(hour) – 범위 : 0~243651000 – 기본값 : 243651000시간(약 1000년)
MIN_PLAN_COUNT	출력할 SQL의 최소 실행계획 수. 예를 들어 2로 지정하면 둘 이상의 실 행계획을 가지는 SQL만 출력. – 범위 : 1 ~ 1000000 – 기본값 : 1
TO_FILE	리포트 내용을 파일로 저장할지를 설정. TRUE : 내용이 파일에 저장됨. (기본값) FALSE : 내용이 화면에 출력됨.

• 예외 상황

예외 상황	설명
PARAMETER_OUT_OF_RANGE	파라미터가 지정된 범위를 벗어난 경우

• 예제

set serveroutput on

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

+-------------------------+
| SQL_PLAN_HISTORY REPORT |
+-------------------------+

FROM: 1997/05/29 17:49:11
TO: 1997/05/30 17:49:11

... 생략 ...

REPORT_PLAN_HISTORY_BY_DATE

조건을 만족하는 모든 SQL의 실행계획 변화 이력을 출력합니다. REPORT_PLAN_HISTORY와 동작이 같 으나 인자의 타입만 다릅니다. 결과는 화면에 출력할 수 있고 파일로 저장할 수도 있습니다. 화면에 출력을 원할 때(인자 TO_FILE이 FALSE일 때)는 serveroutput을 켜고 실행해야 합니다.

다음은 결과가 파일로 저장되는 경로입니다.

$TB_HOME/instance/$TB_SID/dump/report/sph_report.{mthr_pid}.{current_time}

REPORT_PLAN_HISTORY_BY_DATE 프러시저의 세부 내용은 다음과 같습니다.

• 프로토타입

PROCEDURE REPORT_PLAN_HISTORY_BY_DATE
(
    START_DATE          IN DATE           DEFAULT SYSDATE - 365*1000,
    MIN_PLAN_COUNT     IN PLS_INTEGER   DEFAULT 1,
    TO_FILE             IN BOOLEAN        DEFAULT TRUE
);

• 파라미터

파라미터	설명
START_DATE	SQL의 이력을 출력할 기간의 시작 시점. 종료 시점은 항상 현재. 기준 : 실행계획의 마지막 실행 시간 단위 : 일(day) 범위 : SYSDATE - 3651000 ~ SYSDATE 기본값 : SYSDATE - 3651000(SYSDATE로부터 약 1000년)
MIN_PLAN_COUNT	출력할 SQL의 최소 실행계획 수. 예를 들어 2로 지정하면 둘 이상의 실 행계획을 가지는 SQL만 출력. – 범위 : 1 ~ 1000000 – 기본값 : 1
TO_FILE	리포트 내용을 파일로 저장할지를 설정. – TRUE : 내용이 파일에 저장. (기본값) – FALSE : 내용이 화면에 출력.

• 예외 상황

예외 상황	설명
PARAMETER_OUT_OF_RANGE	파라미터가 지정된 범위를 벗어난 경우.

• 예제

set serveroutput on

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

+-------------------------+
| SQL_PLAN_HISTORY REPORT |
+-------------------------+

FROM: 1997/05/29 17:49:11
TO: 1997/05/30 17:49:11

... 생략 ...

TRUNCATE_PLAN_HISTORY

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

TRUNCATE_PLAN_HISTORY 프러시저의 세부 내용은 다음과 같습니다.

• 프로토타입

PROCEDURE TRUNCATE_PLAN_HISTORY
(
    RETENTION_PERIOD      IN PLS_INTEGER    DEFAULT 24*365*1000,
    MAX_COUNT             IN PLS_INTEGER    DEFAULT 1000000
);

• 파라미터

파라미터	설명
RETENTION_PERIOD	SQL 이력을 삭제할 기간을 지정. 이 기간동안 실행되지 않은 실행계획은 모두 삭제됨 – 단위 : 시간(hour) – 범위 : 0 ~ 243651000 – 기본값 : 243651000(약 1000년)
MAX_COUNT	실행계획을 삭제하지 않고 유지할 SQL의 수 RETENTION_PERIOD와 MAX_COUNT를 모두 지정하면 두 조건을 모두 만족시키는 SQL만이 유지 – 범위 : 0 ~ 1000000 – 기본값 : 1000000

• 예외 상황

예외 상황	설명
PARAMETER_OUT_OF_RANGE	파라미터가 지정된 범위를 벗어난 경우

• 예제

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

PSM completed.

TRUNCATE_PLAN_HISTORY_BY_DATE

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

TRUNCATE_PLAN_HISTORY_BY_DATE 프러시저의 세부 내용은 다음과 같습니다.

• 프로토타입

PROCEDURE TRUNCATE_PLAN_HISTORY_BY_DATE
(
    START_DATE     IN DATE           DEFAULT SYSDATE - 365*1000,
    MAX_COUNT      IN PLS_INTEGER    DEFAULT 1000000
);

• 파라미터

파라미터	설명
START_DATE	SQL 이력을 삭제할 기준 시점. 이 시점 이전의 모든 실행계획 정보가 삭제됨 단위 : 시간(hour) 범위 : SYSDATE - 3651000 ~ SYSDATE 기본값 : SYSDATE - 3651000(SYSDATE로부터 약 1000년)
MAX_COUNT	실행계획을 삭제하지 않고 유지할 SQL의 수 START_DATE와 MAX_COUNT를 모두 지정하면 두 조건을 모두 만족시키는 SQL만이 유지됨. – 범위 : 0 ~ 1000000 – 기본값 : 1000000

• 예외 상황

예외 상황	설명
PARAMETER_OUT_OF_RANGE	파라미터가 지정된 범위를 벗어난 경우

• 예제

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

PSM completed.

UPDATE_PLAN_HISTORY

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

UPDATE_PLAN_HISTORY 프러시저의 세부 내용은 다음과 같습니다.

• 프로토타입

PROCEDURE UPDATE_PLAN_HISTORY
(
    MIN_EXEC_COUNT     IN PLS_INTEGER    DEFAULT 1
);

• 파라미터

파라미터	설명
MIN_EXEC_COUNT	업데이트 대상이 될 실행계획의 최소 실행 회수 이 값 미만으로 실행된 실행계획은 SPH에 저장되지 않음 – 범위 : 1 ~ 1000000 – 기본값 : 1

• 예외 상황

예외 상황	설명
PARAMETER_OUT_OF_RANGE	파라미터가 지정된 범위를 벗어난 경우

• 예제

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

PSM completed.