내용 목차
본 장에서는 DBMS_SPH 패키지의 기본 개념과 패키지 내의 프러시저를 사용하는 방법을 설명한다.
DBMS_SPH 패키지는 SQL Plan History(이하 SPH) 기능을 사용하는데 필요로 하는 기능을 제공하기 위한 패키지이다. SPH는 서버가 실행한 SQL의 이력을 저장 및 관리해주는 기능이다.
Tibero는 SQL로부터 실행계획을 만들어 실행한다. 이 실행계획은 V$SQL_PLAN 등의 dynamic view를 통해 조회할 수 있다. 그러나 실행계획이 오래동안 사용되지 않거나 서버가 재부팅되면 그 실행계획은 메모리에서 삭제된다. 이 때문에 오랜 시간에 걸친 실행계획의 변화를 감지하고 추적하기가 어렵다.
SPH는 실행계획을 별도의 테이블에 저장함으로써 시간에 따른 실행계획 변화를 사용자가 쉽게 관리할 수 있게 도와준다. 단, 이 패키지는 SYS 사용자만 사용 가능하다.
본 절에서는 DBMS_SPH 패키지에서 제공하는 프러시저를 설명한다.
지정된 SQL의 실행계획 변화 이력을 출력한다. REPORT_PLANS_BY_DATE와 동작이 같으나 인자의 타입만 다르다. 결과는 화면에 출력할 수 있고 파일로 저장할 수도 있다. 화면에 출력을 원할 때(인자 TO_FILE이 FALSE일 때)는 serveroutput을 켜고 실행해야 한다.
다음은 결과가 파일로 저장되는 경로이다.
$TB_HOME/instance/$TB_SID/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의 이력을 출력할 기간이다. 인자는 시작 시점을 의미하며 종료 시점은 항상 현재이다.
|
| TO_FILE | 리포트 내용을 파일로 저장할지를 설정한다.
|
예외 상황
| 예외 상황 | 설명 |
|---|---|
| PARAMETER_OUT_OF_RANGE | 파라미터가 지정된 범위를 벗어난 경우이다. |
예제
SQL> set serveroutput on
SQL> select hash_value from v$sqlarea where sql_text like 'select hash_value%';
HASH_VALUE
----------
3870695416
1 row selected.
SQL> 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
... 생략 ...
지정된 SQL의 실행계획 변화 이력을 출력한다. REPORT_PLANS와 동작이 같으나 인자의 타입만 다르다. 결과는 화면에 출력할 수 있고 파일로 저장할 수도 있다. 화면에 출력을 원할 때(인자 TO_FILE이 FALSE일 때)는 serveroutput을 켜고 실행해야 한다.
다음은 결과가 파일로 저장되는 경로이다.
$TB_HOME/instance/$TB_SID/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의 이력을 출력할 기간의 시작 시점이다. 종료 시점은 항상 현재이다.
|
| TO_FILE | 리포트 내용을 파일로 저장할지를 설정한다.
|
예외 상황
| 예외 상황 | 설명 |
|---|---|
| PARAMETER_OUT_OF_RANGE | 파라미터가 지정된 범위를 벗어난 경우이다. |
예제
SQL> set serveroutput on
SQL> select hash_value from v$sqlarea where sql_text like 'select hash_value%';
HASH_VALUE
----------
3870695416
1 row selected.
SQL> 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
... 생략 ...
조건을 만족하는 모든 SQL의 실행계획 변화 이력을 출력한다. REPORT_PLAN_HISTORY_BY_DATE와 동작이 같으나 인자의 타입만 다르다. 결과는 화면에 출력할 수 있고 파일로 저장할 수도 있다. 화면에 출력을 원할 때(인자 TO_FILE이 FALSE일 때)는 serveroutput을 켜고 실행해야 한다.
다음은 결과가 파일로 저장되는 경로이다.
$TB_HOME/instance/$TB_SID/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의 이력을 출력할 기간이다. 인자는 시작 시점을 의미하며 종료 시점은 항상 현재이다.
|
| MIN_PLAN_COUNT | 출력할 SQL의 최소 실행계획 수이다. 예를 들어 2로 지정하면 둘 이상의 실행계획을 가지는 SQL만 출력한다.
|
| TO_FILE | 리포트 내용을 파일로 저장할지를 설정한다.
|
예외 상황
| 예외 상황 | 설명 |
|---|---|
| PARAMETER_OUT_OF_RANGE | 파라미터가 지정된 범위를 벗어난 경우이다. |
예제
SQL> set serveroutput on
SQL> exec dbms_sph.report_plan_history(24, 1, FALSE);
-- 화면에 하루 동안 실행된 모든 SQL의 실행계획을 모두 출력
+-------------------------+
| SQL_PLAN_HISTORY REPORT |
+-------------------------+
FROM: 1997/05/29 17:49:11
TO: 1997/05/30 17:49:11
... 생략 ...
조건을 만족하는 모든 SQL의 실행계획 변화 이력을 출력한다. REPORT_PLAN_HISTORY와 동작이 같으나 인자의 타입만 다르다. 결과는 화면에 출력할 수 있고 파일로 저장할 수도 있다. 화면에 출력을 원할 때(인자 TO_FILE이 FALSE일 때)는 serveroutput을 켜고 실행해야 한다.
다음은 결과가 파일로 저장되는 경로이다.
$TB_HOME/instance/$TB_SID/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의 이력을 출력할 기간의 시작 시점이다. 종료 시점은 항상 현재이다.
|
| MIN_PLAN_COUNT | 출력할 SQL의 최소 실행계획 수이다. 예를 들어 2로 지정하면 둘 이상의 실행계획을 가지는 SQL만 출력한다.
|
| TO_FILE | 리포트 내용을 파일로 저장할지를 설정한다.
|
예외 상황
| 예외 상황 | 설명 |
|---|---|
| PARAMETER_OUT_OF_RANGE | 파라미터가 지정된 범위를 벗어난 경우이다. |
예제
SQL> set serveroutput on
SQL> exec dbms_sph.report_plan_history_by_date(sysdate - 1, 1, FALSE);
-- 화면에 하루 동안 실행된 모든 SQL의 실행계획을 모두 출력
+-------------------------+
| SQL_PLAN_HISTORY REPORT |
+-------------------------+
FROM: 1997/05/29 17:49:11
TO: 1997/05/30 17:49:11
... 생략 ...
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 이력을 삭제할 기간을 지정한다. 이 기간동안 실행되지 않은 실행계획은 모두 삭제된다.
|
| MAX_COUNT | 실행계획을 삭제하지 않고 유지할 SQL의 수이다. RETENTION_PERIOD와 MAX_COUNT를 모두 지정하면 두 조건을 모두 만족시키는 SQL만이 유지된다.
|
예외 상황
| 예외 상황 | 설명 |
|---|---|
| PARAMETER_OUT_OF_RANGE | 파라미터가 지정된 범위를 벗어난 경우이다. |
예제
SQL> exec dbms_sph.truncate_plan_history(24);
-- 하루 동안 실행되지 않은 실행계획을 모두 삭제
PSM completed.
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 이력을 삭제할 기준 시점이다. 이 시점 이전의 모든 실행계획 정보가 삭제된다.
|
| MAX_COUNT | 실행계획을 삭제하지 않고 유지할 SQL의 수이다. START_DATE와 MAX_COUNT를 모두 지정하면 두 조건을 모두 만족시키는 SQL만이 유지된다.
|
예외 상황
| 예외 상황 | 설명 |
|---|---|
| PARAMETER_OUT_OF_RANGE | 파라미터가 지정된 범위를 벗어난 경우이다. |
예제
SQL> exec dbms_sph.truncate_plan_history_by_date(sysdate - 1);
-- 하루 동안 실행되지 않은 실행계획을 모두 삭제
PSM completed.
라이브러리 cache의 실행계획을 SPH에 저장한다.
UPDATE_PLAN_HISTORY 프러시저의 세부 내용은 다음과 같다.
프로토타입
PROCEDURE UPDATE_PLAN_HISTORY
(
MIN_EXEC_COUNT IN PLS_INTEGER DEFAULT 1
);
파라미터
| 파라미터 | 설명 |
|---|---|
| MIN_EXEC_COUNT | 업데이트 대상이 될 실행계획의 최소 실행 회수이다. 이 값 미만으로 실행된 실행계획은 SPH에 저장되지 않는다.
|
예외 상황
| 예외 상황 | 설명 |
|---|---|
| PARAMETER_OUT_OF_RANGE | 파라미터가 지정된 범위를 벗어난 경우이다. |
예제
SQL> exec dbms_sph.update_plan_history(2); -- 두 번 이상 실행된 실행계획을 SPH에 저장
PSM completed.