Optimizing Oracle Optimizer (2009년)
목록
SQL Profile Import 0 0 89,683

by 구루비스터디 SQL Profile [2018.07.14]

SQL Profile Import

SQL Profile의 컨셉

  • 오라클의 튜닝을 목적으로한 패키지이다.


SQL Profile과 Stored Outline의 비교

다른 탄생 목적
  • Stored Outline은 Plan stability가 목적
  • SQL Profile은 SQL Tuning이 목적


동일한 용도로 사용
  • Query의 실행계획을 제어하기 위한 Hint의 모임


내부적인 힌트사용의 차이점
  • Stored outline은 Full, index등 직접적인 Hint로 구성
  • SQL Profile은 OPT_ESTIMATE같은 Cardinality를 제어하하는 Hint로 구성


SQL Text를 변경하지 않고 실행계획을 바꿀 수 있는 방법들


Stored outline을 이용한 방법

woong:SYS > grant create any outline, alter any outline, drop any outline to woong;
woong:SYS > grant execute on outln_pkg to woong;
woong:SYS > grant delete, update, insert, select on outln.ol$hints to woong;
woong:SYS > grant delete, update, insert, select on outln.ol$nodes to woong;
woong:SYS > grant delete, update, insert, select on outln.ol$ to woong;

woong:WOONG >
create table t1(c1 int, c2 int);
create index t1_n1 on t1(c1);
create index t1_n2 on t1(c2);

insert into t1
select level, level
  from dual
connect by level <= 1000;

@gather t1

---------------------------------------------------------
concat로 풀릴 때 좋은 실행계획이 나올수 있는 쿼리가 
full table scan으로 풀린다는 가정을 한다.(full 힌트로 추가)

full table scan하는 안 좋은 실행계획을 concat로 풀리는 좋은 실행계획으로 
쿼리 수정없이 stored outline으로 바꿔치기하는 것이 목표
---------------------------------------------------------
woong:WOONG >
  t1  explain plan for
  2  select /*+ full(t1) */
  3    *
  4  from t1
  5  where c1 = 1 or c2 = 2
  6  ;

해석되었습니다.

경   과: 00:00:00.00
woong:WOONG >
  t1  @plan

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     2 |    14 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |     2 |    14 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("C1"=1 OR "C2"=2)

13 개의 행이 선택되었습니다.

경   과: 00:00:00.01

---------------------------------------------------------------
원하는 실행계획
---------------------------------------------------------------
woong:WOONG >
  t1  explain plan for
  2  select /*+ use_concat */
  3    *
  4  from t1
  5  where c1 = 1 or c2 = 2
  6  ;

해석되었습니다.

경   과: 00:00:00.01
woong:WOONG >
  t1  @plan

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------
Plan hash value: 82564388

--------------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |     2 |    14 |     4   (0)| 00:00:01 |
|   1 |  CONCATENATION               |       |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1    |     1 |     7 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | T1_N2 |     1 |       |     1   (0)| 00:00:01 |
|*  4 |   TABLE ACCESS BY INDEX ROWID| T1    |     1 |     7 |     2   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN          | T1_N1 |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

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

   3 - access("C2"=2)
   4 - filter(LNNVL("C2"=2))
   5 - access("C1"=1)

19 개의 행이 선택되었습니다.

경   과: 00:00:00.01

-------------------------------------------------------------
안 좋은 실행계획을 가지는 쿼리의 outline을 test_outln1로 생성한다.
-------------------------------------------------------------
woong:WOONG >
  t1  create or replace outline test_outln1
  2  on
  3  select /*+ full(t1) */
  4    *
  5  from t1
  6  where c1 = 1 or c2 = 2
  7  ;

아웃라인이 생성되었습니다.

경   과: 00:00:00.00

-------------------------------------------------------------
좋은 실행계획을 가지는 쿼리의 outline을 test_outln2로 생성한다.
-------------------------------------------------------------
woong:WOONG >
  t1  create or replace outline test_outln2
  2  on
  3  select /*+ use_concat */
  4    *
  5  from t1
  6  where c1 = 1 or c2 = 2
  7  ;

아웃라인이 생성되었습니다.

경   과: 00:00:00.00

woong:WOONG >
  t1  select hint
  2  from user_outline_hints
  3  where name = 'TEST_OUTLN2'
  4  ;

HINT
--------------------------------------------------------------------------------
INDEX_RS_ASC(@"SEL$1_2" "T1"@"SEL$1_2" ("T1"."C1"))
INDEX_RS_ASC(@"SEL$1_1" "T1"@"SEL$1" ("T1"."C2"))
OUTLINE(@"SEL$1")
OUTLINE_LEAF(@"SEL$1_2")
USE_CONCAT(@"SEL$1" 8)
OUTLINE_LEAF(@"SEL$1_1")
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
DB_VERSION('11.1.0.6')
OPTIMIZER_FEATURES_ENABLE('11.1.0.6')
IGNORE_OPTIM_EMBEDDED_HINTS

11 개의 행이 선택되었습니다.

경   과: 00:00:00.00

