태터데스크 관리자

도움말
닫기
적용하기   첫페이지 만들기

태터데스크 메시지

저장하였습니다.

'Stored Outline'에 해당되는 글 5건

  1. 2009.07.31 Fuction-Based Index의 한계 - Or Expansion
  2. 2009.02.03 SQL Text 수정이 불가능한 경우에 Tuning하기 - SQL Plan baseline 버전(11g)
  3. 2008.07.01 Optimizing Unoptimizable SQL (3)
  4. 2008.03.20 Stored Outline을 이용한 SQL Tuning 기법 (17)
  5. 2007.12.27 Oracle Upgrade에 의한 실행 계획 이상과 Stored Outline

Fuction-Based Index의 한계 - Or Expansion

오라클 2009.07.31 18:19
아래 OTN Forum에 안타까운 사연이 있습니다. :)

http://forums.oracle.com/forums/message.jspa?messageID=3661603

  1. Standard Edition을 쓰고 있구요.
  2. Standard edition은 1) bitmap index 와 그 형제라 할 수 있는 2) index combination 을 지원하지 않습니다.
  3. Fucntion-based Index를 사용하고 있고
  4. 불행하게도 "OR" predicate를 사용하고 있습니다.
  5. 무엇보다 큰 문제는 Query Text를 수정할 수 없다는 겁니다. Software에 내장된 Query라서...
제가 왜 이것을 안타까운 사연이라고 부르는지 아래 데모를 보시면 이해하실 겁니다.

1. Object를 만듭니다.

drop table t1 purge;

create table t1(c1 int, c2 int, c3 int);

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

create index t1_n1 on t1(c1+1);  -- function-based index
create index t1_n2 on t1(c2+1);  -- function-based index
create index t1_n3 on t1(c1);  -- normal index
create index t1_n4 on t1(c2);  -- normal index

exec dbms_stats.gather_table_stats(user, 't1');
2.Index Combination을 사용가능한 경우에는 다음과 같이 아주 효율적인 실행 계획을 만듭니다.
alter session set "_b_tree_bitmap_plans" = true;

explain plan for
select *
from t1
where c1+1 = 1 or c2+1 = 1
;

-------------------------------------------------------------------------------
| Id  | Operation                        | Name  | Rows  | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |       |     2 |    48 |     2   (0)|
|   1 |  TABLE ACCESS BY INDEX ROWID     | T1    |     2 |    48 |     2   (0)|
|   2 |   BITMAP CONVERSION TO ROWIDS    |       |       |       |            |
|   3 |    BITMAP OR                     |       |       |       |            |
|   4 |     BITMAP CONVERSION FROM ROWIDS|       |       |       |            |
|*  5 |      INDEX RANGE SCAN            | T1_N1 |       |       |     1   (0)|
|   6 |     BITMAP CONVERSION FROM ROWIDS|       |       |       |            |
|*  7 |      INDEX RANGE SCAN            | T1_N2 |       |       |     1   (0)|
-------------------------------------------------------------------------------
                                                                               
Predicate Information (identified by operation id):                            
---------------------------------------------------                            
                                                                               
   5 - access("C1"+1=1)                                                        
   7 - access("C2"+1=1)                                                        

3. 하지만 Index Combination이 비활성화되면?
alter session set "_b_tree_bitmap_plans" = false; -- In standard edition, this would be fixed behavior.

explain plan for
select *
from t1
where c1+1 = 1 or c2+1 = 1
;

---------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     2 |    48 |    99   (6)|
|*  1 |  TABLE ACCESS FULL| T1   |     2 |    48 |    99   (6)|
---------------------------------------------------------------
                                                               
Predicate Information (identified by operation id):            
---------------------------------------------------            
                                                               
   1 - filter("C1"+1=1 OR "C2"+1=1)                            

놀랍게도 Full Table Scan을 선택해버립니다.

4. Function-based Index가 아닌 일반 Index인 경우에는 차선책으로 Or-Expansion을 사용합니다. 이 정도만 해도 충분히 효율적입니다.

explain plan for
select *
from t1
where c1 = 1 or c2 = 1
;

---------------------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |     2 |    30 |     4   (0)|
|   1 |  CONCATENATION               |       |       |       |            |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1    |     1 |    15 |     2   (0)|
|*  3 |    INDEX RANGE SCAN          | T1_N4 |     1 |       |     1   (0)|
|*  4 |   TABLE ACCESS BY INDEX ROWID| T1    |     1 |    15 |     2   (0)|
|*  5 |    INDEX RANGE SCAN          | T1_N3 |     1 |       |     1   (0)|
---------------------------------------------------------------------------
                                                                           
Predicate Information (identified by operation id):                        
---------------------------------------------------                        
                                                                           
   3 - access("C2"=1)                                                      
   4 - filter(LNNVL("C2"=1))                                               
   5 - access("C1"=1)                                                      
5. 그렇다면 다음과 같이 힌트를 통해서 완벽하게 제어하면 어떨까요?(이 방법은 이 포스트에 설명되어 있습니다)
explain plan for
select 
		/*+
				INDEX_RS_ASC(@"SEL$1_2" "T1"@"SEL$1_2" "T1_N1")
				INDEX_RS_ASC(@"SEL$1_1" "T1"@"SEL$1" "T1_N2")
				USE_CONCAT(@"SEL$1" 8) */
		*
from t1
where c1+1 = 1 or c2+1 = 1
;

----------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     2 |    48 |   195   (4)|
|   1 |  CONCATENATION     |      |       |       |            |
|*  2 |   TABLE ACCESS FULL| T1   |     1 |    24 |    98   (5)|
|*  3 |   TABLE ACCESS FULL| T1   |     1 |    24 |    98   (5)|
----------------------------------------------------------------

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

   2 - filter("C2"+1=1)
   3 - filter("C1"+1=1 AND LNNVL("C2"+1=1))

역시 안됩니다.

왜 이런 현상이 발생할까요? 아래 문서에 잘 설명되어 있습니다.
http://download.oracle.com/docs/cd/B14117_01/appdev.101/b10795/adfns_in.htm#1006464

불행하게도 Oracle은 Or-Expansion과 Funtion-based Index를 같이 사용하지 못합니다. 따라서 이 경우에는 Full Table Scan이 가장 효율적인 실행 계획이 되어 버린 셈입니다.

6. 한가지 더 시도할 수 있는 방법이 있습니다. Stored Outline을 수동으로 생성하는 것이지요. 이 방법은 이 포스트에 설명되어 있습니다. 하지만 이 경우에는 그나마도 불가능합니다. Hint로 제어할 수 있는 영역을 벗어났기 때문입니다.

7. 마지막 트릭이 있습니다. Oracle 10g에서 제공되는 Advacned Query Rewriting 기능을 이용해 SQL Text 자체를 바꿔버리는 겁니다. 단, 이 기능은 아래와 같은 제한이 있습니다.

  • 역시 Enterprise Edition에서만 사용 가능하고
  • Select 문장만 가능하며
  • Bind 변수가 지원되지 않습니다.
이 포스트의 간단한 데모의 경우에는 다행스럽게도 이 기능으로 해결이 가능합니다.
begin
  sys.dbms_advanced_rewrite.declare_rewrite_equivalence (
     name           => 'rewrite1',
     source_stmt =>
'select *
from t1
where c1+1 = 1 or c2+1 = 1',
    destination_stmt =>
'select *
from t1
where c1 = 0 or c2 = 0',
     validate       => false,
     rewrite_mode   => 'text_match');
