ePapyrus PDF Document

Size: px

Start display at page:

Download "ePapyrus PDF Document"

이현 필
5 years ago
Views:

Goodus 기술노트 [38 회 ] Author 윤병길, 이은정 Creation Date 2009-02-27 Last Updated Version 1.

2 Contents 1. SQL Tunning 은해야한다, 그러나 SQL 은수정할수없다! Stored Outline Outline 사용할 User 에게 Outline 실행할수있는권한부여 Outline Test 환경조성 Outline 생성할 Table 의통계정보생성 수정할수없는 SQL 의 Plan 확인 수정할수없는 SQL 로 Outline 생성 Tunning 된 SQL Plan 확인 Tunning 된 SQL 로 Outline 생성 생성된 Outline 들확인 Outline 수정 Tunning 된 Plan 실행여부확인 Outline Tunning 시유의사항 SQL Profile ( 10g ) SQL Profile 사용할 User 에게필요권한부여 SQL Profile Test 환경조성 수정할수없는 SQL Plan 확인 Tunning 된 SQL 의 Full Hint Naming 확인 SQL Profile 수동생성 Tunning 된 Plan 실행여부확인 SQL Plan Baseline ( 11g ) SQL Plan Baseline Test 환경조성 Tunning 대상 SQL 의 Plan Baseline Capture Tunning 대상 SQL 의 Plan Baseline 확인 Tunning 된 SQL Plan 으로변경 생성된 Baseline 확인 등록한 Plan History 확인 Tunning 된 Plan 활용확인

3 1. SQL Tunning 은해야한다, 그러나 SQL 은수정할수없다! SQL Tunning 을필요로하지만 SQL Text 를수정할수없는환경들이많이생기고있다. Package 로납품된솔루션들이나, Java 의 Hibernate Framework 같은것들이그예이다. 위와같은환경의 SQL Tunning 의뢰를받았을때우리가할수있는방법들을알아보자. 1) Parameter 수정 Parameter 변경은전역적이어서위험성이높다. 그리고실제로사용할수있는파라 미터의수는매우제한적이다. 2) Physical Design 수정 Partition 나누기, Block Size 바꾸기, 기타 Phsyical Atribute 를수정하는방법이있다. 3) 통계정보조작 Wolfgang Breitling 에의해체계화된 TCF(Tuning By Cardinality Feedback) 기법이여 기에속한다. CBO 가올바른판단을할수있도록통계정보를보완해주는기법이다. 조작가능한통계정보에는제한이없으며 Table/Column/Index/Histogram 등모든통계정보를수동으로조작할수있다. 하지만, 수동으로변경된통계정보는자동백업 (10g) 이되지않는다는사실과통계정보수집시기존의조작된통계정보를덮어써버린다는것을유의하여사용하여야한다. Index 를생성하거나 Index Key 를변경하는것도넓은범위에서는이범주에속한다. 4) Stored Outline Stored Outline 은원래 Plan Stability 를보장하기위해제안된개념이지만, 실세계에서이목적으로사용되는경우는거의없다. 오히려 Outline 바꿔치기를통해 Query Tuning 의도구로활용되기도한다. 5) SQL Profile 10g 에서소개된 SQL Profile 는 SQL Tuning Advisor 가제공하는기능중하나이다. SQL Tuning Advisor 는 SQL 분석후 Hint 조작을통해 Query 성능개선이가능한경우에는해당하는 Profile 을제공한다. 이 Profile 를사용하면 Query 의실행계획을조작할수있다. - 3

