DBMS_SQLTUNE

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

개요

DBMS_SQLTUNE은 SQL 튜닝 관련 기능을 위한 인터페이스를 제공하는 패키지입니다. SQL 튜닝 관련 기 능의 목록은 다음과 같습니다.

• Real-Time SQL Monitoring

• SQL Tuning Advisor

• SQL Profile

타입

본 절에서는 DBMS_SQLTUNE 패키지에서 제공하는 별도 정의된 타입들을 알파벳 순으로 설명합니다.

SQLPROF_ATTR

outline 힌트를 목록으로 관리하기 위한 타입입니다.

SQLPROF_ATTR 타입의 세부 내용은 다음과 같습니다.

• 프로토타입

TYPE SQLPROF_ATTR IS VARRAY(2000) OF VARCHAR2(500)

프러시저와 함수

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

ALTER_SQL_PROFILE

생성되어 있는 SQL Profile의 속성을 변경하는 프러시저입니다. 변경 가능한 속성의 목록은 다음과 같으며, 목록 이외의 속성을 변경하기 위해서는 SQL Profile을 삭제한 뒤 재생성해야 합니다.

• STATUS : SQL Profile의 활성화 여부. 'ENABLED' 와 'DISABLED' 중 한 가지 값으로만 설정할 수 있다.

• NAME : SQL Profile의 이름. 다른 SQL Profile과 중복되지 않아야 합니다.

• DESCRIPTION : SQL Profile에 대한 설명. 최대 500 bytes까지 작성할 수 있다.

• CATEGORY : SQL Profile의 분류. 'SQL 질의문 + 분류' 의 조합이 다른 SQL Profile과 중복되지 않아야 합니다.

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

• 프로토타입

DBMS_SQLTUNE.ALTER_SQL_PROFILE
(
    name           IN VARCHAR2,
    attribute_name IN VARCHAR2,
    value          IN VARCHAR2
)

• 파라미터

파라미터	설명
name	속성을 변경하고자 하는 SQL Profile의 이름
attribute_name	SQL Profile에서 변경하고자 하는 속성의 이름
value	변경할 SQL Profile 속성의 값

• 예제

– SQL Profile에 설명 추가

select name, description, status from dba_sql_profiles;

NAME                DESCRIPTION    STATUS
------------------- ------------- -------
SQL_PROFILE_ALTER                 ENABLED

1 row selected.

BEGIN
DBMS_SQLTUNE.ALTER_SQL_PROFILE(
    NAME=>'SQL_PROFILE_ALTER'
    , ATTRIBUTE_NAME=>'DESCRIPTION'
    , VALUE=>'ALTER EXAMPLE'
);
END;
/

PSM completed.

select name, description, status from dba_sql_profiles;

NAME                DESCRIPTION    STATUS
------------------- ------------- -------
SQL_PROFILE_ALTER   ALTER EXAMPLE ENABLED

1 row selected.

– SQL Profile 이름 변경

BEGIN
DBMS_SQLTUNE.ALTER_SQL_PROFILE(
    NAME=>'SQL_PROFILE_ALTER'
    , ATTRIBUTE_NAME=>'NAME'
    , VALUE=>'SQL_PROFILE_RENAME'
);
END;
/

PSM completed.

select name, description, status from dba_sql_profiles;

NAME                DESCRIPTION    STATUS
------------------- ------------- -------
SQL_PROFILE_RENAME  ALTER EXAMPLE ENABLED

1 row selected.

– SQL Profile 비활성화

BEGIN
DBMS_SQLTUNE.ALTER_SQL_PROFILE(
    NAME=>'SQL_PROFILE_RENAME'
    , ATTRIBUTE_NAME=>'STATUS'
    , VALUE=>'DISABLED'
);
END;
/

PSM completed.

select name, description, status from dba_sql_profiles;

NAME                DESCRIPTION    STATUS
------------------- ------------- -------
SQL_PROFILE_RENAME  ALTER EXAMPLE DISABLED

1 row selected.

DROP_SQL_PROFILE

생성되어 있는 SQL Profile을 삭제하는 프러시저입니다.

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

