by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

3. SQL PLAN MANAGEMENT




목차

I. OVERVIEW

II. SQL Tuning 비교

III. SQL Plan Management

IV. SPM 설정

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

SQL 실행 계획 관리의 필요성 예상치 못한 환경 변화에 따른 실행 계획 변화에 능동적으로 대응
  검증된 실행 계획의 유지 관리
  새로운 실행 계획에 대해 검증을 통해 사용 여부 결정
  모든 실행 계획 변화에 대한 이력 관리 기능 제공
  DBA에 의한 SQL 실행 계획의 검증 및 선택적 결정
SQL 실행 계획의 검증 방법 자동 SQL 튜닝에 의한 검증
  SQL Performance Analyzer등을 이용한 DBA에 의한 검증
검증된 실행 계획의 관리 SQL Plan Baseline에 저장되어 관리
  새로운 실행 계획의 검증에 사용

2. SPM(SQL Plan Management) 사용예

SQL Plan이 변경 될 수 있는 요인들 Oracle Database의 Upgrade
  Data의 양이나 성격의 변경
  Object 정의의 변경 (예: Index 변경/삭제)
  System 설정 변경
  Optimizer Parameter 변경

3. SPM과 SQL Profile 차이

SQL Plan Management SQL Profile
  • Execution Plan의 변경으로 발생될지 모르는 성능저하를 예방하기 위한 사전 예방적인 방식이다. (Preventative Mechanism)
  • Plan Hit가 저장되고 Plan History가 관리된다.
  • 저장된 Plan Hit대로 수행되므로 외부적인 환경변화가 있더라도 Plan은 변경되지 않는다.
  • High-Loaded SQL의 Plan을 튜닝한 결과로 생성되므로 사후 조치적인 방식이다.(Reactive Mechanism)
  • SQL이 잘 수행될 수 있도록 일반적인 통계정보 이외의 부가정보를 Dictionary에 저장한다. Plan Hit가 저장되지 않음
  • SQL Profile이 생성되어 있더라도 외부적인 환경변화로 인한 SQL Plan이 변경될 수 있다.

4. SPM과 Stored Outline 차이

SQL Plan Management Stored Outline
  • SQL Plan의 이력관리가 됨.
  • SQL Baseline에 검증된 Plan이 여러 개 존재할 수 있고 실행계획은 그 중에서 선택되어 진다.
  • Plan을 검증하는 기능이 있어 기존의 Plan대비 어느 정도의 Cost와 성능향상이 기대되는지를 분석할 수 있다. 따라서 분석결과에 따라 특정 Plan을 버릴 수도 있고 Plan으로 채택되지 않게 설정을 변경할 수도 있다.
  • SQL Plan의 이력관리가 안됨.
  • Category에 특정 SQL에 대해 오직 하나만의 Plan만 저장할 수 있기 때문에 다른 Plan을 적용시키기 위해서는 Category를 변경하여 저장하거나 새로운 Plan을 Stored Outline으로 저장해야 한다.

5. SPM 장점

1) 반복적으로 수행되는 SQL에 대하여 Execution Plan의 변경이 있을 경우에 변경된 Plan대로 바로 수행하지 않으므로, 혹시라도 발생할 지 모르는 성능저하를 예방할 수 있다.
2) 반복적으로 수행되는 SQL에 대한 Execution Plan의 변경 이력이 자동으로 관리되어 Plan의 History적 분석이 가능하다.
3) SQL Plan Baselines에 저장된 SQL Plan중 검증된 Plan만이 선택되어 수행되므로 SQL의 성능을 보장할 수 있다.
4) 새로운 SQL Plan이 SQL Plan Baseline에 저장된 후 검증과정을 통해 더 좋은 SQL Plan으로 검증되면 이후엔 해당 Plan으로 수행될 수 있으므로 점진적인 SQL 성능향상을 기대할 수 있다.

6. SPM 사용 예

Database Upgrade 데이터베이스가 업그레이드되면 새로운 버전의 Optimizer가 사용되므로 SQL Plan이 변경될 수 있다. 대부분의 SQL Plan은 성능향상이나 이전과 비슷한 수준의 성능을 보이지만 일부 SQL은 Plan변경으로 인해 성능저하를 야기할 수 있다. SPM을 사용하면 업그레이드로 인한 잠재적인 성능저하를 최소화 시킬 수 있다.
운영시스템의 데이터의 변경 데이터베이스내의 테이블의 데이터가 변경되면 일부 SQL에 대한 Plan이 변경될 수 있어 Application성능에 영향을 미칠 수 있다. SPM이 SQL 성능을 안정화 시킬 수 있다.
새로운 Application 추가 테스트 서버에서 개발된 새로운 SQL은 운영서버에서 수행될 경우, 다른 Plan으로 수행될 수 있다. SPM은 SQL Plan Baselines에서 SQL Plan의 이력을 관리하므로 운영환경 하에서 더 좋은 성능을 내는 Plan을 검증해 가면서 SQL의 성능향상을 기대할 수 있다.

