태터데스크 관리자

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

태터데스크 메시지

저장하였습니다.

Hint에 대해 당신이 모르는 것 - Oracle은 Hint를 무시하지 않는다.

오라클 2008.09.17 19:32
Hint를 사용하다 보면 "Oracle이 가끔 내 Hint를 무시하던데..."라는 의문을 갖게 된다.

하지만 놀랍게도 Oracle은 결코 Hint를 무시하지 않는다.

그렇다면 왜 Hint를 사용했음에도 불구하고 원하는 동작을 하지 않는 경우가 발생하는가?
Oracle은 Hint를 무조건 사용하려고 하지만 논리적으로 적용할 수 없는 상황을 만날 수 있다. 그런 경우에는 Hint에서 지정된 동작을 수행하지 못한다.

즉, Hint를 무시하는 것이 아니라 Hint를 고려했지만 사용 불가한 상황을 만났다는 것이 정확한 표현이다.

몇 가지 간단한 예를 통해 Hint가 무시되지 않는다는 사실을 증명해 볼려고 한다.

우선 다음과 같이 필요한 Object를 만든다.

-- create objects
drop table t1 purge;
drop table t2 purge;
drop table t3 purge;

create table t1(c1 int, c2 varchar2(10), c3 varchar2(10));
create table t2(c1 int, c2 int);
create table t3(c1 varchar2(10), c2 varchar2(10));

create index t1_n1 on t1(c1);
create index t1_n2 on t1(c2);
create index t2_n1 on t2(c1);

Data를 생성하되 ('A', 'a'), ('B', 'b')와 같이 짝이 되도록 만든다. 즉 c2 = 'A' and c3 = 'b' 조건은 실제 Row수가 0이 된다.

-- create correlated columns
insert into t1
select level, 'A', 'a'
from dual
connect by level <= 10000
;

insert into t1
select level+10000, 'B', 'b'
from dual
connect by level <= 1000
;

insert into t2
select level, level
from dual
connect by level <= 11000
;

insert into t3 values('A', 'a');
insert into t3 values('B', 'b');

commit;

@gather t1
@gather t2
@gather t3

아주 간단한 예부터 보자.
다음과 같이 INDEX_FFS Hint를 부여한다.

UKJA@ukja102> -- index_ffs hint                                               
UKJA@ukja102> explain plan for                                                
  2  select /*+ index_ffs(t1 t1(c1)) */
  3    c1 
  4  from t1       
  5  ;
                                                                              
Explained.                                                                    
                                                                              
Elapsed: 00:00:00.01 

하지만 Index Fast Full Scan이 아닌 Table Full Scan을 사용한다.
                                                        
UKJA@ukja102>                                                                 
UKJA@ukja102> @plan                                                           
                                                                              
PLAN_TABLE_OUTPUT                                                             
-------------------------------------------------------------------------------
Plan hash value: 3617692013                                                   
                                                                              
--------------------------------------------------------------------------    
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |    
--------------------------------------------------------------------------    
|   0 | SELECT STATEMENT  |      | 11000 | 55000 |     6   (0)| 00:00:01 |    
|   1 |  TABLE ACCESS FULL| T1   | 11000 | 55000 |     6   (0)| 00:00:01 |    
--------------------------------------------------------------------------    
                                                                              
8 rows selected.                                                              

왜? Hint가 무시된 것인가?
아니다. NULL 값을 포함할 수 없다는 B*Tree Index의 기본적인 제한으로 인해 논리적으로 Index를 사용할 수 없기 때문이다. 다음과 같이 NOT NULL 조건을 부여하면 Index를 사용한다.                                                       
                                                       
UKJA@ukja102> explain plan for            
  2  select /*+ index_ffs(t1 t1(c1)) */   
  3    c1                                 
  4  from t1                              
  5  where c1 is not null                 
  6  ;                                    
                                          
Explained.                                
                                          
Elapsed: 00:00:00.00                      
                                                         
                                                      
UKJA@ukja102>                                                                 
UKJA@ukja102> @plan                                                           
                                                                              
PLAN_TABLE_OUTPUT                                                             
-------------------------------------------------------------------------------
Plan hash value: 587075276                                                    
                                                                              
