SPA(SQL Performance Analyze) 를이용한통계정보 수집 엑셈컨설팅본부 /DB 컨설팅팀오경렬 1. SPA 란? SPA(SQL Performance Analyze) 는 RAT(Real Application Testing) 서비스의옵션중에하나 이다. 본문서는 SPA 를이용해좀더안정적으로통계정보를생성하는 Process 에대해다룬다. 2. 통계정보와 SQL Tuning 튜닝은 LEADING(A) USE_NL(B) INDEX(A A(COL1)) 처럼간단한힌트로대부분해결가능하다. 이것이가능한이유는 Leading 과같은힌트로쿼리의키조건이무엇인지지정해주면나머지플랜은 Oracle Optimizer 가 Query Transformation 등최적화알고리즘을적용해최적의성능을낼수있도록플랜을작성하기때문이다. 튜너 A 가사이트에튜닝지원을간다. 사이트에도착한 A 는가지고있던스크립트로능숙하게 Tuning 대상을발췌한다. 수집기준은 Execution 이 1000 번이상이면서 Buffer Gets 이 10000 Block 이상인쿼리무려 200 개의쿼리가추출되었다... A 군은순간당황했지만빠르게안정을되찾고입고있던와이셔츠의소매단추를풀고두번접어올리고나서는차고있던시계를풀어책상위에올려놓는다. 시간은 9 시 30 분을지나고있다. " 흠.. 오늘몇개나할수있을까..." A 군은알듯말듯한미소를지어보이고는중지손가락으로안경을한번치켜올리고이내불꽃튜닝에들어간다 112 2013 기술백서 White Paper
그리고.. 엑셀에하나둘완료된쿼리가늘어날때마다... 희열을느낀다... 이글을보는당신은어떻게할것인가? A 군의판단이잘못된것인가? 잘못된판단이아니다최선의판단이다... 11g 전까지는... 11g New Feature 인 Pending Statistics 를 SPA 와함께사용한다면 A 군의 200 개의튜닝대상쿼리는 10 개내외로줄어들수도있다. 오라클 Optimizer 는생각보다똑똑하다. 3. SPA 사용가능여부확인 RAT Option 이 FALSE 면사용할수없다. SELECT * FROM V$OPTION WHERE PARAMETER = 'Real Application Testing' PARAMETER VALUE -------------------------------------- -------- Real Application Testing TRUE 4. 사용예 간단한사례를다룬다. 본인의것으로흡수하고수정해서실무에적용하길바란다. 시나리오요약 0. 테스트스키마생성및쿼리수행 1. SQL_SET 생성 2. ANALYSIS_TASK #1 생성 3. SCHEMA or TABLE Pending Statistcs 설정 4. 통계정보수집 5. Session Use Pending Statistics 설정 Part 1 ORACLE 113
6. ANALYSIS_TASK #2 생성 7. ANALYSIS_TASK #1, #2 비교 8. 리포팅 9. 비교결과조회스크립트 0. 테스트스키마생성및쿼리수행 DROP TABLE EXEM_T1 PURGE; CREATE TABLE EXEM_T1 AS SELECT LEVEL C1, 'A' C2 FROM DUAL CONNECT BY LEVEL <= 100000; INSERT INTO EXEM_T1 VALUES(1, 'B'); CREATE INDEX I1_EXEM_T1 ON EXEM_T1(C2) TABLESPACE TSD_QM; EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME =>'MAXGAUGE', TABNAME =>'EXEM_T1', NO_INVALIDATE =>FALSE, METHOD_OPT =>'FOR ALL COLUMNS SIZE 3', ESTIMATE_PERCENT =>10); -- SQL 수행 (A, B 두가지가있고분포가좋지않음 -> FTS) SELECT COUNT(C1) FROM EXEM_T1 WHERE C2 = :B1; --COUNT(C1) --100000 1. SQL_SET 생성 SELECT ID, 114 2013 기술백서 White Paper
NAME, OWNER, DESCRIPTION, CREATED, LAST_MODIFIED, STATEMENT_COUNT FROM DBA_SQLSET where name = 'OKR_TEST'; --ID NAME OWNER DESCRIPTION CREATED LAST_MODIFIED STATEMENT_COUNT --24 OKR_TEST MAXGAUGE 2013-06-27 오후 1:37:31 2013-06-27 오후 1:37:43 4 -- SQLSET 과 ANALYSIS_TASK 연결고리확인 SELECT DESCRIPTION FROM DBA_SQLSET_REFERENCES WHERE SQLSET_NAME = 'OKR_TEST'; --DESCRIPTION --created by: SQL Performance Analyzer - task: OKR_SPA SELECT TASK_NAME, EXECUTION_NAME, EXECUTION_TYPE, STATUS, EXECUTION_END FROM USER_ADVISOR_EXECUTIONS WHERE TASK_NAME = 'OKR_SPA'; --TASK_NAME EXECUTION_NAME EXECUTION_TYPE STATUS EXECUTION_END --OKR_SPA OKR_AFTER TEST EXECUTE COMPLETED 2013-06-27 오후 1:48:07 --OKR_SPA OKR_BEFORE TEST EXECUTE COMPLETED 2013-06-27 오후 1:42:52 --OKR_SPA OKR_COMP COMPARE PERFORMANCE COMPLETED 2013-06-27 오후 1:48:34 -- TASK 가존재할경우 SET 삭제전에 TASK 먼저삭제해야그다음 SET 삭제가가능하다. -- ANALYSIS_TASK DROP EXEC DBMS_SQLPA.DROP_ANALYSIS_TASK('OKR_SPA'); -- TUNING SET DROP EXEC DBMS_SQLTUNE.DROP_SQLSET('OKR_TEST'); Part 1 ORACLE 115
2. ANALYSIS_TASK #1 생성 ANALYSIS_TASK 는 4 가지방법으로생성가능하다. (1) Sql text format EXEC :stmt_task := DBMS_SQLPA.CREATE_ANALYSIS_TASK( sql_text => 'select quantity_sold from sales s, times t where s.time_id = t.time_id and s.time_id = TO_DATE(''24-NOV-00'')'); (2) Sql id format (cursor cache) EXEC :stmt_task := DBMS_SQLPA.CREATE_ANALYSIS_TASK(sql_id => 'ay1m3ssvtrh24'); (3) Workload repository format exec :stmt_task := DBMS_SQLPA.CREATE_ANALYSIS_TASK( begin_snap => 1, end_snap => 2, sql_id => 'ay1m3ssvtrh24'); (4) Sql tuning set format (first we need to load an STS, then analyze it) EXEC :sts_task := DBMS_SQLPA.CREATE_ANALYSIS_TASK( --- sqlset_name => 'my_workload', --- order_by => 'BUFFER_GETS', --- description => 'process workload ordered by buffer gets'); -- 아래는 Sql tuning set format 사용한예제이다. --SQLSET 생성 EXEC DBMS_SQLTUNE.CREATE_SQLSET('OKR_TEST'); --내용을채워넣는다 --COMMAND TYPE --2 --> insert --3 --> select (for update) --6 --> update 116 2013 기술백서 White Paper
--7 --> delete --189 --> merge DECLARE cur DBMS_SQLTUNE.SQLSET_CURSOR; BEGIN OPEN cur FOR SELECT VALUE(P) FROM table( DBMS_SQLTUNE.SELECT_CURSOR_CACHE( 'parsing_schema_name in (''MAXGAUGE'') and upper(sql_text) like ''%EXEM_T1%'' AND command_type IN (2,3,6,7,189)', NULL, NULL, NULL, NULL, 1, NULL, 'ALL')) P; DBMS_SQLTUNE.LOAD_SQLSET(sqlset_name => 'OKR_TEST',populate_cursor => cur); END; ----update and delete SQL statements from an STS based on a search condition --BEGIN -- DBMS_SQLTUNE.DELETE_SQLSET( -- sqlset_name => 'my_sql_tuning_set', -- basic_filter => 'executions < 50'); --END; --/ -- -- Task 생성확인 select count(1) from DBA_SQLSET_STATEMENTS where sqlset_name = 'OKR_TEST' --count(1) --4 -- 생성 DECLARE TNAME VARCHAR2(30); BEGIN TNAME := DBMS_SQLPA.CREATE_ANALYSIS_TASK( SQLSET_NAME => 'OKR_TEST', BASIC_FILTER => NULL, ORDER_BY => 'EXECUTIONS', TOP_SQL => 100 Part 1 ORACLE 117
, TASK_NAME => 'OKR_SPA', DESCRIPTION => NULL, SQLSET_OWNER => NULL ); END; -- EXECUTE_ANALYSIS_TASK -- 가장빠른방법은 execution_type => 'CONVERT SQLSET' -- ANALYSIS_TASK 생성 begin DBMS_SQLPA.EXECUTE_ANALYSIS_TASK( task_name => 'OKR_SPA', execution_type => 'EXECUTE', execution_name => 'OKR_BEFORE'); end; -- 생성결과조회 select execution_name,status,execution_end,execution_end from USER_ADVISOR_EXECUTIONS where task_name = 'OKR_SPA'; --EXECUTION_NAME STATUS EXECUTION_END --OKR_BEFORE COMPLETED 2013-07-02 오후 3:04:03 3. SCHEMA or TABLE Pending Statistcs 설정 -- 조회결과가 TRUE 이면 Pending 이걸려있지않은상태다 SELECT DBMS_STATS.GET_PREFS('PUBLISH', 'maxgauge', 'EXeM_T1') PUBLISH FROM DUAL; --PUBLISH --TRUE -- FALSE 로변경 -> 운영간에통계정보생성에의한플랜변경을막기위한조치 Exec dbms_stats.set_schema_prefs('maxgauge', pname => 'PUBLISH', pvalue => 'FALSE'); 118 2013 기술백서 White Paper
4. 통계정보수집 -- 거의대부분의데이터를지우고 Histogram 도생성하기때문에인덱스를이용함 delete exem_t1 where c2 = 'A' -- 통계정보재생성 exec dbms_stats.gather_table_stats(ownname =>'MAXGAUGE', tabname =>'EXEM_T1', no_invalidate =>FALSE, method_opt =>'for all columns size 3', estimate_percent=>10); -- Pending 통계조회 SELECT * FROM DBA_TAB_PENDING_STATS WHERE TABLE_NAME = 'EXEM_T1' -- 하지만통계를 Pending 했으므로여전히 FTS 플랜 select count(c1) from exem_t1 where c2 = :b1; --Pending 상태에서수집한최신통계정보를이용해서 SPA 수행 5. Session Use Pending Statistics 설정 alter session set optimizer_use_pending_statistics = TRUE; -- 하지만통계를 Pending 했으므로여전히 FTS 플랜 select count(c1) from exem_t1 where c2 = :b1; 6. ANALYSIS_TASK #2 생성 -- OKR_AFTER 이름으로 TASK 를만든다. begin DBMS_SQLPA.EXECUTE_ANALYSIS_TASK( Part 1 ORACLE 119
task_name => 'OKR_SPA', execution_type => 'EXECUTE', execution_name => 'OKR_AFTER'); 새섹션 2 페이지 5 execution_name => 'OKR_AFTER'); end; 7. ANALYSIS_TASK #1, #2 비교 begin DBMS_SQLPA.EXECUTE_ANALYSIS_TASK( task_name => 'OKR_SPA', execution_type => 'COMPARE PERFORMANCE', execution_name => 'OKR_COMP', execution_params => dbms_advisor.arglist( 'execution_name1', 'OKR_BEFORE', 'execution_name2', 'OKR_AFTER' ) ); end; 8. 리포팅 set long 100000 longchunksize 100000 linesize 200 head off feedback off echo off spool report.html SELECT dbms_sqlpa.report_analysis_task('okr_spa', 'HTML', 'ALL','ALL') FROM dual; spool off --Click here to see the file report.html in html formart ; 120 2013 기술백서 White Paper
9. 비교결과조회스크립트 with TMP as ( select /*+ materialize */ Part 1 ORACLE 121
b.sql_id,b.plan_hash_value b_ph,a.plan_hash_value a_ph,case when (b.buffer_gets-a.buffer_gets) >= 0 then ' 개선 ' when (b.buffer_gets-a.buffer_gets) < 0 then ' 저하 ' end " 성능 ",b.optimizer_cost b_cost,a.optimizer_cost a_cost,b.executions b_exec,a.executions a_exec,b.rows_processed b_rows,a.rows_processed a_rows,b.elapsed_time B_ET,a.elapsed_time A_ET,b.buffer_gets B_BG,a.buffer_gets A_BG,b.disk_reads b_dr,a.disk_reads a_dr,round(100- (decode(a.buffer_gets,0,1,a.buffer_gets)/decode(b.buffer_gets,0,1,b.buffer_gets))*100, 1) bg_rate,round(100- (decode(a.elapsed_time,0,1,a.elapsed_time)/decode(b.elapsed_time,0,1,b.elapsed_time))* 100,1) et_rate from dba_advisor_sqlstats b,dba_advisor_sqlstats a where 1=1 and b.execution_name = :before and a.execution_name = :after and a.sql_id = b.sql_id and a.plan_hash_value <> b.plan_hash_value ) select /*+ leading(a) use_nl(r) */ hash_value hv,a.*,parsing_schema_name pars_nm,sql_profile Pf,module,executions R_EXEC 122 2013 기술백서 White Paper
,round(buffer_gets/decode(executions,0,1,executions),0) R_BG,round(DISK_READS/decode(executions,0,1,executions),1) R_DR,round(ROWS_PROCESSED/decode(executions,0,1,executions),0) R_ROWS,round(ELAPSED_TIME/decode(executions,0,1,executions)/1000000,1) R_ET,round(CPU_TIME/decode(executions,0,1,executions)/1000000,1) R_CT,last_active_time last_t,first_load_time first_t,sql_fulltext text from tmp a,v$sqlarea r where a.sql_id = r.sql_id 5. 마치며 본문서의내용은프로그램으로치면 "Hello World" 이다. 꼭내용을숙지하고스크립트나엑셀 메크로등으로자동화하여업무에적용하길바란다. 이론은절대경험을이기지못한다. Part 1 ORACLE 123