SQL Plan Management(SPM)

오늘 운영하고 있는 사이트에서 갑자기 Plan이 변경되어 난리가 났다. 

오라클의 Plan이 변경되는건 여러 이유가 있겠지만 대개는 통계정보 갱신 후 옵티마이저의 오판(?)으로 인한 경우가 제일 많은것 같다. 

오늘 Plan 변경의 가장 큰 원인은 4월 파티션 테이블의 통계정보 갱신으로 보이나, 

그렇다고 4월 시작하자마자 어마어마한 데이터가 들어온 건 아니라서 약간 애매한 구석이 있다. 

그래서 플랜 변경을 알아차릴수 있는 방법에 대해 찾아보다 SPM 기능을 좀 살펴보기로 한다. 

---

1. SQL Plan Management 개요

   

SQL Plan의 변경에 대비해 SQL_PROFILE이나 Stored Outline 등을 사용할 수 있지만 대개는 SQL의 Plan이 변경되고 나서 좋았던 Plan을 찾아 고정하는 사후처리 형태가 많고 plan의 히스토리는 저장되지 않았다. 

11g부터는 SPM 패키지를 사용하면 plan이 변경될 만한 요인이 생기더라도 바로 수정된 plan을 반영하지 않고 검증절차를 거친 후에 성능이 비슷하거나 향상된 경우에만 적용하게 된다. 

갑자기 플랜이 변경되어 난리가 나는 상황은 면할 수 있을듯. 

 

※ SQL Plan이 변경되는 이유         

* 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 처리과정

1) SQL Plan Baseline Capture

   실행되는 SQL Plan을 수집해서 Baseline으로 SYSAUX 저장해 놓는데, 두가지 방법을 사용한다. 

   [자동]

. OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES 옵션을 활성화(True로 변경) 한다. (Default는 False, Online 변경가능)

. 자동으로 모든 SQL에 대해 Plan baseline 을 수집한다. 

. 수집한 Plan baseline의 히스토리 정보를 저장한다. accepted, unaccepted 모두 저장된다. (DBA_SQL_PLAN_BASELINES)

. 자동 수집기능은 부하를 발생시킬 수 있으니, 검증 후 적용할 것.

   [수동]

. DBMS_SPM 패키지를 사용하여 수동으로 저장한다. 

. Baseline이 없으면 새로 생성하고, 있다면 기존 Baseline에 실행계획이 추가된다. 

. 수동으로 플랜정보를 저장하는 경우에는 default로 Accepted로 저장된다. 

      . 수동 수집방법 1 - SQLSET

--LOAD_PLAN_FROM_SQLSET DECLARE l_plans_loaded PLS_INTEGER; BEGIN l_plans_loaded := DBMS_SPM.load_plans_from_sqlset( sqlset_name => 'my_sqlset'); END; /

      .수동 수집방법 2 - CURSOR_CACHE

-- LOAD_PLAN_FROM_CURSOR_CACHE DECLARE l_plans_loaded PLS_INTEGER; BEGIN l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache( sql_id => '1ghe87md0917n'); END;       /

2) SQL Plan Baseline Selection

    . 최초에 플랜정보가 수집되면, enabled, accepted 가 모두 YES인 상태로 적용된다. 

    . SQL이 실행될 때, baseline이 존재하면 flag-accepted 상태인 실행계획을 검색해서 있다면 사용한다. 

    . 플랜이 변경될 수 있는 작업이 수행된다면 baseline에 플랜정보가 추가되나, enabled YES, accepted NO 상태이다.  

    [SQL Selection Decision Tree]

   

   

3) SQL Plan Baseline Evolution

    . 새로 만들어진 실행계획 중, unaccepted 상태의 실행계획을 검증하는 절차이다. 

    . 이 단계가 완료되면 accepted 나 rejected 가 결정된다. 

    . DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE를 수행한다. 

SQL> SELECT sql_handle, plan_name, enabled, accepted 2 FROM dba_sql_plan_baselines 3 WHERE sql_handle = 'SYS_SQL_7a5adea0a1422e62'; SQL_HANDLE PLAN_NAME ENA ACC ------------------------------ ------------------------------ --- --- SYS_SQL_7a5adea0a1422e62 SQL_PLAN_7nqqyn2hn4bm23034dc33 YES NO SYS_SQL_7a5adea0a1422e62 SQL_PLAN_7nqqyn2hn4bm2f2fc655a YES YES SQL> SET LONG 10000 SQL> SELECT DBMS_SPM.evolve_sql_plan_baseline(sql_handle => 'SYS_SQL_7a5adea0a1422e62') 2 FROM dual; DBMS_SPM.EVOLVE_SQL_PL AN_BASELINE(SQL_HANDLE=>'SYS_SQL_7A5ADEA0A1422E62') -------------------------------------------------------------------------------- ------------------------------------------------------------------------------- Evolve SQL Plan Ba seline Report ------------------------------------------------------------------------------- Inputs: ------- SQL_HANDLE = SYS_SQL_7a5adea0a1422e62 PLAN_NAME = TIME_LIMIT = DBMS_SPM.AUTO_LIMIT VERIFY = YES DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(S QL_HANDLE=>'SYS_SQL_7A5ADEA0A1422E62') -------------------------------------------------------------------------------- COMMIT = YES Plan: SQL_PLAN_7nqqyn2hn4bm23034dc33 ------------------------------------ Plan was verified: Time used .172 second s. Plan passed performance criterion: 15.33 times better than baseline plan. Plan was changed to an accepted plan. Baseline Plan Test Plan Stats Ratio ------------- --------- ----------- Execution Status: COMPLETE COMPLETE DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(SQL_HANDLE=>'SYS_SQL_7A5ADEA0A1422E62') -------------------------------------------------------------------------------- Rows Processed: 1 1 Elapsed Time(ms): .174 .012 14.5 CPU Time(ms): 0 0 Buffer Gets: 46 3 15.33 Physical Read Reque sts: 0 0 Physical Write Requests: 0 0 Physical Read Bytes: 0 0 Physical Write Bytes: 0 0 Executions: 1 1 ------------------------------------------------------------------------------- DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(SQL_HANDLE=>'SYS_SQL_7A5ADEA0A1422E62') -------------------------------------------------------------------------------- Report Summary ------------------------------------------------------------------------------- Number of plans verified: 1 Number of plans accepted: 1

    . accept가 결정되면 accepted YES 상태로 변경되고 옵티마이저는 cost가 적은 실행계획을 사용한다. 

    . evolve 작업은 필요할 경우 수행하거나, 자동으로 돌도록 등록할 수 있다. SYS_AUTO_SPM_EVOLVE_TASK 작업이 매일 scheduler에서 수행되며 다음 작업을 한다. 

        - Selects and ranks unaccepted plans for verification

         - Accepts each plan if it satisfies the performance threshold

