태터데스크 관리자

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

태터데스크 메시지

저장하였습니다.

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

티스토리 툴바