4 DBMS_SQLTUNE 패키지의 UNDOCUMENTED PROCEDURE 들을이용하면수동으로 Profile 을생성하고조작할수도있다. 이관점에서보면 Stored Outline 을사용하는것과거의동일한기법이라고할수있다. 6) Advanced Rewrite 10g 에서소개된 Advanced Rewrite 는특정 SQL Text 를가로채서다른 SQL Text 로 변환하는기능을의미한다. DBMS_ADVANCED_REWRITE 패키지를이용한다. 언뜻보면가장강력하고확실한기법으로보인다. 하지만, Bind 변수가있는 Query 등이기본적으로지원되지않는다는점은치명적이다. 또한 Parse 과정에서의부하를생각해보면 OLTP 에는맞지않다는결론을얻을수있다. DW 성의쿼리에서사용될목적으로고안된것이다. 하지만 Parse 과정에서의오버헤드를감수해서라도 Query 성능을높여야할명분이있다면고려해볼만한방법이다 이번기술노트에서는 Stored Outline, 10g SQL Profile, 11g 의 SQL Baseline 을소개 하겠다. 2. Stored Outline Stored Outline 으로 Plan 을고정시킬수있다. 기존의문제되는쿼리를가지고, Outline 을생성하여해당 SQL Text 에대한 Plan 을 Tunning 된 SQL 에대한 Plan 으로해결되도록유도하여수정불가한 SQL 에대한 Tunning 을시도할수있다 Outline 사용할 User 에게 Outline 실행할수있는권한부여 SQL> conn /as sysdba SQL> grant create any outline to nero; SQL> grant execute on dbms_outln to nero; SQL> grant execute on dbms_outln_edit to nero; 2.2. Outline Test 환경조성 SQL> create table nero_detail as select scott.dept; SQL> create table nero_master as select scott.emp; Index 생성 - 4

5 SQL> alter table nero_detail add constraint nero_detail_pk primary key(empno); SQL> alter table nero_master add constraint nero_master_pk primary key(deptno); 2.3. Outline 생성할 Table 의통계정보생성 SQL> exec dbms_stats.gather_table_stats(user,'nero_detail', cascade=>true, no_invalidate=>false); SQL> exec dbms_stats.gather_table_stats(user,'nero_master', cascade=>true, no_invalidate=>false); 2.4. 수정할수없는 SQL 의 Plan 확인 SQL> set autotrace traceonly explain SQL> select e.ename from nero_detail e, nero_master d where e.deptno = d.deptno; Id Operation Name Rows Bytes Cost (%CPU) Time SELECT STATEMENT (0) 00:00:01 1 NESTED LOOPS (0) 00:00:01 * 2 TABLE ACCESS FULL nero_detail (0) 00:00:01 * 3 INDEX UNIQUE SCAN nero_master_pk (0) 00:00: Nested Loop Join 으로풀리는것을확인한다 수정할수없는 SQL 로 Outline 생성 SQL> set autotrace off SQL> create or replace outline ORG_OTLN on select e.ename from nero_detail e, nero_master d where e.deptno = d.deptno; 2.6. Tunning 된 SQL Plan 확인 SQL> set autotrace traceonly explain SQL> select /*+ use_hash(d) */ e.ename from nero_detail e, nero_master d where e.deptno = d.deptno; Id Operation Name Rows Bytes Cost (%CPU) Time

6 0 SELECT STATEMENT (20) 00:00:01 * 1 HASH JOIN (20) 00:00:01 2 INDEX FULL SCAN nero_master_pk (0) 00:00:01 * 3 TABLE ACCESS FULL nero_detail (0) 00:00: Tunning 된 SQL 로 Outline 생성 SQL> create or replace outline NEW_OTLN on select /*+ use_hash(d) */ e.ename from nero_detail e, nero_master d where e.deptno = d.deptno; 2.8. 생성된 Outline 들확인 SQL> select name, sql_text from user_outlines; NAME SQL_TEXT ORG_OTLN select e.ename from nero_detail e, nero_master d where e.deptno = d.deptno NEW_OTLN select /*+ use_hash(d) */ e.ename from nero_detail e, nero_master d where e.dept 2.9. Outline 수정 - Stored Outline 을수정하기위하여 outln user 로변경 SQL> conn outln/outln - ORG_OTLN, NEW_OTLN 에대한실제 Outline 확인한다. SQL> select ol_name, hint#, hint_text from ol$hints; OL_NAME HINT# HINT_TEXT ORG_OTLN ORG_OTLN ORG_OTLN ORG_OTLN ORG_OTLN ORG_OTLN 1 USE_NL(@"SEL$1" "D"@"SEL$1") 2 LEADING(@"SEL$1" "E"@"SEL$1" "D"@"SEL$1") 3 INDEX(@"SEL$1" "D"@"SEL$1" ("nero_master"."deptno")) 4 FULL(@"SEL$1" "E"@"SEL$1") 5 OUTLINE_LEAF(@"SEL$1") 6 ALL_ROWS - 6

