SQL Profile을 이용한 SQL Plan 변경

Oracle Database를 운영 중인 DBA분들은 가끔 잘 실행되던 SQL이 비효율적은 실행계획으로 성능이 저하되는 것을 보았을 것입니다.

저도 몇번 경험을 해보았지만 해결 방법은 SQL을 변경하여 AP 서버에 변경된 SQL을 적용하고 WAS를 재시작한 후 변경된 SQL로 실행되는지 확인하는 이런 과정을 거쳐야 합니다. 서비스 시간 중에 이 작업을 하려면 서비스가 중단되어야하는 문제가 있습니다.또는 무식한 방법?이지만 shared_pool을 flush 시키다 보면 원래의 실행 계획으로 언젠가 바뀔 순 있습니다. 하지만 전체 SQL을 다시 parsing하고 shared_pool에 reload 하는 부하가 어마어마하니 권장하지는 않습니다.

이런 이유로 ORACLE은 SQL 문장을 수정하지 않고 SQL Plan을 변경하는 방법을 여러가지 제공하고 있습니다.

그 중 하나인 SQL Profile은 SQL Tuning이 목적으로 제공되며 10g 이상 버전 부터 사용할 수 있습니다.

SQL Profile에 대한 소개는 검색을 통해서 쉽게 찾아 볼 수 있으며 더 자세하게 설명할 자신이 없어 링크로 공유합니다.

http://www.dbguide.net/knowledge.db?cmd=view&boardUid=184184&boardConfigUid=21

https://dioncho.wordpress.com/2009/02/03/how-to-tune-sql-statement-without-changing-text-plan-baseline-version/

SQL Profile을 이용하여 실행 계획을 변경하는 것을 자동화 시켜보았습니다.

SQL Profile을 실행 계획을 변경하기 위한 단계는 다음과 같습니다.

1. DBMS_SQLTUNE 패키지 설치 확인

2. 권한 확인

3. 성능이 좋지 않은 SQL의 SQL_ID와 SQL_FULLTEXT 확인

4. 성능이 개선된 SQL의 SQL ID 확인

5. DBMS_SQLTUNE 패키지의 Hidden Procedure인 import_sql_profile을 이용하여 실행 계획 정보(OUTLINE) 변경.

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

※ OUTLINE ORACLE Optimizer는 사용자가 실행한 SQL을 Parsing 할 때 Query Transformer->Estimator->Plan Generator라는 과정을 거치며 최적의 실행 계획을 만듭니다. 이때 만들어진 실행계획을 수행하기 위해 적용되는 Hint의 모음이 Outline입니다. 확인은 10053 Trace의 가장 마지막 부분이나 DBMS_XPLAIN 패키지를 이용하여 확인 할 수 있습니다.

1. DBMS_SQLTUNE 패키지 설치 확인

select owner,object_name, object_type   from dba_objects  where object_name = 'DBMS_SQLTUNE' ; - 설치 SQL> @ ?/rdbms/admin/dbmssqlt.sql

2. 권한 확인

select grantee,privilege,admin_option   from dba_sys_privs  where grantee = &owner    and PRIVILEGE in ('ADMINISTER ANY SQL TUNING SET',                'CREATE ANY SQL PROFILE',                'DROP ANY SQL PROFILE',                'ALTER ANY SQL PROFILE') ; - 권한 설정 SQL> grant administer any sql tuning set to &owner ; SQL> grant create any sql profile to &owner ; SQL> grant drop any sql profile to &owner ; SQL> grant alter any sql profile to &owner ;

3. 성능이 좋지 않은 SQL의 SQL_ID와 SQL_FULLTEXT 확인

- 비효율 SQL_ID의 실행 계획

4. 성능이 개선된 SQL의 SQL ID 확인
-성능이 개선된 SQL의 실행계획

5. DBMS_SQLTUNE 패키지의 Hidden Procedure인 import_sql_profile을 이용하여 실행 계획 정보(OUTLINE) 변경.

1) 성능이 개선된 SQL을 더블 클릭하여 From SQL_ID로 설정.

2) 비효율적인 SQL을 더블 클릭하여 To SQL_ID로 설정.

3) From(튜닝 SQL) -> To(비효율 SQL)가 지정된 것을 확인 하고 Convert 버튼 클릭.

4) 정상적으로 변경이 되면 DBA_SQL_PROFILES에 변경 내역을 조회할 수 있음. 

6. 변경된 실행 계획으로 SQL이 실행되는지 확인
- Note에 생성된 SQL Profile의 Name이 사용되고 있는 것을 확인 할 수 있음.

- hash join과 full table scan으로 수행되던 비효율 SQL이 Outline을 적용한 후 Nested Loop join과 index scan으로 실행 계획이 변경되어 실행됨

다운로드 : https://sites.google.com/site/lmnetfordbms/