태터데스크 관리자

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

태터데스크 메시지

저장하였습니다.

[Oracle Is Mad] { INSERT ... SELECT }의 실행 계획 이상

오라클 2007.11.24 23:13
가끔 다음과 같은 질문을 하는 경우가 있다.

"{ SELECT } 문장만 실행하는 경우에는 속도가 빨랐는데, { INSERT ... SELECT } 문장을 실행하면 속도가 지나치게 느려진다."

물 론 INSERT 작업이 추가로 발생하기 때문에 { SELECT } 만 수행한 경우에 비해 느린 것은 당연하다. 하지만 어떤 경우에는 INSERT 작업의 오버헤드를 감안하더라도 너무 지나치게 수행 속도가 느린 경우가 가끔 발생한 다.

왜 이런 현상이 발생할까? 우선 아래의 간단한 예를 보자.

-- 테스트를 위해 Optimizer Mode를 FIRST_ROWS로 변경한다.
SQL> alter session set optimizer_mode = first_rows;

SQL> SELECT * FROM PL_TEST WHERE ID BETWEEN 1 AND 10000;
==>
 SELECT STATEMENT FIRST_ROWS-Cost : 1602
  TABLE ACCESS BY INDEX ROWID OWI.PL_TEST(1)
   INDEX RANGE SCAN OWI.PL_TEST_IDX (ID) ("ID">=1 AND "ID"<=10000)

SQL> INSERT INTO PL_TEST SELECT * FROM PL_TEST WHERE ID BETWEEN 1 AND 10000;
==>
 INSERT STATEMENT FIRST_ROWS-Cost : 115
  TABLE ACCESS FULL OWI.PL_TEST(2) ("ID">=1 AND "ID"<=10000)


위의 예를 보면 완전히 동일한 { SELECT } 문이 { INSERT ... SELECT } 문장 안에서 실행되는 경우(FTS)와 그렇지 않은 경우(Index Range Scan) 전혀 다른 실행 계획을 보이는 것을 확인할 수 있다.


이런 이상한 현상이 발생하는 이유를 알려면 10053 Event를 이용해 Optimizer가 어떤 값을 이용해서 어떤 실행 계획을 선택하는지 살펴볼 필요가 있다.

SQL> alter session set events '10053 trace name context forever, level 1';

1. SELECT 만 있는 경우