------------------------------------------------------------------------------
| Id  | Operation            | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |       | 11000 | 55000 |     6   (0)| 00:00:01 |
|*  1 |  INDEX FAST FULL SCAN| T1_N1 | 11000 | 55000 |     6   (0)| 00:00:01 |
------------------------------------------------------------------------------

Hint가 무시된 것이 아니다!!!

좀 더 복잡한 예들 보자.
다음과 같이 ORDERED Hint를 부여했다.

UKJA@ukja102> explain plan for                       
  2  select /*+ ordered */                           
  3    *                                             
  4  from                                            
  5    t1, t2                                        
  6  where                                           
  7    t1.c1 in (select c1 from t3 where c2 > :b1)   
  8  ;                                               
                                                     
Explained.                                           
                                                     
Elapsed: 00:00:00.01                                 

하지만 실제 Join Order는 T3->T1->T2로 전혀 예상치 않은 결과가 나온다.                                                                                       
UKJA@ukja102>                                                                         
UKJA@ukja102> @plan                                                                   
                                                                                      
PLAN_TABLE_OUTPUT                                                                     
---------------------------------------------------------------------------------------
Plan hash value: 1938539998                                                           
                                                                                      
--------------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       | 22000 |   429K|    16   (7)| 00:00:01 |
|   1 |  MERGE JOIN CARTESIAN        |       | 22000 |   429K|    16   (7)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1    |     1 |     8 |     2   (0)| 00:00:01 |
|   3 |    NESTED LOOPS              |       |     2 |    24 |     6  (17)| 00:00:01 |
|   4 |     SORT UNIQUE              |       |     2 |     8 |     3   (0)| 00:00:01 |
|*  5 |      TABLE ACCESS FULL       | T3    |     2 |     8 |     3   (0)| 00:00:01 |
|*  6 |     INDEX RANGE SCAN         | T1_N1 |     1 |       |     1   (0)| 00:00:01 |
|   7 |   BUFFER SORT                |       | 11000 | 88000 |    14   (8)| 00:00:01 |
|   8 |    TABLE ACCESS FULL         | T2    | 11000 | 88000 |     5   (0)| 00:00:01 |
--------------------------------------------------------------------------------------
                                                                                      
Hint가 무시된 것인가?
그렇지 않다. Subquery Unnesting에 의해 Subquery가 Main Query안으로 들어가면서 From 절의 마치 T3, T1, T2 인 것처럼 변환되었기 때문이다.

그 증거로 NO_UNNEST Hint를 이용해서 Subquery Unnesting을 막으면 Hint가 동작한다.
                                                                                      
UKJA@ukja102> explain plan for                                     
  2  select /*+ ordered */                                         
  3    *                                                           
  4  from                                                          
  5    t1, t2                                                      
  6  where                                                         
  7    t1.c1 in (select /*+ no_unnest */ c1 from t3 where c2 > :b1)
  8  ;                                                             
                                                                   
Explained.                                                         
                                                                   
Elapsed: 00:00:00.00                                               
                                                            

UKJA@ukja102>                                                                         
UKJA@ukja102> @plan                                                                   
                                                                                      
PLAN_TABLE_OUTPUT                                                                     
---------------------------------------------------------------------------------------
Plan hash value: 712372556                                                            
                                                                                      
------------------------------------------------------------------------------        
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |        
------------------------------------------------------------------------------        
|   0 | SELECT STATEMENT      |      | 11000 |   171K|  2635K  (1)| 08:47:08 |        
|*  1 |  FILTER               |      |       |       |            |          |        
|   2 |   MERGE JOIN CARTESIAN|      |   121M|  1846M| 49259   (3)| 00:09:52 |        
|   3 |    TABLE ACCESS FULL  | T1   | 11000 | 88000 |     6   (0)| 00:00:01 |        
|   4 |    BUFFER SORT        |      | 11000 | 88000 | 49253   (3)| 00:09:52 |        
|   5 |     TABLE ACCESS FULL | T2   | 11000 | 88000 |     4   (0)| 00:00:01 |        
|*  6 |   TABLE ACCESS FULL   | T3   |     1 |     4 |     3   (0)| 00:00:01 |        
------------------------------------------------------------------------------        

더 좋은 방법은 LEADING Hint를 사용하는 것이다.
                                                                                      
