SQL PLAN MANAGEMENT 활용 엑셈컨설팅본부 /DB 컨설팅팀장정민 개요 오라클은비롯한많은관계형 DBMS 에서는사용자의 SQL 질의를효율적으로처리하기위해옵티마이저를사용하고있다. 옵티마이저는유저가수행하는 SQL 을받아실행계획을생성하고, 실제 SQL 은이실행계획을

SQL PLAN MANAGEMENT 활용 엑셈컨설팅본부 /DB 컨설팅팀장정민 개요 오라클은비롯한많은관계형 DBMS 에서는사용자의 SQL 질의를효율적으로처리하기위해옵티마이저를사용하고있다. 옵티마이저는유저가수행하는 SQL 을받아실행계획을생성하고, 실제 SQL 은이실행계획을통해서수행된다. 데이터베이스운영시평소잘수행되던 SQL 이성능이슈를발생시키는때가있는데, 그원인이 SQL 실행계획변화에있는경우가많다. SQL 의실행계획이변하는이유는다양한데, 통계정보의변경이나인덱스의상태변화, DB 의 파라메터나 version 변경등에의해 SQL 의실행계획이변할수있다. 그런데 SQL 의실행계획 변화를막기위해이런 DB 작업을하지않을수는없다. 이러한실행계획의변화로인해발생될지모르는성능저하를예방하기위해 Oracle 11g 부터 SPM(SQL Plan Management) 라는기능을제공하고, 이를이용해 SQL 외부적인요소에의한 영향을최소화할수있다. SPM 의사용목적과특성 SPM 은 SQL 외부적인요소의변화에의한영향을최소화하는데그목적이있다. SPM 의주요 특성으로는다음의두가지를들수가있다. 1. Execution Plan 의변경에의한성능저하를사전예방 2. Plan History 관리를통한 SQL Plan 의이력관리가능 SPM 은 SQL Plan Baseline(Plan 과 Hint) 을 DB 에저장해놓고검증된실행계획만을사용할 수있도록하면서, 자동으로변경되는 SQL 의실행계획을관리한다. 새로운실행계획이생성될 경우, 검증이끝날때까지사용하지않도록하여 SQL 실행계획변경으로발생할수있는성능 Part 1 ORACLE 225

문제를사전에예방할수있다. 또새로생성된실행계획은검증과정에서현재의실행계획과비 교해성능이향상된경우에만사용할수있도록하는것이가능하다. SPM 사용순서 SPM 의사용은다음과같은순서로진행한다. SQL_PLAN_BASELINES 관련파라미터변경 SQL_PLAN_BASELINES 에실행계획등록 DBA_SQL_PLAN_BASELINES 뷰를통해확인 SQL_PLAN_BASELINES 속성변경을통한사용실행계획제어 SQL_PLAN_BASELINES 관련파라메터변경 관련파라메터조회 SQL> show parameter sql_plan_baselines NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ optimizer_capture_sql_plan_baselines boolean FALSE optimizer_use_sql_plan_baselines boolean TRUE optimizer_use_sql_plan_baselines : SPB 를활성화하는파라메터 optimizer_capture_sql_plan_baselines : 2 회이상수행되는 SQL 을 SPB 에자동등록하도록하는파라메터 226 2013 기술백서 White Paper

SQL_PLAN_BASELINES 에실행계획등록 & 삭제 SPB 에실행계획을등록하는방법은두가지가있는데 DBMS_SPM 패키지를사용하여수동으로직접등록하는방법과, optimizer_capture_sql_plan_baselines 파라메터설정을통해자동으로실행계획을등록시키는방법이있다. SPB 수동등록 SPB Baseline 등록패키지내용 DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE ( sql_id IN VARCHAR2, plan_hash_value IN NUMBER := NULL, fixed IN VARCHAR2 := 'NO', enabled IN VARCHAR2 := 'YES') RETURN PLS_INTEGER; 등록하려는 SQL 정보확인 SELECT sql_id, plan_hash_value, sql_fulltext FROM v$sql WHERE sql_text LIKE '%spmtest%' SQL_ID PLAN_HASH_VALUE SQL_FULLTEXT ------------- --------------- ---------------------------------------- 93zny6hkjj6s7 1690735414 SELECT * FROM spmtest WHERE lv = 10 93zny6hkjj6s7 4164974113 SELECT * FROM spmtest WHERE lv = 10 SQL_ID 와 PLAN_HASH_VALUE 이용하여 SPB 등록 DECLARE pls pls_integer ; BEGIN pls := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE ( '93zny6hkjj6s7', '1690735414') ; dbms_output.put_line( pls ' 개등록 ' ) ; END ; / Part 1 ORACLE 227

