태터데스크 관리자

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

태터데스크 메시지

저장하였습니다.

'explain plan'에 해당되는 글 2건

  1. 2008.04.22 Explain Plan과 Clustering Factor
  2. 2007.11.21 [오라클 팁] GATHER_PLAN_STATISTICS 힌트 (1)

Explain Plan과 Clustering Factor

오라클 2008.04.22 15:35
Clustering Factor(이하 CF)는 Index Scan과 Table Scan간의 선택에 있어서 가장 중요한 비용 요소 중 하나이다. 보통 Index Scan을 통해 데이터 추출의 비용(Cost)은 다음 공식을 따른다.

Index Scan Cost =
  BLevel +
  Ceiling(Leaf Blocks * Index Selectivity) +
  Ceiling(Clusteing Factor * Table Selectivity)


보통 Blevel은 1~3 사이의 적은 값이고, Index의 Leaf Block 수는 대부분의 경우 Table Block수보다 작다. 따라서 Clustering Factor가 주는 영향은 매우 클 수 밖에 없다.

여기서 한가지 조금은 작위적인 퀴즈를 만들어 본다. Explain Plan에서 실행 계획과 예상 비용만을 보고 CF가 좋은지 안좋은지 알 수 있을까?

정답은 어느 정도 가능하다는 것이다. 아래 테스트 결과를 보자.

UKJA@ukja10>                                                                   
UKJA@ukja10> -- create objects
UKJA@ukja10> drop table t_clsf purge;

Table dropped.

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

Table created.

Elapsed: 00:00:00.01
UKJA@ukja10>
UKJA@ukja10> create index t_clsf_i1 on t_clsf(c1);

Index created.

Elapsed: 00:00:00.01
UKJA@ukja10> create index t_clsf_i2 on t_clsf(c2);

Index created.

Elapsed: 00:00:00.00
UKJA@ukja10> -- 데이터 양은 동일하지만 CF만 차이가 나는 데이터를 만든다
UKJA@ukja10> -- c1 : same order as table, c2 : random
UKJA@ukja10> insert into t_clsf
  2  select rownum, lvl
  3  from
  4    (select level lvl
  5    from dual connect by level <= 10000
  6    order by dbms_random.random)
  7  ;

10000 rows created.

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

Commit complete.

Elapsed: 00:00:00.00
UKJA@ukja10>
UKJA@ukja10> -- gather stats
UKJA@ukja10> exec dbms_stats.gather_table_stats(user, 't_clsf', -
>   method_opt=>'for all columns size 1', -
>   cascade=>true);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.34
UKJA@ukja10>
UKJA@ukja10> select
  2           table_name, index_name, blevel, leaf_blocks,
  3           distinct_keys, clustering_factor, sample_size,
  4           to_char(last_analyzed,'yyyy/mm/dd hh24:mi:ss') as last_anal
  5  from
  6           dba_ind_statistics
  7  where
  8           index_name = upper('&I_NAME') or
  9           table_name = upper('&I_NAME')
 10  ;
old   8:     index_name = upper('&I_NAME') or
new   8:     index_name = upper('t_clsf') or
old   9:     table_name = upper('&I_NAME')
new   9:     table_name = upper('t_clsf')

TABLE_NAME           INDEX_NAME               CLUSTERING_FACTOR
-------------------- --------------------     ----------
T_CLSF               T_CLSF_I1                   18  <-- Good CF
T_CLSF               T_CLSF_I2                 9436  <-- Ouch. Bad CF
                                                                               

Elapsed: 00:00:00.01
UKJA@ukja10>
UKJA@ukja10> -- compare the cost
UKJA@ukja10> explain plan for
  2  select /*+ good clsf index(t_clsf) */ *
  3  from t_clsf
  4  where c1 between 1 and 100;

Explained.

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

PLAN_TABLE_OUTPUT                                                              
------------------------------------------------------------------------------
Plan hash value: 4073175222
------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)|
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |   100 |   700 |     3   (0)|
|   1 |  TABLE ACCESS BY INDEX ROWID| T_CLSF    |   100 |   700 |     3   (0)|
|*  2 |   INDEX RANGE SCAN          | T_CLSF_I1 |   100 |       |     2   (0)|
------------------------------------------------------------------------------
<-- CF가 좋은 경우 Index에서 Table로의 Lookup에서 비용이 "1"(3-2)만큼만 증가한다.                                                                          
Predicate Information (identified by operation id):
---------------------------------------------------                            
   2 - access("C1">=1 AND "C1"<=100)

