태터데스크 관리자

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

태터데스크 메시지

저장하였습니다.

FIRST_ROWS vs. FIRST_ROWS_N 그리고 Cost의 개념

오라클 2008.10.23 10:39
최근에 다음과 같은 질문을 받았다. 

FIRST_ROWS 와 FIRST_ROWS_N은 어떤 기준으로 실행 계획을 세우는가?

실제 질문은 더 복잡했다. 정리하면 다음과 같다.

- ALL_ROWS는 Throughput 기준, FIRST_ROWS는 Response Time 기준이다.
- 그렇다면 ALL_ROWS는 Cost를 기준으로 선택할 거 같고
- FIRST_ROWS는 Response Time을 기준으로 선택할 것 같다.
- Response Time을 어떻게 계산하고 어떻게 관찰할 수 있는가?

정답을 말하면 Optimizer가 실행 계획을 선택하는 기준은 항상 "Cost"라는 것이다. 
그렇다면 FIRST_ROWS가 Response Time 기준이라는 것은 개뼈다귀같은 소리인가?

사람들이 잘 모르는 진실은 Cost = Response Time 이라는 것이다. 적어도 Oracle 9i부터 Cost는 Response Time으로 바뀌었으며(하지만 Default로는 I/O) 10g부터는 우리도 모르는 사이에 Response Time으로 바뀌었다. 

더 정확하게 말하면 Cost 는 다음과 같이 계산된다. 
 
Cost = Total Estimated Response Time / Average Single Block I/O Time 
        = (Total Multi Block I/O Time + Total Single Block I/O Time + Total CPU Time) / Average Single Block I/O Time

다시 원래 질문으로 돌아가서, 그렇다면 Oracle은 FIRST_ROWS와 FIRST_ROWS_N 을 어떻게 처리하는가? 

간단한 Test로 쉽게 알 수 있다. 우선 아래와 같이 Object를 만든다. 

drop table t1 purge;

create table t1(c1 int, c2 int);

create index t1_n1 on t1(c1)
;

insert into t1
select mod(level, 100)+1, level
from dual
connect by level <= 10000
;

@gather t1

ALL_ROWS, FIRST_ROWS, FIRST_ROWS(1), FIRST_ROWS(10), FIRST_ROWS(100) Hint를 사용해 Cost가 어떻게 계산되는지 확인한다. Cost의 변화에 주목하자.

ALL_ROWS의 경우에는 Cost가 12이며 이것은 Table Full Scan의 Cost와 일치한다.

explain plan for
select /*+ all_rows */
  *
from t1
where c1 = 1
;

@plan
---------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   100 |   600 |    12   (0)|
|*  1 |  TABLE ACCESS FULL| T1   |   100 |   600 |    12   (0)|
---------------------------------------------------------------

FIRST_ROWS의 경우에는 Cost는 18이다. 즉 Index를 경유하는 경우에는 Cost가 18로 Table Full Scan보다 높다.

그렇다면 왜 Oracle이 Index를 선택하는가? 그 이유는 FIRST_ROWS는 실제로는 Rule Base로 동작하기 때문이다. 즉, Cost가 더 높더라도 Index가 있다면 Index를 선택하는 Rule을 사용한다. 

explain plan for
select /*+ first_rows */
  *
from t1
where c1 = 1
;

@plan
--------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |   100 |   600 |    18   (0)|
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |   100 |   600 |    18   (0)|
|*  2 |   INDEX RANGE SCAN          | T1_N1 |   100 |       |     1   (0)|
--------------------------------------------------------------------------

반면 FIRST_ROWS_1의 경우에는 Cost가 "2"로 줄어든다. 왜? 
Oracle은 하나의 Row만 Fetch한다는 가정하에서 모든 Cost를 다시 계산하기 때문이다. 이 결과 Index Scan의 Cost가 Table Full Scan의 Cost보다 낮게 나왔을 뿐이다. 

explain plan for
select /*+ first_rows(1) */
  *
