SQL Plan Baseline을 이용한 SQL Plan 변경

앞서 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을 이용하여 실행 계획을 변경하는 방법을 요약하면 다음과 같습니다.

1. DBMS_SPM 패키지 설치 확인 2. 권한 및 파라미터 설정 정보 확인 3. 성능이 좋지 않은 SQL의 Plan Baseline 생성 4. Cursor Cache로 부터 성능이 개선된 Plan Baseline 생성 5. 성능이 좋지 않은 SQL의 Baseline을 DISABLE으로 변경 6. 변경된 실행 계획으로 SQL이 실행되는지 확인

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

1. DBMS_SPM 패키지 설치 확인

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

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

select grantee,privilege,admin_option   from dba_sys_privs  where grantee = &grantee    and privilege in ('ADMINISTER SQL MANAGEMENT OBJECT'); - 권한 설정 SQL> grant ADMINISTER SQL MANAGEMENT OBJECT to &grantee; - 파라미터 확인 select name,        value,        isdefault   from v$parameter  where name in ('optimizer_capture_sql_plan_baselines',                'optimizer_use_sql_plan_baselines') ;

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

실제 Plan hash value : 828281118로 실행 계획을 사용하는지 확인 해보겠습니다.

Note에서 성능이 개선된 SQL Plan Baseline을 사용하는 것을 확인할 수 있습니다.