14 rows selected.

Elapsed: 00:00:00.03
UKJA@ukja10>
UKJA@ukja10> explain plan for
  2  select /*+ bad clsf index(t_clsf) */ *
  3  from t_clsf
  4  where c2 between 1 and 100;

Explained.

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

PLAN_TABLE_OUTPUT                                                              
------------------------------------------------------------------------------
Plan hash value: 3421580928
------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)|
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |   100 |   700 |    97   (0)|
|   1 |  TABLE ACCESS BY INDEX ROWID| T_CLSF    |   100 |   700 |    97   (0)|
|*  2 |   INDEX RANGE SCAN          | T_CLSF_I2 |   100 |       |     2   (0)|
------------------------------------------------------------------------------
<-- CF가 안좋은 경우 Index에서 Table로의 Lookup에서 비용이 "95"(97-2)이나 증가한다.                                                                              
Predicate Information (identified by operation id):
---------------------------------------------------                            
   2 - access("C2">=1 AND "C2"<=100)

14 rows selected.

Elapsed: 00:00:00.03
UKJA@ukja10>
UKJA@ukja10> spool off

위의 예를 보면 CF가 불량한 경우 Index Range Scan에서 Table Access By Index Rowid로 넘어가는 과정에서 큰 비용차가 발생하는 것을 알 수 있다. 이런 경우 불량한 CF를 의심해볼 수 있고, 통계 정보를 통해서 이를 확인할 수 있을 것이다. 운이 좋으면 왜 우량한 Index를 두고도 Oracle이 굳이 Table Full Scan을 할려고 하는지 원인을 찾을 수도 있을 것이다.

한 가지 주의할 것은 이런 현상이 항상 CF에 의해서만 발생하는 것은 아니라는 것이다. Table에서 Filtering을 수행하는 과정에서 비용이 증가하는 경우도 있기 때문이다.

CF에 대한 기본적인 설명은 Exem Wiki를 참조한다.


신고
Trackback 0 : Comment 0

Write a comment


[오라클 팁] GATHER_PLAN_STATISTICS 힌트

오라클 2007.11.21 11:42
Oracle 10g에 추가된 힌트 중 아주 재밌는 것을 알게 되어 공유한다.

힌트 명은 GATHER_PLAN_STATISTICS 이다. GATHER_PLAN_STATISTICS 힌트는 "Row source execution" 정보를 활성화한다.

Row source execution 이란 Execute Plan에서 보는 Row수의 예측 값이 아닌 실제 실행 과정에서 추출된 Row 수를 보여주는 것을 의미한다. SQL Trace나 10046 Event를 이용해 Tkprof Report를 추출해본 경험이 있다면 아마 잘 알 것이다.

이 힌트의 개념을 이해하려면 일단 오라클에서 실행 정보를 보는 방법이 다음과 같이 두 개로 나누어진다는 것을 이해할 필요가 있다.
- Explain Plan
- Row Source Operation

1. Explain Plan
Explain Plan은 "Explain Plan" 명령을 통해 생성되는 실행 계획의 "예측" 정보를 의미한다. 다음 예제를 보자.

SQL> explain plan for
select * from delete_test where id < 1000;

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
Plan hash value: 1827500787

-----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                 |     1 |     8 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| DELETE_TEST     |     1 |     8 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | DELETE_TEST_IDX |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ID"<1000)

위의 정보는 통계 정보에 기반한 "예측" 정보만을 보여준다.

2. Rowsource execution
SQL Trace나 10046 Event를 이용하면 Rowsource operation, 즉 실제 수행하면서 추출한 로우수를 알 수 있다. 아래 예를 보자.

SQL> alter session set events '10046 trace name context forever, level 12';
SQL> explain plan for
select * from delete_test where id < 1000;
SQL> alter session set events '10046 trace name context off';

