카테고리 없음

Outline - SQL 변경 불가 시 Tunning 방법

kto2005 2011. 6. 29. 23:57

 

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 의 실행


계획을 조 작할 수 있다.
 

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 을 시도 할 수 있다.

 

 

2.1.   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 생성

 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         |

---------------------------------------------------------------------------------

|     0 | SELECT  STATEMENT      |                    |       14 |      168 |         3     (0)| 00:00:01 |

|     1 |  NESTED LOOPS             |                    |       14 |     168 |         3     (0)| 00:00:01 |

|*   2 |     TABLE ACCESS  FULL|  nero_detail     |       14 |      126 |         3     (0)| 00:00:01 |

|*   3 |     INDEX UNIQUE SCAN|  nero_master_PK |         1 |         3 |         0     (0)|  00:00:01  |

---------------------------------------------------------------------------------

 

 

Nested Loop Join 으로 풀리는 것을 확인 한다.

 

 

2.5.   수정할 수 없는 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         |

---------------------------------------------------------------------------------
|     0 | SELECT  STATEMENT      |                    |       14 |      168 |         5    (20)| 00:00:01 |

|*   1 |  HASH  JOIN              |                    |       14 |     168 |         5    (20)| 00:00:01 |

|     2 |     INDEX FULL  SCAN     | nero_master_PK |         4 |       12 |         1     (0)|  00:00:01  |

|*   3 |     TABLE ACCESS  FULL|  nero_detail     |       14 |      126 |         3     (0)| 00:00:01 |

---------------------------------------------------------------------------------

 

 

2.7.   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         1 USE_NL(@"SEL$1"  "D"@"SEL$1")

ORG_OTLN             2 LEADING(@"SEL$1" "E"@"SEL$1" "D"@"SEL$1")

ORG_OTLN             3 INDEX(@"SEL$1" "D"@"SEL$1" ("nero_master"."DEPTNO")) ORG_OTLN                         4 FULL(@"SEL$1" "E"@"SEL$1")

ORG_OTLN             5 OUTLINE_LEAF(@"SEL$1") ORG_OTLN      6 ALL_ROWS


 

ORG_OTLN             7 OPTIMIZER_FEATURES_ENABLE('10.2.0.1') ORG_OTLN         8 IGNORE_OPTIM_EMBEDDED_HINTS

NEW_OTLN             2 LEADING(@"SEL$1" "D"@"SEL$1" "E"@"SEL$1") NEW_OTLN 3 FULL(@"SEL$1" "E"@"SEL$1")

NEW_OTLN             4 INDEX(@"SEL$1" "D"@"SEL$1" ("nero_master"."DEPTNO")) NEW_OTLN                         5 OUTLINE_LEAF(@"SEL$1")

NEW_OTLN             6 ALL_ROWS

NEW_OTLN             7 OPTIMIZER_FEATURES_ENABLE('10.2.0.1') NEW_OTLN         8 IGNORE_OPTIM_EMBEDDED_HINTS

NEW_OTLN             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';


 

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

1

0

3

65

1 SEL$1

NEW_OTLN   DEFAULT

1

0

3

84

1 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;

 

 

 

 

 

 

2.10. 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         |

---------------------------------------------------------------------------------


 

|     0 | SELECT  STATEMENT      |                    |       14 |      168 |         5    (20)| 00:00:01 |

|*   1 |    HASH  JOIN               |                    |       14 |      168 |         5    (20)| 00:00:01 |

|     2 |     INDEX FULL  SCAN     | nero_master_PK |         4 |       12 |         1     (0)|  00:00:01  |

|*   3 |     TABLE ACCESS  FULL|  nero_detail     |       14 |      126 |         3     (0)| 00:00:01 |

---------------------------------------------------------------------------------

 

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

 

1 - access("E"."DEPTNO"="D"."DEPTNO")

3 - filter("E"."DEPTNO"  IS  NOT  NULL)

 

 

Note

-----

