Stored Outline을 이용한 SQL Plan 변경

앞서 SQL Plan 변경을 위해 SQL Profile을 잠깐 소개 및 자동화 시킨 부분을 포스트 하였었습니다.

이번 포스트는 SQL 실행계획 변경 방법 중 하나인 Stored Outline을 이용하여 실행계획을 변경하고 고정하는 방법을 소개하려고 합니다.

Stored Outline은 Plan Stability(안정화)를 위하여 9i부터 제공되었으며 11g에서 Deprecated 되었습니다.

그리고 Stored Outline의 자세한 개요나 설명 보다는 사용자가 쉽게 사용할 수 있는 자동화에 기준을 맞춰 작성된 점 참조하시기 바랍니다.

Stored Outline을 이용하여 실행 계획을 변경하는 방법을 요약하면 다음과 같습니다.

1. DBMS_OUTLN 패키지 설치 확인 2. 권한 확인 3. 성능이 좋지 않은 SQL의 Outline 생성 4. 성능이 개선된 SQL의 Outline 생성 5. 성능이 개선된 Outline을 성능이 좋지 않은 SQL의 Outline으로 변경 6. 변경된 실행 계획으로 SQL이 실행되는지 확인

위 과정대로 SQL 실행 계획을 자동화한 프로그램으로 한번 바꿔 보도록하겠습니다. .

1. DBMS_OUTLN 패키지 설치 확인

select owner,object_name, object_type   from dba_objects  where object_name = 'DBMS_OUTLN' ; - 설치 SQL> @ $ORACLE_HOME/rdbms/admin/dbmsol.sql

2. 권한 확인

select grantee,privilege,admin_option   from dba_sys_privs  where grantee = &grantee    and privilege in ('CREATE ANY OUTLINE');  select grantee,granted_role,admin_option   from dba_role_privs  where grantee = &grantee    and granted_role in ('EXECUTE_CATALOG_ROLE') ; - 권한 설정 SQL> grant create any outline to &grantee; SQL> grant execute_catalog_role to &grantee; SQL> grant create any outline, alter any outline, drop any outline to &grantee; SQL> grant execute on outln_pkg to &grantee; SQL> grant delete, update, insert, select on outln.ol$hints to &grantee; SQL> grant delete, update, insert, select on outln.ol$nodes to &grantee; SQL> grant delete, update, insert, select on outln.ol$ to &grantee;

※ Stored Outline 생성 방법 Stored Outline을 생성은 크게 세가지가 있습니다. - create_stored_outlines 파라미터 설정 - CREATE OUTLINE SQL Statement - DBMS_OUTLN.CREATE OUTLINE

3. 성능이 좋지 않은 SQL의 HASH_VALUE 확인 및 Outline 생성

1) Stored Outline을 만들기 위한 비효율 SQL 확인

2) 만들어진 Stored Outline의 Outline 정보 확인

4. 성능이 개선된 SQL의 HASH_VALUE 확인 및 Outline 생성

1) Stored Outline을 만들기 위해 성능이 개선된 SQL 확인

2) 만들어진 Stored Outline의 Outline 정보 확인

5. 성능이 개선된 Outline을 성능이 좋지 않은 SQL의 Outline으로 변경

성능이 개선된 SQL의 Outline name을 From name으로, 비효율 SQL의 Outline name을 To Name으로 한 후 CONVERT를 하여 성능이 개선된 SQL의 Outline 정보를 비효율 SQL의 Outline으로 변경함.

6. 변경된 실행 계획으로 SQL이 실행되는지 확인

stored outlie을 사용하기 위해서는 use_stored_outlines 파라미터를 설정해줘야합니다.

Syntax : alter session set use_stored_outlines=CATEGORY; SQL> alter session set use_stored_outlines=LMNET_CAT_20160818173936;

Hash Join이 Nested Loop Join으로 변경되었고 테이블에 대해 각각 index scan 하고 있으며 정상적으로 Stored Outline 이 동작한다면 Note에 use_stored_outlines 파라미터에 지정한 Catalog의 Outline name을 확인할 수 있습니다.

그리고 DBA_OUTLINES 딕셔너리의 USED 컬럼의 값이 UNUSED에서 USED로 바뀌는 것을 확인할 수 있습니다.