• 프로토타입

DBMS_SQLTUNE.DROP_SQL_PROFILE
(
    name    IN VARCHAR2,
    ignore  IN BOOLEAN := FALSE
)

• 파라미터

파라미터	설명
name	삭제하고자 하는 SQL Profile의 이름
ignore	에러 발생 시 무시 여부

• 예제

select name, sql_text from dba_sql_profiles;

NAME                SQL_TEXT
------------------- -----------------------------------------------
SQL_PROFILE_DROP    select /*+full(t)*/ * from t where c1 = 1

1 row selected.

BEGIN
DBMS_SQLTUNE.DROP_SQL_PROFILE(
    NAME=>'SQL_PROFILE_DROP'
);
END;
/
PSM completed.

select name, sql_text from dba_sql_profiles;

0 row selected.

GET_PROFILE_HINTS

TPR에 등록되어 있는 플랜에 대해, 해당 플랜을 생성하기 위한 outline 힌트의 목록을 반환하는 함수입니다. (대상 플랜이 OPTIMIZER_LOG_OUTLINE=Y 상태에서 하드파싱 되었어야 합니다)

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

• 프로토타입

DBMS_SQLTUNE.GET_PROFILE_HINTS
(
    in_sql_id         IN VARCHAR2,
    in_plan_hash_value IN NUMBER
)
RETURN SQLPROF_ATTR;

• 파라미터

파라미터	설명
in_sql_id	Outline 힌트를 추출할 플랜의 SQL ID
in_plan_hash_value	Outline 힌트를 추출할 플랜의 Plan hash value

• 반환값

반환값	설명
SQLPROF_ATTR 데이터	대상 플랜으로부터 추출한 outline 힌트의 목록을 반환

• 예제

select sql_id, plan_hash_value
from sys._tpr_sql_plan
where other_xml is not null;

SQL_ID        PLAN_HASH_VALUE
------------  --------------
ddutpd81pq9hb 3055006916
03pumk0caww6w 198868953
a5ks9fhw2v9sl 56788869
...
gun74vzm8a8n3 1223639473

57 rows selected.

select column_value as outline_hint
  from table(dbms_sqltune.get_profile_hints('ddutpd81pq9hb', 3055006916));

OUTLINE_HINT
-------------------------
OPT_PARAM('_OPT_IDX_JOIN_REF_REP_CARD', 'TRUE')
OPT_PARAM('_ENABLE_HASH_JOIN', 'TRUE')
...
INDEX_FFS@LPN$4(_DD_OBJ_IDX2)
INDEX_UNIQUE@LPN$6(_DD_USER_IDX1)

60 rows selected.

GET_PROFILE_HINTS_GV

Physical Plan Cache에 등록되어 있는 플랜에 대해, 해당 플랜을 생성하기 위한 outline 힌트의 목록을 반 환하는 함수입니다. (대상 플랜이 OPTIMIZER_LOG_OUTLINE=Y 상태에서 하드파싱 되었어야 합니다)

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

• 프로토타입

DBMS_SQLTUNE.GET_PROFILE_HINTS_GV
(
    in_sql_id          IN VARCHAR2,
    in_plan_hash_value IN NUMBER,
    in_child_number    IN NUMBER
)
RETURN SQLPROF_ATTR;

• 파라미터

파라미터	설명
in_sql_id	Outline 힌트를 추출할 플랜의 SQL ID
in_plan_hash_value	Outline 힌트를 추출할 플랜의 Plan hash value
in_child_number	Outline 힌트를 추출할 플랜의 Child number

• 반환값

반환값	설명
SQLPROF_ATTR 데이터	대상 플랜으로부터 추출한 outline 힌트의 목록을 반환

• 예제

– 플랜으로부터 outline 힌트 추출하여 조회

alter session set optimizer_log_outline=y;

Session altered.

set autot traceonly exp
select /*+index_rs(t idx)*/ * from t where c1 = 1;

SQL ID: f7f774av26z91z
Child number: 442
Plan hash value: 3534975077