from t1
where c1 = 1
;

@plan
--------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     2 |    12 |     2   (0)|
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     2 |    12 |     2   (0)|
|*  2 |   INDEX RANGE SCAN          | T1_N1 |       |       |     1   (0)|
--------------------------------------------------------------------------

FIRST_ROWS_10의 경우에는 Index를 경유하는 Cost가 "3"으로 약간 증가했다. 10건의 Row를 Fetch한다고 가정하고 Cost를 다시 계산하기 때문이다.

explain plan for
select /*+ first_rows(10) */
  *
from t1
where c1 = 1
;

@plan
--------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |    10 |    60 |     3   (0)|
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |    10 |    60 |     3   (0)|
|*  2 |   INDEX RANGE SCAN          | T1_N1 |       |       |     1   (0)|
--------------------------------------------------------------------------

FIRST_ROWS_100을 사용하면? 웁스. Table Full Scan으로 바뀐다. 100개의 Row를 Fetch하는 경우 Index Scan의 Cost가 12보다 더 커진다는 것을 의미한다. 

explain plan for
select /*+ first_rows(100) */
  *
from t1
where c1 = 1
;

@plan
---------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   100 |   600 |    12   (0)|
|*  1 |  TABLE ACCESS FULL| T1   |   100 |   600 |    12   (0)|
---------------------------------------------------------------

10053 Trace를 이용하면 FIRST_ROWS와 FIRST_ROWS_N이 얼마나 다른 방법을 사용하는지 잘 알 수 있다. 

@ukja102

@trace_on 10053 1

select /*+ first_rows */
  *
from t1
where c1 = 1
;

@trace_off
@trace_file
ho copy &trace_file trc1.out

@ukja102

@trace_on 10053 1

select /*+ first_rows(1) */
  *
from t1
where c1 = 1
;

@trace_off
@trace_file
ho copy &trace_file trc2.out

@diff trc1.out trc2.out


FIRST_ROWS의 경우에는 다음과 같은 정보가 출력된다.

