태터데스크 관리자

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

태터데스크 메시지

저장하였습니다.

OPT_PARAM Hint - Statement Level에서 Optimizer Parameter 변경하기

오라클 2008.02.20 17:18
간혹 다음과 같은 요구사항이 생길 수 있다.

"특정 Statement 레벨로 Optimizer Parameter를 변경할 수는 없을까?"

가령 _b_tree_bitmap_plans 파라미터는 BTree Index Scan을 Bitmap 연산으로 변환해주는 기능을 활성화/비활성화하는 기능을 제공한다. Bitmap Conversion은 대부분의 경우 성능에 유리하지만 간혹 특정 쿼리는 이것을 비활성화하는 것이 나을 때가 있다. 가령

alter session set "_b_tree_bitmap_plans" = false;

와 같이 파라미터를 변경하는 것이다. 하지만 여기에는 심각한 문제가 있다. Session 레벨이나 System 레벨에서 값은 변경하는 것은 다른 쿼리들에도 영향을 미친다. 원치 않은 부작용이 우려되는 것이다. 다행히 소스 코드를 마음대로 수정할 수 있어서 쿼리 수행 직전/직후에 Disable/Enable시키는 것이 가능하다면 문제가 되지 않을 것이다. 하지만 이것이 여의치 않고(어떤 이유에서든) 쿼리를 수정하는 것만이 가능하다면?
(요즘 Java Programming에서 유행하는 iBatis나 Hibernate 같은 Framework에서는 이런 사례가 흔하게 발생할 수 있다)

이런 경우는 SQL Statement를 처음부터 다시 작성하지 않고 간단하게 다음과 같은 힌트를 부여하면 된다.

select /*+ opt_param('_b_tree_bitmap_plans', 'false') */ ....

OPT_PARAM 힌트는 10gR2에서 처음 소개되었다. 이름 그대로 Optimizer와 관련된 Parameter 값을 Statement 레벨에서 제어하는 힌트다.

OPT_PARAM 힌트는 매우 강력한 도구다. 따라서 Optimizer의 Parameter에 대한 정확한 이해를 바탕으로 잘 사용해야 한다.

PS) 몇가지 테스트를 해보면 모든 Optimizer 관련 파라미터에 대해서 이 힌트가 다 작동하는 것은 아니다. 실제 사용시에는 철저한 테스가 필요하다.
신고
Trackback 0 : Comments 6
  1. 욱짜 2008.02.27 17:46 신고 Modify/Delete Reply

    ^^ 고맙습니다. 한국에서도 이 포럼에서 활동하는 분들이 많아졌으면 좋겠네요.

  2. 오동규 2008.02.27 20:24 신고 Modify/Delete Reply

    이파라미터 관련해서 포럼에 재미있는 논쟁을 하셨더군요.
    하도 어이가 없어서 제가 좀 거들었습니다.
    http://forums.oracle.com/forums/thread.jspa?messageID=2369693&#2369693
    위주소를 클릭하니 포럼으로 들어가지 않고 copy 후 주소창에 붙여넣으니 들어가네요.
    Mister O 가 제 아이디 입니다.

  3. McLaren 2008.04.25 13:34 신고 Modify/Delete Reply

    안녕하세요 Bitmap Conversion에 대해 질문이 있습니다.

    제가 DBA로 있을 때 9.2.0.4에서 특정 SQL이 NL로 풀리던 것이
    Index Rebuild 작업을 하고 나서 open을 하니
    Bitmap Conversion 으로 실행계획이 풀려 낭패를 본적이 있습니다. DB내리고 힌트 추가해 해결했습니다
    오라클 버그라고 판명이 났습니다만
    그때의 그 충격과 공포는 이루말할 수가 없습니다 --;;

    9207에서 부턴가 (10g는 당연없겠죠?) 에서는 그런 위험성이 없다고 합니다만,
    글에서 말씀하신 간혹 특정 Query는 비활성화하는
    것이 나을때가 있다라고 말씀하신 상황에 대해서
    정리 좀 해주실 수 있는지요?

    부탁드립니다.

  4. 욱짜 2008.04.26 07:04 신고 Modify/Delete Reply

    특별히 정리된 목록을 가지고 있지는 않습니다. 글에서의 의도는 Oracle이 Bitmap Conversion이 유리하다고 판단하는 것이 잘못된 판단일 경우가 여전히 종종 있다는 것을 의미하는 것이구요.

    참, 그리고 말씀하신 버그의 번호를 알 수 있을까요?

  5. oracler 2008.05.10 23:20 신고 Modify/Delete Reply

    특정 SQL 이라는 상황이라는것에 주목합니다.
    IT 라는게 참~~ 때로는 뜬구름 잡기가 될수 있는데요.

    그런 상황을 가상적으로 만들어서 재연하는것은 매우 번거롭고 어려운 일이므로
    이미 벌어진 상황을 샘플로 하는것이 효율적이고 쉬울수 있습니다.

    무슨 말이냐면

    과거 NL 로 풀리던것이 rebuild 로 인해 다르게 풀렸다는것을 알았다는것은
    (수많은 SQL 들의 개별 SQL 별로 과거 플랜의 히스토리를 전부 알수 있는 방법은 없고
    index rebuild 로 영향을 받는 쿼리는 한두개가 아닐것이므로)

    갑자기 플랜이 다르다는것을 알았다는건
    문제가 된 SQL 이 원래 특이한 SQL 이라서 애초에 처음부터 관리대상이었다는 말이 되는데요.

    그렇다면 바로 그 SQL 이 "특정 SQL" 이라는것의 sample 이 될수 있는것 아닐까요.

    McLaren 님이 SQL/플랜/통계정보나 파라메터등 관련 팩터을 올려주시면 고맙겠습니다.

  6. McLaren 2008.06.03 17:12 신고 Modify/Delete Reply

    답변이 늦어 죄송합니다.
    제가 회사를 옮겼더니 메타링크 id가 잠겨버렸네요..
    고로, SQL, 플랜, 통계정보를 올려드리지 못하겠네요..ㅡㅡ;;

    참고로 9.2.0.4 버전에 RBO 였구요.
    파티션 테이블을 Reorg 했습니다.

    해결책으로 _b_tree_bitmap_plans 파라미터 값을 false로 바꾼 기억이 나네요^^

    별로 도움이 되지 않았네요~

Write a comment

티스토리 툴바