Execution Plan
----------------------------------------------------------
   1 TABLE ACCESS (ROWID): T (Cost:3, %%CPU:0, Rows:1)
   2 INDEX (RANGE SCAN): IDX (Cost:2, %%CPU:0, Rows:1)

Predicate Information
----------------------------------------------------------
   2 - access: ("T"."C1" = 1) (0.010)

set autot off
select column_value as outline_hint
  from table(dbms_sqltune.get_profile_hints_gv('f7f774av26z91z', 3534975077, 442));

OUTLINE_HINT
----------------------------------------------------------
OPT_PARAM('_OPT_IDX_JOIN_REF_REP_CARD', 'TRUE')
OPT_PARAM('_ENABLE_HASH_JOIN', 'TRUE')
...
ROWID@LPN$3(T)
INDEX_RS@LPN$3(IDX)

41 rows selected.

– 추출한 outline 힌트를 이용하여 SQL Profile 생성

BEGIN
DBMS_SQLTUNE.IMPORT_SQL_PROFILE(
    SQL_TEXT=>'select * from t where c1 = 1'
    , PROFILE=>DBMS_SQLTUNE.GET_PROFILE_HINTS_GV('f7f774av26z91z',3534975077,442)
    , NAME=>'SQL_PROFILE_IMPORT'
);
END;
/

PSM completed.

IMPORT_SQL_PROFILE

SQL Profile을 생성하는 프러시저입니다. SQL Profile을 생성한 뒤 대상 SQL 질의를 수행하면 해당 질의에 기재되어 있는 힌트는 무시되고, 대신 SQL Profile에 명시되어 있는 outline 힌트가 적용됩니다.

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

• 프로토타입

DBMS_SQLTUNE.IMPORT_SQL_PROFILE
(
    sql_text      IN CLOB,
    profile       IN SQLPROF_ATTR,
    category      IN VARCHAR2 := 'DEFAULT',
    name          IN VARCHAR2 := NULL,
    description   IN VARCHAR2 := NULL,
    replace       IN boolean := FALSE,
    force_match   IN boolean := FALSE
)

• 파라미터

파라미터	설명
sql_text	생성할 SQL Profile의 대상 SQL 질의문
profile	생성할 SQL Profile의 대상 SQL 질의에 적용하고자 하는 outline 힌트의 목록
category	생성할 SQL Profile의 분류
name	생성할 SQL Profile의 이름
description	생성할 SQL Profile에 대한 설명
replace	동일한 ‘SQL 질의 + 분류’ 조합의 SQL Profile이 이미 존재하는 경우, 현재 생성하고자 하는 내용으로 대체할지 여부
force_match	생성할 SQL Profile의 적용 범위 TRUE: 대상 SQL 질의와 리터럴만 다른 형태의 질의에 대해서도 SQL Profile을 적용 (CURSOR_SHARING=FORCE에 대응) FALSE : 대상 SQL 질의와 완벽하게 동일한 형태의 질의에 대해서만 SQL Profile을 적용 (CURSOR_SHARING=EXACT 에 대응)

• 예제

– 튜닝 플랜으로부터 outline 힌트를 추출하여 SQL Profile 생성

alter session set optimizer_log_outline=y;

Session altered.

set autot traceonly exp
select /*+index_rs(t idx)*/ * from t where c1 = 1;

SQL ID: f7f774av26z91z
Child number: 349
Plan hash value: 3534975077

Execution Plan
----------------------------------------------------------
   1 TABLE ACCESS (ROWID): T (Cost:3, %%CPU:0, Rows:1)
   2 INDEX (RANGE SCAN): IDX (Cost:2, %%CPU:0, Rows:1)

Predicate Information
----------------------------------------------------------
   2 - access: ("T"."C1" = 1) (0.010)

BEGIN
DBMS_SQLTUNE.IMPORT_SQL_PROFILE(
    SQL_TEXT=>'select /*+full(t)*/ * from t where c1 = 1'
    , PROFILE=>DBMS_SQLTUNE.GET_PROFILE_HINTS_GV('f7f774av26z91z',3534975077,349)
    , NAME=>'SQL_PROFILE_IMPORT1'
);
END;
/