UKJA@ukja102> explain plan for                       
  2  select /*+ leading(t1 t2) */                    
  3    *                                             
  4  from                                            
  5    t1, t2                                        
  6  where                                           
  7    t1.c1 in (select c1 from t3 where c2 > :b1)   
  8  ;                                               
                                                     
Explained.                                           
                                                     
Elapsed: 00:00:00.00                                 
                                                        

UKJA@ukja102>                                                                         
UKJA@ukja102> @plan                                                                   
                                                                                      
PLAN_TABLE_OUTPUT                                                                     
---------------------------------------------------------------------------------------
Plan hash value: 564500826                                                           
------------------------------------------------------------------------------        
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |        
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      | 11000 |   214K| 49878   (4)| 00:09:59 |        
|*  1 |  HASH JOIN RIGHT SEMI |      | 11000 |   214K| 49878   (4)| 00:09:59 |        
|*  2 |   TABLE ACCESS FULL   | T3   |     2 |     8 |     3   (0)| 00:00:01 |        
|   3 |   MERGE JOIN CARTESIAN|      |   121M|  1846M| 49259   (3)| 00:09:52 |        
|   4 |    TABLE ACCESS FULL  | T1   | 11000 | 88000 |     6   (0)| 00:00:01 |        
|   5 |    BUFFER SORT        |      | 11000 | 88000 | 49253   (3)| 00:09:52 |        
|   6 |     TABLE ACCESS FULL | T2   | 11000 | 88000 |     4   (0)| 00:00:01 |        
------------------------------------------------------------------------------

DYNAMIC_SAMPLING Hint도 종종 오해를 불러 일으킨다.

아래 Query를 보면 Oracle은 Correlated Column에 대해서는 완전한 오동작을 하는 것을 알 수 있다.

UKJA@ukja102> explain plan for      
  2  select                         
  3    *                            
  4  from t2,                       
  5      (select                    
  6          c1, c2                 
  7      from t1                    
  8      where t1.c2 = 'A' and      
  9          t1.c3 = 'b'            
 10      ) v                        
 11  where t2.c1 = v.c1             
 12  ;                              
                                    
Explained.                          
                                    
Elapsed: 00:00:00.00                
                                  

UKJA@ukja102>                                                  
UKJA@ukja102> @plan                                            
                                                               
PLAN_TABLE_OUTPUT                                              
----------------------------------------------------------------
Plan hash value: 1838229974                                    
                                                               
----------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |  2750 | 44000 |    13   (8)|
|*  1 |  HASH JOIN         |      |  2750 | 44000 |    13   (8)|
|*  2 |   TABLE ACCESS FULL| T1   |  2750 | 22000 |     6   (0)|
|   3 |   TABLE ACCESS FULL| T2   | 11000 | 88000 |     6   (0)|
----------------------------------------------------------------

실제 Row 수는 "0"건이지만 무려 2750건을 예측한다.
이때 사용할 수 있는 Hint가 DYANMIC_SAMPLING Hint이다.

UKJA@ukja102> explain plan for                
  2  select                                   
  3    *                                      
  4  from t2,                                 
  5      (select /*+ dynamic_sampling(t1 4) */
  6          c1, c2                           
  7      from t1                              
  8      where t1.c2 = 'A' and                
  9          t1.c3 = 'b'                      
 10      ) v                                  
 11  where t2.c1 = v.c1                       
 12  ;                                        
                                              
Explained.                                    
                                              
Elapsed: 00:00:00.01                          

다음과 같이 완벽하게 예측해낸다.
                                                 
UKJA@ukja102>                                                            
UKJA@ukja102> @plan                                                      
                                                                         
PLAN_TABLE_OUTPUT                                                        
--------------------------------------------------------------------------
Plan hash value: 1409599701                                              
                                                                         
--------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    16 |     8   (0)|
|   1 |  TABLE ACCESS BY INDEX ROWID| T2    |     1 |     8 |     2   (0)|
|   2 |   NESTED LOOPS              |       |     1 |    16 |     8   (0)|
|*  3 |    TABLE ACCESS FULL        | T1    |     1 |     8 |     6   (0)|
|*  4 |    INDEX RANGE SCAN         | T2_N1 |     1 |       |     1   (0)|
--------------------------------------------------------------------------

하지만 다음과 같은 Query는 어떤가?