end;
/

alter session set query_rewrite_integrity = trusted;

explain plan for
select *
from t1
where c1+1 = 1 or c2+1 = 1
;
        
---------------------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |     2 |    30 |     4   (0)|
|   1 |  CONCATENATION               |       |       |       |            |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1    |     1 |    15 |     2   (0)|
|*  3 |    INDEX RANGE SCAN          | T1_N4 |     1 |       |     1   (0)|
|*  4 |   TABLE ACCESS BY INDEX ROWID| T1    |     1 |    15 |     2   (0)|
|*  5 |    INDEX RANGE SCAN          | T1_N3 |     1 |       |     1   (0)|
---------------------------------------------------------------------------
                                                                           
Predicate Information (identified by operation id):                        
---------------------------------------------------                        
                                                                           
   3 - access("C2"=0)                                                      
   4 - filter(LNNVL("C2"=0))                                               
   5 - access("C1"=0)                                                      
하지만, 실제 운영 환경에서는 사용하기에는 너무 제약이 많죠?

OTN Forum에 이 질문을 올린 분께는 미안하지만, 참으로 안타까운 상황에 처하고 말았네요.

저작자 표시
신고
Trackback 0 : Comment 0

Write a comment


SQL Text 수정이 불가능한 경우에 Tuning하기 - SQL Plan baseline 버전(11g)

오라클 2009.02.03 16:41
SQL Text 수정이 불가능한 경우에도 Tuning은 이루어져야 한다.

다행히 몇몇 기법들이 성공적으로 적용되었고 공개되어 있다. 이 블로그에서도 몇 번 소개한 바가 있다.

- 통계 정보의 Manual 수정
- Outline 바꿔치기
- SQL Profile Import 하기

위의 기법들은 특히 이번에 출간된 [Optimizing Oracle Optimizer]에 잘 설명되어 있다.

Oracle 11g에서 새롭게 추가된 놀라운 기능 중 하나인 SPM(SQL Plan Management)를 응용하면 동일한 효과를 얻을 수 있다.

자세한 내용은 아래 영문 포스트에서 소개한다.

How to tune SQL statement without changing text - Plan baseline version




신고
Trackback 0 : Comment 0

Write a comment


Optimizing Unoptimizable SQL

오라클 2008.07.01 15:02

Optimization이 불가능한(즉, SQL Text 수정이 불가능한) Query를 Optimization할 수 있는가?


[Oracle is Mad(R)] 7월 세미나에서 준비하고 있는 주제 중 하나이다.

SQL Text 수정이 불가능한 경우에는 어떻게 튜닝을 할 수 있는가? 이 질문은 점점 더 중요해질 것이다. ERP, CRM 같은 Package 형태의 업무 어플리케이션들이 늘어나고, Java, .NET에서 Persistent Framework(Hibernate같은)들이 보편화되면서 SQL Text에 대한 직접 수정이 불가능한 경우가 점점 늘어나고 있기 때문이다.

직간접적으로 Query 튜닝과 관련된 모든 사람들에게 중요한 도전인 셈이다.

(내가 알기로는) 이런 문제에는 6가지 정도의 해결책이 있다.
(Oracle Upgrade를 제외하고)

1.  Parameter 수정
Parameter 변경은 전역적이어서 위험성이 높다. 그리고 실제로 사용할 수 있는 파라미터의 수는 매우 제한적이다.

2. Physical Design 수정
Partition 나누기, Block Size 바꾸기, 기타 Phsyical Atribute를 수정하는 방법이 있다. 이런 변화는 전역적이고 영구적(Permanent)이어서 쉽사리 선택하기 힘들다. 선택의 폭 또한 매우 제한적이다.

3. 통계 정보 조작
Wolfgang Breitling에 의해 체계화된 TCF(Tuning By Cardinality Feedback) 기법이 여기에 속한다. CBO가 올바른 판단을 할 수 있도록 통계 정보를 보완해주는 기법이다.

언뜻 생각하면 제한적인 것 같지만, 매우 강력한 기법이다. CBO에 대한 신뢰를 바탕으로 CBO를 도와준다는 관점에서 보면 가장 Oracle 친화적인 방법이라고 할 수 있다.

조작 가능한 통계 정보에는 제한이 없으며 Table/Column/Index/Histogram 등 모든 통계 정보를 수동으로 조작할 수 있다. 하지만, 수동으로 변경된 통계 정보는 자동 백업(10g)이 되지 않는다는 사실과 통계 정보 수집시 덮어 써버린다는 사실에 주의해야 한다.

Index를 생성하거나 Index Key를 변경하는 것도 넓은 범위에서는 이 범주에 속한다.


4. Stored Outline
Stored Outline은 원래 Plan Stability를 보장하기 위해 제안된 개념이지만, 실세계에서 이 목적으로 사용되는 경우는 거의 없다. 오히려 Outline 바꿔치기를 통해 Query Tuning의 도구로 활용되는 경우가 많을 정도이다. 아래 글을 참조...

2008/03/20 - [Advanced Oracle] - Stored Outline을 이용한 SQL 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 성능을 높여야할 명분이 있다면 고려해볼 만한 방법이다.

혹시 다른 방법을 알고 있는 분들이 있는지...?


신고

'오라클' 카테고리의 다른 글

Why Creativity Matters?  (0) 2008.07.07
Automize your test case  (0) 2008.07.04
Optimizing Unoptimizable SQL  (3) 2008.07.01
Subquery is just like Join  (4) 2008.06.22
Visual의 힘  (0) 2008.06.20
Trackback 0 : Comments 3
  1. extremedb 2008.07.02 09:07 신고 Modify/Delete Reply

    집계테이블이나 목적성 테이블을 만들 경우는 목적성 테이블 대신에 Mview 를 만들고 Query Rewrite 를 적용하시면 되겠습니다.

  2. 쏘심이 2008.07.02 18:44 신고 Modify/Delete Reply

    블로그가 날로 활성화 되는거 같아 제가 다 기분이 좋네요.. 정보 얻어 갑니다.

  3. orapybubu 2008.07.09 10:37 신고 Modify/Delete Reply

    열린 질문으로 글을 마치셨기에 덧글을 달아봅니다. ^^

    (1) 하드웨어 추가
    (2) resource manager를 이용해서 가용 자원을 조정

Write a comment


Stored Outline을 이용한 SQL Tuning 기법

오라클 2008.03.20 13:22
Stored OutlinePlan Stablility를 보장하기 위한 툴이다. 특정 Query에 대해 Stored Outline이 지정되면 경천동지할(가령 사용하던 Index가 없어진다든가...) 변화가 생기지 않는 한 실행 계획이 고정되어 버린다.

하지만, Stored Outline의 이런 속성을 이용해서 Query를 튜닝할 수 있다는 것은 잘 알려지지 않은 사실이다. 다음과 같은 상황을 가정해보자.

1. SQL 문장 수정을 할 수 없다.(CRM/ERP/Billing 등의 Package 사용. J2EE Query Generator 사용 등...)
2. 특정 SQL 문장이 느리다.
3. SQL 문장 수정도 할 수 없고, 통계 정보를 수집해도 성능이 개선되지 않는다.


이런 경우라면 어떻게 해야할까? 아마 대부분의 사람들이 아무런 방법이 없다고 생각할 것이다.

