태터데스크 관리자

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

태터데스크 메시지

저장하였습니다.

dbms_xplan.display_cursor를 교묘하게 사용하기...

오라클 2008. 3. 13. 22:39
dbms_xplan.display_cursor 류의 함수를 이용하면 Runtime 시의 실행 계획을 간편하게 구할 수 있다는 사실은 이미 블로그에서 여러 차례 다룬바 있다.

아래에 이 기능을 교묘하고 지능적으로 이용하는 예가 있다.

UKJA@ukja10> -- Object를 생성한다.                                                                         
UKJA@ukja10> drop table t1 purge;

Table dropped.

Elapsed: 00:00:00.04
UKJA@ukja10>
UKJA@ukja10> create table t1(id int, name char(10));

Table created.

Elapsed: 00:00:00.01
UKJA@ukja10>
UKJA@ukja10> create index t1_idx on t1(id);

Index created.

Elapsed: 00:00:00.03
UKJA@ukja10>
UKJA@ukja10> insert into t1
  2  select level, 'name'
  3  from dual
  4  connect by level <= 10000;

10000 rows created.

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

Commit complete.

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

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.07
UKJA@ukja10> -- T1_IDX 인덱스를 사용하는 쿼리 몇 가지를 수행한다.
UKJA@ukja10> select count(*)
  2  from t1
  3  where id between 1 and 10;

  COUNT(*)                                                              
----------                                                             
        10                                                                            
Elapsed: 00:00:00.01
UKJA@ukja10>
UKJA@ukja10> select *
  2  from t1
  3  where id = 100;

        ID NAME                                                        
---------- ------------------------------                              
       100 name                                                        

Elapsed: 00:00:00.01
UKJA@ukja10>
UKJA@ukja10> select *
  2  from t1
  3  where id between 11 and 15;

        ID NAME                                                        
---------- ------------------------------                              
        11 name                                                        
        12 name                                                        
        13 name                                                        
        14 name                                                        
        15 name                                                           
Elapsed: 00:00:00.03
UKJA@ukja10>
UKJA@ukja10> -- T1_IDX 인덱스를 경유하는 모든 쿼리의 Runtime 실행 계획을 구하고 싶다면???
UKJA@ukja10> -- 아래와 같이 조인을 하면 된다.
UKJA@ukja10> select
  2           p.plan_table_output
  3  from
  4           (select distinct sql_id, child_number
  5               from v$sql_plan s
  6               where s.object_name = 'T1_IDX') s,
  7           table(dbms_xplan.display_cursor
  8               (s.sql_id, s.child_number, 'typical')) p
  9  ;


PLAN_TABLE_OUTPUT                                                      

--------------------------------------------------------------------------------------
SQL_ID  5t00d999pa91k, child number 0                                  
-------------------------------------                                  
select * from t1 where id = 100                                           

Plan hash value: 546753835                                                

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1     |     1 |    14 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T1_IDX |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------
                                                                          
Predicate Information (identified by operation id):                    
---------------------------------------------------                    
   2 - access("ID"=100)                                                               
                                                                       
SQL_ID  c8zp0app13y94, child number 0                                  
-------------------------------------                                  
select * from t1 where id between 11 and 15                                           
Plan hash value: 546753835                                                
--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |       |       |     3 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1     |     6 |    84 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T1_IDX |     6 |       |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------
           
Predicate Information (identified by operation id):                       
---------------------------------------------------                    
   2 - access("ID">=11 AND "ID"<=15)                                                  
SQL_ID  ft74mk1132zjd, child number 0                                  
-------------------------------------                                  
select /*+ no_parallel_index(t,T1_IDX) dbms_stats cursor_sharing_exact 
use_weak_name_resl dynamic_sampling(0) no_monitoring no_expand         
index(t,"T1_IDX") */ count(*) as nrw,count(distinct                    
sys_op_lbid(54875,'L',t.rowid)) as nlb,count(distinct "ID") as         
ndk,sys_op_countchg(substrb(t.rowid,1,15),1) as clf from "UKJA"."T1" t 
where "ID" is not null                                                                
Plan hash value: 2167531999                                            
---------------------------------------------------------------------------
| Id  | Operation        | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT |        |       |       |    27 (100)|          |
|   1 |  SORT GROUP BY   |        |     1 |    14 |            |          |
|*  2 |   INDEX FULL SCAN| T1_IDX | 10000 |   136K|    27   (4)| 00:00:01 |
---------------------------------------------------------------------------
           
Predicate Information (identified by operation id):                    
---------------------------------------------------                    
   2 - filter("ID" IS NOT NULL)                                                       

SQL_ID  6cjn5zr6mftft, child number 0                                  

-------------------------------------                                  
select count(*) from t1 where id between 1 and 10                         
Plan hash value: 383751810                                             
----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     | ---------------------------------------------------------------------------- |   0 | SELECT STATEMENT  |        |       |       |     2 (100)|          |          
|   1 |  SORT AGGREGATE   |        |     1 |     4 |            |          |          
|*  2 |   INDEX RANGE SCAN| T1_IDX |    10 |    40 |     2   (0)| 00:00:01 |          
----------------------------------------------------------------------------          
Predicate Information (identified by operation id):                    
---------------------------------------------------                    
   2 - access("ID">=1 AND "ID"<=10)                                                   
81 rows selected.
Elapsed: 00:00:00.25
UKJA@ukja10>
UKJA@ukja10> spool off

매우 간단하지 않은가? v$sql_plan 뷰와 table 함수의 결과를 조인하기만 하면 된다.
위의 방법을 잘 응용하면 매우 다양한 관점에서 데이터를 수집할 수 있다. 가령...

- Logical Reads 상위 Top 10 쿼리들의 Runtime Plan을 수집한다.
- 특정 Table을 Full Scan하는 쿼리들의 Runtime Plan을 수집한다.
- dbms_xplan.display_awr을 이용하면 과거 시점 기준으로도 데이터를 수집할 수 있다.

특정 상황에서는 많은 수작업을 줄여줄 수 있는 전략이다. 오라클이 제공하는 이 파워풀한 기능을 잘 활용할 수 있기를 바란다.
Trackbacks 0 : Comments 0

Write a comment