UKJA@ukja102> explain plan for                                  
  2  select /*+ dynamic_sampling(t1 4) */                     
  3    *                                                        
  4  from                                                       
  5    t1                                                       
  6  where                                                      
  7    c2 = 'A' and                                             
  8    c3 = (select max(c2) from t3)                            
  9  ;                                                          
                                                                
Explained.                                                      
                                                                
Elapsed: 00:00:00.00                                            

실제 Row 수는 0건에 불과하지만 5000건으로 예측한다. Dynamic Sampling이 동작하지 않은 것처럼 보인다.
                                          
UKJA@ukja102>                                                   
UKJA@ukja102> @plan                                             
                                                               
PLAN_TABLE_OUTPUT                                               
-----------------------------------------------------------------
Plan hash value: 3428387421                                     
                                                               
-----------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)|
-----------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |  2750 | 22000 |     9   (0)|
|*  1 |  TABLE ACCESS FULL  | T1   |  2750 | 22000 |     6   (0)|
|   2 |   SORT AGGREGATE    |      |     1 |     2 |            |
|   3 |    TABLE ACCESS FULL| T3   |     2 |     4 |     3   (0)|
-----------------------------------------------------------------


아래 예는 또 어떤가?

UKJA@ukja102> explain plan for                                    
  2  select /*+ dynamic_sampling(t1 4) dynamic_sampling(t2 4) */  
  3    *                                                          
  4  from                                                         
  5    t1, t2                                                     
  6  where                                                        
  7    t1.c1 = t2.c1+100000                                       
  8  ;                                                            
                                                                  
Explained.                                                        
                                                                  
Elapsed: 00:00:00.14  


역시 실제 Row수는 0건이지만 무려 11000건을 예상해버린다.
                                           
UKJA@ukja102>                                                  
UKJA@ukja102> @plan                                            
                                                               
PLAN_TABLE_OUTPUT                                              
----------------------------------------------------------------
Plan hash value: 1838229974                                    
                                                               
----------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------
|   0 | SELECT STATEMENT   |      | 11000 |   171K|    13   (8)|
|*  1 |  HASH JOIN         |      | 11000 |   171K|    13   (8)|
|   2 |   TABLE ACCESS FULL| T1   | 11000 | 88000 |     6   (0)|
|   3 |   TABLE ACCESS FULL| T2   | 11000 | 88000 |     6   (0)|
----------------------------------------------------------------

DYNAMIC_SAMPLING Hint를 무시한 것인가?
전혀 그렇지 않다. 그렇다면 왜 이런 현상이 발생하는가?

Oracle의 Dynamic Sampling Hint는 Table Level이다. 즉, Query Level이 아니다. 개개의  Table별로 Dynamic Sampling이 이루어진다.

따라서 Join이 이루어지거나 Sampling시에 알수 없는 값이나 형식이(위의 예에서는 Subquery) 사용되면 전혀 효과가 없다. 즉, DYNAMIC_SAMPLING Hint를 무시한 것이 아니라 Dynamic Sampling 자체의 한계로 인해 전혀 도움이 안되는 Sampling만 수행한 것이다.

Oracle은 절대 Hint를 무시하지 않는다. 우리가 문맥(Context)에 맞지 않게 Hint를 사용했을 경우 마치 무시하는 것처럼 보일 뿐이다.
신고
tags :
Trackback 0 : Comments 2
  1. oraking 2008.10.02 00:12 신고 Modify/Delete Reply

    논리적으로, 문법적으로 전혀 문제가 없는데도 오라클이 Hint를 무시하는 경우는 종종 발생합니다. 오라클 메타링크 문서 "Why is my hint ignored?"를 보면 "They should not be ignored and should be obeyed at all times by the optimizer."라고 표현하고 있어 정말로 "Oracle은 결코 Hint를 무시하지 않는다."라고 생각할 수 있는데, 꼼꼼히 읽어 보면 "그럼에도 불구하고 무시된다면 버그일 것이다"라고 써 있습니다. 말장난에 가깝죠.

  2. 욱짜 2008.10.02 12:45 신고 Modify/Delete Reply

    10053 Trace나 기타 어떤 방법으로도 Hint가 무시되는 이유를 알 수 없다면 Bug라고 할 수 있을 겁니다. 불행히도 10053 Trace를 봐도 이유를 알 수 없는 경우가 있긴 하지만요.

Write a comment

티스토리 툴바