-----------------------------------------------------------
outln.ol은 안 좋은 실행계획을 갖는 원래 쿼리문장 및 정보를 가지고 있는 테이블이다.
안 좋은 실행계획에서 사용하는 힌트수를 좋은 실행계획에서 사용하는 힌트수로 update한다.
-----------------------------------------------------------
woong:WOONG >
  t1  update outln.ol$
  2  set hintcount = (
  3    select hintcount
  4    from outln.ol$
  5    where ol_name = 'TEST_OUTLN2')
  6  where
  7    ol_name = 'TEST_OUTLN1'
  8  ;

1 행이 갱신되었습니다.

-----------------------------------------------------------
안 좋은 실행계획을 세우는 outline 힌트를 삭제하고
좋은 실행계획을 세우는 outline 힌트를 OVER WRITE한다.
-----------------------------------------------------------
경   과: 00:00:00.00
woong:WOONG >
  t1
woong:WOONG >
  t1  delete from outln.ol$hints
  2  where ol_name = 'TEST_OUTLN1'
  3  ;

6 행이 삭제되었습니다.

경   과: 00:00:00.00
woong:WOONG >
  t1  update outln.ol$hints
  2  set ol_name = 'TEST_OUTLN1'
  3  where ol_name = 'TEST_OUTLN2'
  4  ;

11 행이 갱신되었습니다.

경   과: 00:00:00.00
woong:WOONG >
  t1  delete from outln.ol$nodes
  2  where ol_name = 'TEST_OUTLN1'
  3  ;

1 행이 삭제되었습니다.

경   과: 00:00:00.00
woong:WOONG >
  t1
woong:WOONG >
  t1  update outln.ol$nodes
  2  set ol_name = 'TEST_OUTLN1'
  3  where ol_name = 'TEST_OUTLN2'
  4  ;

3 행이 갱신되었습니다.

경   과: 00:00:00.00
woong:WOONG >
  t1  commit;

커밋이 완료되었습니다.

경   과: 00:00:00.00

-----------------------------------------------------------
outline을 사용하기 위해 세션 설정을 바꾼다.
이제부터 oulln.ol$에 등록된 쿼리는 아웃라인에 적용된다.
outline카테고리에 등록시켜서 사용하면 특정 문장만 적용시킬 수도 있다.
-----------------------------------------------------------
woong:WOONG >
  t1  alter session set use_stored_outlines = true;

세션이 변경되었습니다.

경   과: 00:00:00.01
woong:WOONG >
  t1  explain plan for
  2  select /*+ full(t1) */
  3    *
  4  from t1
  5  where c1 = 1 or c2 = 2
  6  ;

해석되었습니다.

경   과: 00:00:00.00
woong:WOONG >
  t1  @plan

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------
Plan hash value: 82564388

--------------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |     2 |    14 |     4   (0)| 00:00:01 |
|   1 |  CONCATENATION               |       |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1    |     1 |     7 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | T1_N2 |     1 |       |     1   (0)| 00:00:01 |
|*  4 |   TABLE ACCESS BY INDEX ROWID| T1    |     1 |     7 |     2   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN          | T1_N1 |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

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

   3 - access("C2"=2)
   4 - filter(LNNVL("C2"=2))
   5 - access("C1"=1)

Note
-----
   - outline "TEST_OUTLN1" used for this statement

17 개의 행이 선택되었습니다.

경   과: 00:00:00.01


SQL Profile을 이용한 방법

woong:SYS >
@F:\app\Administrator\product\11.1.0\db_1\RDBMS\ADMIN\dbmssqlt.sql
grant administer any sql tuning set to woong;
grant administer any sql tuning set to woong;
grant create any sql profile to woong;
grant drop any sql profile to woong;
grant alter any sql profile to woong;

woong:WOONG >
create table t1(c1 int, c2 int);
create index t1_n1 on t1(c1);
create index t1_n2 on t1(c2);

insert into t1
select level, level
  from dual
connect by level <= 1000;

@gather t1

---------------------------------------------------------
concat로 풀릴 때 좋은 실행계획이 나올수 있는 쿼리가 
full table scan으로 풀린다는 가정을 한다.(full 힌트로 추가)

full table scan하는 안 좋은 실행계획을 concat로 풀리는 좋은 실행계획으로 
쿼리 수정없이 stored outline으로 바꿔치기하는 것이 목표
---------------------------------------------------------
woong:WOONG >
  t1  explain plan for
  2  select /*+ full(t1) */
  3    *
  4  from t1
  5  where c1 = 1 or c2 = 2
  6  ;

해석되었습니다.

경   과: 00:00:00.00
woong:WOONG >
  t1  @plan

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     2 |    14 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |     2 |    14 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("C1"=1 OR "C2"=2)

13 개의 행이 선택되었습니다.

경   과: 00:00:00.01

---------------------------------------------------------------
원하는 실행계획
---------------------------------------------------------------
woong:WOONG >
  t1  explain plan for
  2  select /*+ use_concat */
  3    *
  4  from t1
  5  where c1 = 1 or c2 = 2
  6  ;

해석되었습니다.

경   과: 00:00:00.01
woong:WOONG >
  t1  @plan

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
Plan hash value: 82564388