IV. SPM 설정

1. SPM 설정

1) Parameter
Parameters Descriptions
OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES 자동으로 SQL Plan을 저장 관리함
OPTIMIZER_USE_SQL_PLAN_BASELINES SQL Plan Baselines에 저장된 Plan을 사용한다.
alter system optimizer_capture_sql_plan_baselines = true;
2) 관련 View
  • DBA_SQL_PLAN_BASELINES
    COLUMN DESCRIPTION
    SQL_HANDLE SQL의 Unique Identifier
    SQL_TEXT SQL Full Text
    PLAN_NAME SQL Plan의 Unique Identifier
    ORIGIN Plan Baseline이 어떻게 생성되었는지를 지정
      MANUAL-LOAD
      AUTO-CAPTURE
      MANUAL-SQLTUNE
      AUTO-SQLTUNE
    ENABLED 해당Plan이 ENABLE 되었는지(YES / NO)
    ACCEPTED 해당Plan이 Accept 되었는지(YES / NO)
      ACCEPTED=NO이면 SQL Plan Baseline에는 저장되어 있으나 Optimizer 에 의해 선택되지 않는다.
    FIXED 해당Plan이 Fix 되었는지(YES / NO)
      Fixing 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 파라미터
    Parameter Description
    sql_handle SQL statement identifier. Unless plan_name is specified, NULL means to consider all statements with non-accepted plans in their SQL plan baselines.
    plan_name Plan identifier. Default NULL means to consider all non- accepted plans in the SQL plan baseline of either the identified SQL statement or all SQL statements if sql_handle is NULL.
    plan_list A list of plan names. Each plan in the list can belong to same or different SQL statement.
    time_limit Time limit in number of minutes. This applies only if verify = 'YES'. The time limit is global and it is used as follows: The time limit for first non-accepted plan verification is set equal to the input value; the time limit for second non-accepted plan verification is set equal to (input value - time spent in first plan verification); and so on.
    > DBMS_SPM.AUTO_LIMIT (Default) lets the system choose an appropriate time limit based on the number of plan verifications required to be done.
    > DBMS_SPM.NO_LIMIT means there is no time limit.
    > A positive integer value represents a user specified time limit
    verify Specifies whether to execute the plans and compare the performance before changing non-accepted plans into accepted plans. A performance verification involves executing a non-accepted plan and a plan chosen from corresponding SQL plan baseline and comparing their performance statistics. If non-accepted plan shows performance improvement, it is changed to an accepted plan.
    > 'YES' (Default) will verify that a non-accepted plan gives better performance before changing it to an accepted plan.
    > 'NO' means not to execute plans but only to change non-accepted plans into accepted plans.
    commit Specifies whether to update the ACCEPTED status of non-accepted plans from 'NO' to 'YES'.
    > 'YES' (Default) - perform updates of qualifying non-accepted plans and generate a report that shows the updates and the result of performance verification when verify = 'YES'.
    > 'NO' - generate a report without any updates. Note that commit = 'NO' together with verify = 'NO' represents a no-op.
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 파라미터
    Parameter Description
    sql_handle SQL statement handle. It identifies plans associated with a SQL statement for an attribute change. If NULL then plan_name must be specified.
    plan_name Plan name. It identifies a specific plan. Default NULL means set the attribute for all plans associated with a SQL statement identified by sql_handle. If NULL then sql_handle must be specified.
    attribute_name Name of plan attribute to set (see table below).
    attribute_value Value of plan attribute to use (see table below)
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 파라미터
    Parameter Description
    sql_handle SQL statement handle. It identifies a SQL statement whose plan(s) are to be displayed.
    plan_name Plan name. It identifies a specific plan. Default NULL means all plans associated with identified SQL statement are explained and displayed.
    format Format string determines what information stored in the plan displayed. One of three format values ('BASIC', 'TYPICAL', 'ALL') can be used, each representing a common use case.
6) SQL Mamagement Base(SMB)
  • SMB는 SYSAUX tablespace에 저장된 Data Dictionary 의 일부분
  • SQL Plan Baselines, SQL Profiles 등을 저장
  • Disk Space Usage
항목 공간사용범위
Default 공간 사용률 SYSAUX 크기의 10% 이하만 차지하도록 자동적으로 관리됨
공간 사용률 지정 범위 SYSAUX의 1% ~ 50%
  • Retention Period
    Retention Period
    Default Retention 기간 53주
    Retention 지정 범위 5 weeks ~ 523 weeks(10 years)
  • 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 한다

참고 사이트

문서에 대하여

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.