7 ORG_OTLN ORG_OTLN NEW_OTLN NEW_OTLN NEW_OTLN NEW_OTLN NEW_OTLN NEW_OTLN NEW_OTLN NEW_OTLN 7 OPTIMIZER_FEATURES_ENABLE(' ') 8 IGNORE_OPTIM_EMBEDDED_HINTS 2 LEADING(@"SEL$1" "D"@"SEL$1" "E"@"SEL$1") 3 FULL(@"SEL$1" "E"@"SEL$1") 4 INDEX(@"SEL$1" "D"@"SEL$1" ("nero_master"."deptno")) 5 OUTLINE_LEAF(@"SEL$1") 6 ALL_ROWS 7 OPTIMIZER_FEATURES_ENABLE(' ') 8 IGNORE_OPTIM_EMBEDDED_HINTS 1 USE_HASH(@"SEL$1" "E"@"SEL$1") SQL> select ol_name, sql_text, category, hintcount from outln.ol$; OL_NAME SQL_TEXT CATEGORY HINTCOUNT NEW_OTLN select /*+ use_hash(d) */ e.ename from o DEFAULT 8 ORG_OTLN select e.ename from nero_detail e, nero_master d DEFAULT 8 - 힌트카운트를맞추기위해 ORG_OTLN 의 Hintcount 를 NEW_OTLN 의 Hintcount 로변경 SQL> update outln.ol$ set hintcount = (select hintcount from outln.ol$ where ol_name='new_otln') where ol_name='org_otln'; 1 row updated - Nested Loop 실행계획요약본은이제더이상필요없으므로 ORG_OTLN 의힌 트들은삭제 SQL> delete from ol$hints where ol_name = 'ORG_OTLN'; 8 rows deleted. - ORG_OTLN 의힌트를삭제후 NEW_OTLN 번의힌트들이 ORG_OTLN 의힌트가 되도록 OL_NAME 을바꿈. SQL> update ol$hints set ol_name = 'ORG_OTLN' where ol_name='new_otln'; - 7

8 8 rows updated. - ol$nodes 뷰의 ORG_OTLN 의내용삭제후업데이트 SQL> select * from ol$nodes where ol_name in('org_otln','new_otln'); OL_NAME CATEGORY NODE_ID PARENT_ID NODE_TYPE NODE_TEXTLEN NODE_TEXTOFF NODE_NAME ORG_OTLN DEFAULT SEL$1 NEW_OTLN DEFAULT SEL$1 SQL> delete from ol$nodes where ol_name ='ORG_OTLN'; 1 row deleted. - ORG_OTLN 의힌트를삭제후 NEW_OTLN 번의힌트들을 ORG_OTLN 의힌트가되도록 OL_NAME 을바꿔버림 SQL> update ol$nodes set ol_name = 'ORG_OTLN' where ol_name ='NEW_OTLN'; 1 row updated SQL> commit; Tunning 된 Plan 실행여부확인. SQL> conn nero/nero SQL> alter session set use_stored_outlines=true; SQL> set autotrace traceonly explain SQL> select e.ename from nero_detail e, nero_master d where e.deptno = d.deptno; -- 같은 SQL 문장이지만실행계획은 Hash Join 으로수행됨을확인가능함 Id Operation Name Rows Bytes Cost (%CPU) Time

