Recent Comments
Toobit Exchange: https://toobitexchange...
야튜브: What's up!
What a nice..
한국야동: Hi,
What a nice post!
..
야유브: Hi,
What an good blog!..
Stanmorenhq: from lat. manus - "hand..
Pouringkhu: Century to a kind of de..이번 칼럼은 10g부터 소개된 SQL_PROFILE의 기능에 대한 소개를 하고자 합니다.
SQL_PROFILE이란, 특정 SQL_ID의 실행계획이 비효율적으로 생성될 경우, SQL 구문 변경 없이 실행계획만 변경시키거나 고정시키기 위해 사용합니다.일반적으로 SQL 튜닝을 하기 위해서는 아래와 같은 절차를 통해 개선을 합니다.
* 튜닝 대상 SQL에 대한 구문 변경
* 튜닝 대상 SQL에 대한 힌트 사용
* 튜닝 대상 프로그램에 대한 로직 변경
* 필요할 경우 인덱스 생성
문제는 위와 같은 튜닝을 하기 위해서는 DB Object 변경이 수반되어야 한다는 점입니다. 운영 상태에서 DB Object 변경은 상당한 Risk(Object Invalidation, Re-Parsing 등)를 가지고 있기 때문에, 해당 이슈를 처리하다가 장애까지 이어질 수 있으므로 이와 같은 튜닝을 적용하기에는 무리가 있습니다. 이 경우, SQL 구문 변경 없이(Object 변경 없이) 원하는 실행계획을 유도할 경우, 긴급하게 이슈 대응이 가능하기 때문에 튜너라면 반드시 이 기능을 숙지하고 있어야 합니다.
이제 SQL_PROFILE을 어떻게 사용하는지 살펴보겠습니다.
1. SQL_PROFILE 사용 예제
1-1) 오라클 버전 확인
|
1 2 3 4 5 6 |
SELECT * FROM V$VERSION; BANNER -------------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit PL/SQL Release 10.2.0.3.0 - Production |
1-2) 테이블 생성
|
1 2 3 4 5 6 |
DROP TABLE DEPT; CREATE TABLE DEPT (DEPTNO NUMBER(2), DNAME VARCHAR2(14), LOC VARCHAR2(13) ); |
1-3) 데이터 생성
|
1 2 3 4 5 6 7 |
INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK'); INSERT INTO DEPT VALUES (20, 'RESEARCH', 'DALLAS'); INSERT INTO DEPT VALUES (30, 'SALES', 'CHICAGO'); INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON'); COMMIT; / |
1-4) 인덱스 생성 및 통계정보 생성
|
1 2 3 |
CREATE UNIQUE INDEX USER.DEPT_U1 ON USER.DEPT (DEPTNO); EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'DEPT', CASCADE => TRUE); |
1-5) 데이터 확인
|
1 2 3 4 5 6 7 8 9 10 11 |
SELECT * FROM DEPT ; DEPTNO DNAME LOC ------ ------------- ----------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON ; |
1-6) 실행계획 확인
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
EXPLAIN PLAN FOR SELECT * FROM DEPT D WHERE D.DEPTNO = :B1 ; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); --------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 20 | 1 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 1 (0)| 00:00:01 | |* 2 | INDEX UNIQUE SCAN | DEPT_U1 | 1 | | 0 (0)| 00:00:01 | --------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("D"."DEPTNO"=TO_NUMBER(:B1)) ; |
지금까지 DEPT 테이블을 만든 후 데이터, 인덱스 및 통계정보를 생성한 뒤, 실행계획을 살펴보았습니다. 실행계획은 당연히 DEPTNO 조건이 들어오기 때문에 이 컬럼으로 구성된 DEPT_U1 인덱스를 사용한 것을 볼 수 있습니다. 이제 이 실행계획을 SQL_PROFILE을 통해 FULL SCAN을 하도록 유도하려고 하는데, 그림으로 표현하면 아래와 같습니다.
즉, DEPT_U1 인덱스를 사용하는 SQL을 A라 하고, FULL SCAN 하는 SQL을 B라 할 때, SQL_PROFILE을 통해 B로 변경을 하는 방식입니다.
1-7) B 실행계획 유도
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
EXPLAIN PLAN FOR SELECT /*+ FULL(D) */ -- 힌트추가(KJS) * FROM DEPT D WHERE D.DEPTNO = :B1 ; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 20 | 5 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| DEPT | 1 | 20 | 5 (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("D"."DEPTNO"=TO_NUMBER(:B1)) ; |
이제 원하는 B 실행계획이 나왔으므로, 이 힌트에 대한 OUTLINE FULL HINT를 통해 값을 가져오도록 하겠습니다.
1-8) B 실행계획 OUTLINE FULL HINT
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
EXPLAIN PLAN FOR SELECT /*+ FULL(D) */ -- 힌트추가(KJS) * FROM DEPT D WHERE D.DEPTNO = :B1 ; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, NULL, 'OUTLINE')); -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 20 | 5 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| DEPT | 1 | 20 | 5 (0)| 00:00:01 | -------------------------------------------------------------------------- Outline Data ------------- /*+ BEGIN_OUTLINE_DATA FULL(@"SEL$1" "D"@"SEL$1") OUTLINE_LEAF(@"SEL$1") ALL_ROWS OPT_PARAM('_optim_peek_user_binds' 'false') OPT_PARAM('_fast_full_scan_enabled' 'false') OPT_PARAM('_b_tree_bitmap_plans' 'false') OPTIMIZER_FEATURES_ENABLE('10.2.0.3') IGNORE_OPTIM_EMBEDDED_HINTS END_OUTLINE_DATA */ Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("D"."DEPTNO"=TO_NUMBER(:B1)) ; |
위에서 나온 ‘Outline Data’를 SQL_PROFILE에 등록합니다(등록 시 모두 가져올 필요는 없으며, 필요한 힌트만 취득하면 되나 테스트를 위해 전체를 가져옴)
SQL_PROFILE은 SQL_TEXT를 구분자로 인식하기 때문에 반드시 SQL_TEXT가 일치해야 하므로, 가급적 Shared Pool에 있는 SQL_TEXT를 이용하는 것이 좋습니다. 이를 이용하기 위해 해당 SQL을 수행토록 하겠습니다.
1-9) Shared Pool에 등록 및 SQL_ID 확인
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
※ 이 절차는 실제 운영 시에는 필요 없으며, 테스트를 위한 절차임 -- SQL 실행 var B1 NUMBER EXEC :B1 := 10 SELECT * FROM DEPT D WHERE D.DEPTNO = :B1 ; DEPTNO DNAME LOC ---------- ---------------------------- --------- 10 ACCOUNTING NEW YORK ; -- SQL_ID 및 FULL_TEXT 확인 SELECT SQL_ID, SQL_TEXT, SQL_FULLTEXT FROM V$SQL WHERE SQL_TEXT LIKE '%DEPT D%' ; SQL_ID SQL_TEXT SQL_FULLTEXT ------------- -------------------------------------------- ------------------------ abfnv4rva7df9 SELECT * FROM DEPT D WHERE D.DEPTNO = :B1 SELECT * FROM DEPT D WHERE D.DEPTNO = :B1 ; |
SQL 실행 후 V$SQL에서 SQL_ID 및 SQL_FULLTEXT를 확인 하였으므로, 이를 활용하여 SQL_PROFILE을 등록토록 하겠습니다.
1-10) SQL_PROFILE 등록
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
DECLARE -- SQL_FULLTEXT 데이터타입이 CLOB이므로, 변수도 CLOB으로 선언 V_SQL_TEXT CLOB; BEGIN -- V$SQL의 SQL_FULLTEXT를 변수에 저장 SELECT SQL_FULLTEXT INTO V_SQL_TEXT FROM V$SQL WHERE SQL_ID = 'abfnv4rva7df9'; -- DBMS_SQLTUNE.IMPORT_SQL_PROFILE 패키지를 이용하여 등록 DBMS_SQLTUNE.IMPORT_SQL_PROFILE( NAME => 'DEPT_PROFILE_1', DESCRIPTION => 'DEPT_PROFILE_1', CATEGORY => 'DEPT_PROFILE_1', SQL_TEXT => V_SQL_TEXT, PROFILE => SQLPROF_ATTR('BEGIN_OUTLINE_DATA', 'FULL(@"SEL$1" "D"@"SEL$1")', 'OUTLINE_LEAF(@"SEL$1")', 'ALL_ROWS', 'IGNORE_OPTIM_EMBEDDED_HINTS', 'END_OUTLINE_DATA' ), REPLACE => TRUE ); END; / PL/SQL procedure successfully completed. ; |
이제 해당 SQL_PROFILE이 DICTIONARY에 등록 되었으며, 해당 내용을 사용해 보도록 하겠습니다.
1-11) SQL_PROFILE 사용
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 |
-- 1. SQL_PROFILE 사용 전 EXPLAIN PLAN FOR SELECT * FROM DEPT D WHERE D.DEPTNO = :B1 ; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); --------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 20 | 1 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 1 (0)| 00:00:01 | |* 2 | INDEX UNIQUE SCAN | DEPT_U1 | 1 | | 0 (0)| 00:00:01 | --------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("D"."DEPTNO"=TO_NUMBER(:B1)) ; -- 2. SQL_PROFILE 활성화 ALTER SESSION SET SQLTUNE_CATEGORY = DEPT_PROFILE_1 ; Session altered. ; -- 3. SQL_PROFILE 사용 후 EXPLAIN PLAN FOR SELECT * FROM DEPT D WHERE D.DEPTNO = :B1 ; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 20 | 5 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| DEPT | 1 | 20 | 5 (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("D"."DEPTNO"=TO_NUMBER(:B1)) Note ----- - SQL profile "DEPT_PROFILE_1" used for this statement ; |
위의 결과처럼, Session-Level에서 ‘DEPT_PROFILE_1′ SQL_PROFILE을 사용한 결과, 힌트 없이 FULL SCAN하는 것을 알 수 있으며, ‘Note’에서도 ‘DEPT_PROFILE_1′을 사용했다는 것을 확인할 수 있습니다. 이와 같이 SQL_PROFILE은 SQL별로 등록할 수 있으며, 이에 대해 DBA_SQL_PROFILES 뷰에서도 확인이 가능합니다.
1-12) DBA_SQL_PROFILES View
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
col NAME format a30 col DESCRIPTION format a30 col CATEGORY format a30 col SQL_TEXT format a100 col CREATED format a30 col LAST_MODIFIED format a30 col TYPE format a30 col STATUS format a30 col FORCE_MATCHING format a30 SELECT NAME, DESCRIPTION, CATEGORY, SQL_TEXT, CREATED, LAST_MODIFIED, STATUS, FORCE_MATCHING FROM DBA_SQL_PROFILES WHERE NAME = 'DEPT_PROFILE_1' ; NAME DESCRIPTION CATEGORY SQL_TEXT --------------- --------------- -------------- ---------------------- CREATED LAST_MODIFIED STATUS FORCE_MATCHING --------------- --------------- -------------- ---------------------- DEPT_PROFILE_1 DEPT_PROFILE_1 DEPT_PROFILE_1 SELECT * FROM DEPT D WHERE D.DEPTNO = :B1 11-JAN-13 11-JAN-13 ENABLED NO ; |
1-13) CATEGORY 기능
|
1 2 3 4 5 6 7 8 9 10 11 12 |
- CATEGORY 기능은 2가지 측면에서 활용이 가능합니다. ① CATEGORY GROUPING - 'CATEGORY GROUPING'은 하나의 프로그램 내에 여러 SQL을 SQL_PROFILE을 통해 개선을 해야 하는 경우도 있습니다. - 만약, 하나의 프로그램에 5개의 SQL을 등록 후 사용할 경우 Session-Level에서 5개의 SQL_PROFILE을 모두 호출해야 하는데, 이를 CATEGORY에서 하나의 이름으로 등록할 경우, 이 이름으로 한 번만 호출하여 5개 SQL을 모두 사용할 수 있습니다. ② System-Level - 위의 'CATEGORY GROUPING'을 통해 여러 SQL을 하나의 CATEGORY로 묶을 수 있지만, 이를 사용하기 위해서는 어느 지점에서 반드시 'ALTER SESSION'을 해야 합니다. - 하지만, 'ALTER SESSION'을 할 수 없는 상황일 경우, 이를 System-Level로 등록하여 세션 변경 없이 사용이 가능합니다. - System-Level로 등록하기 위해서는 값을 'Default'로 설정하면 됩니다. |
1-14) DEFAULT로 등록
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
DECLARE -- SQL_FULLTEXT 데이터타입이 CLOB이므로, 변수도 CLOB으로 선언 V_SQL_TEXT CLOB; BEGIN -- V$SQL의 SQL_FULLTEXT를 변수에 저장 SELECT SQL_FULLTEXT INTO V_SQL_TEXT FROM V$SQL WHERE SQL_ID = 'abfnv4rva7df9'; -- DBMS_SQLTUNE.IMPORT_SQL_PROFILE 패키지를 이용하여 등록 DBMS_SQLTUNE.IMPORT_SQL_PROFILE( NAME => 'DEPT_PROFILE_1', DESCRIPTION => 'DEPT_PROFILE_1', CATEGORY => 'DEFAULT', SQL_TEXT => V_SQL_TEXT, PROFILE => SQLPROF_ATTR('BEGIN_OUTLINE_DATA', 'FULL(@"SEL$1" "D"@"SEL$1")', 'OUTLINE_LEAF(@"SEL$1")', 'ALL_ROWS', 'IGNORE_OPTIM_EMBEDDED_HINTS', 'END_OUTLINE_DATA'), REPLACE => TRUE ); END; / |
2. SQL_PROFILE 패키지 정리
2-1) DBMS_SQLTUNE Package
|
1 2 3 4 |
- SQL_PROFILE을 등록 또는 삭제하기 위해서는 DBMS_SQLTUNE 패키지를 사용해야 한다. => SQL_PROFILE 등록 : DBMS_SQLTUNE.IMPORT_SQL_PROFILE => SQL_PROFILE 삭제 : DBMS_SQLTUNE.DROP_SQL_PROFILE(NAME =>'DEPT_PROFILE_1'); => SQL_PROFILE DISABLE : DBMS_SQLTUNE.ALTER_SQL_PROFILE (NAME =>'DEPT_PROFILE_1',ATTRIBUTE_NAME=>'STATUS',VALUE=>'DISABLED'); |
2-2) SQL_PROFILE 활성화
|
1 2 |
- 활성화 : ALTER SESSION SET SQLTUNE_CATEGORY = DEPT_PROFILE_1 ; - 비활성화 : ALTER SESSION SET SQLTUNE_CATEGORY = FALSE; |
3. FORCE_MATCH 기능
SQL_PROFILE의 구분자는 위에서 설명한 것처럼 SQL 구문으로 체크를 합니다. 그런데, 만약 바인드 변수가 아닌 Literal 값으로 들어오는 Dynamic SQL일 경우, SQL 구문이 모두 틀리기 때문에 SQL_PROFILE을 등록하여도 공유해서 사용을 할 수가 없습니다. 이 때, FORCE_MATCH 기능을 사용할 경우 CURSOR_SHARING 기능처럼 Literal 값을 바인드 변수로 자동변경 해주므로, 하나만 등록하여도 나머지 SQL을 공유해서 사용 할 수 있습니다.
3-1) 기존 SQL_PROFILE 삭제
|
1 2 3 |
EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE(NAME =>'DEPT_PROFILE_1'); PL/SQL procedure successfully completed. |
3-2) Literal SQL을 CATEGORY ‘DEFAULT’로 하고, FORCE_MATCH 기능 없이 등록하여 공유가 되는지 체크
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
DECLARE BEGIN -- DBMS_SQLTUNE.IMPORT_SQL_PROFILE 패키지를 이용하여 등록 DBMS_SQLTUNE.IMPORT_SQL_PROFILE( NAME => 'DEPT_PROFILE_1', DESCRIPTION => 'DEPT_PROFILE_1', CATEGORY => 'DEFAULT', -- System-Level로 등록 SQL_TEXT => 'SELECT * FROM DEPT D WHERE D.DEPTNO = 10', PROFILE => SQLPROF_ATTR('BEGIN_OUTLINE_DATA', 'FULL(@"SEL$1" "D"@"SEL$1")', 'OUTLINE_LEAF(@"SEL$1")', 'ALL_ROWS', 'IGNORE_OPTIM_EMBEDDED_HINTS', 'END_OUTLINE_DATA'), REPLACE => TRUE ); END; / |
3- 3) 실행계획 확인
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 |
-- DEPTNO = 10 EXPLAIN PLAN FOR SELECT * FROM DEPT D WHERE D.DEPTNO = 10 ; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 20 | 5 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| DEPT | 1 | 20 | 5 (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("D"."DEPTNO"=10) Note ----- - SQL profile "DEPT_PROFILE_1" used for this statement ; -- DEPTNO = 20 EXPLAIN PLAN FOR SELECT * FROM DEPT D WHERE D.DEPTNO = 20 ; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); --------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 20 | 1 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 1 (0)| 00:00:01 | |* 2 | INDEX UNIQUE SCAN | DEPT_U1 | 1 | | 0 (0)| 00:00:01 | --------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("D"."DEPTNO"=20) ; -- DEPTNO = 30 EXPLAIN PLAN FOR SELECT * FROM DEPT D WHERE D.DEPTNO = 30 ; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); --------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 20 | 1 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 1 (0)| 00:00:01 | |* 2 | INDEX UNIQUE SCAN | DEPT_U1 | 1 | | 0 (0)| 00:00:01 | --------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("D"."DEPTNO"=30) ; -- DEPTNO = 40 EXPLAIN PLAN FOR SELECT * FROM DEPT D WHERE D.DEPTNO = 40 ; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); --------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 20 | 1 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 1 (0)| 00:00:01 | |* 2 | INDEX UNIQUE SCAN | DEPT_U1 | 1 | | 0 (0)| 00:00:01 | --------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("D"."DEPTNO"=40) ; |
위의 결과에서 보듯이, ‘DEPT=10′에 대해서만 SQL_PROFILE을 등록하였기 때문에, 이 경우에만 FULL SCAN을 하고 나머지 20, 30, 40일 경우 INDEX SCAN을 사용했습니다. 즉, 20, 30, 40인 경우에는 SQL_PROFILE을 사용하지 못하는 것을 확인할 수 있습니다.
이제는 FORCE_MATCH를 사용하여 CURSOR_SHARING 기능이 사용되는지 체크해보도록 하겠습니다.
3-4) Literal SQL을 CATEGORY ‘DEFAULT’로 하고, FORCE_MATCH 기능 포함하여 등록한 후 공유가 되는지 체크
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 |
EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE(NAME =>'DEPT_PROFILE_1'); PL/SQL procedure successfully completed. DECLARE BEGIN -- DBMS_SQLTUNE.IMPORT_SQL_PROFILE 패키지를 이용하여 등록 DBMS_SQLTUNE.IMPORT_SQL_PROFILE( NAME => 'DEPT_PROFILE_1', DESCRIPTION => 'DEPT_PROFILE_1', CATEGORY => 'DEFAULT',-- System-Level로 등록 SQL_TEXT => 'SELECT * FROM DEPT D WHERE D.DEPTNO = 10', PROFILE => SQLPROF_ATTR('BEGIN_OUTLINE_DATA', 'FULL(@"SEL$1" "D"@"SEL$1")', 'OUTLINE_LEAF(@"SEL$1")', 'ALL_ROWS', 'IGNORE_OPTIM_EMBEDDED_HINTS', 'END_OUTLINE_DATA'), REPLACE => TRUE, FORCE_MATCH => TRUE -- CURSOR_SHARING 기능 사용 ); END; / -- DEPTNO = 10 EXPLAIN PLAN FOR SELECT * FROM DEPT D WHERE D.DEPTNO = 10 ; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 20 | 5 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| DEPT | 1 | 20 | 5 (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("D"."DEPTNO"=10) Note ----- - SQL profile "DEPT_PROFILE_1" used for this statement ; -- DEPTNO = 20 EXPLAIN PLAN FOR SELECT * FROM DEPT D WHERE D.DEPTNO = 20 ; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 20 | 5 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| DEPT | 1 | 20 | 5 (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("D"."DEPTNO"=20) Note ----- - SQL profile "DEPT_PROFILE_1" used for this statement ; -- DEPTNO = 30 EXPLAIN PLAN FOR SELECT * FROM DEPT D WHERE D.DEPTNO = 30 ; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 20 | 5 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| DEPT | 1 | 20 | 5 (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("D"."DEPTNO"=30) Note ----- - SQL profile "DEPT_PROFILE_1" used for this statement ; -- DEPTNO = 40 EXPLAIN PLAN FOR SELECT * FROM DEPT D WHERE D.DEPTNO = 40 ; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 20 | 5 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| DEPT | 1 | 20 | 5 (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("D"."DEPTNO"=40) Note ----- - SQL profile "DEPT_PROFILE_1" used for this statement ; |
이처럼, SQL_PROFILE에서 FORCE_MATCH 기능을 통해 Literal SQL도 극복할 수 있으므로, 아주 유용한 기능이라 할 수 있습니다.
4. 주의사항
1) SQL_PROFILE 체크는 SQL 구문으로 체크하기 때문에 Shared Pool에 등록된 SQL을 SQL_PROFILE 시 사용하는 것이 좋습니다.
2) 1)에서 이야기한 것 처럼 SQL 구문으로 인식하기 때문에, 튜닝 SQL이 힌트가 아닌 구문 변경일 경우 사용할 수 없습니다.
3) CATEGORY 등록 시 ‘DEFAULT’ 즉, System-Level로 등록할 경우 시스템에 부하를 줄 수 있기 때문에 가급적 긴급 대응일 경우에만 사용하는 것이 좋습니다.
이상으로 오라클에서 제공하는 SQL_PROFILE에 대해 살펴보았습니다.
내용을 보시고 궁금하신 점이 있으시면 리플 달아주시기 바랍니다.
감사합니다.
reference site : http://docs.oracle.com/cd/B14117_01/appdev.101/b10802/d_sqltun.htm
About the Author
View all posts by 강정식● (주)더비소프트 SI&ITO사업부 ERP사업팀 팀장
● 경력 10년(현 LG전자 GERP DBA/Tuning 업무)
● 세미나 : 오라클 SQL 튜닝 방법의 이해, DBMS_XPLAN과 10046 Trace를 이용한 SQL문 튜닝, 개발자를 위한 어플리케이션튜닝 세미나
● 경력 10년(현 LG전자 GERP DBA/Tuning 업무)
● 세미나 : 오라클 SQL 튜닝 방법의 이해, DBMS_XPLAN과 10046 Trace를 이용한 SQL문 튜닝, 개발자를 위한 어플리케이션튜닝 세미나
Related Posts
- [DB 칼럼] INDEX SCAN (MIN/MAX) 성능 개선 방안 14-11-19 일반태그: Technical Architecture. DB태그: Oracle.
팀장님 매번 좋은 정보 감사합니다^^