PSM completed.

select /*+full(t)*/ * from t where c1 = 1;

SQL ID: 25c9tnzt4v2m4
Child number: 374
Plan hash value: 3534975077

Execution Plan
----------------------------------------------------------
   1 TABLE ACCESS (ROWID): T (Cost:3, %%CPU:0, Rows:1)
   2 INDEX (RANGE SCAN): IDX (Cost:2, %%CPU:0, Rows:1)

Predicate Information
----------------------------------------------------------
   2 - access: ("T"."C1" = 1) (0.010)

Note
----------------------------------------------------------
   0 - SQL profile SQL_PROFILE_IMPORT1 used

– outline 힌트를 직접 작성하여 SQL Profile 생성

BEGIN
    DBMS_SQLTUNE.IMPORT_SQL_PROFILE(
        SQL_TEXT=>'select /*+full(t)*/ * from t where c1 = 2'
        , PROFILE=>SQLPROF_ATTR('ROWID@LPN$3(T)'
                               ,'INDEX_RS@LPN$3(IDX)')
        , NAME=>'SQL_PROFILE_IMPORT2'
    );
END;
/

PSM completed.

select /*+full(t)*/ * from t where c1 = 2;

SQL ID: c362f0cv4p6cf
Child number: 398
Plan hash value: 3534975077

Execution Plan
----------------------------------------------------------
   1 TABLE ACCESS (ROWID): T (Cost:3, %%CPU:0, Rows:1)
   2 INDEX (RANGE SCAN): IDX (Cost:2, %%CPU:0, Rows:1)

Predicate Information
----------------------------------------------------------
   2 - access: ("T"."C1" = 2) (0.010)

Note
----------------------------------------------------------
   0 - SQL profile SQL_PROFILE_IMPORT2 used

NORMALIZE_SQLTEXT

대상 SQL 질의문으로부터 SIGNATURE를 얻을 때의 질의문 형태를 반환하는 함수입니다.

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

• 프로토타입

DBMS_SQLTUNE.NORMALIZE_SQLTEXT
(
    sql_text     IN CLOB,
    force_match  IN BINARY_INTEGER := 0
)
RETURN CLOB;

• 파라미터

파라미터	설명
sql_text	형태를 변환할 SQL 질의문
force_match	1 : 질의문의 리터럴을 바인드 변수로 치환(CURSOR_SHAR ING=FORCE 에 대응) 0 : 질의문의 리터럴을 바인드 변수로 치환하지 않음(CURSOR_SHAR ING=EXACT 에 대응)

• 반환값

반환값	설명
CLOB 데이터	변환된 SQL 질의문을 반환

• 예제

DECLARE
    ret1 CLOB;
    ret2 CLOB;
BEGIN
    ret1 := DBMS_SQLTUNE.NORMALIZE_SQLTEXT('SELECT 1 FROM DUAL', 0);
    ret2 := DBMS_SQLTUNE.NORMALIZE_SQLTEXT('SELECT 2 FROM DUAL', 0);
    DBMS_OUTPUT.PUT_LINE(ret1);
    DBMS_OUTPUT.PUT_LINE(ret2);
    ret1 := DBMS_SQLTUNE.NORMALIZE_SQLTEXT('SELECT 1 FROM DUAL', 1);
    ret2 := DBMS_SQLTUNE.NORMALIZE_SQLTEXT('SELECT 2 FROM DUAL', 1);
    DBMS_OUTPUT.PUT_LINE(ret1);
    DBMS_OUTPUT.PUT_LINE(ret2);
END;
/

REPORT_SQL_ACCESS_ADVISOR

대상 플랜의 수행 결과를 기반으로, SQL 질의의 성능 향상에 기여할 수 있는 인덱스 생성을 추천하는 함 수입니다. 추천 결과는 텍스트 형태로 확인할 수 있다.

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

• 프로토타입

DBMS_SQLTUNE.REPORT_SQL_ACCESS_ADVISOR
(
    sql_id       IN VARCHAR,
    child_number IN NUMBER
)
RETURN CLOB;

• 파라미터

