SQL Profile

긴급 튜닝에 활용할 수 있는 SQL Profile 기능을 살펴보자.

테스트를 위해 아래와 같이 쿼리를 수행하고, 각각의 SQL Id를 확인하자.

-- 1-1 : 7qyp0y3qquxqy
SELECT /*+ ORDERED USE_NL(B) */
       *
  FROM dept a, emp b
 WHERE b.deptno = a.deptno;

-----------------------------------------------
| Id  | Operation                    | Name   |
-----------------------------------------------
|   0 | SELECT STATEMENT             |        |
|   1 |  NESTED LOOPS                |        |
|   2 |   NESTED LOOPS               |        |
|   3 |    TABLE ACCESS FULL         | DEPT   |
|*  4 |    INDEX RANGE SCAN          | EMP_X1 |
|   5 |   TABLE ACCESS BY INDEX ROWID| EMP    |
-----------------------------------------------

-- 1-2 : 86y83gmzgmdj7
SELECT /*+ ORDERED USE_HASH(B) */
       *
  FROM dept a, emp b
 WHERE b.deptno = a.deptno;

-----------------------------------
| Id  | Operation          | Name |
-----------------------------------
|   0 | SELECT STATEMENT   |      |
|*  1 |  HASH JOIN         |      |
|   2 |   TABLE ACCESS FULL| DEPT |
|   3 |   TABLE ACCESS FULL| EMP  |
-----------------------------------

아래는 SQL Profile을 적용하는 PL/SQL 블록이다. NL 조인으로 수행되던 쿼리가 HASH 조인으로 수행되어, 원래 실행 계획이었던 NL 조인으로 복구하는 시나리오다. force_match 옵션은 해당 SQL에 대해 cursor_sharing을 force로 설정한다.

-- 2
DECLARE
    l_profile    SYS.SQLPROF_ATTR;
BEGIN
    FOR c1 IN (SELECT   a.sql_id
                      , a.sql_fulltext
                      , b.other_xml
                   FROM v$sql a
                      , v$sql_plan b
                  WHERE 1 = 1
                    AND a.sql_id = '86y83gmzgmdj7' -- AS-IS
                    AND a.child_number = 0
                    AND b.sql_id = '7qyp0y3qquxqy' -- TO-BE
                    AND b.child_number = 0
                    AND b.other_xml IS NOT NULL)
    LOOP
        SELECT a.hint
          BULK COLLECT
          INTO l_profile
          FROM XMLTABLE ('other_xml/outline_data/hint'
                         PASSING XMLPARSE (CONTENT c1.other_xml)
                         COLUMNS hint VARCHAR2 (4000) PATH 'text()') a;

        DBMS_SQLTUNE.IMPORT_SQL_PROFILE (
            sql_text    => c1.sql_fulltext
          , profile     => l_profile
          , name        => 'profile_' || c1.sql_id
          , replace     => TRUE
          , force_match => TRUE);
    END LOOP;
END;
/

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL Profile 적용 후 실행 계획이 NL 조인으로 변경된 것을 확인할 수 있다. outline에 포함된 IGNORE_OPTIM_EMBEDDED_HINTS 힌트에 의해 SQL에 기술된 힌트가 무시된다. 적용된 SQL Profile을 Note에서 확인할 수 있다.

-- 3
EXPLAIN PLAN FOR
SELECT /*+ ORDERED USE_HASH(B) */ * FROM dept a, emp b WHERE b.deptno = a.deptno;

-----------------------------------------------
| Id  | Operation                    | Name   |
-----------------------------------------------
|   0 | SELECT STATEMENT             |        |
|   1 |  NESTED LOOPS                |        |
|   2 |   NESTED LOOPS               |        |
|   3 |    TABLE ACCESS FULL         | DEPT   |
|*  4 |    INDEX RANGE SCAN          | EMP_X1 |
|   5 |   TABLE ACCESS BY INDEX ROWID| EMP    |
-----------------------------------------------

Note
-----
   - SQL profile "profile_86y83gmzgmdj7" used for this STATEMENT

dba_sql_profiles 뷰와 sys.dbmshsxp_sql_profile_attr 뷰에서 SQL Profile 정보를 확인할 수 있다.

-- 4-1
SELECT name, status, force_matching FROM dba_sql_profiles;

NAME                  STATUS  FORCE_MATCHING
--------------------- ------- --------------
profile_86y83gmzgmdj7 ENABLED YES

1개의 행이 선택되었습니다.

-- 4-2
SELECT * FROM sys.dbmshsxp_sql_profile_attr;

PROFILE_NAME          COMP_DATA
--------------------- -----------------
profile_86y83gmzgmdj7 <outline_data>...

1개의 행이 선택되었습니다.

DBMS_SQLTUNE.DROP_SQL_PROFILE 프로시저로 SQL Profile을 삭제할 수 있다.

-- 5
EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE ('profile_86y83gmzgmdj7');

PL/SQL 처리가 정상적으로 완료되었습니다

SQL Profile이 삭제되어 원래의 실행 계획이 적용된 것을 확인할 수 있다.

-- 6
EXPLAIN PLAN FOR
SELECT /*+ ORDERED USE_HASH(B) */ * FROM dept a, emp b WHERE b.deptno = a.deptno;

-----------------------------------
| Id  | Operation          | Name |
-----------------------------------
|   0 | SELECT STATEMENT   |      |
|*  1 |  HASH JOIN         |      |
|   2 |   TABLE ACCESS FULL| DEPT |
|   3 |   TABLE ACCESS FULL| EMP  |
-----------------------------------

SQL Profile은 임시처방이다. 비정상적인 실행 계획에 대한 원인를 찾아 해결하고, SQL Profile을 삭제한 후 실행 계획이 정상적으로 수립되는지 확인해야 한다.