DATOR


튜닝-optimizer_mode의 기준이 무엇일까?

사이트에 컨설팅을 가다보면 가끔 optimizer_mode을 무엇으로 해야 하나요 라는 질문을 받기도 한다.
 

그러면 역으로 사용하는 시스템은 무엇을 위주로 사용하는 시스템인가요? 라고 재질문을 하기도 한다.

여기서 무엇을 위주란 ?
 해당 시스템이 OLTP시스템인지 아니면 배치 전용 시스템인지에 따라서 optimizer_mode의 전략을 다르게 세워야 한다.
 
혼란스러운 경우가 배치와,OLTP가 같이 혼용해서 사용하는 경우이다.
그런데 대부분의 시스템은 두가지의 비율이 틀리지만 혼용해서 사용하고 있다.

대부분 OLTP가 주로 90%사용하고 배치는 새벽 혹은 한두시간에 한번씩 수행되는 경우가 많다.

새벽에 수행되는 경우에는 배치SQL의 상단에 아래와 같이 처리해서 사용하면 문제가 없다.

ALTER SESSION SET optimizer_mode=ALL_ROWS

 

 

이런경우에 OLTP이기때문에 부분범위 처리가 최적화로 수행될수 있도록 FIRST_ROWS을 해야 한다.

그런데 혼란스러운것이 있다.

optimizer_mode에 FIRST_ROWS,FIRST_ROWS_N으로 할수 있다.

 

그러면 위 두가지의 값중에서 무엇으로 해야 될까?

결론은 COST의 계산하는 가중치를 optimizer_mode에 부여하는 것인데 11G에서 optimizer_mode 가 너무 바보같은 선택을 하는것 같다.

 

요즘 11G을 사용하면서 느끼는 것이 공부하는어린이(optimizer_mode)에게 너무 많은 과외(통계정보,여타 COST계산하는 방법)
을 시키다 보니 공부하는 어린이가 복잡하게 풀어서 제출 하는 것 같지만 결론은 창의적이지 않고 그냥 단지 외워서 제출하는 답만 제출 하고 간혹 말도 안되는 답을 제출 하기도 한다.
그리고 공부를 나름 하다보니 선생님 충고(힌트)는 무시하고 자기 고집을 피우기도 한다.(힌트 완전 무시)


여기서 한번 테스트를 해보도록 합시다.


테스트데이타:1억5천 만건
IX_테스트데이타_01 :CUST_NO(고객번호)+ ORD_DTM(생성일시)
IX_테스트데이타_03 :ORD_DTM(생성일시)
최종 결과건수:581건 

해당 테이블은 통계정보가 생성되어 있음(11G의 통계정보 자동 생성)

 

 

ALTER SESSION SET optimizer_mode=first_rows
 
SELECT /*+ gather_plan_statistics */
        *
  FROM 테스트데이타 A 
WHERE ORD_DTM BETWEEN TO_DATE('201101220900','YYYYMMDDHH24MISS') 
   AND TO_DATE('201101220910','YYYYMMDDHH24MISS')
ORDER BY CUST_NO,ORD_DTM
 

 
Plan hash value: 3588723072
 
-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                 |      1 |        |    581 |00:02:59.37 |     475K|    294K|
|*  1 |  FILTER                      |                 |      1 |        |    581 |00:02:59.37 |     475K|    294K|
|   2 |   TABLE ACCESS BY INDEX ROWID| 테스트데이타       |      1 |    849 |    581 |00:02:59.37 |     475K|    294K|
|*  3 |    INDEX FULL SCAN           | IX_테스트데이타_01 |      1 |    908 |    581 |00:02:28.11 |     475K|    294K|
-------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(TO_DATE('201101220900','YYYYMMDDHH24MISS')<=TO_DATE('201101220910','YYYYMMDDHH24MISS'))
   3 - access("ORD_DTM">=TO_DATE('201101220900','YYYYMMDDHH24MISS') AND
              "ORD_DTM"<=TO_DATE('201101220910','YYYYMMDDHH24MISS'))
       filter(("ORD_DTM">=TO_DATE('201101220900','YYYYMMDDHH24MISS') AND
              "ORD_DTM"<=TO_DATE('201101220910','YYYYMMDDHH24MISS')))

 