파라미터	설명
sql_id	인덱스 추천에 참조할 플랜의 SQL ID
child_number	인덱스 추천에 참조할 플랜의 Child number

• 반환값

반환값	설명
CLOB 데이터	인덱스 추천 결과가 기재되어 있는 문자열을 CLOB 형태로 반환

• 예제

set long 1000000;
select dbms_sqltune.report_sql_access_advisor('c43va6z5ccg9b', 50) from dual;

REPORT_SQL_ADVISOR

대상 플랜의 수행 결과를 기반으로, SQL 질의의 성능 향상에 기여할 수 있는 인덱스 생성 및 통계 수집을 추천하는 함수입니다. 추천 결과는 텍스트 형태로 확인할 수 있다.

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

• 프로토타입

DBMS_SQLTUNE.REPORT_SQL_ADVISOR
(
    sql_id       IN VARCHAR,
    child_number IN NUMBER
)
RETURN CLOB;

• 파라미터

파라미터	설명
sql_id	인덱스 및 통계 수집 추천에 참조할 플랜의 SQL ID
child_number	인덱스 및 통계 수집 추천에 참조할 플랜의 Child number

• 반환값

반환값	설명
CLOB 데이터	인덱스 및 통계 수집 추천 결과가 기재되어 있는 문자열을 CLOB 형태로 반환

• 예제

set long 1000000;
select dbms_sqltune.report_sql_advisor('c43va6z5ccg9b', 50) from dual;

REPORT_SQL_MONITOR

특정 SQL 수행에 대해서 실시간 SQL 모니터링 기능에 의해 수집된 성능 관련 정보를 보고서 형태로 돌려 주는 함수입니다. 보고서 형식은 텍스트 형식입니다.

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

• 프로토타입

DBMS_SQLTUNE.REPORT_SQL_MONITOR
(
    sql_id        IN VARCHAR DEFAULT NULL,
    session_id    IN NUMBER  DEFAULT NULL,
    session_serial IN NUMBER DEFAULT NULL,
    sql_exec_start IN DATE   DEFAULT NULL,
    sql_exec_id    IN NUMBER DEFAULT NULL
)
RETURN CLOB;

• 파라미터

파라미터	설명
sql_id	보고서를 생성할 SQL 수행의 SQL 식별자 여기에 NULL을 명시하면 현재 시스템에서 가장 최근에 모니터링된 SQL 수행에 대한 보고서를 생성
session_id	이 값이 NULL이 아니면 해당 세션에서 수행된 SQL 수행에 한정하여 보고서를 생성할 수 있음 만약 이 파라미터에 NULL이 아닌 값을 명시하고 sql_id 파라미터에 NULL을 명시할 경우, 해당 세션에서 가장 최근에 모니터링된 SQL 수행에 대한 보고서를 생성
session_serial	원하는 특정 세션을 확실히 한정하고 싶을 때 추가적으로 명시할 수 있음 단, session_id 파라미터가 NULL일 경우 이 파라미터는 무시됨
sql_exec_start	sql_id 파라미터를 명시했을 때만 이 파라미터를 사용할 수 있음 모니터링된 SQL 수행 중에서 해당 sql_id를 가진 것 중 sql_exec_start 값이 일치하는 모니터링 정보를 찾아 보고서를 생성
sql_exec_id	sql_id 파라미터를 명시했을 때만 이 파라미터를 사용할 수 있음 모니터링된 SQL 수행 중에서 해당 sql_id를 가진 것 중 sql_exec_id 값이 일치하는 모니터링 정보를 찾아 보고서를 생성

• 반환값

반환값	설명
CLOB 데이터	보고서 형식으로 생성된 문자열을 CLOB 형태로 반환

• 예제

set long 1000000;
select dbms_sqltune.report_sql_monitor from dual;

REPORT_SQL_STAT_ADVISOR

대상 플랜의 수행 결과를 기반으로, SQL 질의의 성능 향상에 기여할 수 있는 통계 수집을 추천하는 함수 입니다. 추천 결과는 텍스트 형태로 확인할 수 있다.

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

• 프로토타입