하지만, 우선 블로그에서 소개한 Tuning By Cardinality 기법을 이용할 수 있다. 또 하나의 가능한 방법이 Stored Outline을 교묘하게 이용하는 것이다. 이 기법은 다음과 같이 정리할 수 있다.

1. 현재 느린 SQL 문장(SQL#1이라 칭함)에 대해 Stored Outline(이하 Outline#1이라 칭함)을 만든다.
2. SQL문장의 개선판(SQL#2라 칭함)을 만들고 이에 대해 Stored Outline(이하 Outline#2라 칭함)을 만든다.
3. Outline#2의 데이터를 Outline#1로 덮어쓴다.
4. SQL#1 수행시 Outline#1이 수행되도록 한다(USE_STORED_OUTLINES=TRUE)
5. 이후 SQL#1이 하드 파스될 때 Outline#1에 의해 실행 계획이 생성될 것이다. Outline#1은 실제로는 개선된 SQL#2의 Outline을 가지고 있으므로 개선된 실행 계획이 생성된다.


즉, 개선된 SQL 문장에 대해 Stored Outline을 만들어서 교묘하게 이 Outline을 사용하게끔 트릭을 구사하는 것이다. TCF(Tuning By Cardinality Feedback)을 통해서도 해결이 안될 때 사용할 수 있는 좋은 방법이다. 또는 이 방법 외에는 전혀 대안이 없는 경우도 있다. 동일한 SQL 문장이 Application#1에서 수행될 때와 Application#2에서 수행될 때 실행 계획이 전혀 다르게 풀리고 이를 피할 수 있는 방법이 없다면(물론 기본 가정은 SQL 문장을 수정할 수 없다는 것이다)? TCF로는 해결할 수 없다. Stored Outline을 이용하는 거의 유일한 대안이 될 것이다.

아래에 간단한 예제가 있다.


UKJA@ukja10> -------------------------------------------------------
UKJA@ukja10> -- initialize stored outline
UKJA@ukja10> alter session set create_stored_outlines = false;

Session altered.

Elapsed: 00:00:00.01
UKJA@ukja10> exec dbms_outln.drop_by_cat('TEST_OUTLN');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
UKJA@ukja10> exec dbms_outln.drop_unused;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
UKJA@ukja10>
UKJA@ukja10>
UKJA@ukja10> -- create objects
UKJA@ukja10> drop table t_outln purge;

Table dropped.

Elapsed: 00:00:00.17
UKJA@ukja10>
UKJA@ukja10> create table t_outln(c1 int, c2 int);

Table created.

Elapsed: 00:00:00.00
UKJA@ukja10>
UKJA@ukja10> create index t_outln_i1 on t_outln(c1);

Index created.

Elapsed: 00:00:00.01
UKJA@ukja10> create index t_outln_i2 on t_outln(c2);

Index created.

Elapsed: 00:00:00.00
UKJA@ukja10>
UKJA@ukja10> insert into t_outln
  2  select level, mod(level,10)
  3  from dual
  4  connect by level <= 10000;

10000 rows created.

Elapsed: 00:00:00.26
UKJA@ukja10>
UKJA@ukja10> commit;

Commit complete.

Elapsed: 00:00:00.01
UKJA@ukja10>
UKJA@ukja10> exec dbms_stats.gather_table_stats(user, 't_outln', -
>     cascade=>true, no_invalidate=>false);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.34
UKJA@ukja10>
UKJA@ukja10> -- check execution plan. t_outln_i1 인덱스를 경유하는 실행 계획이 생성되는 것을 확인할 수 있다.
UKJA@ukja10> explain/* dummy */ plan for
  2  select *
  3  from t_outln
  4  where c1 = 1 and c2 = 1;

Explained.

Elapsed: 00:00:00.00
UKJA@ukja10>
UKJA@ukja10> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
Plan hash value: 1332862074

--------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |     1 |     6 |     2 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| T_OUTLN    |     1 |     6 |     2 |
|*  2 |   INDEX RANGE SCAN          | T_OUTLN_I1 |     1 |       |     1 |
--------------------------------------------------------------------------

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

   1 - filter("C2"=1)
   2 - access("C1"=1)
                    
Note
-----
   - cpu costing is off (consider enabling it)

19 rows selected.

Elapsed: 00:00:00.01
UKJA@ukja10>
UKJA@ukja10>     -- check outline
UKJA@ukja10> col name format a30
UKJA@ukja10> col category format a20
UKJA@ukja10>
UKJA@ukja10> select name, category, used, enabled, sql_text
  2  from user_outlines;

no rows selected

Elapsed: 00:00:00.01
UKJA@ukja10>
UKJA@ukja10> select name, hint
  2  from user_outline_hints;

no rows selected

Elapsed: 00:00:00.00
UKJA@ukja10>
UKJA@ukja10>
UKJA@ukja10> -- create stored outline
UKJA@ukja10> alter session set create_stored_outlines = TEST_OUTLN;

Session altered.

Elapsed: 00:00:00.00
UKJA@ukja10> select *
  2  from t_outln
  3  where c1 = 1 and c2 = 1;

        C1         C2
---------- ----------
         1          1

Elapsed: 00:00:00.01
UKJA@ukja10> alter session set create_stored_outlines = false;

Session altered.

Elapsed: 00:00:00.00
UKJA@ukja10> -- check outline
UKJA@ukja10> select name, category, used, enabled, sql_text
  2  from user_outlines;

NAME                           CATEGORY             USED               ENABLED
------------------------------ -------------------- ------------------ ---------
SQL_TEXT                                                                       
--------------------------------------------------------------------------------
SYS_OUTLINE_08032011254993104  TEST_OUTLN           UNUSED             ENABLED 
select *                                                                       
from t_outln                                                                   
where c1 = 1 and c2 = 1  
                                                                               

Elapsed: 00:00:00.03
UKJA@ukja10>
UKJA@ukja10> select name, hint
  2  from user_outline_hints;

NAME                           HINT                                            
------------------------------ -------------------------------------------------
SYS_OUTLINE_08032011254993104  OUTLINE_LEAF(@"SEL$1")                          
SYS_OUTLINE_08032011254993104  FIRST_ROWS(1)                                   
SYS_OUTLINE_08032011254993104  OPT_PARAM('_optimizer_cost_model' 'io')         
SYS_OUTLINE_08032011254993104  OPTIMIZER_FEATURES_ENABLE('10.2.0.1')           
SYS_OUTLINE_08032011254993104  IGNORE_OPTIM_EMBEDDED_HINTS                     
SYS_OUTLINE_08032011254993104  INDEX(@"SEL$1" "T_OUTLN"@"SEL$1" ("T_OUTLN"."C1"))

6 rows selected.

Elapsed: 00:00:00.00
UKJA@ukja10> -- Let's use stored outline
UKJA@ukja10> alter session set use_stored_outlines = TEST_OUTLN;

Session altered.

Elapsed: 00:00:00.00
UKJA@ukja10> -- 이후 동일한 Query를 수행하면...
UKJA@ukja10> select *
  2  from t_outln
  3  where c1 = 1 and c2 = 1;

        C1         C2
---------- ----------
         1          1

Elapsed: 00:00:00.01
UKJA@ukja10>
UKJA@ukja10> -- check outline
UKJA@ukja10> select name, category, used, enabled, sql_text
  2  from user_outlines;

NAME                           CATEGORY             USED               ENABLED
------------------------------ -------------------- ------------------ ----------
SQL_TEXT  
--------------------------------------------------------------------------------
SYS_OUTLINE_08032011254993104  TEST_OUTLN           USED               ENABLED
select *                                                                        
from t_outln                                                                    
where c1 = 1 and c2 = 1                                                         
                                                                            

Elapsed: 00:00:00.00
UKJA@ukja10>
UKJA@ukja10> -- check how plan is established when outline is being used
UKJA@ukja10> explain plan for
  2  select *
  3  from t_outln
  4  where c1 = 1 and c2 = 1;

Explained.

Elapsed: 00:00:00.00
UKJA@ukja10>
UKJA@ukja10> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT 
--------------------------------------------------------------------------
Plan hash value: 1332862074

--------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |     1 |     6 |     2 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| T_OUTLN    |     1 |     6 |     2 |
|*  2 |   INDEX RANGE SCAN          | T_OUTLN_I1 |     1 |       |     1 |
--------------------------------------------------------------------------
                                                   
Predicate Information (identified by operation id):
---------------------------------------------------
                                                   
   1 - filter("C2"=1)
   2 - access("C1"=1)

Note
-----
   - cpu costing is off (consider enabling it)
   - outline "SYS_OUTLINE_08032011254993104" used for this statement
(outline이 사용되는 것을 확인할 수 있다)
20 rows selected.

Elapsed: 00:00:00.04
UKJA@ukja10> -- change data distribution a lot. Data 분포를 완전히 바꾸어서 t_outln_i2 인덱스가 훨씬 유리하게끔 변경한다.
UKJA@ukja10> delete from t_outln;

10000 rows deleted.

Elapsed: 00:00:00.34
UKJA@ukja10>
UKJA@ukja10> insert into t_outln
  2  select mod(level, 10), level
  3  from dual
  4  connect by level <= 10000;

10000 rows created.

Elapsed: 00:00:00.39
UKJA@ukja10>
UKJA@ukja10> commit;

Commit complete.

Elapsed: 00:00:00.00
UKJA@ukja10>
UKJA@ukja10> exec dbms_stats.gather_table_stats(user, 't_outln', -
>     cascade=>true, no_invalidate=>false);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.89
UKJA@ukja10>
UKJA@ukja10>
UKJA@ukja10>
UKJA@ukja10> -- compare the workload
UKJA@ukja10> alter session set statistics_level = all;

Session altered.

Elapsed: 00:00:00.00
UKJA@ukja10>
UKJA@ukja10> select *
  2  from t_outln
  3  where c1 = 1 and c2 = 1;

        C1         C2
---------- ----------
         1          1

Elapsed: 00:00:00.00
UKJA@ukja10>
UKJA@ukja10> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  47hs65gxd5c1t, child number 0
-------------------------------------
select * from t_outln where c1 = 1 and c2 = 1

Plan hash value: 1332862074

----------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------
|*  1 |  TABLE ACCESS BY INDEX ROWID| T_OUTLN    |      1 |      1 |      1 |00:00:00.01 |      29 |
|*  2 |   INDEX RANGE SCAN          | T_OUTLN_I1 |      1 |   1000 |   1000 |00:00:00.01 |       6 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
                                                  
   1 - filter("C2"=1)                             
   2 - access("C1"=1)                             
                                                  
Note                                              
-----                                             
   - cpu costing is off (consider enabling it)    
   - outline "SYS_OUTLINE_08032011254993104" used for this statement
 

24 rows selected.

Elapsed: 00:00:00.04
UKJA@ukja10>
UKJA@ukja10> alter session set statistics_level = all;

Session altered.

Elapsed: 00:00:00.00
UKJA@ukja10>
UKJA@ukja10> select /* dummy */ *
  2  from t_outln
  3  where c1 = 1 and c2 = 1;

        C1         C2
---------- ----------
         1          1

Elapsed: 00:00:00.00
UKJA@ukja10>
UKJA@ukja10> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------
SQL_ID  717x2u9bs23tb, child number 0
-------------------------------------
select /* dummy */ * from t_outln where c1 = 1 and c2 = 1
 
Plan hash value: 606665385

----------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------
|*  1 |  TABLE ACCESS BY INDEX ROWID| T_OUTLN    |      1 |      1 |      1 |00:00:00.01 |       6 |
|*  2 |   INDEX RANGE SCAN          | T_OUTLN_I2 |      1 |      1 |      1 |00:00:00.01 |       5 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
                                                  
   1 - filter("C1"=1)                             
   2 - access("C2"=1)                             
                                                  
Note                                              
-----                                          
   - cpu costing is off (consider enabling it)  
                                               

23 rows selected.
-- t_outln_i2 인덱스가 훨씬 유리하지만 기존에 Outline이 있기 때문에 여전히 t_outln_i1 인덱스를 이용한다.

Elapsed: 00:00:00.03
UKJA@ukja10> -- 강제로 t_outln_i2 인덱스를 경유하는 새로운 Outline을 만든다.
UKJA@ukja10> create outline tmp_outln for category test_outln
  2  on
  3  select /*+ index(t_outln t_outln_i2) */ *
  4  from t_outln
  5  where c1 = 1 and c2 = 1;

Outline created.

Elapsed: 00:00:00.00
UKJA@ukja10>
UKJA@ukja10> -- check outline
UKJA@ukja10> select ol_name, sql_text, hintcount
  2  from outln.ol$
  3  where category = 'TEST_OUTLN';

OL_NAME                                                                       
------------------------------------------------------------------------------------------
SQL_TEXT                                                                          HINTCOUNT
-------------------------------------------------------------------------------- ----------
TMP_OUTLN                                                                                 
select /*+ index(t_outln t_outln_i2) */ *                                                 6
from t_outln                                                                              
where c1 = 1 and c2 = 1                                                                   
                                                                                          
SYS_OUTLINE_08032011254993104                                                             
select *                                                                                  6
from t_outln                                                                              
where c1 = 1 and c2 = 1
                                                                                          

Elapsed: 00:00:00.00
UKJA@ukja10>
UKJA@ukja10> select name, hint
  2  from user_outline_hints
  3  order by name, node;

NAME                           HINT       
------------------------------ ------------------------------------------------------------
SYS_OUTLINE_08032011254993104  OUTLINE_LEAF(@"SEL$1")
SYS_OUTLINE_08032011254993104  FIRST_ROWS(1) 
SYS_OUTLINE_08032011254993104  OPT_PARAM('_optimizer_cost_model' 'io') 
SYS_OUTLINE_08032011254993104  INDEX(@"SEL$1" "T_OUTLN"@"SEL$1" ("T_OUTLN"."C1"))
SYS_OUTLINE_08032011254993104  OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
SYS_OUTLINE_08032011254993104  IGNORE_OPTIM_EMBEDDED_HINTS 
TMP_OUTLN                      INDEX(@"SEL$1" "T_OUTLN"@"SEL$1" ("T_OUTLN"."C2"))
TMP_OUTLN                      OUTLINE_LEAF(@"SEL$1")    
TMP_OUTLN                      FIRST_ROWS(1)             
TMP_OUTLN                      OPT_PARAM('_optimizer_cost_model' 'io')
TMP_OUTLN                      OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
TMP_OUTLN                      IGNORE_OPTIM_EMBEDDED_HINTS      

12 rows selected.

Elapsed: 00:00:00.03

-- 이제
TMP_OUTLN Outline의 내용을 SYS_OUTLINE_08032011254993104 Outline으로 덮어써버린다.이렇게 함으로써 SYS_OUTLINE_08032011254993104 Outline이 t_outln_i2 인덱스를 경유하게끔 조작한다.

UKJA@ukja10> update outln.ol$ set hintcount = (select hintcount from outln.ol$ where
  2    ol_name = '&OL_NAME') where ol_name = 'TMP_OUTLN';
Enter value for ol_name: SYS_OUTLINE_08032011254993104
old   2:   ol_name = '&OL_NAME') where ol_name = 'TMP_OUTLN'
new   2:   ol_name = 'SYS_OUTLINE_08032011254993104') where ol_name = 'TMP_OUTLN'