수행시간 5분 이상 수행됨.

소팅을 피하기 위해서 인덱스를 FULL SCAN(1억5천만건)해서 581건을 가져옴.(놀라운 사실)

 

ALTER SESSION SET optimizer_mode=first_rows_100
 
SELECT /*+ gather_plan_statistics */
        *
  FROM 테스트데이타 A 
WHERE ORD_DTM BETWEEN TO_DATE('201101220900','YYYYMMDDHH24MISS') 
   AND TO_DATE('201101220910','YYYYMMDDHH24MISS')
ORDER BY CUST_NO,ORD_DTM 

 

Plan hash value: 2606244439
--------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                 |      1 |        |    581 |00:00:00.01 |     587 |       |       |          |
|   1 |  SORT ORDER BY                |                 |      1 |    849 |    581 |00:00:00.01 |     587 |   302K|   302K|  268K (0)|
|*  2 |   FILTER                      |                 |      1 |        |    581 |00:00:00.02 |     587 |       |       |          |
|   3 |    TABLE ACCESS BY INDEX ROWID| 테스트데이타       |      1 |    849 |    581 |00:00:00.02 |     587 |       |       |          |
|*  4 |     INDEX RANGE SCAN          | IX_테스트데이타_03 |      1 |    849 |    581 |00:00:00.01 |       6 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter(TO_DATE('201101220900','YYYYMMDDHH24MISS')<=TO_DATE('201101220910','YYYYMMDDHH24MISS'))
   4 - access("ORD_DTM">=TO_DATE('201101220900','YYYYMMDDHH24MISS') AND "ORD_DTM"<=TO_DATE('201101220910','YYYYMMDDHH24MISS'))
 

옵티마이저가 ORD_DTM을 선두로 하는 인덱스를 사용한다.수행시간 1초 이내

 

결과를 보면 FIRST_ROWS와 FIRST_ROWS_N에 의한 COST계산 하는 방식이 너무 극단적으로 옵티마이저가 계산을 한다.
본인이 보기에 FIRST_ROWS인 경우에는 옵티마이저가 소팅의 부담을 줄이기 위해서 해당 ORDER BY 컬럼에 해당하는
인덱스를 극단적으로 사용하는것 같다.즉 COST의 계산을 바보같이 하는 것 같다.(이런경우 아무리 COST가 높아도 해당 인덱스를 선택 하는 경우가 많이 있다.)

 

FIRST_ROWS_N은 COST BASED로 계산하기 위해서 나름 극닥적인것은 피하도록 노력하는것 같다.


FIRST_ROWS는 내부적으로 룰베이스 속성을 가지고 있다.(ORACLE 9i 부터는 사용중지되었다.)

사용할때 FIRST_ROWS_N으로 사용하기를 권장한다.

 

주의점)
  1.현재 운영중에 optimizer_mode을 변경했다가 엄청난 문제가 발생할수 있다.
  (기존 PLAN이 변경되어서 잘 수행되는 SQL이 갑자기 먹통이 발생할수도 있음)

  2.해당 적용은 시스템을 새롭게 개발해서 오픈할때 적용하는 것이다.
  3.해당 값을 적용 한다고 시스템이 빨라 지는것은 아니다.
   FIRST_ROWS_N,ALL_ROWS로 해서 기준이 정해지면 그 기준에 의해서 튜닝을 하는 것이지
   해당 값에 의해서 시스템이 갑자기 빨라 진다는 생각은 버리는 것이 좋다.

  

아..옵티마이저여 똑똑해 지는 것은 좋은데 극단적인 답은 내지 말기를........

 

 

 

 

 

Tag

Leave Comments