DBMS_SQLTUNE.REPORT_SQL_STAT_ADVISOR
(
    sql_id       IN VARCHAR,
    child_number IN NUMBER
)
RETURN CLOB;

• 파라미터

파라미터	설명
sql_id	통계 수집 추천에 참조할 플랜의 SQL ID
child_number	통계 수집 추천에 참조할 플랜의 Child number

• 반환값

반환값	설명
CLOB 데이터	통계 수집 추천 결과가 기재되어 있는 문자열을 CLOB 형태로 반환

• 예제

set long 1000000;
select dbms_sqltune.report_sql_stat_advisor('c43va6z5ccg9b', 50) from dual;

SQLTEXT_TO_SIGNATURE

SQL 질의문을 SIGNATURE로 변환하여, 그 값을 반환하는 함수입니다.

SIGNATURE는 DBA_SQL_PROFILES에서 SQL 질의문을 식별하는 데 사용됩니다.

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

• 프로토타입

DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE
(
    sql_text     IN CLOB,
    force_match  IN BINARY_INTEGER := 0
)
RETURN NUMBER;

• 파라미터

파라미터	설명
sql_text	SIGNATURE로 변환할 SQL 질의문
force_match	1 : 질의문의 리터럴을 바인드 변수로 치환한 뒤 SIGNATURE로 변환 ( CURSOR_SHARING=FORCE 에 대응 ) 0 : 질의문의 리터럴을 치환하지 않은 채 SIGNATURE로 변환 ( CURSOR_SHARING=EXACT 에 대응 )

• 반환값

반환값	설명
NUMBER 데이터	SQL 질의문의 signature를 반환

• 예제

DECLARE
    ret1 NUMBER;
    ret2 NUMBER;
BEGIN
    ret1 := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE('SELECT 1 FROM DUAL', 0);
    ret2 := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE('SELECT 2 FROM DUAL', 0);
    DBMS_OUTPUT.PUT_LINE(ret1);
    DBMS_OUTPUT.PUT_LINE(ret2);
    ret1 := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE('SELECT 1 FROM DUAL', 1);
    ret2 := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE('SELECT 2 FROM DUAL', 1);
    DBMS_OUTPUT.PUT_LINE(ret1);
    DBMS_OUTPUT.PUT_LINE(ret2);
END;
/

SQL_TUNE_BY_ADJUST_SELECT

대상 플랜의 수행 결과를 기반으로, 인덱스 컬럼에 대한 조건문의 selectivity를 보정하는 프러시저입니다.

조건문의 selectivity 보정을 통해 더욱 성능이 좋은 플랜을 유도할 수 있게 됩니다.

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

• 프로토타입

DBMS_SQLTUNE.SQL_TUNE_BY_ADJUST_SELECT
(
    sql_id         IN VARCHAR2,
    pp_id          IN NUMBER,
    create_outline IN VARCHAR2
)

• 파라미터

파라미터	설명
sql_id	selectivity를 보정할 플랜의 SQL ID
pp_id	selectivity를 보정할 플랜의 Child number
create_outline	selectivity 보정 결과를 outline 힌트로 생성할지 여부

• 예제

BEGIN
    DBMS_SQLTUNE.SQL_TUNE_BY_ADJUST_SELECT('0crmttatjbs7b', 1636, 'Y');
END;
/

PSM completed.

VERIFY_SQL_PROFILE

생성되어 있는 SQL Profile이 유효한지 검사하는 프러시저입니다. VERIFY_SQL_PROFILE 프러시저에서

SQL Profile의 유효성을 검사하는 과정은 다음과 같습니다.

1. SQL Profile의 대상 SQL 질의문을 하드파싱합니다.

2. 하드파싱을 통해 얻은 플랜으로부터 outline 힌트를 추출한 뒤, SQL Profile의 outline 힌트 목록과 비교 합니다.

3. 플랜의 outline 힌트와 SQL Profile의 outline 힌트 목록이 일치하는 경우, 해당 SQL Profile이 유효한 것 으로 간주합니다.

