Yi

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

이번에는 ORACLE 11g에서 새로운 기능으로 제공하는 SPM(SQL Plan Management)을 이용하여 실행계획을 변경하고 고정하는 방법을 소개하려고 합니다.

SPM은 실행계획을 자동으로 관리하고 새로운 실행 계획이 Capture되더라도 검증하여 더 나은 실행 계획을 사용할 수 있도록 해주며 사용자에 의해 Auto와 Manual로 관리할 수 있습니다.

SQL Plan Capture는 optimizer_capture_sql_plan_basline 파라미터(default: false)를 이용하여 자동으로 수집 가능하며 SQL Tuning Set 또는 Cusor Cache로 부터 Capture하는 방식을 제공합니다.

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

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

1. DBMS_SPM 패키지 설치 확인

2. 권한 및 파라미터 설정 정보 확인

3. 성능이 좋지 않은 SQL의 Plan Baseline 생성

비효율 SQL을 선택 후 우클릭을 하면 아래와 같은 실행 스크립트가 만들어 지며, SQL*Plus에서 실행하여 SQL 실행 계획이 Capture가 되도록 합니다.

정상적으로 Capture가 이루어지면 DBA_SQL_PLAN_BASELINES 딕셔너리에서 Capture된 SQL들을 확인 할 수 있습니다.

4. Cursor Cache로 부터 성능이 개선된 Plan Baseline 생성

dbms_spm.load_plans_from_cursor_cache 를 이용하여 기존에 생성된 비효율 적인 SQL Plan Baseline의 SQL_HANDLE에 성능이 개선된 SQL 실행 계획을 생성 및 추가 합니다.

동일한 SQL_HANDLE로 새로운 SQL Plan Baseline이 생성되는 것을 확인 할 수 있습니다.

5. 성능이 좋지 않은 SQL의 Baseline을 DISABLE으로 변경

성능이 개선된 SQL Plan Baseline을 사용하기 위해 비효율 SQL의 Plan Baseline을 DISABLE로 변경합니다.

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

동일한 SQL_HANDLE로 SQL Plan Baseline이 등록되면 다음과 같이 실행 가능한 Baseline의 정보를 확인 할 수 있습니다.

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

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

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

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

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

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

1. DBMS_OUTLN 패키지 설치 확인

2. 권한 확인

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 파라미터를 설정해줘야합니다.

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

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

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이 실행되는지 확인

1. DBMS_SQLTUNE 패키지 설치 확인

2. 권한 확인

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이 사용되고 있는 것을 확인 할 수 있음.