SQL_ID 와 PLAN_HASH_VALUE 를실행계획을유일하게식별가능하다. 만약 PLAN_HASH_VALUE 값을입력하지않을경우 SQL_ID 에해당하는 PLAN 을모두 SPB 에등록한다. optimizer_capture_sql_plan_baselines 파라메터를 true 로설정하면 2 회이상수행되는모든 SQL 의 PLAN 을자동으로 SPB 에등록한다. SPB Baseline 삭제패키지 DBMS_SPM.DROP_SQL_PLAN_BASELINE ( sql_handle IN VARCHAR2 := NULL, plan_name IN VARCHAR2 := NULL) RETURN PLS_INTEGER; DECLARE pls pls_integer ; BEGIN pls := DBMS_SPM.DROP_SQL_PLAN_BASELINE ( 'SYS_SQL_351516f2a705638a') ; dbms_output.put_line( pls ' 개삭제 ' ) ; END ; / Baseline 의삭제는 sql_handle 과 plan_name 을입력해서수행한다. 만약값을입력하지않으면모든값이해당된다. DBA_SQL_PLAN_BASELINES 뷰를통해확인 DBA_SQL_PLAN_BASELINES 뷰조회 SQL>SELECT * SQL>FROM dba_sql_plan_baselines SIGNATURE SQL_HANDLE SQL_TEXT PLAN_NAME CREATOR ORIGIN ---------- ------------------------ ---------- ------------------------------ -------- ----- 3.82498868 SYS_SQL_351516f2a705638a SELECT * SQL_PLAN_3a58qyamhaswa2b869b05 SYS MANUAL-LOAD 228 2013 기술백서 White Paper

DBA_SQL_PLAN_BASELINES 뷰칼럼내용 Column Datatype Description SIGNATURE NUMBER SQL ID. V$SQL(AREA) 뷰의 SQL_HANDLE VARCHAR2(30) BASLINE ID SQL_TEXT CLOB SQL text PLAN_NAME VARCHAR2(30) Plan ID CREATOR VARCHAR2(30) BASELINE 생성유저 ORIGIN VARCHAR2(14) BASELINE 생성경로 DESCRIPTION VARCHAR2(500) BASELINE 설명 VERSION VARCHAR2(64) BASELINE 생성시 DB 버젼 CREATED TIMESTAMP(6) BASELINE 생성시간 LAST_MODIFIED TIMESTAMP(6) BASELINE 마지막수정시간 LAST_EXECUTED TIMESTAMP(6) BASELINE 마지막실행시간 LAST_VERIFIED TIMESTAMP(6) BASELINE 마지막검증시간 ENABLED VARCHAR2(3) Optimizer 에의해사용될수있는지를나타내는속성 ACCEPTED VARCHAR2(3) ACCEPTED 속성 FIXED VARCHAR2(3) FIXED 속성 AUTOPURGE VARCHAR2(3) 사용하지않고일정시간이지날경우자동 purge OPTIMIZER_COST NUMBER BASELINE 생성시 COST MODULE VARCHAR2(48) Application 모듈명 ACTION VARCHAR2(32) Application Action EXECUTIONS NUMBER BASELINE 생성시점의값 ELAPSED_TIME NUMBER BASELINE 생성시점의값 CPU_TIME NUMBER BASELINE 생성시점의값 BUFFER_GETS NUMBER BASELINE 생성시점의값 DISK_READS NUMBER BASELINE 생성시점의값 DIRECT_WRITES NUMBER BASELINE 생성시점의값 ROWS_PROCESSED NUMBER BASELINE 생성시점의값 FETCHES NUMBER BASELINE 생성시점의값 END_OF_FETCH_COUNT NUMBER BASELINE 생성시점의값 Part 1 ORACLE 229