1 row updated.

Elapsed: 00:00:00.01
UKJA@ukja10> delete from outln.ol$hints where ol_name = '&OL_NAME';
Enter value for ol_name: SYS_OUTLINE_08032011254993104
old   1: delete from outln.ol$hints where ol_name = '&OL_NAME'
new   1: delete from outln.ol$hints where ol_name = 'SYS_OUTLINE_08032011254993104'

6 rows deleted.

Elapsed: 00:00:00.00
UKJA@ukja10> update outln.ol$hints set ol_name = '&OL_NAME' where ol_name = 'TMP_OUTLN';
Enter value for ol_name: SYS_OUTLINE_08032011254993104
old   1: update outln.ol$hints set ol_name = '&OL_NAME' where ol_name = 'TMP_OUTLN'
new   1: update outln.ol$hints set ol_name = 'SYS_OUTLINE_08032011254993104' where ol_name = 'TMP_OUTLN'

6 rows updated.

Elapsed: 00:00:00.00
UKJA@ukja10> delete from outln.ol$nodes where ol_name = '&OL_NAME';
Enter value for ol_name: SYS_OUTLINE_08032011254993104
old   1: delete from outln.ol$nodes where ol_name = '&OL_NAME'
new   1: delete from outln.ol$nodes where ol_name = 'SYS_OUTLINE_08032011254993104'