- outline "ORG_OTLN"  used for  this statement

 

 

2.11. Outline Tunning시 유의 사항

 

1) 바꿀 PLAN 이 USE_CONCAT 힌트를 사용하는 OR-Expansion 의 경우에는

concatenation 되는 2 번째 쿼리블럭은 OL$HINTS 에서 HINT 제어를 할수 없다.

2)   10.2.0.3 이하 버전에서는 Outline 생성 후 LCO 를 invalidation 시켜야 하는데, Cursor 별 Purge 기능(patch5614566 - aix,linux 는 10203 에도 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 로 구성되어 있다.

 

 

3.1.   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;


 

 

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          |

------------------------------------------------------------------------------

|     0 | SELECT  STATEMENT      |               |       14 |      168 |         8     (0)| 00:00:01 |

|     1 |    NESTED LOOPS               |               |       14 |      168 |         8     (0)| 00:00:01 |

|     2 |     TABLE ACCESS  FULL|  LMS_MST |       4 |       12 |         3     (0)| 00:00:01 |

|*   3 |     TABLE ACCESS  FULL|  LMS_DTL |       4 |       36 |         1     (0)| 00:00:01 |

------------------------------------------------------------------------------

 

 

3.4.   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          |

------------------------------------------------------------------------------

|     0 | SELECT  STATEMENT      |               |       14 |      168 |         7    (15)| 00:00:01 |

|*   1 |    HASH  JOIN               |               |       14 |      168 |         7    (15)| 00:00:01 |

|     2 |     TABLE ACCESS  FULL|  LMS_MST |       4 |       12 |         3     (0)| 00:00:01 |

|     3 |     TABLE ACCESS  FULL|  LMS_DTL |     14 |      126 |         3     (0)| 00:00:01 |

------------------------------------------------------------------------------


 

 

PLAN_TABLE_OUTPUT

------------------------------------------------------------- Query  Block  Name / Object  Alias (identified by operation  id):

-------------------------------------------------------------

 

 

1 - 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('10.2.0.4') IGNORE_OPTIM_EMBEDDED_HINTS END_OUTLINE_DATA

*/

 

 

PLAN_TABLE_OUTPUT

-------------------------------------------------------------

 

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

 

1 - access("E"."DEPTNO"="D"."DEPTNO")


 

-----------------------------------------------------------

 

 

1 - (#keys=1) "E"."ENAME"[VARCHAR2,10]

2 - "D"."DEPTNO"[NUMBER,22]

 

 

PLAN_TABLE_OUTPUT

-------------------------------------------------------------

3 - "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.

 

 

3.6.   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);

 

-----------------------------------------------------------------------------


 

| Id    | Operation                 | Name       | Rows    | Bytes | Cost  (%CPU)| Time          |

------------------------------------------------------------------------------

|     0 | SELECT  STATEMENT      |               |       14 |      168 |         7    (15)| 00:00:01 |

|*   1 |    HASH  JOIN               |               |       14 |      168 |         7    (15)| 00:00:01 |

|     2 |     TABLE ACCESS  FULL|  LMS_MST |       4 |       12 |         3     (0)| 00:00:01 |

|     3 |     TABLE ACCESS  FULL|  LMS_DTL |     14 |      126 |         3     (0)| 00:00:01 |

------------------------------------------------------------------------------

 

 

 

 

PLAN_TABLE_OUTPUT

-------------------------------------------------------------------------------- Predicate Information (identified by operation id):

---------------------------------------------------

1 - 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;


 

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


 

---------- 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';

 

 

1.2569E+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

 

 

11.1.0.6.0                                                                                              09/03/03

20:47:11.000000                                                                                         09/03/03


 

20:47:11.000000

09/03/03  20:47:36.000000

YES  YES  NO YES                        3  SQL*Plus

 

 

-+  Column Value 만 포함 하였음.

 

 

4.4.   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

------------- ---------------

0qwm4r9r8dyyn           1553352241

 

 

SQL> var nero_var number;


 

 

 

SQL> exec :nero_var := dbms_spm.load_plans_from_cursor_cache(-

sql_id=>'0qwm4r9r8dyyn', - plan_hash_value=>1553352241, - sql_handle=>'SYS_SQL_ae6d7e4ca2ffd67c');

 

 

PL/SQL procedure successfully  completed.

 

 

4.5.   생성된 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

--------------------------------------------------------------------------------


 

--------------------------------------------------------------------------------

Plan name: SYS_SQL_PLAN_a2ffd67c3c380fcf

Enabled:  YES         Fixed: NO            Accepted:  YES         Origin: AUTO-CAPTURE

-------------------------------------------------------------------------------- Plan hash value: 386123697

 

-------------------------------------------------------------------------------------

| Id    | Operation                 | Name                   | Rows    | Bytes  | Cost (%CPU)|  Time         |

-------------------------------------------------------------------------------------

|     0 | SELECT  STATEMENT      |                           |       14 |     168 |         3     (0)|  00:00:01  |

|     1 |    NESTED LOOPS               |                           |       14 |     168 |         3     (0)|  00:00:01  |

|     2 |     TABLE ACCESS  FULL|  BLS_DTL           |       14 |     126 |         3     (0)|  00:00:01  |

|*   3 |     INDEX UNIQUE SCAN|  NERO_MASTER_PK |          1 |         3 |         0     (0)|  00:00:01  |

-------------------------------------------------------------------------------------

 

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

 

3 - access("E"."DEPTNO"="D"."DEPTNO")

 

 

-------------------------------------------------------------------------------- Plan name: SYS_SQL_PLAN_a2ffd67c694097be

Enabled:  YES         Fixed: NO            Accepted:  YES         Origin: MANUAL-LOAD

-------------------------------------------------------------------------------- Plan hash value: 1553352241

 

-------------------------------------------------------------------------------------

| Id    | Operation                 | Name                   | Rows    | Bytes  | Cost (%CPU)|  Time         |

-------------------------------------------------------------------------------------

|     0 | SELECT  STATEMENT      |                           |       14 |     168 |         5    (20)|  00:00:01  |

|*   1 |    HASH  JOIN               |                           |       14 |     168 |         5    (20)|  00:00:01  |

|     2 |     INDEX FULL  SCAN     | NERO_MASTER_PK |          4 |       12 |         1     (0)|  00:00:01  |

|     3 |     TABLE ACCESS  FULL|  BLS_DTL           |       14 |     126 |         3     (0)|  00:00:01  |

-------------------------------------------------------------------------------------

 

 

Predicate Information (identified by operation id):


 

---------------------------------------------------

 

 

1 - 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: 386123697

 

 

-------------------------------------------------------------------------------------

| Id    | Operation                 | Name                   | Rows    | Bytes  | Cost (%CPU)|  Time         |

-------------------------------------------------------------------------------------

|     0 | SELECT  STATEMENT      |                           |       14 |     168 |         3     (0)|  00:00:01  |

|     1 |    NESTED LOOPS               |                           |       14 |     168 |         3     (0)|  00:00:01  |

|     2 |     TABLE ACCESS  FULL|  BLS_DTL           |       14 |     126 |         3     (0)|  00:00:01  |

|*   3 |     INDEX UNIQUE SCAN|  NERO_MASTER_PK |          1 |         3 |         0     (0)|  00:00:01  |

-------------------------------------------------------------------------------------

 

 

Predicate Information (identified by operation id):

---------------------------------------------------

3 - access("E"."DEPTNO"="D"."DEPTNO") Note

-----

-  SQL plan baseline "SYS_SQL_PLAN_a2ffd67c3c380fcf" used for this  statement

 

 

조정된 Query 가 Cost 가 높으므로, 조정 전의 Query 의 Plan 이 실행된다.

 

저작자표시 비영리 변경금지 (새창열림)