SQL_PLAN_BASELINES 속성변경을통한사용실행계획제어 SPB 속성변경패키지내용 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; 변경가능속성 : enabled, fixed, autopurge, plan_name, description Baseline 의실행계획사용시우선순위를결정하는속성으로 ENABLED, ACCEPTED, FIXED 의 3 가지속성이있다. 위세가지속성중에서 ENABLED 와 ACCEPTED 속성은 Baseline 이사용가능한지를나타내는속성으로모두 YES 상태인 Baseline 만사용이가능하다. ENABLED 속성은사용자가직접변경가능하다. ACCEPTED 속성의경우최초등록되는 Baseline 과사용자가커서캐시에서수동으로등록하는 Baseline 은 YES 상태로등록된다. 실행계획변경이발생하여자동등록되는실행계획의경우 NO 상태로등록된다. FIXED 속성은 Baseline 사용시우선순위를결정하는속성으로 ENABLED 와 ACCEPTED 속성이모두 YES 인 Baseline 중 FIXED 속성이 YES 인 Baseline 이우선적으로선택된다. 만일우선순위가같은 Baseline 이여러개존재할경우에는 Optimizer 에의해 Cost 가낮게판단되는 Baseline 이선택된다. 일단 Baseline 에등록되어사용가능한 Baseline 이존재하고, DB 파라메터가 Baseline 을사용하도록설정되어있으면 SQL 실행시 Baseline 을통해실행계획을반영한다. 이후에 SQL 외적인요인으로 SQL 의실행계획에변화가생길경우새로생성되는실행계획은바로 SQL 수행에반영되지않고, ACCEPTED 속성이 NO 인상태로자동으로 SPB 에저장된다. 새로생성된 Baseline 은검증과정을거쳐 ACCEPTED 속성이 YES 인상태로바뀔때까지 Baseline 선택에서제외된다. 230 2013 기술백서 White Paper

SPB 검증패키지 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; 패키지를실행하면 non accepted 상태의 Baseline 에대해검증작업을수행한다. Verify 값이 yes 일경우 time_limit 으로설정된시간이내에실제검증작업을수행한다. Commit 이 yes 일경우 accepted 속성을 yes 로바꾸고, no 일경우바뀌지않는다. 결과값으로검증작업에대한레포트를생성한다. 테스트 SQL>SELECT signature, SQL> sql_handle, SQL> plan_name, SQL> origin, SQL> enabled, SQL> accepted, SQL> fixed SQL>FROM dba_sql_plan_baselines SIGNATURE SQL_HANDLE PLAN_NAME ORIGIN ENABLED ACCEPTED FIXED OPTIMIZER_COST ---------- ----------------------- ------------------------ -------------- ------- --- 3.82498868 SYS_SQL_351516f2a705638a SQL_PLAN_3a58qyamhaswa2b869b05 AUTO-CAPTURE YES NO NO 1938 3.82498868 SYS_SQL_351516f2a705638a SQL_PLAN_3a58qyamhaswaeaf1df04 AUTO-CAPTURE YES YES NO 2 현재아래쪽 Baseline 을사용중새로운실행계획의생성으로위쪽 Baseline 이생성된상태. ( 위 FULL SCAN, 아래 INDEX SCAN) Part 1 ORACLE 231

Accepted 가 NO 인상태이기때문에 Baseline 이사용되지않는다. YES 로바꾸기위한검증작 업실행 DECLARE pls clob ; BEGIN pls := dbms_spm.evolve_sql_plan_baseline( 'SYS_SQL_351516f2a705638a', 'SQL_PLAN_3a58qyamhaswa2b869b05', DBMS_SPM.AUTO_LIMIT, 'yes', 'yes' ) ; dbms_output.put_line( pls ) ; END ; / Evolve SQL Plan Baseline Report Inputs: ------- SQL_HANDLE = SYS_SQL_351516f2a705638a PLAN_NAME = SQL_PLAN_3a58qyamhaswa2b869b05 TIME_LIMIT = DBMS_SPM.AUTO_LIMIT VERIFY = yes COMMIT = yes Plan: SQL_PLAN_3a58qyamhaswa2b869b05 ------------------------------------ Plan was verified: Time used.12 seconds. Plan failed performance criterion: 95.07 times worse than baseline plan. Baseline Plan Test Plan Stats Ratio ------------- --------- ----------- Execution Status: COMPLETE COMPLETE Rows Processed: 1 1 Elapsed Time(ms):.159 10.617.01 CPU Time(ms):.111 10.553.01 Buffer Gets: 3 7153 0 Physical Read Requests: 0 0 Physical Write Requests: 0 0 Physical Read Bytes: 0 0 Physical Write Bytes: 0 0 Executions: 1 1 232 2013 기술백서 White Paper