1 row deleted.

Elapsed: 00:00:00.00
UKJA@ukja10> update outln.ol$nodes set ol_name = '&OL_NAME' where ol_name = 'TMP_OUTLN';
Enter value for ol_name: SYS_OUTLINE_08032011254993104
old   1: update outln.ol$nodes set ol_name = '&OL_NAME' where ol_name = 'TMP_OUTLN'
new   1: update outln.ol$nodes set ol_name = 'SYS_OUTLINE_08032011254993104' where ol_name = 'TMP_OUTLN'

1 row updated.

Elapsed: 00:00:00.00
UKJA@ukja10> commit;

Commit complete.

Elapsed: 00:00:00.00
UKJA@ukja10> -- check outline
UKJA@ukja10> select name, category, used, enabled, sql_text
  2  from user_outlines;

NAME                           CATEGORY             USED               ENABLED
------------------------------ -------------------- ------------------ ----------
SQL_TEXT                                                                      
--------------------------------------------------------------------------------
TMP_OUTLN                      TEST_OUTLN           UNUSED             ENABLED 
select /*+ index(t_outln t_outln_i2) */ *                                      
from t_outln                                                                   
where c1 = 1 and c2 = 1  

SYS_OUTLINE_08032011254993104  TEST_OUTLN           USED               ENABLED
select *                                                                 
from t_outln                                                             
where c1 = 1 and c2 = 1 
                        

Elapsed: 00:00:00.00
UKJA@ukja10> -- SYS_OUTLINE_08032011254993104 Outline이 t_outln_i2 인덱스(INDEX(@"SEL$1" "T_OUTLN"@"SEL$1" ("T_OUTLN"."C2")))를 사용하게끔 조작된 것을 확인할 수 있다.
UKJA@ukja10> select name, hint
  2  from user_outline_hints;

NAME                           HINT                                           
------------------------------ ---------------------------------------------------
SYS_OUTLINE_08032011254993104  INDEX(@"SEL$1" "T_OUTLN"@"SEL$1" ("T_OUTLN"."C2"))
SYS_OUTLINE_08032011254993104  OUTLINE_LEAF(@"SEL$1")                 
SYS_OUTLINE_08032011254993104  FIRST_ROWS(1)            
SYS_OUTLINE_08032011254993104  OPT_PARAM('_optimizer_cost_model' 'io')
SYS_OUTLINE_08032011254993104  OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
SYS_OUTLINE_08032011254993104  IGNORE_OPTIM_EMBEDDED_HINTS 

6 rows selected.

Elapsed: 00:00:00.01
UKJA@ukja10> -- 이제 정말로 조작된 Outline을 사용하는지 확인한다.
UKJA@ukja10> -- flush shared pool
UKJA@ukja10> -- Why? execution plan is re-established only when the sql is reparsed
UKJA@ukja10> alter system flush shared_pool;

System altered.

Elapsed: 00:00:00.01
UKJA@ukja10>
UKJA@ukja10> alter session set statistics_level = all;

Session altered.

Elapsed: 00:00:00.00
UKJA@ukja10>
UKJA@ukja10> select *
  2  from t_outln
  3  where c1 = 1 and c2 = 1;

        C1         C2       
---------- ----------       
         1          1       

Elapsed: 00:00:00.20
UKJA@ukja10>
UKJA@ukja10> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

PLAN_TABLE_OUTPUT           
---------------------------------------------------------------------------------------------------
SQL_ID  47hs65gxd5c1t, child number 0
-------------------------------------
select * from t_outln where c1 = 1 and c2 = 1
                                             
Plan hash value: 606665385                   
        
----------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------
|*  1 |  TABLE ACCESS BY INDEX ROWID| T_OUTLN    |      1 |      1 |      1 |00:00:00.01 |       6 |
|*  2 |   INDEX RANGE SCAN          | T_OUTLN_I2 |      1 |      1 |      1 |00:00:00.01 |       5 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):  
---------------------------------------------------  
                                                     
   1 - filter("C1"=1)                                
   2 - access("C2"=1)                                
                                                     
Note                                                 
-----                                                
   - cpu costing is off (consider enabling it)       
   - outline "SYS_OUTLINE_08032011254993104" used for this statement


24 rows selected.

Elapsed: 00:00:00.34
UKJA@ukja10> -- t_outln_i2 인덱스를 경유하며, 일량도 줄어든 것을 확인할 수 있다.



위의 기법을 잘 활용하면 전혀 튜닝이 불가능할 것 같은 상황에서도 어렵지 않게 튜닝을 시도할 수 있다. 특히 CRM/ERP와 같은 Package를 사용하는 환경이나 J2EE의 Query Generator(Persistent Framework)를 사용하는 환경에서는 대단히 유용한 기법 중 하나로 활용해볼만 하다.