SINGLE TABLE ACCESS PATH
  Column (#1): ID(NUMBER)
    AvgLen: 4.00 NDV: 10108 Nulls: 0 Density: 9.8932e-05 Min: 2 Max: 10000
  Table: PL_TEST  Alias: PL_TEST     
    Card: Original: 643558  Rounded: 643558  Computed: 643558.00  Non Adjusted: 643558.00
  Access Path: TableScan
    Cost:  851.42  Resp: 851.42  Degree: 0
      Cost_io: 828.00  Cost_cpu: 200665460
      Resp_io: 828.00  Resp_cpu: 200665460
  Access Path: index (RangeScan)
    Index: PL_TEST_IDX
    resc_io: 641332.00  resc_cpu: 4804007358
    ix_sel: 1  ix_sel_with_filters: 1
    Cost: 641892.71  Resp: 641892.71  Degree: 1
  Best:: AccessPath: IndexRange  Index: PL_TEST_IDX
         Cost: 641892.71  Degree: 1  Resp: 641892.71  Card: 643558.00  Bytes: 0
...
*********************************
Number of join permutations tried: 1
*********************************
Final - First Rows Plan:  Best join order: 1
  Cost: 641892.7087  Degree: 1  Card: 643558.0000  Bytes: 9009812
  Resc: 641892.7087  Resc_io: 641332.0000  Resc_cpu: 4804007358
  Resp: 641892.7087  Resp_io: 641332.0000  Resc_cpu: 4804007358

2. { INSERT ... SELECT } 문인 경우

SINGLE TABLE ACCESS PATH
  Column (#1): ID(NUMBER)
    AvgLen: 4.00 NDV: 10108 Nulls: 0 Density: 9.8932e-05 Min: 2 Max: 10000
  Table: PL_TEST  Alias: PL_TEST     
    Card: Original: 643558  Rounded: 643558  Computed: 643558.00  Non Adjusted: 643558.00
  Access Path: TableScan
    Cost:  851.42  Resp: 851.42  Degree: 0
      Cost_io: 828.00  Cost_cpu: 200665460
      Resp_io: 828.00  Resp_cpu: 200665460
  Access Path: index (RangeScan)
    Index: PL_TEST_IDX
    resc_io: 641332.00  resc_cpu: 4804007358
    ix_sel: 1  ix_sel_with_filters: 1
    Cost: 641892.71  Resp: 641892.71  Degree: 1
  Best:: AccessPath: TableScan
         Cost: 851.42  Degree: 1  Resp: 851.42  Card: 643558.00  Bytes: 0
...
*********************************
Number of join permutations tried: 1
*********************************
Final - All Rows Plan:  Best join order: 1
  Cost: 851.4210  Degree: 1  Card: 643558.0000  Bytes: 9009812
  Resc: 851.4210  Resc_io: 828.0000  Resc_cpu: 200665460
  Resp: 851.4210  Resp_io: 828.0000  Resc_cpu: 200665460


위의 10053 Event Trace 결과를 보면 다음과 같은 사실을 알 수 있다.

1. Full Table Scan에 의한 비용이 Index Range Scan에 의한 비용보다 저렴하다고 오라클은 판단한다.
2. 하지만 { SELECT } 인 경우에는 Index Range Scan을 선택하고, { INSERT ... SELECT } 인 경우에는 Full Table Scan을 선택한다.
3. 그 이유를 보면 { SELECT }인 경우에는 Session 레벨에서 주어진 FIRST_ROWS 모드를 사용하는 반면, { INSERT ... SELECT }인 경우에는 내부적으로 ALL_ROWS 모드를 사용한다.

즉, { INSERT ... SELECT { 문은 System 레벨이나 Session 레벨에서 주어진 Optimizer Mode를 무시하고 내부적으로 항상 ALL_ROWS 모드를 사용한다. 이런 이유 때문에 { SELECT } 문장과 { INSERT ... SELECT } 문장이 전혀 다른 실행 계획을 보일 수 있는 것이다.

가령 NL 조인으로 풀리던 것이 ALL_ROWS의 특성에 의해 비효율적인 Sort Merge 조인으로 풀리면서 성능 저하 현상이 유발되는 것이다.  { INSERT ... SELECT }의 성능 튜닝을 할 때는 이점을 기억할 필요가 있다.

Oracle 10g부터는 Optimizer Mode의 기본값이 ALL_ROWS이다. 따라서 Optimizer Mode의 차이로 인한 { INSERT ... SELECT } 문장의 실행 계획 이상은 더 이상 발생하지 않을 것으로 본다.


신고
Trackback 0 : Comments 8
  1. steve_kim 2007.12.05 01:30 신고 Modify/Delete Reply

    ALL_ROWS 모드인경우에도 {SELECT}와 {INSERT..SELECT}의 PLAN이 다른경우가 존재합니다. 만일 {INSERT..SELECT}절의 SELECT 쿼리의 PLAN을 {SELECT}와 동일하게 풀고 싶을 경우에는 일반적으로 2가지 방법이 있습니다.

    1. SQL> alter session set "_complex_view_merging"=false
    2. [1]번으로 해결되지 않는 경우가 있는데, 이럴 경우에는 SELECT절에 ROWNUM을 써서 원천적으로 VIEW Merging이 되지 않게 하면됩니다.
    exam>
    Before: INSERT INTO T1 SELECT * FROM T2 WHERE .....
    After : INSERT INTO T1 SELECT a.ID, a.NAME FROM (SELECT ID,NAME,ROWNUM FROM T2 WHERE ...) a

  2. 욱짜 2007.12.24 21:40 신고 Modify/Delete Reply

    _complex_view_merging(9i~), _optimizer_cost_based_transformation(10g~) 같은 Hidden Parameter에 의해서도 이런 현상이 주로 발생합니다. 8i,9i에서 10g로 Upgrade시 주의해야 할 사항 중 하나~

  3. 오동규 2008.05.19 21:46 신고 Modify/Delete Reply

    insert -select 뿐만아니라 update, delete, merge 등도 그렇게 되지않을까 생각해봅니다.
    물론 자세한 내용은 10053 trace 를 봐야 하겠지만
    기본적으로 DML 은 부분범위 처리가 필요가 없다고 생각합니다.
    DML 이나 CTAS 등은 모든처리가 끝나야지만 결과가 RETURN 되기 때문이죠.
    FIRST_ROWS_N 등은 부분범위처리를 위한것이라 ...
    만약 이것이 안된다면 12 버젼에서 되기를 기대해 봅니다.

  4. 욱짜 2008.05.19 21:58 신고 Modify/Delete Reply

    저도 그렇게 생각했고, 그런 사례가 보고되고 있습니다. 원리가 동일하기 때문에 충분히 합리적인 가정이라고 봅니다. 추가적으로 CTAS(Create Table As Select) 또한 같은 문제가 있습니다.

  5. 유수익 2009.10.15 10:51 신고 Modify/Delete Reply

    1. select절에서 보면 Access Path: TableScan Cost: 851.42 ,
    Access Path: index (RangeScan) Cost: 641892.71 이면 TableScan이 일어나야 하는것 아닌가요?
    왜 index scan을 수행했을까요? 2번의 CTAS는 cost대로 full scan한거 같은데요..

    • 욱짜 2009.10.15 11:10 신고 Modify/Delete

      FIRST_ROWS의 동작 방식 때문입니다.

      FIRST_ROWS 모드는 Cost를 어느 정도 무시하고 내부적인 룰을 따를 것을 강요하기 때문입니다. FTS 대신 Index Range Scan을 Hash Join 대신 Nested Loops Join을 선택하는 등의 룰이 동작하게 됩니다.

  6. 유수익 2009.10.19 20:53 신고 Modify/Delete Reply

    음.. 저도 실 운영환경은 ALL_ROWS로 운영(10.1.0.5)하고 있습니다. FIRST_ROWS는 depricate되었고
    FIRST_ROWS(N)을 설정하기도 기준이 없고해서 모드를 무었으로 할지 고민이네요? OLTP로 운영되는데
    FIRST_ROWS는 어느정도 RULE로 운영된다면 부분범위처리를 어떻게 해야하나요? ALL_ROWS, FIRST_ROWS
    좀 더 헷갈리네요? 개발서버에 FIRST_ROWS로 했더니 왼만하면 NL 둘더라구요..

    • 욱짜 2009.10.20 09:43 신고 Modify/Delete

      Oracle이 자체적으로 설정하고 있는 기본값이 ALL_ROWS라는 것은 중요한 사실을 암시하고 있습니다. 우리가 Query를 작성한다는 것은 해당 Query의 결과를 다 얻겠다는 것을 암시합니다. 따라서 ALL_ROWS가 논리적으로 가장 합당한 모드입니다. FISRT_ROWS, ALL_ROWS의 구분을 만든 것 자체가 우리를 혼란스럽게 하는 것이죠.

      ALL_ROWS에서 가장 문제가 되었던 것이 부분범위 처리, 그 중에서도 Pagination Query입니다. 하지만 이런 Query 또한 논리적으로는 ALL_ROWS가 맞습니다. 제대로 작성된 Pagination Query라면 ROWNUM이나 기타 기법을 써서 특정 부분만 가져오도록 작성이 되었을 것입니다. 따라서 해당 Query에 대해서는 ALL_ROWS를 사용하는 것이 논리적이요. 그렇지 않고 Oracle이 인식할 수 없는 편법을 사용하는 경우라면 ALL_ROWS와 충돌하게 됩니다.

      결국 Query를 어떤 패턴으로, 정확하게 말하면 얼마나 Oracle 친화적으로 하느냐에 따라 ALL_ROWS의 합당성이 결정됩니다.

      저는 개인적으로 FIRST_ROWS... 류의 모드는 없어져야 한다고 봅니다.

      참고로, Oracle 10g부터는 ROWNUM을 사용한 경우에 내부적으로 FIRST_ROWS(K) 모드로 동작하는 옵션을가지고 있습니다. _optimizer_rownum_pred_based_fkr 파라미터로 설정할 수 있고 기본값은 true입니다.

Write a comment

티스토리 툴바