태터데스크 관리자

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

태터데스크 메시지

저장하였습니다.

'gahter_plan_statistics'에 해당되는 글 1건

  1. 2007.11.21 [오라클 팁] GATHER_PLAN_STATISTICS 힌트 (1)

[오라클 팁] 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

티스토리 툴바