9 0 SELECT STATEMENT (20) 00:00:01 * 1 HASH JOIN (20) 00:00:01 2 INDEX FULL SCAN nero_master_pk (0) 00:00:01 * 3 TABLE ACCESS FULL nero_detail (0) 00:00: Predicate Information (identified by operation id): access("e"."deptno"="d"."deptno") 3 - filter("e"."deptno" IS NOT NULL) Note outline "ORG_OTLN" used for this statement Outline Tunning 시유의사항 1) 바꿀 PLAN 이 USE_CONCAT 힌트를사용하는 OR-Expansion 의경우에는 concatenation 되는 2 번째쿼리블럭은 OL$HINTS 에서 HINT 제어를할수없다. 2) 이하버전에서는 Outline 생성후 LCO 를 invalidation 시켜야하는데, Cursor 별 Purge 기능 (patch aix,linux 는 에도 backporting 됨 ) 을사용하지못한다. 3) cursor_sharing = force(similar) 를사용하는경우사용할수없다. 4) Outline 이 SQL Text 를비교하는방식이라서 SQL Text 가완벽하게동일해야한다. 3. SQL Profile ( 10g ) Stored Outline 은 Full, Index 와같은 Direct Hint 로구성되지만, SQL Profile 은 OPT_ESTIMATE 와같이 Cardinality 를제어하는 Hint 로구성되어있다 SQL Profile 사용할 User 에게필요권한부여 SQL> grant ALTER ANY SQL PROFILE to sqlprf; 3.2. SQL Profile Test 환경조성 SQL> create table lms_mst as select * from scott.dept; SQL> create table lms_dtl as select * from scott.emp; - 9

10 Index 생성 SQL> alter table lms_dtl add constraint nero_detail_pk primary key(empno); SQL> alter table lms_mst add constraint nero_master_pk primary key(deptno); 3.3. 수정할수없는 SQL Plan 확인 SQL> explain plan for select /*+ use_nl(e d) */ e.ename from lms_dtl e, lms_mst d where e.deptno = d.deptno; SQL> select * from table(dbms_xplan.display); Id Operation Name Rows Bytes Cost (%CPU) Time SELECT STATEMENT (0) 00:00:01 1 NESTED LOOPS (0) 00:00:01 2 TABLE ACCESS FULL LMS_MST (0) 00:00:01 * 3 TABLE ACCESS FULL LMS_DTL (0) 00:00: Tunning 된 SQL 의 Full Hint Naming 확인 Advanced Option 을이용하여 Full Hint Naming 을확인한다. SQL Profile 을수동조작 하려면 Full Hint Naming Convention 을사용해야한다. SQ> explain plan for select /*+ use_hash(d) */ e.ename from lms_dtl e, lms_mst d where e.deptno = d.deptno; SQL> select * from table(dbms_xplan.display(null, null, advanced)); Id Operation Name Rows Bytes Cost (%CPU) Time SELECT STATEMENT (15) 00:00:01 * 1 HASH JOIN (15) 00:00:01 2 TABLE ACCESS FULL LMS_MST (0) 00:00:01 3 TABLE ACCESS FULL LMS_DTL (0) 00:00:

11 PLAN_TABLE_OUTPUT Query Block Name / Object Alias (identified by operation id): SEL$1 2 - SEL$1 / D@SEL$1 3 - SEL$1 / E@SEL$1 Outline Data /*+ PLAN_TABLE_OUTPUT BEGIN_OUTLINE_DATA USE_HASH(@"SEL$1" "E"@"SEL$1") LEADING(@"SEL$1" "D"@"SEL$1" "E"@"SEL$1") FULL(@"SEL$1" "E"@"SEL$1") FULL(@"SEL$1" "D"@"SEL$1") OUTLINE_LEAF(@"SEL$1") ALL_ROWS OPTIMIZER_FEATURES_ENABLE(' ') IGNORE_OPTIM_EMBEDDED_HINTS END_OUTLINE_DATA */ PLAN_TABLE_OUTPUT Predicate Information (identified by operation id): access("e"."deptno"="d"."deptno") Column Projection Information (identified by operation id): - 11

