OPTIM PEEK USER BINDS

EXEM Knowledge Base

Jump to: navigation, 찾기

목차

[편집] 기본 정보

[편집] Parameter 정보


Syntax _OPTIM_PEEK_USER_BINDS
Default TRUE
설정방법
  • Parameter File
  • ALTER SYSTEM SET "_OPTIM_PEEK_USER_BINDS" =
지원 9i, 10g


[편집] 설명

Bind Variable Peeking(이하 BVP. 바인드 변수 엿보기)기능을 활성화할지의 여부를 결정한다. BVP란 Bind 변수를 포함한 SQL 문장에 대한 최적화 작업을 수행할 때, Bind 변수가 가리키는 값을 참조하는 것을 의미한다.

Oracle은 BVP를 통해 SQL 문장이 실행될 당시의 Bind 값을 이용하기 때문에 최적의 실행 계획을 수립할 수 있다.


[편집] 참고 사항

[편집] Bind Variable Peeking과 Explain Plan

BVP를 사용할 경우 Explain Plan 명령문을 통해서 확인한 실행 계획(Execution Plan)이 실제 운영 환경에서는 적용되지 않을 수 있다. Explain Plan 명령문에서는 BVP가 적용되지 않기 때문에 BVP가 적용된 런타임의 실행 계획과는 다른 실행 계획을 보고할 가능성이 있다.

만일 테스트 환경에서 성공적으로 수행된 SQL 문장이 런타임에 느린 성공을 보인다면 일차적으로 BVP에 의한 사이드 이펙트가 아닌지 검증해보아야 한다. 런타임의 실행 계획은 V$SQL_PLAN 뷰를 통해 확인 가능하다.


[편집] Bind Variable Peeking과 Histogram

BVP와 Histogram의 조합으로 인해 예상치 못한 성능 문제가 발생하는 경우가 종종 있다. 다음과 같은 경우를 예를 들어 보자.

* Table A의 Columum C는 "x" 값이 1000개, "y"값이 1개이다. 이런 류의 Column을 Skewed 되어 있다고 표현한다.
* Column C에는 Index가 생성되어 있으며, Histogram이 생성되어 있다.
* SELECT * FROM a WHERE c = :B1 문장을 수행한다. 

위와 같은 상황에서 BVP가 적용되는 경우 다음과 같은 문제가 발생한다.

  • BVP가 적용되는 시점에 :B1 값이 "x"이었다고 하면 Full Table Scan가 수행될 것이다. 이후 시점에 :B1 값이 "y"이 지정되는 경우에는 Index Range Scan을 사용하는 것이 훨씬 바람직한데도 불구하고 이미 생성된 Full Table Scan을 사용하게 되는 문제가 발생한다.
  • BVP가 적용되는 시점에 :B1 값이 "y"이었다고 하면 Index Range Scan이 수행될 것이다. 이후 시점에 :B1 값이 "x"이 지정되는 경우에는 Full Table Scan이 훨씬 바람직한데도 불구하고 기존에 생성된 Index Range Scan을 사용하게 되는 문제가 발생한다.

즉, Historgram이 생성된 Colunm이 Skewed 되어 있고, 이 Column에 대해 BVP가 적용되면 Bind 변수의 값에 따라 성능이 일정치 못한 문제가 발생하게 된다. 이 문제에 대한 Workaround는 다음과 같다.

  • Histogram이 생성된 Column에 대해서는 Literal 문장을 사용한다.
SELECT * FROM a WHERE c = 'x'
  • _OPTIM_PEEK_USER_BINDS 옵션 값을 FALSE로 지정한다.


[편집] Oracle 10g와 DBMS_STATS 패키지

통계 정보를 수집하기 위해 DBMS_STATS 패키지를 사용하는 경우 METHOD_OPT 파라미터의 Default 값으로 인해 10g에서 예상치 못한 문제가 발생할 수 있다. Oracle 9i까지는 METHOD_OPT 파라미터의 기본값이 FOR ALL COLUMNS SIZE 1이다. 즉, Histogram을 생성하지 않는 것이 기본 방식이다. 하지만 Oracle 10g부터는 이 파라미터의 기본값이 FOR ALL COLUMNS SIZE AUTO로 변경되었다. 즉, Histogram의 생성여부를 Oracle이 알아서 판단하게 된다.
이러한 버전간의 변화는 다음과 같은 상황에서 문제를 일으킬 수 있다.

  • Oracle 9i에서 Oracle 10g로 업그레이드를 수행한다.
  • DBMS_STATS 패키지를 이용해 통계 정보를 수집한다.
  • METHOD_OPT 파라미터의 값을 지정하지 않고 Default 값을 사용한다.

이런 상황에서는 이전에는 Histogram이 없던 컬럼에 Histogram이 생성될 수 있고, 이 컬럼에 대해 BVP가 적용되는 경우 실행 계획이 변경되어 SQL 문장의 성능이 크게 저하되는 현상이 발생할 수 있다. 이 문제에 대한 Workaround는 다음과 같다.

  • METHOD_OPT 파라미터의 값을 Default 값을 사용하지 않고 명확하게 지정한다.
예:BEGIN
         DBMS_STATS.GATHER_table_STATS (OWNNAME => 'U', 
                TABNBAM => 'T', 
                METHOD_OPT => 'FOR COLUMNS SIZE 10 column_1');
    END;
   /

[편집] Oracle 11g의 Bind-aware cursor matching

위에서 언급한 BVP와 Histogram 문제 때문에 BVP 기능을 꺼버리는 경우가 많다. 다행히 Oracle 11g에서는 Histogram에 의한 사이드 이펙트 문제가 해결되었다.

  1. Oracle 11g에서는 Bind-aware cursor matching이라는 기능이 추가되었다. 말 그대로 Cursor를 비교할 때 bind를 인식하겠다는 의미이다.
  2. Histogram이 생성된 Column에 대해 BVP가 사용되는 경우, 해당 SQL Cursor는 Bind-Sensitive Cursor로 지정되고, 이 Cursor에 대해서 Bind 값에 따라 실행 계획의 변동이 크다고 판단되면 Bind-aware cursor matching이 활성화된다.
  3. Bind-aware cursor matching이 활성화되면 Bind Peeking이 발생하더라도 Bind 값에 따라 서로 다른 최적의 실행 계획을 가지게 된다.


[편집] 관련된 정보들

  1. V$SQL_BIND_DATA
  2. V$SQL_BIND_CAPTURE

[편집] 외부 참조

  1. Oracle 11g Performance Tuning Guide