신고
Trackback 0 : Comments 17
  1. 푸름사랑 2008.04.24 16:23 신고 Modify/Delete Reply

    제가 있는 사이트에서 응급조치용 plan stability방안으로 stored outline Hint바꿔치기 방식을 사용하고 있습니다..
    Stored outline이 응급조치용으로는 좋은데 바꿔치기할 PLAN이 use_concat힌트를 사용하는 OR-Expansion의 경우에는 concatenation되는 2번째 쿼리블럭은 OL$HINTS 에서 HINT제어를 못한다는거..
    그리고 10.2.0.3 이하 버전에서는 응급조치(outline생성)후 LCO를 invalidation시켜야 하는데, 커저별 purge 기능(단 patch5614566 - aix,linux는 10203에도 백포팅됨)을 사용하지 못한다는 거.. 이런 제약사항이 있더군요...

  2. 욱짜 2008.04.25 09:49 신고 Modify/Delete Reply

    cursor_sharing = force(similar)를 사용하는 경우에도 사용할 수 없다는 제약이 있죠. Outline이 SQL Text를 비교하는 방식이라서 SQL Text가 완벽하게 동일해야 합니다.
    그 외에도 의외로 제약이 상당히 많습니다. 여기에 관해서는 나중에 시간이 나면 다시 글을 싣도록 하겠습니다.
    그래서 Outline을 생성할 때 정확하게 어떤 Hint가 사용될지 미리 알 수 있다면 도움이 됩니다. 다음과 같이 사용될 Outline을 미리 확인할 수 있습니다.

    explain plan for ...;
    select * from table(dbms_xplan.display(null,null,'outline'));

    Outline이라는 것이 어차피 힌트이기 때문에 어떤 힌트가 사용되는지 정확하게 알아야 의외의 복병을 피할 수 있습니다.

  3. 강정식 2008.09.09 11:06 신고 Modify/Delete Reply

    안녕하세요. 욱짜님

    이번에 욱짜님 블로그의 stored outline 내용 덕분에 제가 지원하는 사이트에서 한건의 이슈를 해결 하였습니다.
    이슈는 튜닝대상의 SQL이 Oracle ERP에서 Java로 Complie된 Class 파일 안에 내용이 있어서 이 SQL을 건드리지
    못했는데 stored outline을 이용해서 실행계획을 변경하여 해결하였습니다.

    이번에 이 기술을 사용해서 몇가지 특이한 내용을 알게되서 글을 남겨드립니다.

    1. create outline xxsews0035_outline on
    욱짜님 블로그에서 stored outline sql을 등록하실 때 아래의 구문을 활용하셨는데요.

    alter session set create_stored_outlines = TEST_OUTLN;
    SQL 내용
    alter session set create_stored_outlines = false;

    이렇게 SQL을 등록할 때 바인드 변수가 :b1인 경우 'variable b1'으로 선언하고 SQL의 실행계획을
    가져올 수 있는데 저 같은 경우에는 Trace에서 나온 SQL이 :b1이 아니라 :1으로 나와서 변수선언을
    할 수 없었습니다. 이 경우 'create outline xxsews0035_outline on' 명령어를 이용하여
    대상 SQL을 등록하면 :b1이든 :1이든 등록이 가능하더라구요. 그래서 혹시 바인드 변수로 인해
    SQL을 등록할 때 이슈가 될 경우 이를 이용하시면 될 것 같습니다.

    2. SQL 매칭
    오라클에서는 하드파싱과 소프트파싱을 구분할 떄 대소문자, 공백 등에 대해 100% 매칭될 때
    이를 메모리에서 재활용하는데 stored outline 또한 이처럼 SQL이 완벽히 매칭되야 하는줄 알았습니다.
    하지만 실제 테스트 해본 결과 SQL만 같을 경우 대소문자나 공백의 차이에 대해서는 옵티마이저가
    구분을 하여 이들을 같은 SQL로 인식을 하는것으로 확인되었습니다.

    예를들어

    -- Trace에서 추출된 SQL
    SELECT /*+ FULL(EMP) */ * FROM EMP WHERE EMPNO = :1;

    -- stored outline에 등록된 SQL
    select /*+ full(emp) */ * from emp where empno = :1;

    이처럼 대문자로 수행되는 SQL에 대해 소문자로 stored outline을 등록할 경우 이를 같은것으로 인식하여
    실행계획을 변경하는게 가능합니다.

    이 2개의 내용이 Troubleshooting을 하는 과정에서 특이사항으로 생각되어 글을 남겨드립니다.
    혹시 이에 대해 추가적으로 아시는 내용 있으시면 공유 부탁드립니다. ^^

  4. 욱짜 2008.09.09 11:23 신고 Modify/Delete Reply

    그렇군요. 좋은 정보 감사합니다.

    alter session 을 이용한 방법은 대량으로 Outline을 생성하고자 할 때 적당하고 create stored outline... 은 문제가 되는 특정 SQL을 조작할 때 유용한 방법이 될 거 같습니다.

    문제 해결에 도움이 되었다니 다행이네요~

  5. 욱짜 2008.09.10 09:21 신고 Modify/Delete Reply

    추가적으로, Oracle 10g이상이라면 Stored Outline 대신 SQL Profile을 이용해 볼 것을 권장합니다. Outline과는 달리 LCO를 Invalidation시키지 않아도 된다는 큰 장점이 있습니다.

  6. 강정식 2008.09.10 10:39 신고 Modify/Delete Reply

    욱짜님 블로그의 'Stored Outline' 내용처럼 'SQL Profile'에 대한 내용을 참고할만한 사이트가 있을까요?

  7. 욱짜 2008.09.10 10:54 신고 Modify/Delete Reply

    아래 URL을 참조하세요.

    http://sites.google.com/site/ukja/oracle-is-madr/optimizing---sql-profile

  8. 강정식 2008.09.10 11:39 신고 Modify/Delete Reply

    감사합니다 ^^

  9. 강정식 2008.10.17 14:10 신고 Modify/Delete Reply

    안녕하세요 욱짜님.. Stored Outline 사용시 이슈가 있어서 질문을 드립니다.

    지난번 Select 구문에 대해서는 Stored Outline이 잘 등록이 되었는데 이번에는 Select 구문이 아닌
    Update 구문을 Stored Outline으로 등록을 하려고 합니다.

    그런데 Update 대상이 테이블이 아닌 Synonym인데 이 Synonym을 Update하는 구문에 대해
    Stored Outline으로 등록하여 확인해보려고 하는데 이슈가 생겼습니다.

    이슈가 생긴 부분은 Stored Outline의 3개 테이블에 등록은 되었고
    'ALTER SESSION SET USE_STORED_OUTLINES' 명령어로 호출한 뒤에
    'EXPLAIN /* DUMMY */ PLAN FOR' 명령어로 플랜을 등록하려고 하는데 에러가 났습니다.

    에러 구문은 다음과 같습니다.

    ERROR at line 2:
    ORA-01732: data manipulation operation not legal on this view

    즉 Synonym에 대해서는 Stored Outline을 사용할 수 없었는데요.
    이것처럼 혹시 Stored Outline을 사용할 때 특정 Object는 사용할 수 없다던지 하는 제약사항에 대해
    알고 계신게 있는지 궁금합니다.

    혹시 알고 계시다면 리플 부탁드리겠습니다.

    감사합니다.

  10. 욱짜 2008.10.17 14:45 신고 Modify/Delete Reply

    Stored Outline 자체에는 그런 제약이 없을겁니다. ORA-01732 에러는 대부분 권한 문제와 관련이 있는 것으로 알고 있습니다. Metalink에서 "ORA-01732 Synonym"과 같은 키워드로 검색해보시면 비슷한 사례가 있을 것으로 봅니다.

  11. 강정식 2008.10.17 15:29 신고 Modify/Delete Reply

    욱짜님 답변 감사드립니다.
    하지만 욱짜님께서 알려주신 키워드로 메타링크에서 검색한 결과 욱짜님께서 말씀하신 것처럼 권한문제로
    접근한 글이 다수였습니다.

    해당 글에서 제시한 솔루션은 다음과 같았는데요.

    GRANT MERGE ANY VIEW TO <username>;
    GRANT ALL ON <owner.table> TO <username>;

    해당 권한을 주고 다시 수행해봐도 같은 ora error가 계속 나타나고 있어서 다시한번 글 남깁니다.
    혹시 다른 접근 방법에 대해 알고 계신것이 있으시면 리플 부탁드리겠습니다.

    감사합니다.

  12. 욱짜 2008.10.17 15:46 신고 Modify/Delete Reply

    발생하고 있는 문제를 간단한 테스트 스크립트로 재현 가능한가요?

  13. 강정식 2008.10.17 16:01 신고 Modify/Delete Reply

    현재 간략한 스크립트로 재현을 해보려고 하였으나 잘 되지 않고 있습니다.

    다만 새로운 사실 하나를 알았는데요. 해당 SQL의 explain plan을 실행할 때 해당 Stored Outline으로
    Alter session을 실행하는 것이 아니라 전혀 상관이 없는 Stored Outline을 활성화시키고 해당 SQL의
    Explain plan을 해도 같은 Error가 나오고 있습니다.

    이걸로 추측해 보건데 Stored Outline의 문제라기 보다는

    ALTER SESSION SET USE_STORED_OUTLINES

    EXPLAIN /* DUMMY */ PLAN FOR

    이 사이에 뭔가 미스매치가 되어 계속 에러가 나오는것 같은데 이 원인이 뭔지를 모르겠습니다.

    제가 질문올린 글에 계속 관심 가져주셔서 감사드립니다.
    혹시 제가 질문드린 내용을 보시고 어떤 부분때문인지 감이 오시면 알려주시기 바랍니다.
    저도 이슈가 해결되면 리플 달도록 하겠습니다.

    감사합니다.

  14. ktlee67@show.co.kr 2010.01.15 15:09 신고 Modify/Delete Reply

    SQL Profile의 경우 대소문자나 공백, 개행문자, 그리고 심지어 문자열변수의 경우 리터럴 문자변수를 바인드처리하여 같은 경우로 비교하는 듯 싶습니다.
    sql profile 관련 주요뷰를 보면 signature 컬럼이 있고, 이것은 dbms_sqltune.sqltext_to_signature 함수에의해 도출되는 값과 같은 로직으로 도출되는 것으로 보이는데, 여기서 sqltext를 clob 인자로 받아서 나오는 값이 sql text가 동일하지 않아도 공백이나 개행문자, 대소문자에 상관없이 동일한 signature값을 return해주더군요.
    이함수는 이외에도 일반적으로 동일한 sql인지를 비교하기 위하여, sql_text를 인자로 받아서 hash값을 만들어 비교할때, 유용할것 같습니다.
    기존 암호화관련 패키지의 MD5등의 hash 함수는 공백이나 대소문자가 틀리면 당연히 틀린 HASH값을 보여주니까요.
    SQL Profile의 적용시 제약사항도 있을 것 같은데요.
    이러한 부분에 대해서도 있다면 outline과 마찬가지로 공유가 되었으면 합니다. ^^

  15. 욱짜 2010.01.18 12:51 신고 Modify/Delete Reply

    http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/d_sqltun.htm#CACHJDEG

    위의 내용을 보면 Literal 값의 변환은 force_match 파라미터에 의해 결정되는 것 같습니다. 기타 개행이나 공백 문자등은 자동으로 제거가 되는 것으로 보입니다.

  16. ktlee67@show.co.kr 2010.01.18 14:50 신고 Modify/Delete Reply

    dbms_sqltune.sqltext_to_signature 함수의 경우
    force_match의 경우 cursor_sharing의 force 옵션과 동일한 matching algorithm을 사용하는데,
    merge into... 구문이나 with 구문의 경우는 바인드 처리를 하지 못합니다.
    실제로 cursor_sharing = force의 경우도 merge into 구문과 with 구문을 처리하지 못하더군요.
    (10.2.0.4에서). 버그인지 아니면 지원을 못하는 건지.. 모르겠지만.
    또한 dbms_sqltune.sqltext_to_signature는 force_match의 경우 literal과 바인드가 혼재된
    sql의 경우 역시 바인드 처리를 하지 못합니다.

  17. 강정식 2010.11.17 19:02 신고 Modify/Delete Reply

    안녕하세요. 욱짜님.
    최근에 stored outline 관련해서 새로운 내용을 알게 되어 공유해 드립니다.

    지난번에 욱짜님 블로그와 OOO 책에서, stored outline을 create하고 session 레벨에서 call할 때 outline 이름으로 테스트를 진행하시어 제가 지금 있는 사이트에서도 모두 이 방법으로 적용이 되어 있는데요.

    문제는 stored outline을 call할때 outline 이름으로 call을 하는것이 아니라 category 이름으로 call을 해야 한다는 것을 알았습니다.

    발견한 경유는, 현재 outline으로 설정된 대상이 20개인데, 이 중 첫번째만 session 레벨에서 outline 활성화를 하고 우연히 다른 sql을 수행하였는데 stored outline이 먹는것이 발견되더라구요.

    그래서 확인해보니, session 레벨에서 outline이름으로 call을 할 때는 의미가 없으며(이렇게 활성화를 할 경우 현재 구성되어 있는 모든 outline SQL이 활성화됨) category 이름을 불러와야 개별 SQL만 활성화가 되는것을 알게 되었습니다.

    하여, 욱짜님 블로그의 stored outline post에서 다시 찾아보니 아래처럼 category로 다시 수정을 하셨더라구요.

    create outline tmp_outln for category test_outln
    on
    select /*+ index(t_outln t_outln_i2) */ *
    from t_outln
    where c1 = 1 and c2 = 1

    이에 대해 저는 한동안 왜 stored outline을 구분할 수 있는 이름이 있음에도 불구하고 그 이름을 사용하지 않고 category를 사용할 까 의문이 들었었는데, 생각해보니 한 프로그램에 여러 SQL이 stored outline 대상일 경우 이를 한꺼번에 호출하려면 N개 만큼 alter session을 해야하는데 이처럼 category로 그룹을 지을 경우 하나만 호출해서 해결이 가능하기 때문에 이렇게 하는거라는 생각이 들더군요.

    stored outline post 내용에서는 category로 구성하셨지만 OOO책에는 여전히 outline 이름이 되어 있으므로 향후 개정판에 수정해 주시면 공부하시는 분들에게 더 좋을 것 같습니다.

    그럼 수고하세요