3. SPM Management

   

   1) ALTER_SQL_PLAN_BASELINE 함수를 사용하여 baseline 속성 값을 변경할 수 있다.

      - Enabled : accepted 일 경우에 옵티마이저가 선택할지(YES) 안할지(NO) 결정함.

      - Fixed : 플랜을 Fix할때 사용, YES일 경우 새로운 실행계획이 들어와도 EVOLVING하지 않는다.

      - autopurge : 일정기간 사용하지 않은 baseline을 자동삭제함.

      - plan_name : sql 플랜이름

      - description : sql 플랜설명

SQL> DECLARE 2 l_plans_altered PLS_INTEGER; 3 BEGIN 4 l_plans_altered := DBMS_SPM.alter_sql_plan_baseline( 5 sql_handle => 'SYS_SQL_7a5adea0a1422e62', 6 plan_name => 'SQL_PLAN_7nqqyn2hn4bm23034dc33', 7 attribute_name => 'fixed', 8 attribute_value => 'YES'); 9 10 DBMS_OUTPUT.put_line('Plans Altered: ' || l_plans_altered); 11 END; 12 / Plans Altered: 1

   2) 테이블스페이스 (SYSAUX) 관리가 필요하다. 

       - space_budget_percent : SYSAUX에 저장할수 있는 최대 용량 (%, 1~50, default 10)

       - plan_retention_weeks : 사용하지 않는 실행계획에 대한 보관주기 (5~523, default )

SQL> SELECT parameter_name, parameter_value 2 FROM dba_sql_management_config; PARAMETER_NAME PARAMETER_VALUE ------------------------------ --------------- SPACE_BUDGET_PERCENT 10 PLAN_RETENTION_WEEKS 53 SQL> BEGIN 2 DBMS_SPM.configure('space_budget_percent', 11); 3 DBMS_SPM.configure('plan_retention_weeks', 54); 4 END; 5 / PL/SQL 처리가 정상적으로 완료되었습니다. SQL> SQL> SELECT parameter_name, parameter _value 2 FROM dba_sql_management_config; PARAMETER_NAME PARAMETER_VALUE ------------------------------ --------------- SPACE_BUDGET_PERCENT 11 PLAN_RETENTION_WEEKS 54

   3) Baseline 삭제

       - DROP_SQL_PLAN_BASELINE 기능을 통해 베이스라인을 삭제할 수 있다.

SQL> SET SERVEROUTPUT ON SQL> DECLARE 2 l_plans_dropped PLS_INTEGER; 3 BEGIN 4 l_plans_dropped := DBMS_SPM.drop_sql_plan_base line ( 5 sql_handle => 'SYS_SQL_7a5adea0a1422e62', 6 plan_name => NULL); 7 8 DBMS_OUTPUT.put_line(l_plans_dropped); 9 END; 10 / 2 PL/SQL 처리가 정상적으로 완료되었습니다. SQL> SELECT sql_handle, plan_name, enabled, accepted 2 FROM dba_sq l_plan_baselines 3 WHERE sql_handle = 'SYS_SQL_7a5adea0a1422e62'; 선택된 레코드가 없습니다.

    

---

11g 부터는 자동통계 작업이 10g보다 훨씬 좋아져서 많이들 사용하고 있지만, 갑작스럽게 플랜이 변경되는 일을 막기는 어렵다. 

그래서 SPM을 활용해보면 장애를 줄이는데 도움이 될것 같다. 

하지만 자동으로 수집할 경우의 부하문제, Evolve 스케줄, 테이블스페이스 관리 등의 문제가 남아있고

SQL 튜닝을 빈번하게 하는경우 튜닝적용을 위한 절차가 늘어나게 된다. 

주요 SQL 관리를 위해서 수동으로 적용해 보는것은 해볼만 하겠다. 

이 기능을 사용하지 않고, 플랜변경된 SQL을 감지해서 플랜을 고정할 수 있는 SQL Profile에 대해서 다음번에 정리하겠다.

참고 : https://docs.oracle.com/database/121/TGSQL/tgsql_spm.htm#TGSQL615