DBMS_SPM

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

개요

현재 DBMS_SPM 패키지는 SQL_PLAN_BASELINE 정보를 수정하는 ALTER_SQL_PLAN_BASELINE, 지우는 DROP_SQL_PLAN_BASELINE, 검증하는 EVOLVE_SQL_PLAN_BASELINE을 제공하는 패키지입니다.

프러시저와 함수

본 절에서는 DBMS_SPM 패키지에서 제공하는 프러시저와 함수를 알파벳 순으로 설명합니다.

ALTER_SQL_PLAN_BASELINE

기존 SQL PLAN_BASELINE의 특정 속성을 변경합니다.

ALTER_SQL_PLAN_BASELINE 함수의 세부 내용은 다음과 같습니다.

• 프로토타입

DBMS_SPM.ALTER_SQL_PLAN_BASELINE
(
   sql_handle          IN VARCHAR2 := NULL,
   plan_name           IN VARCHAR2 := NULL,
   attribute_name      IN VARCHAR2,
   attribute_value     IN VARCHAR2
)
RETURN NUMBER;

• 파라미터

파라미터	설명
sql_handle	변경할 대상의 unique한 SQL 질의문 handle 만약 비어있다면 plan_name이 특정되어야 함
plan_name	변경할 대상의 plan name. sql_handle이 지정되어 있고, plan_name이 비어있다면 sql_handle에 해당하는 모든 plan에 대해서 변경을 적용 만약 비어있다면 sql_handle이 특정되어야 함
attribute_name	변경할 특성의 이름
attribute_value	변경할 특성의 값 ENABLED : 사용 가능한 상태를 나타내는 값. 'YES' 또는 'NO'로 설정 할 수 있음. FIXED : 고정 여부를 나타내는 값. 고정된 SQL_PLAN_BASELINE은 evolve 되지 않고 사용될 때 우선순위를 가짐. 'YES' 또는 'NO'로 설정할 수 있음. PLAN_NAME : 플랜의 이름으로 30자 이내의 문자열. DESCRIPTION : attribute에 대한 설명으로 500자 이내의 문자열.

• 반환값

반환값	설명
NUMBER 데이터	변경된 플랜의 수를 반환

• 예제

– ALTER 전

SQL> select signature, sql_handle, plan_name, sql_text, description
from dba_sql_plan_baselines;

SIGNATURE SQL_HANDLE
---------  ---------------------------------
PLAN_NAME
--------------------------------------------
SQL_TEXT
--------------------------------------------
DESCRIPTION
--------------------------------------------
2.8525E+18 SYS_BASELINE_d8626e27730e74e4
SQL_PLAN_339798362
select * from t where c1 = 4 and c2 = 4

– ALTER 구문

BEGIN :a := DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
   SQL_HANDLE => 'SYS_BASELINE_d8626e27730e74e4',
   PLAN_NAME => 'SQL_PLAN_339798362',
   ATTRIBUTE_NAME => 'DESCRIPTION',
   ATTRIBUTE_VALUE => 'TEST');
END;
/

– ALTER 후

SQL> select signature, sql_handle, plan_name, sql_text, description
from dba_sql_plan_baselines;

SIGNATURE  SQL_HANDLE
---------  ---------------------------------
PLAN_NAME
--------------------------------------------
SQL_TEXT
--------------------------------------------
DESCRIPTION
--------------------------------------------
2.8525E+18 SYS_BASELINE_d8626e27730e74e4
SQL_PLAN_339798362
select * from t where c1 = 4 and c2 = 4
TEST

DROP_SQL_PLAN_BASELINE

특정 SQL PLAN_BASELINE을 데이터베이스에서 삭제합니다.

DROP_SQL_PLAN_BASELINE 함수의 세부 내용은 다음과 같습니다.

• 프로토타입

DBMS_SPM.DROP_SQL_PLAN_BASELINE
(
   sql_handle         IN VARCHAR2 := NULL,
   plan_name          IN VARCHAR2 := NULL
)
RETURN NUMBER;

• 파라미터

파라미터	설명
sql_handle	변경할 대상의 unique한 SQL 질의문 handle. 만약 비어있다면 plan_name이 특정되어야 함
plan_name	변경할 대상의 plan name. sql_handle이 지정되어 있고, plan_name이 비어있다면 sql_handle에 해당하는 모든 plan에 대해서 변경을 적용 만약 비어있다면 sql_handle이 특정되어야 함

• 예제

– DROP 전

SQL> select signature, sql_handle, plan_name, sql_text, description
from dba_sql_plan_baselines;

SIGNATURE  SQL_HANDLE
---------  ---------------------------------
PLAN_NAME
--------------------------------------------
SQL_TEXT
--------------------------------------------
DESCRIPTION
--------------------------------------------
2.8525E+18 SYS_BASELINE_d8626e27730e74e4
SQL_PLAN_339798362
select * from t where c1 = 4 and c2 = 4

– DROP 구문

BEGIN :a := DBMS_SPM.DROP_SQL_PLAN_BASELINE(
    SQL_HANDLE => 'SYS_BASELINE_d8626e27730e74e4',
    PLAN_NAME => 'SQL_PLAN_339798362');
END;
/

– DROP 후

SQL>select signature, sql_handle, plan_name, sql_text, description
from dba_sql_plan_baselines;

0 row selected.

EVOLVE_SQL_PLAN_BASELINE

accept 되지 않은 SQL PLAN_BASELINE들을 검증하고 accept로 변경합니다.

EVOLVE_SQL_PLAN_BASELINE 함수의 세부 내용은 다음과 같습니다.

• 프로토타입

DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE
(
   plan_list            IN DBMS_SPM.NAME_LIST,
   time_limit           IN INTEGER := 0,
   verify                IN VARCHAR2 := 'YES',
   do_commit            IN VARCHAR2 := 'YES'
)
RETURN CLOB;

DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE
(
   sql_handle           IN VARCHAR2 := NULL,
   plan_name            IN VARCHAR2 := NULL,
   time_limit           IN number := 0,
   verify                IN VARCHAR2 := 'YES',
   do_commit            IN VARCHAR2 := 'YES'
)
RETURN CLOB;

• 파라미터

파라미터	설명
sql_handle	변경할 대상의 unique한 SQL 질의문 handle. 만약 비어있고 plan_name이 특정되지 않으면 모든 unaccepted plan을 검증.
plan_name	변경할 대상의 plan name
plan_list	변경할 대상의 plan name 목록
time_limit	검증하는 시간을 제한하는 값 현재는 미지원 파라미터
verify	검증할 플랜을 실제로 수행할 지 결정하는 파라미터. 'YES' 또는 'NO'로 설정할 수 있고, 'NO'인 경우 검증하지 않고 unaccepted plan을 accepted plan으로 변경
do_commit	accepted plan보다 빠른 것으로 검증된 unaccepted plan을 실제로 accepted plan으로 변경할 지 결정하는 파라미터 'YES' 또는 'NO'로 설정할 수 있고, 'NO'인 경우 보고서를 리턴