Write a comment


Oracle Upgrade에 의한 실행 계획 이상과 Stored Outline

오라클 2007.12.27 11:04
Oracle Upgrade시 가장 골치 아픈 문제를 뽑으라면? 아래 한 문장으로 요약할 수 있다.

"Oracle 9i에서 10g로 Upgrade했더니 쿼리 성능이 느려졌다!!"

절대 발생하지 말아야할 일임에도 불구하고 여전히 많은 시스템들이 Upgrade에 의한 쿼리 성능 저하로 고생을 하는 경우가 많다.

Oracle Upgrade이후 성능이 느려지는 이유는 너무나 다양하다. 이 중 가장 보편적으로 발생하는 것이 바로 실행 계획의 불안정성이다.

가령 9i에서는 Index Range Scan을 하던 쿼리가 10g로 업그레이드 후 별안간 Full Table Scan을 한다. 또는 Netsted Loop Join을 하던 쿼리가 별안안 Hash Join을 수행한다. 이런 경우를 흔히 "실행 계획이 변했다", "실행 계획의 안정성이 깨어졌다"라고 표현할 수 있겠다.

왜 이런 현상이 발생하는 것일까? 불행하게도 원인은 너무 다양하다. 하지만 오라클의 동작 원리를 이해하면 어느 정도 원인 추적이 가능하다.

