목차

I. OVERVIEW

• 1. SQL 실행계획
• 2. SPM(SQL Plan Management)이란?
• 3. SQL Plan Capture 지원방식

II. SQL Tuning 비교

• 1. 10g SQL Tuning 과정
• 2. 11g 자동 SQL 튜닝 과정

III. SQL Plan Management

• 1. SQL Plan Management
• 2. SPM(SQL Plan Management) 사용예
• 3. SPM과 SQL Profile 차이
• 4. SPM과 Stored Outline 차이
• 5. SPM 장점
• 6. SPM 사용 예

IV. SPM 설정

• 1. SPM 설정
  • 1) Parameter
  • 2) 관련 View
• 2. SQL Plan Baselines
  • 1) Capturing SQL Plan Baselines
  • 2) Selecting SQL Plan Baselines
  • 3) Evolving SQL Plan Baselines
  • 4) Fixing SQL Plan Baselines
  • 5) Displaying SQL Plan Baselines
  • 6) SQL Mamagement Base(SMB)
  • 7) Exporting & Importing SQL Plan Baselines

I. OVERVIEW

1. SQL 실행계획

• 오라클에서는 같은 SQL이라 하더라도, 아래의 경우에 의해 실행계획이 변경될 수 있음

* New Optimizer version
* Changes to optimizer statistics and optimizer parameters
* Changes to schema and metadata definitions
* Changes to system settings
* SQL profile creating
* Drop index

2. SPM(SQL Plan Management)이란?

• 오라클 11g에서 새로운 기능으로 추가되었으며, 실행계획에 대한 수집, 평가를 관리함.
• SPM을 통해 실행계획을 자동으로 관리하고, 관리할 수 있으며, 새로운 실행계획이 capture 되었을 경우, 현재의 실행계획과 비교하여 더 우수하다고 판단될 때, 새로운 실행계획으로 전환함.
• SPM은 사용자에 의해 Auto 및 Manual로 관리 가능함.

3. SQL Plan Capture 지원방식

• Capturing Plans Automatically 방식
• Capturing Plans in Bulk 방식
  • SQL Tuning Set(STS) 방식
  • Cursor Cache 로부터 캡쳐하는 방식

II. SQL Tuning 비교

1. 10g SQL Tuning 과정

2. 11g 자동 SQL 튜닝 과정

• 자동 튜닝 후 이를 검증하여 자동 적용하며, 실행 계획 변화에 대한 이력 관리 기능을 제공
  • 자체 검증을 통해 3배 이상의 성능 향상 요소가 있는 경우 자동 적용
  • 환경 변화 및 자동 튜닝에 의한 실행 계획 변화 이력 관리
    

III. SQL Plan Management

1. SQL Plan Management

2. SPM(SQL Plan Management) 사용예

3. SPM과 SQL Profile 차이

4. SPM과 Stored Outline 차이

5. SPM 장점

6. SPM 사용 예

IV. SPM 설정

1. SPM 설정

1) Parameter

alter system optimizer_capture_sql_plan_baselines = true;

2) 관련 View

• DBA_SQL_PLAN_BASELINES

2. SQL Plan Baselines

• SPM이 Enable되면 SQL의 Execution Plan들은 SQL Plan Baselines라는 이름으로 저장 관리된다
  • Capturing SQL Plan Baselines
  • Selecting SQL Plan Baselines
  • Evolving SQL Plan Baselines
  • Fixing SQL Plan Baselines
  • Displaying SQL Plan Baselines
  • SQL Management Base
  • Exporting and Importing SQL Plan Baselines

1) Capturing SQL Plan Baselines

• Automatic Plan Capture
  • 자동으로 SQL Plan을 저장하는 방식
  • 한번만 수행된SQL은 Baselines에 저장되지않고 Identifier만 저장
  • 반복적으로 수행되는 SQL만Baselines에 저장
  • ACCEPTED=YES 만이 유효한Plan임
  • Init parameter OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=True

• Manual Plan Loading
  • SQL Tuning Set 이나 Cursor Cache에서 SQL Plan을Baseline에 Loading
  • 검증과정 없이바로 ACCEPTED=YES로설정됨

2) Selecting SQL Plan Baselines

• SQL Plan Baseline을 활용을 Enable시키기 위한 Init Parameter
  • OPTIMIZER_USE_SQL_PLAN_BASELINES = TRUE (default)