Report Summary Number of plans verified: 1 Number of plans accepted: 0 검증작업실행시 Commit 값을 yes 로입력했으나. 실제검증시에새로생성된 SQL 이비효율적이라판단되서인증되지않았다. 새로운 Baseline 사용하려면현재사용되는 Baseline 보다우선순위를높게만들어줘야한다. Accepted 값을바꾸기위해실제 Verify( 검증 ) 하지않고강제변경 Evolve SQL Plan Baseline Report Inputs: ------- SQL_HANDLE = SYS_SQL_351516f2a705638a PLAN_NAME = SQL_PLAN_3a58qyamhaswa2b869b05 TIME_LIMIT = DBMS_SPM.AUTO_LIMIT VERIFY = no COMMIT = yes Plan: SQL_PLAN_3a58qyamhaswa2b869b05 ------------------------------------ Plan was changed to an accepted plan. Report Summary Number of plans verified: 0 Number of plans accepted: 1 Fixed 속성변경 DECLARE pls pls_integer ; BEGIN pls := DBMS_SPM.ALTER_SQL_PLAN_BASELINE ( 'SYS_SQL_351516f2a705638a', 'SQL_PLAN_3a58qyamhaswa2b869b05', 'fixed', 'yes') ; dbms_output.put_line( pls ' 개변경 ' ) ; END ; / Part 1 ORACLE 233

SIGNATURE SQL_HANDLE PLAN_NAME ORIGIN ENABLED ACCEPTED FIXED OPTIMIZER_COST ---------- ----------------------- ------------------------ ------------ ------- ----- 3.82498868 SYS_SQL_351516f2a705638a SQL_PLAN_3a58qyamhaswa2b869b05 AUTO-CAPTURE YES YES YES 1938 3.82498868 SYS_SQL_351516f2a705638a SQL_PLAN_3a58qyamhaswaeaf1df04 AUTO-CAPTURE YES YES NO 2 SQL_ID SQL_TEXT SQL_PLAN_BASELINE PLAN_HASH_VALUE ----------- ------------------------------- --------------------------- -------------- 93zny6hkjj6s7 SELECT * FROM spmtest WHERE lv = 100 SQL_PLAN_3a58qyamhaswa2b869b05 1690735414 - Baseline 사용시 Fixed 된것이우선순위가높기때문에해당 Baseline 사용. 만약 2 개모두 Fixed 될경우 DECLARE pls pls_integer ; BEGIN pls := DBMS_SPM.ALTER_SQL_PLAN_BASELINE ( 'SYS_SQL_351516f2a705638a', 'SQL_PLAN_3a58qyamhaswaeaf1df04', 'fixed', 'yes') ; dbms_output.put_line( pls ' 개등록 ' ) ; END ; / SIGNATURE SQL_HANDLE PLAN_NAME ORIGIN ENABLED ACCEPTED FIXED OPTIMIZER_COST --------- ------------------------ ------------------------ ------------ ------- ----- 3.82498868 SYS_SQL_351516f2a705638a SQL_PLAN_3a58qyamhaswa2b869b05 AUTO-CAPTURE YES YES YES 1938 3.82498868 SYS_SQL_351516f2a705638a SQL_PLAN_3a58qyamhaswaeaf1df04 AUTO-CAPTURE YES YES YES 2 SQL_ID SQL_TEXT SQL_PLAN_BASELINE PLAN_HASH_VALUE ----------- ------------------------------- ------------------------------ ----------- 93zny6hkjj6s7 SELECT * FROM spmtest WHERE lv = 100 SQL_PLAN_3a58qyamhaswaeaf1df04 1577308861 234 2013 기술백서 White Paper

- 우선순위가같은 Baseline 이존재할경우 COST 가낮은쪽의 Baseline 이선택된다. 일단 SPB 에어떤 SQL 에대한 Baseline 이존재하면 SQL 의 Text 가동일한 SQL 에대해서실행계획의변화가생길때해당실행계획에대한 Baseline 이자동으로등록된다. 다만 SPB 의 FIXED 속성이 YES 인 Baseline 이존재하고, 해당 Baseline 이사용되고있는경우에는 SQL 의실행계획에영향을미치는변화가생겨도새로운실행계획을생성하지않는다. 이상의내용을다음표와같이정리할수있다. 속성 적용여부 우선적용적용가능적용안됨적용안됨 ENABLED YES YES ALL NO ACCEPTED YES YES NO ALL FIXED YES NO ALL ALL 새실행계획생성안함 SPB 등록 ( 비검증 ) 결론 DB 를운영하는데있어서통계정보의변경이나인덱스의상태변화, DB 의파라메터변경, DB version 변경등 SQL 의실행계획을변화시킬수있는요인은많이있다. 또이로인한성능문제를겪는경우도있을수있다. 그런데이런문제가생길수있다고해서해당작업들을아예하지않을수는없는일이다. 이럴때 SQL 의실행계획변화에의한문제를막기위해 SPM 사용을고려해볼수있다. SPM 은여러개의실행계획을저장해놓고유동적으로사용이가능하다. Part 1 ORACLE 235

SPM 에대한내용을숙지하고, 적절하게사용할수있다면, DB 를운영하는데도움이될수있 을것이다. 236 2013 기술백서 White Paper