오라클의 Optimizer는 간단한게 표현하면 다음과 같은 절차를 통해 실행 계획을 생성한다.

1. Query Rewrite
Query를 내부적으로 변환한다. 이 과정에서 Transitivity, View Merging, Subquery Unnesting, Predicate Pushing, Materialized View Rewrite 등이 발생한다.
2. Optimizing
통계 정보를 이용해서 비용을 계산하고 가장 저렴한 비용을 갖는 실행 계획을 찾는다.

즉, Query Rewrite 과정과 Optimizing 과정을 통해 실행 계획이 생성된다. 문제는 Oracle이 버전업될 때마다 이 과정들의 알고리즘들이 바뀐다는 것이다.

가령 8i에서 9i로 업그레이드시 _COMPLEX_VIEW_MERGING이라는 히든 파라미터가 추가되면서(8i에서는 히든 파라미터가 아니었고 기본값이 False였음) 기본값이 True로 변경되었다.
이로 인해 View를 사용하던 쿼리들에 대해서 View Merging이 훨씬 공격적으로 발생하게 되었다.
View Merging은 대부분의 경우 성능에 유리하지만, 특정 생각지 못한 상황에서는 변형된 쿼리에 의해 오히려 실행 계획의 변형을 불러온다. 이로 인해 갑자기 쿼리 성능이 저하되는 것이다.

Oracle은 이런 식으로 버전 업에 따른 Query Rewrite 기능 개선을 위해 많은 수의 히든 파라미터를 활용한다. 이 히든 파라미터들의 True/False 여부에 따라 예상치 못한 성능 문제가 발생하게 되는 것이다.

Oracle 10g에서 이런 류의 파라미터가 많다. _OPTIMIZER_TRANSITIVITY_RETAIN, _OPTIMIZER_COST_BASED_TRANSFORMATION, _OPTIMIZER_PUSH_PRED_COST_BASED, _GBY_HASH_AGGREGATION_ENABLED 등이 이에 해당한다. 경우에 따라서 이런 파라미터들을 다 False로 변경해야 하는 극단적인 경우도 발생한다. 때로는 OPTIMIZER_FEATURES_ENABLE을 과거 버전으로 회귀시킴으로써 발전된 Optimizer의 기능을 사용하지 못하게 막아버리는 경우도 허다하다.

비용(Cost)를 계산하는 방식의 변화도 종종 문제를 일으킨다. System Statistics와 CPU Model이 추가되면서 이전과는 다른 비용을 산출할 수 있고 이로 인해 실행 계획의 이상이 발생할 수 있다.

통계 정보 문제는 더 심각하다. 10g의 통계 정보 생성 방식이 9i와 다르기 때문에 10g에서 새로 통계 정보를 수집한 경우 실행 계획의 변화가 오는 경우가 발생할 수 있다. 통계 정보 방식의 미세한 차이점을 이해해야만 통계 정보 생성 방식을 최적화할 수 있다. DBMS_STATS 프로시저의 파라미터의 값의 기본값(Default)이 변경되는 것만으로 일부 쿼리는 극단적인 성능 저하를 일으킬 수 있다.

이런 모든 문제들을 사전에 방지하려면 역시 충분한 테스트를 수행하는 수 밖에 없다. 하지만 시간적/공간적 제약으로 완벽한 테스트를 수행한다는 것은 역시 불가능한 경우가 많을 것이다.

이런 실행 계획 이상 현상을 원천적으로 해결하기 위해 Oracle이 제안하는 기능이 "Stored Outline"이다. 즉 쿼리가 수행되는 윤곽(Outline)을 저장해두었다가 이를 사용함으로써 실행 계획 이상 현상을 막겠다는 것이다.

"Stored Plan"이 아니라 "Stored Outline"이라고 불리는 이유을 이해할 필요가 있겠다. 실행 계획을 저장하고 있을 것이라는 착각과는 달리 Stored Outline은 실행 계획을 생성하기 위한 "윤곽"만을 가지고 있다. 정확하게 말하면 실행 계획을 재현할 수 있도록 "힌트"를 저장하고 있다.
아래 쿼리 결과를 보면 이것을 잘 이해할 수 있다.

select name, category, sql_text from user_outlines;
==>
Name: SYS_OUTLINE_07122621581798118   
Category: DEFAULT   
Sql_Text: SELECT /*+ outline_proc */ COUNT(*) FROM PL_TEST

select * from user_outline_hints
where name = 'SYS_OUTLINE_07122621581798118';
==>
NAME                                            NODE    STAGE    JOIN_POS    HINT
SYS_OUTLINE_07122621581798118     1      1           1    FULL(@"SEL$1" "PL_TEST"@"SEL$1")
SYS_OUTLINE_07122621581798118     1      1           0    OUTLINE_LEAF(@"SEL$1")
SYS_OUTLINE_07122621581798118     1      1           0    FIRST_ROWS(1)
SYS_OUTLINE_07122621581798118     1      1           0    OPT_PARAM('_optimizer_mode_force' 'false')
SYS_OUTLINE_07122621581798118     1      1           0    OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
SYS_OUTLINE_07122621581798118     1      1           0    IGNORE_OPTIM_EMBEDDED_HINTS

Stored Outline을 이용하면 Oracle Upgrade에 의한 실행 계획 이상 현상을 대부분 방지할 수 있으며, 가장 손쉬운 방법이기도 한다. 하지만 실제로는 많이 사용되지 않는 것 같다.
아마 새로운 기능을 익히는데 대한 부담감도 있을 것이고 100% 방지는 되지 않는다는(어차피 힌트만 저장하고 있기 때문에) 불안감도 있을 수 있겠다. 혹은 특정 시스템에서의 실패담이 고착화되어 사용해서는 안되는 기능으로 인식되었는지도 모르겠다.

하지만 Oracle Upgrade시마다 히든 파라미터를 이전 버전과 동일한 수준으로 맞추어 Oracle의 새로운 기능을 사용하지 못한다든지, 통계 정보 수집을 이전 버전과 동일한 수준으로 해야한다든지하는 퇴행적인 방식보다는, Stored Outline을 사용하는 방식이 훨씬 체계적이고, 쉽다.

Stored Outline을 이용해 기본적인 실행 계획 호환성을 맞춘 다음, 쿼리 자체를 변경하는 작업은 여유를 가지고 진행할 수도 있다. 실제로 많이 사용되고 경험담이 공유되었으면 하는 바램이 있다.

PS)
Oracle 11g에서는 Stored Outline이 deprecated되었고(지원은 되지만 더이상 발전은 없음), SQL Plan Baseline이라는 기능이 추가되었다.





신고
Trackback 0 : Comment 0

Write a comment

티스토리 툴바