SQL Profile

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

테스트를 위해 아래와 같이 쿼리를 수행하고, 각각의 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 31

-- 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로 설정한다.

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

-- 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에서 확인할 수 있다.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

-- 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 정보를 확인할 수 있다.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

-- 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을 삭제할 수 있다.

1 2 3 4

-- 5 EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE ('profile_86y83gmzgmdj7');   PL/SQL 처리가 정상적으로 완료되었습니다

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

1 2 3 4 5 6 7 8 9 10 11 12

-- 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을 삭제한 후 실행 계획이 정상적으로 수립되는지 확인해야 한다.