플랜의 outline 힌트와 SQL Profile의 outline 힌트 목록이 일치하지 않거나, 대상 SQL 질의문을 하드파 싱 할 수 없는 경우, 해당 SQL Profile이 유효하지 않은 것으로 간주합니다.

프로시저 수행이 종료되면 다음의 결과가 출력됩니다. (serveroutput이 켜져 있는 상태여야 합니다)

• SQL Profile의 유효성(SUCCESS/FAILURE)

• 대상 SQL 질의문 정보 (SQL Profile이 유효하지 않은 경우)

• SQL Profile과 플랜 간 outline 힌트 비교 (대상 SQL의 하드파싱에는 성공하였으나, SQL Profile이 유효 하지 않은 경우)

• 대상 SQL 질의문의 Logical Plan 정보 (대상 SQL의 하드파싱에는 성공하였으나, SQL Profile이 유효하 지 않은 경우)

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

• 프로토타입

DBMS_SQLTUNE.VERIFY_SQL_PROFILE
(
    name        IN VARCHAR2 DEFAULT NULL,
    schema_name IN VARCHAR2 DEFAULT NULL
)

• 파라미터

파라미터	설명
name	검사하고자 하는 SQL Profile의 이름 NULL일 경우 생성되어 있는 모든 SQL Profile을 검사
schema_name	SQL Profile의 대상 SQL 질의문을 하드파싱할 스키마의 이름 NULL일 경우, 대상 질의문을 하드파싱 할 수 있는 스키마 중 userid가 가장 작은 것으 로 하드파싱

• 예제

– 유효한 SQL Profile 예시

select /*+full(t)*/ * from t where c1 = 1;

SQL ID: 256gtnzt4v2m4
Child number: 572
Plan hash value: 3534975077

Execution Plan
----------------------------------------------------------
   1 TABLE ACCESS (ROWID): T (Cost:3, %%CPU:0, Rows:1)
   2 INDEX (RANGE SCAN): IDX (Cost:2, %%CPU:0, Rows:1)

Predicate Information
----------------------------------------------------------
   2 - access: ("T"."C1" = 1) (0.010)

Note
----------------------------------------------------------
   0 - SQL profile SQL_PROFILE_VERIFY used

set serveroutput on
BEGIN
    DBMS_SQLTUNE.VERIFY_SQL_PROFILE('SQL_PROFILE_VERIFY', USER);
END;
/
----------------------------------------------------------
SQL Profile "SQL_PROFILE_VERIFY" verify success
----------------------------------------------------------

PSM completed.

– 유효하지 않은 SQL Profile 예시

drop index idx;

Index 'IDX' dropped.

set serveroutput on
BEGIN
    DBMS_SQLTUNE.VERIFY_SQL_PROFILE('SQL_PROFILE_VERIFY', USER);
END;
/
----------------------------------------
SQL Profile "SQL_PROFILE_VERIFY" verify failure
SQL Profile cannot be applied as expected
----------------------------------------

--------- User Information ---------
TIBERO

--------- SQL Information ---------
select /*+full(t)*/ * from t where c1 = 1

--------- Expected Outline Hints ---------
 OPT_PARAM('_OPT_IDX_JOIN_REF_REP_CARD', 'TRUE')
 OPT_PARAM('_ENABLE_HASH_JOIN', 'TRUE')
 ...
 OPT_PARAM('_COUNT_PESET_MT_NODE', 'TRUE')
 - ROWID@LPN$3(T)
 - INDEX_RS@LPN$3(IDX)

--------- Actual Outline Hints ---------
 OPT_PARAM('_OPT_IDX_JOIN_REF_REP_CARD', 'TRUE')
 OPT_PARAM('_ENABLE_HASH_JOIN', 'TRUE')
 ...
 OPT_PARAM('_COUNT_PESET_MT_NODE', 'TRUE')
 + FULL@LPN$3

--------- Logical Plan Information ---------
QB LP_IDX DEPTH P_IDX LPN_TYPE DETAIL
0  1     0     0    LPN_PROJ   --
0  2     1     1    LPN_SEL    - filter -
("T"."C1" = 1)
0  3     2     2    LPN_TBL    --

PSM completed.