Rows     Row Source Operation
-------  ---------------------------------------------------
   1000  TABLE ACCESS BY INDEX ROWID DELETE_TEST (cr=85 pr=0 pw=0 time=2077 us)
   1000   INDEX RANGE SCAN DELETE_TEST_IDX (cr=43 pr=0 pw=0 time=1054 us)

Explain Plan 정보와 Row Sourc Operation을 비교해보면 예측 로우 수와 실제 로우수가 큰 차이를 보이는 것을 확인할 수 있다. 이런 정보들을 잘 확인하면 왜 Oracle이 잘못된 실행 계획을 따르는지를 추론할 수 있는 근거가 된다.

이런 기능을 한번에 할 수 있도록 해주는 것이 GATHER_PLAN_STATISTICS 힌트이다. 아래 예를 보자.

SQL> select
/*+ gather_plan_statistics */
 *
from delete_test where id < 1000;

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
-- null, null 값은 가장 최근에 실행된 쿼리를 의미한다. 만일 특정 쿼리를 지정하고 싶다면 SQL ID와 Child Number를 주면 된다.

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  04zt89r2fw2uw, child number 0
-------------------------------------
select /*+ gather_plan_statistics */  * from delete_test where id < 1000

Plan hash value: 1827500787

---------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------
|   1 |  TABLE ACCESS BY INDEX ROWID| DELETE_TEST     |      1 |      1 |   1000 |00:00:00.01 |     140 |
|*  2 |   INDEX RANGE SCAN          | DELETE_TEST_IDX |      1 |      1 |   1000 |00:00:00.01 |      70 |


Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ID"<1000)

위의 예를 보면 예측 로우수(E-Rows)와 실제 로우수(A-Rows) 사이에 큰 차이가 있다는 것을 알 수 있다. 이런 큰 차이로 인해 자칫 Optimizer가 잘못된 실행 계획을 선택할 확률이 높아진다.
한 가지 주의해서 볼 항목은 "Starts" 항목이다. Starts는 Row Source Operation이 수행된 회숫를 의미한다. 가령 Fetch를 수행한 회수로 이해할 수 있다. 가령 Starts=10, E-Rows=10, A-Rows=100 이었다고 하면 10*10 = 100 이므로 실제로는 예측 로우수와 실제 로우수가 일치하는 경우에 해당한다.

지 금까지 Explain Plan과 Rowsource Operation의 결과를 수동으로 비교하는 번거로운 작업을 많이 했다면, 이 새로운 GATHER_PLAN_STATISTICS 힌트를 이용해 좀 더 효율적인 SQL 튜닝 작업을 시도해보기 바란다.

엑셈 Knowledge Base
http://wiki.ex-em.com/index.php/GATHER_PLAN_STATISTICS

PS1) 복잡한 쿼리에서는 정확한 값이 나오지 않는 경우가 있다는 보고가 있다. 이 경우에는 STATISTICS_LEVEL = ALL을 줌으로써 해결 가능하다.

PS2) 한번 수집한 통계 정보가 계속 재활용되는 듯 하다. 가령 다음과 같이 했을 때...

-- 첫번째
SQL> select /*+ gather_plan_statistics */  * from delete_test t where id < 1000;
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

-- 통계 정보 수집
SQL> exec dbms_stats.gather_table_stats(user, 'DELETE_TEST', cascade=>true);

-- 두번째
SQL> select /*+ gather_plan_statistics */  * from delete_test t where id < 1000;
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

두 번째 경우에는 갱신된 통계 정보에 의한 예측 로우수가 나와야함에도 불구하고 계속 이전의 값이 나오는 경우가 있다. 이 경우에는 Shared Pool을 Flush하거나, Sql Id와 Child Number를 지정, 또는 SQL Text를 살짝 바꾸어서 통계 정보가 새로 수집되게 하는 방법을 사용할 수 있다.

참고 자료)
http://jonathanlewis.wordpress.com/2006/11/09/dbms_xplan-in-10g

http://jonathanlewis.wordpress.com/2007/04/26/heisenberg/
신고
Trackbacks 2 : Comment 1
  1. 욱짜 2008.02.18 16:44 신고 Modify/Delete Reply

    Oracle 10gR1에서는 select * from table(dbms_xplan.display_cursor(null,null,'RUNSTATS_LAST')); 와 같이 사용...

Write a comment

티스토리 툴바