3) Evolving SQL Plan Baselines

• Non-accepted 로 저장된 새로운 Plan의 성능을 검증하고 Accepted=YES 로 바꾸는 과정
  • EM 및 dbms_spm Package이용

DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE (sql_handle IN VARCHAR2 := NULL,
                                   plan_name  IN VARCHAR2 := NULL,
                                   time_limit IN INTEGER  := DBMS_SPM.AUTO_LIMIT,
                                   verify IN VARCHAR2     := 'YES',
                                   commit IN VARCHAR2     := 'YES')
RETURN CLOB;

DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE (plan_list  IN DBMS_SPM.NAME_LIST,
                                   time_limit IN INTEGER  := DBMS_SPM.AUTO_LIMIT,
                                   verify     IN VARCHAR2 := 'YES',
                                   commit     IN VARCHAR2 := 'YES')
RETURN CLOB;

-- Example
variable report clob

exec :report:=DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(sql_handle=>'SYS_SQL_593bc74fca8e6738');

Print report

• DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE 파라미터

4) Fixing SQL Plan Baselines

• Baselines의 Plan중 특정 Plan을 Fix
  • Optimizer는 Fixed Plan중에서 가장 cost가 낮은 Plan을 선택
• EM 및 dbms_spm Package이용

DBMS_SPM.ALTER_SQL_PLAN_BASELINE (sql_handle      IN VARCHAR2 := NULL,
                                  plan_name       IN VARCHAR2 := NULL,
                                  attribute_name  IN VARCHAR2,
                                  attribute_value IN VARCHAR2)
RETURN PLS_INTEGER;

-- Example 
exec dbms_spm.alter_sql_plan_baseline(sql_handle => 'SYS_SQL_593bc74fca8e6738',
                                      attribute_name => 'fixed',
                                      attribute_value => 'YES' ) ;

• DBMS_SPM.ALTER_SQL_PLAN_BASELINE 파라미터

5) Displaying SQL Plan Baselines

• SQL Plan Baseline의 Attribute를 확인
• EM 및 View, dbms_xplan Package이용

DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE (sql_handle      IN VARCHAR2 := NULL,
                                      plan_name       IN VARCHAR2 := NULL,
                                      format          IN VARCHAR2 := 'TYPICAL')
RETURN dbms_xplan_type_table;

SELECT SQL_HANDLE,
       PLAN_NAME,
       ENABLED,
       ACCEPTED,
       FIXED
FROM   DBA_SQL_PLAN_BASELINES
;


SQL_HANDLE               PLAN_NAME                     ENA ACC FIX
------------------------ ----------------------------- --- --- ----
SYS_SQL_209d10fabbedc741 SYS_SQL_PLAN_bbedc741a57b5fc2 YES NO  NO
SYS_SQL_209d10fabbedc741 SYS_SQL_PLAN_bbedc741f554c408 YES YES NO

SELECT *
FROM   TABLE(DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(SQL_HANDLE => 'SYS_SQL_209d10fabbedc741',
                                                  FORMAT     => 'basic'));

• DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE 파라미터

6) SQL Mamagement Base(SMB)

• SMB는 SYSAUX tablespace에 저장된 Data Dictionary 의 일부분
• SQL Plan Baselines, SQL Profiles 등을 저장
• Disk Space Usage

• Retention Period

• SMB Configuration 조회
  • DBA_SQL_MANAGEMENT_CONFIG View 이용

7) Exporting & Importing SQL Plan Baselines

• SQL Plan Baselines는 한 시스템에서 다른 시스템으로 Oracle Data Pump를 사용해 옮길 수 있다
  • Staging Table 만들기
  • SQL Plan Baselines를 Pack 한다
  • Datapump를 이용 Staging Table을 Export
  • Datapump를 이용 Staging Table을 import
  • SQL Plan Baselines를 Unpack 한다

참고 사이트

• http://www.toadworld.com/KNOWLEDGE/KnowledgeXpertforOracle/tabid/648/TopicID/STAT64/Default.aspx

문서에 대하여

• 최초작성자 : ~xsoft
• 최초작성일 : 2011년 10월 15일
• 이 문서는 오라클클럽 코어 오라클 데이터베이스 스터디 모임에서 작성하였습니다.