SINGLE TABLE ACCESS PATH
  Column (#1): C1(NUMBER)
    AvgLen: 3.00 NDV: 100 Nulls: 0 Density: 0.01 Min: 1 Max: 100
  Table: T1  Alias: T1     
    Card: Original: 10000  Rounded: 100  Computed: 100.00  Non Adjusted: 100.00
  Access Path: TableScan
    Cost:  12.26  Resp: 12.26  Degree: 0
      Cost_io: 12.00  Cost_cpu: 2144429
      Resp_io: 12.00  Resp_cpu: 2144429
  Access Path: index (AllEqRange)
    Index: T1_N1
    resc_io: 18.00  resc_cpu: 166036
    ix_sel: 0.01  ix_sel_with_filters: 0.01
    Cost: 18.02  Resp: 18.02  Degree: 1
  Best:: AccessPath: IndexRange  Index: T1_N1
         Cost: 18.02  Degree: 1  Resp: 18.02  Card: 100.00  Bytes: 0

위의 정보는 Index Scan의 Cost가 18로 Table Full Scan의 12보다 훨씬 높지만, 내부적인 Rule에 의해서 Index를 선택했다는 것을 의미한다. Rule Base로 동작한 것이다.

FIRST_ROWS_1의 경우에는 다음과 같은 정보가 출력된다.

SINGLE TABLE ACCESS PATH
  Table: T1  Alias: T1     
    Card: Original: 10000  Rounded: 100  Computed: 100.00  Non Adjusted: 100.00
  Access Path: TableScan
    Cost:  12.26  Resp: 12.26  Degree: 0
      Cost_io: 12.00  Cost_cpu: 2144429
      Resp_io: 12.00  Resp_cpu: 2144429
  Access Path: index (AllEqRange)
    Index: T1_N1
    resc_io: 18.00  resc_cpu: 166036
    ix_sel: 0.01  ix_sel_with_filters: 0.01
    Cost: 18.02  Resp: 18.02  Degree: 1
  Best:: AccessPath: TableScan
         Cost: 12.26  Degree: 1  Resp: 12.26  Card: 100.00  Bytes: 0

...

SINGLE TABLE ACCESS PATH (First K Rows)
  Table: T1  Alias: T1     
    Card: Original: 100  Rounded: 2  Computed: 1.59  Non Adjusted: 1.59
  Access Path: TableScan
    Cost:  2.00  Resp: 2.00  Degree: 0
      Cost_io: 2.00  Cost_cpu: 27161
      Resp_io: 2.00  Resp_cpu: 27161
  Access Path: index (AllEqRange)
    Index: T1_N1
    resc_io: 2.00  resc_cpu: 15833
    ix_sel: 0.015873  ix_sel_with_filters: 0.015873
    Cost: 2.00  Resp: 2.00  Degree: 1
  Best:: AccessPath: IndexRange  Index: T1_N1
         Cost: 2.00  Degree: 1  Resp: 2.00  Card: 1.59  Bytes: 6


위의 결과를 보면 FIRST_ROWS_1을 고려하지 않은 Cost와 FIRST_ROWS_1을 고려한(하나의 Row만 Fetch한다고 가정) Cost를 별도로 계산한 후 가장 저렴한 Cost를 선택한다는 것을 알 수 있다. 완벽하게 Cost Based인 셈이다. 

위의 Test 결과를 해독하기 귀찮은 분들을 위해 결론을 정리하면 다음과 같다.

- Cost는 Estimated Response Time의 다른 말이다. 
- FIRST_ROWS는 내부적으로 Rule Based의 속성을 가지고 있다. 이런 이유 때문에 9i에서는 deprecated되었다.
- FIRST_ROWS_N은 완벽하게 Cost Based로 동작한다.



Trackback 0 : Comments 10
  1. Moon 2008.10.23 14:27 신고 Modify/Delete Reply

    질문 드렸던 사람입니다. 우선 바쁘신 와중에도 답변해주셔서 감사드립니다.^^

    추가적인 질문과 의문사항을 올려봅니다.
    제가 처음 생각했던 내용과 거의 일치하고 테스트 케이스도 거의 비슷해 보자마자 이해할수는 있었습니다.
    다만 빠른 답변을 주시느라 몇가지 놓치신것이 있는듯합니다.

    제가 처음 질문을 드리기 전에 설명해주신 케이스 이외에 조금 더 여러가지 상황을 테스트 해보았습니다.
    (코멘트로 달려니 테스트한 케이스나 트레이스 결과 등을 올리기가 좀 그렇군요. 내용이 길어지다보니..^^)

    그러던 중 알게 된 것이 FIRST_ROWS_n 모드에서, 결과행이 n보다 작거나 같은 경우엔 실제로는 FIRST_ROWS_n가 적용이 되질 않습니다.
    본문 글의 설명중
    explain plan for select /*+ first_rows(100) */ * from t1 where c1 = 1;
    의 실행시 테이블 스캔으로 되는것은 맞지만, 그 당시의 10053 Trace를 확인해보셨는지요?
    이 SQL문의 실행 결과행이 100행이다보니
    'SINGLE TABLE ACCESS PATH (First K Rows)' 의 계산은 하지 않습니다.
    단지 ALL_ROWS 모드와 같은 'SINGLE TABLE ACCESS PATH'계산만을 실행하고, 그때 Table Scan의 Cost가 낮음으로 인해 Table Scan이 되고 있습니다.
    (1) explain plan for select /*+ first_rows(100) */ * from t1 where c1 = 1; -> first_rows_100 미적용됨
    (2) explain plan for select /*+ first_rows(100) */ * from t1 where c1 >= 1; -> first_rows_100 적용됨
    같은 테스트 케이스에서 (1)은 Table scan을 (2)는 Index scan을 하게 됩니다.
    (보통과는 정반대의 상황이로군요...^.^)

    그리고 (2)의 상황에서 문제점이 하나 더 나타나는데 (2)의 10053 트레이스를 확인하면
    Index scan의 Cost가 Table scan보다 높음에도 불구하고 Index scan을 사용하게 됩니다.

    이런 상황은 Index의 Blevel, LeafBlock, ClusteringFactor등을 조절해 Index의 cost를 높여서
    first_rows_n모드에서 테스트를 실시 해 봐도 나타납니다.
    인덱스의 비용이 비싸도 인덱스를 사용하게 되는 경우가 대부분입니다.(물론 아닌 경우도 있습니다.)

    이런 상황이다보니 first_rows_n모드에서 Cost가 아닌 다른 기준이 적용이 되는 것이 아닐까 하는 의문이 생기고, 메뉴얼이나 여러곳에서 굳이 Throughput과 Response Time을 구분하는 것을 보면 여기서 말하는 Response Time을 확인할 수 있을까 하는 취지에서 질문을 드린것입니다.

    글이 주저리주저리 길어지고 말았군요. 메일로 문의 드릴것을 그랬습니다..ㅠㅠ
    우선은 다시 한번 확인해 보시고 알려주시면 정말 감사하겠습니다.

    그럼 수고하십시오.

  2. 욱짜 2008.10.23 17:04 신고 Modify/Delete Reply

    원래 아래와 같이 Comment를 했는데 테스트가 좀 더 필요하겠네요.

    추후에 추가적인 결과를 더 논의하도록 하겠습니다.

    ----------------------------------------------------

    COST 외에 다른 기준은 없습니다. 다만 First K Rows 모드를 사용할 때 기준이 하나 있습니다.
    바로 예측 Row 건수 입니다. 위의 예에서

    explain plan for select * from t1 where c1 = 1;

    -------------------------------------------
    | Id | Operation | Name | Rows |
    -------------------------------------------
    | 0 | SELECT STATEMENT | | 100 |
    |* 1 | TABLE ACCESS FULL| T1 | 100 |
    -------------------------------------------

    이렇게 되죠. 즉 예측 Row 건수(Cardinality)가 100입니다. 따라서 FIRST_ROWS(100) 이상이 되는 순간 무조건 Full Table Scan을 선택하게 됩니다. Cardinality 보다 더 많은 Row의 추줄을 요청하게 되니 사실상 FIRST_ROWS 모드가 아닌 셈이죠.

    반면에 FIRST_ROWS(100) 이하의 값을 선택하면 10053 Trace에 다음과 같은 정보가 출력되면서 First K Rows 모드로 다시 한번 Cost를 계산하게 됩니다.

    First K Rows: K/N ratio = 0.200000000000000, qbc=04CEAAE4

    K/N 에서 K = First Rows로 지정한 값, N = Cardinality가 됩니다.

  3. Moon 2008.10.23 17:38 신고 Modify/Delete Reply

    답변 감사합니다.
    코멘트로 달아주신 추가답변의 내용은 제가 코멘트에 한 내용과 표현의 차이일뿐 같은 의미인듯 싶군요.

    -----
    그러던 중 알게 된 것이 FIRST_ROWS_n 모드에서, 결과행이 n보다 작거나 같은 경우엔 실제로는 FIRST_ROWS_n가 적용이 되질 않습니다.
    ...
    SQL문의 실행 결과행이 100행이다보니 'SINGLE TABLE ACCESS PATH (First K Rows)' 의 계산은 하지 않습니다
    -----
    예측 Row 건수(Cardinality)가 100입니다. 따라서 FIRST_ROWS(100) 이상이 되는 순간 무조건 Full Table Scan을 선택하게 됩니다. Cardinality 보다 더 많은 Row의 추줄을 요청하게 되니 사실상 FIRST_ROWS 모드가 아닌 셈이죠.
    -----
    쉽게 풀어서 쓰려다보니 Cardinality를 결과행의수라고 했지만 같은 것을 말하고 있었습니다.
    제가 표현하는 방법을 더 길러야겠군요..ㅠㅠ
    어쨋던 First_rows_n 모드의 적용기준은 n < Cardinality 이고, First_rows_n가 적용되면
    First K Rows 모드로 다시 한번 Cost를 계산하게 되는것은 문제가 없을듯 합니다.

    하지만 제가 문제점으로 생각하는 부분은 그 아래의 First_rows_n 모드가 적용되었을 시,
    First K Rows 모드로 다시 한번 Cost를 계산하고 그때 Cost가 적은 것을 선택한다고 하셨는데
    테스트 도중 인덱스의 Cost가 높음에도 불구하고 인덱스를 선택하는 경우가 있다는 것입니다.

    본문의 예로
    select * from t1 where c1 = 1; 의 경우는 사실상 FIRST_ROWS 모드가 아니지만,
    select * from t1 where c1 >= 1; 의 경우는 Cardinality가 10000이 되므로 FIRST_ROWS(100) 가 적용되고
    First K Rows 모드로 다시 한번 Cost를 계산합니다.

    10053 Trace 를 보면
    SINGLE TABLE ACCESS PATH (First K Rows)
    Single Table Cardinality Estimation for T1[T1]
    Table: T1 Alias: T1
    Card: Original: 100.000000 Rounded: 100 Computed: 100.00 Non Adjusted: 100.00
    Access Path: TableScan
    Cost: 2.00 Resp: 2.00 Degree: 0
    Cost_io: 2.00 Cost_cpu: 29121
    Resp_io: 2.00 Resp_cpu: 29121
    Access Path: index (RangeScan)
    Index: T1_N1
    resc_io: 19.00 resc_cpu: 172307
    ix_sel: 1.000000 ix_sel_with_filters: 1.000000
    Cost: 19.01 Resp: 19.01 Degree: 1
    Best:: AccessPath: IndexRange
    Index: T1_N1
    Cost: 19.01 Degree: 1 Resp: 19.01 Card: 100.00 Bytes: 6
    ...
    ============
    Plan Table
    ============
    -----------------------------------------------+-----------------------------------+
    | Id | Operation | Name | Rows | Bytes | Cost | Time |
    -----------------------------------------------+-----------------------------------+
    | 0 | SELECT STATEMENT | | | | 19 | |
    | 1 | TABLE ACCESS BY INDEX ROWID | T1 | 100 | 600 | 19 | 00:00:01 |
    | 2 | INDEX RANGE SCAN | T1_N1 | | | 2 | 00:00:01 |
    -----------------------------------------------+-----------------------------------+

    분명히 인덱스의 코스트가 높음에도 불구하고 인덱스를 타게 됩니다.
    이런 상황에 대한 설명이 되지 않아 의문을 가지게 된 것입니다.

    여기저기 찾아보고 테스트 해봐도 이렇다할 답이 안나오네요..ㅜㅜ
    그럼 수고하십시오..^^

    아 참고적으로
    -----
    FIRST_ROWS(100) 이상이 되는 순간 무조건 Full Table Scan을 선택하게 됩니다.
    -----
    이런 내용을 써주셨는데 무조건 테이블이 되는건 아니고, 그때 계산된 Cost가 낮은 방법을 선택하게 됩니다.
    상황에 따라서 다르지만, 인덱스의 Cost를 낮춰서 테스트해보면 인덱스를 타게되네요.

    explain plan for select * from t1 where c1 = 1;

    --------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    --------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 100 | 600 | 7 (0)| 00:00:01 |
    |* 1 | TABLE ACCESS FULL| T1 | 100 | 600 | 7 (0)| 00:00:01 |
    --------------------------------------------------------------------------

    EXEC DBMS_STATS.SET_INDEX_STATS( USER, 'T1_N1', INDLEVEL => 0, NUMLBLKS => 0, CLSTFCT => 0 );

    explain plan for select * from t1 where c1 = 1;

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

  4. 욱짜 2008.10.23 17:46 신고 Modify/Delete Reply

    이 부분은 저도 치밀한 검증 없이 단순히 FIRST_ROWS(N) Hint는 100% Cost Base다라고만 이해하고 있었습니다. 이런 선입견이 있어서 Test의 치밀함이 떨어졌습니다.

    말씀하신 것처럼 FIRST_ROWS(N) Hint가 사용된 경우 Cost가 높더라도 Table Full Scan이 아닌 Index Scan을 선택하는 현상이 목격됩니다. 따라서 100% Cost Base가 아닐 수도 있다는 결론에 도달하게 되네요. 이 부분을 지적하신 걸로 이해됩니다.

    이 부분은 확실히 추가적인 조사가 필요해 보입니다. 저도 좀 더 알아 보겠습니다. 혹시 추가적인 정보를 얻게 되시면 메일이나 Comment로 공유 부탁드립니다.

  5. 욱짜 2008.10.24 11:32 신고 Modify/Delete Reply

    FIRST_ROWS(K) Optimization은 10gR1까지는 100% Cost Base로 동작하다가 10gR2부터는 Heuristic Logic이추가된 것으로 보입니다. 흐음...

    좀 더 조사를 해서 결론이 나면 블로그를 통해 공유하도록 하겠습니다.

  6. extremedb 2008.10.25 23:49 신고 Modify/Delete Reply

    FIRST_ROWS(N) 뿐만 아니라 ALL_ROWS 도 그런 경우가 있습니다.
    http://scidb.tistory.com/entry/NO-Costing-in-CBO

  7. 욱짜 2008.10.26 13:21 신고 Modify/Delete Reply

    아마도 10gR2 이후로 ALL_ROWS와 FIRST_ROWS_N Mode에 대해 Heuristic Logic이 추가된 것으로 보입니다. 안타깝게도 Metalink를 통해 이런 Feature에 대한 문서화가 되어있는지 조사해봐도 단서를 찾을 수가 없네요. SR이라도 해야되는가... ㅠㅠ

    OTN Forum에 문의해도 답변이 없네요. .

  8. Moon 2008.10.28 09:45 신고 Modify/Delete Reply

    저도 여기저기 뒤져봐도 먼가가 나오질 않는군요..ㅡ0ㅡㅋ
    OTN Forum에 올리신 글 봤는데 Jonathan Lewis아저씨가 답변을 달긴해주셨는데..
    그분께서도 확답은 없으신듯하군요...ㅠㅠ 버그쪽으로 생각하고계신듯...
    어쨋던 이래저래 알아봐주셔서 감사합니다...^^ 언제던 알게되면 서로 공유부탁드립니다...
    (SR하면 그런 확인도 해주나요? 신기해라..ㅎ)

  9. extremedb 2008.10.28 13:12 신고 Modify/Delete Reply

    참고로 이런성격의 SR 은 비추 입니다.
    한국오라클에서 해결할수 있으면 답이 한달안에 올수도 있지만
    그런경우는 거의 없었고 한 3~6개월 지긋이 기다릴수 있으면 SR 도 좋은 방법이긴 하지요.
    차라리 optimizer 설계자와 직접 대화가 가능한 ASK Tom 사이트에 올리시는 것이...
    이런면에서 저는 Jonathan Lewis 보다 Tom 을 더 신뢰합니다.^^
    Lewis 는 말끝을 흐리는 경우가 많습니다.
    예를 들면 ~ 인것 같다. 처럼요.

  10. 욱짜 2008.11.13 09:38 신고 Modify/Delete Reply

    Asktom을 통해서 힘겹게(여기는 질문을 Tom이 여유가 있을 때만 받기 때문에!) 질문했는데, 잘 모르겠다는 답변을 받았습니다. Support를 통해 File을 해보라는(즉 SR을 해보라는 의미인 듯) 답변이네요.

Write a comment