12 (#keys=1) "E"."ENAME"[VARCHAR2,10] 2 - "D"."DEPTNO"[NUMBER,22] PLAN_TABLE_OUTPUT "E"."ENAME"[VARCHAR2,10], "E"."DEPTNO"[NUMBER,22] 3.5. SQL Profile 수동생성 SQL Profile 을구성할 Hint 의목록을제공해야하며, 반드시 Full Hint Naming Convention 을사용해야한다. Tunning 된 Full Hint Name 을, 수정할수없는 SQL 과함께 import 한다. SQL> begin dbms_sqltune.import_sql_profile( name=>'nero_prof', sql_text=> 'select /*+ use_nl(e d) */ e.ename from lms_dtl e, lms_mst d where e.deptno = d.deptno', profile=>sqlprof_attr('use_hash(@"sel$1" "E"@"SEL$1")', 'LEADING(@"SEL$1" "D"@"SEL$1" "E"@"SEL$1")', 'FULL(@"SEL$1" "E"@"SEL$1")', 'FULL(@"SEL$1" "D"@"SEL$1")') ); end; / PL/SQL procedure successfully completed Tunning 된 Plan 실행여부확인. SQL> explain plan for select /*+ use_nl(e d) */ e.ename from lms_dtl e, lms_mst d where e.deptno = d.deptno; SQL> select * from table(dbms_xplan.display);

13 Id Operation Name Rows Bytes Cost (%CPU) Time SELECT STATEMENT (15) 00:00:01 * 1 HASH JOIN (15) 00:00:01 2 TABLE ACCESS FULL LMS_MST (0) 00:00:01 3 TABLE ACCESS FULL LMS_DTL (0) 00:00: PLAN_TABLE_OUTPUT Predicate Information (identified by operation id): access("e"."deptno"="d"."deptno") Note SQL profile "nero_prof" used for this statement 4. SQL Plan Baseline ( 11g ) - SQL Magement Base SQL Plan Management 를관리하는 Library - Statement Log 반복되는 SQL 문장을 plan history 에기록, 필요시마다활용 - Automatic SQL Tuning Task Load 량이많은 SQL 문을동등하거나더나은계획만을사용 - SMB (Segment Management Base) SQL Profile, Plan History, Plan Baselines, Plan History, Statement Log 4.1. SQL Plan Baseline Test 환경조성 SQL> create table bls_mst as select * from scott.dept; SQL> create table bls_dtl as select * from scott.emp; - 13

14 Index 생성 SQL> alter table bls_dtl add constraint nero_detail_pk primary key(empno); SQL> alter table bls_mst add constraint nero_master_pk primary key(deptno); 통계정보생성 SQL> exec dbms_stats.gather_table_stats(user, 'bls_mst', no_invalidate=>false); SQL> exec dbms_stats.gather_table_stats(user, 'bls_dtl', no_invalidate=>false); 4.2. Tunning 대상 SQL 의 Plan Baseline Capture SQL> alter session set optimizer_capture_sql_plan_baselines = true; SQL> select /*+ use_nl(e d) */ e.ename from bls_dtl e, bls_mst d where e.deptno = d.deptno; ENAME SMITH ALLEN WARD JONES MARTIN BLAKE CLARK SCOTT KING TURNER ADAMS JAMES FORD MILLER 14 rows selected. SQL> select /*+ use_nl(e d) */ e.ename from bls_dtl e, bls_mst d where e.deptno = d.deptno; ENAME - 14

15 SMITH ALLEN WARD JONES MARTIN BLAKE CLARK SCOTT KING TURNER ADAMS JAMES FORD MILLER 14 rows selected. SQL> alter session set optimizer_capture_sql_plan_baselines = false; 4.3. Tunning 대상 SQL 의 Plan Baseline 확인 SQL> col sql_handle new_value v_sql_handle SQL> select sql_handle from dba_sql_plan_baselines 2 where sql_text like 'select /*+ use_nl(e d) */ e.ename%'; SQL_HANDLE SYS_SQL_ae6d7e4ca2ffd67c SQL> select * from dba_sql_plan_baselines where sql_handle ='SYS_SQL_ae6d7e4ca2ffd67c'; E+19 SYS_SQL_ae6d7e4ca2ffd67c select /*+ use_nl(e d) */ e.ename from bls_dtl e, bls_mst d where e.deptno = d.d SYS_SQL_PLAN_a2ffd67c3c380fcf SQLBSLN AUTO-CAPTURE SQLBSLN /03/03 20:47: /03/03-15

16 20:47: /03/03 20:47: YES YES NO YES 3 SQL*Plus Column Value 만포함하였음 Tunning 된 SQL Plan 으로변경 SQL> select /*+ use_hash(d) */ e.ename from bls_dtl e, bls_mst d where e.deptno = d.deptno; ENAME SMITH ALLEN WARD JONES MARTIN BLAKE CLARK SCOTT KING TURNER ADAMS JAMES FORD MILLER 14 rows selected. SQL> select sql_id, plan_hash_value from v$sql where sql_text like 'select /*+ use_hash(d) */ e.ename%'; SQL_ID PLAN_HASH_VALUE qwm4r9r8dyyn SQL> var nero_var number; - 16

17 SQL> exec :nero_var := dbms_spm.load_plans_from_cursor_cache(- sql_id=>'0qwm4r9r8dyyn', - plan_hash_value=> , - sql_handle=>'sys_sql_ae6d7e4ca2ffd67c'); PL/SQL procedure successfully completed 생성된 Baseline 확인. Tunning 되지못한 SQL Text 로두개의 Baseline 이생성된것을확인한다. SQL> select sql_handle, sql_text from dba_sql_plan_baselines where sql_text like 'select /*+ use_nl(e d) */ e.ename%'; SQL_HANDLE SQL_TEXT SYS_SQL_ae6d7e4ca2ffd67c select /*+ use_nl(e d) */ e.ename from bls_dtl e, bls_mst d where e.deptno = d.d SYS_SQL_ae6d7e4ca2ffd67c select /*+ use_nl(e d) */ e.ename from bls_dtl e, bls_mst d where e.deptno = d.d 4.6. 등록한 Plan History 확인. SQL> select * from table(dbms_xplan.display_sql_plan_baseline(' SYS_SQL_ae6d7e4ca2ffd67c )); PLAN_TABLE_OUTPUT SQL handle: SYS_SQL_ae6d7e4ca2ffd67c SQL text: select /*+ use_nl(e d) */ e.ename from bls_dtl e, bls_mst d where e.deptno = d.deptno

18 Plan name: SYS_SQL_PLAN_a2ffd67c3c380fcf Enabled: YES Fixed: NO Accepted: YES Origin: AUTO-CAPTURE Plan hash value: Id Operation Name Rows Bytes Cost (%CPU) Time SELECT STATEMENT (0) 00:00:01 1 NESTED LOOPS (0) 00:00:01 2 TABLE ACCESS FULL BLS_DTL (0) 00:00:01 * 3 INDEX UNIQUE SCAN NERO_MASTER_PK (0) 00:00: Predicate Information (identified by operation id): access("e"."deptno"="d"."deptno") Plan name: SYS_SQL_PLAN_a2ffd67c694097be Enabled: YES Fixed: NO Accepted: YES Origin: MANUAL-LOAD Plan hash value: Id Operation Name Rows Bytes Cost (%CPU) Time SELECT STATEMENT (20) 00:00:01 * 1 HASH JOIN (20) 00:00:01 2 INDEX FULL SCAN NERO_MASTER_PK (0) 00:00:01 3 TABLE ACCESS FULL BLS_DTL (0) 00:00: Predicate Information (identified by operation id): - 18

19 access("e"."deptno"="d"."deptno") 4.7. Tunning 된 Plan 활용확인 SQL> alter session set optimizer_use_sql_plan_baselines = true; SQL> explain plan for 2 select /*+ use_nl(e d) */ e.ename from bls_dtl e, bls_mst d where e.deptno = d.deptno; SQL> select * from table(dbms_xplan.display); Plan hash value: Id Operation Name Rows Bytes Cost (%CPU) Time SELECT STATEMENT (0) 00:00:01 1 NESTED LOOPS (0) 00:00:01 2 TABLE ACCESS FULL BLS_DTL (0) 00:00:01 * 3 INDEX UNIQUE SCAN NERO_MASTER_PK (0) 00:00: Predicate Information (identified by operation id): access("e"."deptno"="d"."deptno") Note SQL plan baseline "SYS_SQL_PLAN_a2ffd67c3c380fcf" used for this statement 조정된 Query 가 Cost 가높으므로, 조정전의 Query 의 Plan 이실행된다. - 19

Jerry Held

Jerry Held ,, - - - : DELETE : ROW (ROWID) row ROWID : I/O Full Table Scan I/O Index Scan ROWID I/O Fast Full Index Scan scan scan scan I/O scan scan Unique, nonunique. (Concatenated Index) B* Tree Bitmap Reverse