--------------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |     2 |    14 |     4   (0)| 00:00:01 |
|   1 |  CONCATENATION               |       |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1    |     1 |     7 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | T1_N2 |     1 |       |     1   (0)| 00:00:01 |
|*  4 |   TABLE ACCESS BY INDEX ROWID| T1    |     1 |     7 |     2   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN          | T1_N1 |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

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

   3 - access("C2"=2)
   4 - filter(LNNVL("C2"=2))
   5 - access("C1"=1)

19 개의 행이 선택되었습니다.

경   과: 00:00:00.01

--------------------------------------------------------------
어떤 outline힌트를 사용하는 확인한다.
--------------------------------------------------------------
woong:WOONG >
  t1  select * from table(dbms_xplan.display(null, null, 'advanced'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
Plan hash value: 82564388

--------------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |     2 |    14 |     4   (0)| 00:00:01 |
|   1 |  CONCATENATION               |       |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1    |     1 |     7 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | T1_N2 |     1 |       |     1   (0)| 00:00:01 |
|*  4 |   TABLE ACCESS BY INDEX ROWID| T1    |     1 |     7 |     2   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN          | T1_N1 |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

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

   1 - SEL$1
   2 - SEL$1_1 / T1@SEL$1
   3 - SEL$1_1 / T1@SEL$1
   4 - SEL$1_2 / T1@SEL$1_2
   5 - SEL$1_2 / T1@SEL$1_2

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      INDEX_RS_ASC(@"SEL$1_2" "T1"@"SEL$1_2" ("T1"."C1"))
      INDEX_RS_ASC(@"SEL$1_1" "T1"@"SEL$1" ("T1"."C2"))
      OUTLINE(@"SEL$1")
      OUTLINE_LEAF(@"SEL$1_2")
      USE_CONCAT(@"SEL$1" 8)
      OUTLINE_LEAF(@"SEL$1_1")
      OUTLINE_LEAF(@"SEL$1")
      ALL_ROWS
      DB_VERSION('11.1.0.6')
      OPTIMIZER_FEATURES_ENABLE('11.1.0.6')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

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

   3 - access("C2"=2)
   4 - filter(LNNVL("C2"=2))
   5 - access("C1"=1)

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "T1".ROWID[ROWID,10], "C1"[NUMBER,22], "C2"[NUMBER,22]
   2 - "T1".ROWID[ROWID,10], "C1"[NUMBER,22], "C2"[NUMBER,22]
   3 - "T1".ROWID[ROWID,10], "C2"[NUMBER,22]
   4 - "T1".ROWID[ROWID,10], "C1"[NUMBER,22], "C2"[NUMBER,22]
   5 - "T1".ROWID[ROWID,10], "C1"[NUMBER,22]

56 개의 행이 선택되었습니다.

경   과: 00:00:00.06

---------------------------------------------------------------------------
dbms_sqltune.import_sql_profile라는 히든 프로시져를 이용하여 
등록된 쿼리와 함께 수행될 힌트를 함께 등록한다.
---------------------------------------------------------------------------
woong:WOONG >
  t1  begin
  2
  3    dbms_sqltune.import_sql_profile(
  4      name => 'test_prof',
  5      sql_text =>
  6  'select /*+ full(t1) */
  7    *
  8  from t1
  9  where c1 = 1 or c2 = 2',
 10      profile => sqlprof_attr('USE_CONCAT(@"SEL$1" 8)',
 11                              'INDEX(@"SEL$1_2" "T1"@"SEL$1_2" ("T1"."C1"))',
 12                              'INDEX(@"SEL$1_1" "T1"@"SEL$1" ("T1"."C2"))')
 13    );
 14  end;
 15  /

PL/SQL 처리가 정상적으로 완료되었습니다.

경   과: 00:00:00.00


----------------------------------------------------------------
안좋은 실행계획으로 수행했어도 
SQL profile "test_prof"을 사용하여 원하는 실행계획으로 풀린다.
----------------------------------------------------------------
woong:WOONG >
  t1  explain plan for
  2  select /*+ full(t1) */
  3    *
  4  from t1
  5  where c1 = 1 or c2 = 2
  6  ;

해석되었습니다.

경   과: 00:00:00.01
woong:WOONG >
  t1  @plan

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
Plan hash value: 82564388

--------------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |     2 |    14 |     4   (0)| 00:00:01 |
|   1 |  CONCATENATION               |       |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1    |     1 |     7 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | T1_N2 |     1 |       |     1   (0)| 00:00:01 |
|*  4 |   TABLE ACCESS BY INDEX ROWID| T1    |     1 |     7 |     2   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN          | T1_N1 |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

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

   3 - access("C2"=2)
   4 - filter(LNNVL("C2"=2))
   5 - access("C1"=1)

Note
-----
   - SQL profile "test_prof" used for this statement

23 개의 행이 선택되었습니다.

경   과: 00:00:00.01
"데이터베이스 스터디모임" 에서 2009년에 "OPTIMIZING ORACLE OPTIMIZER " 도서를 스터디하면서 정리한 내용 입니다.

- 강좌 URL : http://www.gurubee.net/lecture/3898

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

목록
댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입