_optim_peek_user_binds

bind_peek와 관련한 hidden parameter 이다.


hidden parameter 조회 쿼리

SELECT RPAD(I.KSPPINM, 35) || ' = ' || V.KSPPSTVL AS PARAM
     , I.KSPPDESC DESCRIPTION
     , DECODE(V.KSPPSTDF,'FALSE','F','TRUE','T')  AS DFLT
     , P.ISSES_MODIFIABLE
     , P.ISSYS_MODIFIABLE
  FROM X$KSPPI     I
     , X$KSPPCV    V
     , V$PARAMETER P
 WHERE V.INDX    = I.INDX
   AND V.INST_ID = I.INST_ID
   AND I.KSPPINM = P.NAME(+)
   AND I.KSPPINM LIKE lower('%' || '&NAME' || '%')
 ORDER BY I.KSPPINM

 

1. SQL문의 수행 순서(SELECT)
   가. PARSIING
       A. 동일 SQL문(모든 글자 및 띄어쓰기 포함)이 LIBRARY CACHE 내에 존재 하는지 확인
          A-1. 존재 한다면 해당 SQL 문의 각종 정보를 그대로 사용한후 "나" 단계로 이동
          A-2. 존재 하지 않는다면 B번으로 이동
       B. 해당 SQL문을 정상적으로 처리하기 위해 SHARED POOL 내에 공간 확보
       C. 문법 검사
       D. 의미 검사(SEMANTIC CHECK) : 테이블 존재 여부, 권한 등
       E. 실행계획 생성
          테이블 및 인덱스 정보 확인, 통계 정보 확인등을 통해 최적의 SQL 실행계획 수립
          모든 실행계획을 테스트 해보는 것은 아니고 자주 사용되는 실행계획을 테스트
       F. 실행계획을 LIBRARY CACHE내의 해당 SQL과 관련된 공간에 저장
       G. 실행 코드 작성(BINARY CODE)
   나. EXECUTE
   다. FETCH

2. HARD PARSE vs SOFT PARSE
   가. HARD PARSE : 1번의 A 단계에서 A-2 인 경우
       HARD PARSE가 발생하는 경우 SQL은 해당 문장에 대한 실행계획을 작성해야 하며,
       이러한 HARD PARSE가 동시에 많이 발생하는 경우 LIBRARY CACHE 공간 할당 및 PARSING에 대한 경합이 발생
       이러한 경합은 latch:library cache 혹은 library cache lock의 형태로 나타나게 되며,
       경합이 집중(과다)되어 지는 경우 HANG 현상이 발생할 수 있음.
      
       -- 두 SQL 문장이 동일한 경우
       SQL1> SELECT * FROM EMP;
       SQL2> SELECT * FROM EMP;
      
       -- 두 SQL 문장이 다른 경우
       SQL1> SELECT * FROM EMP WHERE EMPNO = 1;
       SQL2> SELECT * FROM EMP WHERE EMPNO = 2;
       --> 상수 값이 다른 경우, 해당 상수값을 모두 포함하여 동일 SQL문 여부를 판단
      
       장점 : 실행계획 생성시 상수 값을 포함하여 실행계획을 예측 하므로 HISTOGRAM(세부 통계 정보)이 존재 하는
              경우 정확한 실행계획을 생성 할 수 있음.
              즉, 통계에 포함되어 있는 해당 값의 분포도를 상대적으로 정확히 예측할 수 있음.
              반드시 최신의 통계 정보여야 함.
      
   나. SOFT PARSE : 1번의 단계에서 A-1 인 경우
       해당 SQL문과 동일한 SQL 문이 존재 함으로 추가적인 실행계획 생성 없이 해당 SQL문이 가지고 있는 실행계획을
       그대로 이용
       LIBRARY CACHE에 대한 자원 경합이 존재하지 않으므로 빠른 처리 및 자원 경합에 따른 HANG 현상 방지
      
       -- 상수가 다른 두 SQL 문장을 같게 만든 일반적인 방법
       SQL1> SELECT * FROM EMP WHERE EMPNO = :B1;       :B1 = 1
       SQL2> SELECT * FROM EMP WHERE EMPNO = :B1;       :B1 = 2
      
       단점 : 변수에 binding 되는 상수 값을 알 수 없는 상태에서 실행계획을 작성해야 하기 때문에,
              해당 값이 통계를 기준으로 전체 몇 %가 되는지를 정확히 확인할 수 없음.
              따라서 오라클은 일반적인 값인 5%를 사용.
              즉, binding 변수를 쓰는 경우 해당 값은 분포도가 항상 5%라고 정의한 후 실행계획 생성.

3. BIND PEEKING
   가. 상기 2-나 의 단점으로 기술한 바와 같이 binding 변수를 사용하는 경우 해당 변수의 값이 항상 5%라는 가정에 의해
       적절하지 않은 실행계획이 생성될 수도 있음.
      
       ex1> 실제 존재하는 데이터 분포
           총 10개의 값  : 1, 1, 1, 1, 1, 2, 2, 2, 2, 2
           중복 제외 값  : 2
           최소값        : 1
           최대값        : 2
          
       -- 상수 사용
       SQL1> SELECT * FROM EMP WHERE COL1 = 1 ;   --> 실제 분포도 50%
       SQL2> SELECT * FROM EMP WHERE COL1 = 2 ;   --> 실제 분포도 50%
      
       -- 변수 사용
       SQL1> SELECT * FROM EMP WHERE COL1 = :B1 ;   :B1 = 1   --> 예상 분포도 : 5%,  실제 분포도 50%
       SQL2> SELECT * FROM EMP WHERE COL1 = :B1 ;   :B1 = 2   --> 예상 분포도 : 5%,  실제 분포도 50%
      
       --> 이 경우 bind 변수를 사용한다면 실행계획상에서 문제의 소지가 있을 수 있다.
           예상 분포도와 실제 분포도가 다르기 때문이다.
           이러한 경우는 오히려 상수를 이용한 쿼리 수행이 훨씬 유리하다.
           이렇듯 예상과 실제 분포도가 다른 경우를 위해 bind peeking 기법을 10g 부터 도입하였다.
          
   나. bind peeking 사용
       상기 '1-가-A-2에 해당하는 경우 오라클은 실행계획을 재 생성해야 하며, 이때 binding 변수를 사용하고 있다면,
       해당 bind 변수와 결합되는 상수값을 이용해서 실행계획을 생성하게 된다.

       ex1> 실제 존재하는 데이터 분포
           총 10개의 값  : 1, 1, 1, 1, 1, 2, 2, 2, 2, 2
           중복 제외 값  : 2
           최소값        : 1
           최대값        : 2
      
       SQL1> SELECT * FROM EMP WHERE COL1 = :B1 ;   :B1 = 1
       --> bind peeking을 사용하는 경우 예상 분포도는 50%가 되며,
           통계 정보가 잘 수집되어 있다면 이 예상 분포도는 실제 분포도와 일치할 수 있다.
           이후 들어오는 모든 SQL문은 50%의 분포도를 갖는, 먼저 수행된 실행계획을 이용하게 된다.
      
      
   다. bind peeking의 단점
       ex2> 실제 존재하는 데이터 분포
           총 10개의 값  : 1, 1, 1, 1, 1, 1, 2, 3, 9, 10
           중복 제외 값  : 10
           최소값        : 1
           최대값        : 10
          
       -- 상수 사용
       SQL1> SELECT * FROM EMP WHERE COL1 = 2 ;   --> 실제 분포도 10%
       SQL2> SELECT * FROM EMP WHERE COL1 = 9 ;   --> 실제 분포도 10%
       SQL3> SELECT * FROM EMP WHERE COL1 = 1 ;   --> 실제 분포도 60%
      
       -- 변수 사용
       SQL1> SELECT * FROM EMP WHERE COL1 = :B1 ;   :B1 = 1
       --> bind peeking을 사용하는 경우 예상 분포도는 50%가 되며, 50%를 기준으로 실행계획을 생성
           해당 값이 1인 경우에 최적인 실행계획이 생성
          
           하지만 다음의 SQL이 이후에 많이 수행되는 경우라면 문제 발생
       SQL2> SELECT * FROM EMP WHERE COL1 = :B1 ;   :B1 = 2  
       --> 이미 50%의 분포도를 갖는 실행계획이 메모리에 존재하나 실제 분포도는 10%
           따라서 원하지 않는 실행계획이 발생할 수 있음.
          
4. BIND PEEKING에 의한 문제 발생시 해결 방법
   가. 오라클 자체에서 BIND PEEKING을 사용하지 않도록 설정
       _optim_peek_user_binds = false
       
       단점 : pro*c, pro*cobol 등에서는 버그 존재(bind peeking을 계속 사용)
              시스템 전체에서 bind peeking이 되지 않기 때문에 bind peeking이 필요한 쿼리들이 bind peeking을 사용 못함
             
   나. 적절한 통계 수집
       통계가 적절하지 못한 경우 bind peeking 시에 처음 유입되는 값에 따라 잘못된 실행계획이 생성 될수 있음.
       따라서, 테이블과 인덱스의 통계를 적절히 수집하여 유입되는 값에 따른 실행계획 이상을 사전에 차단.
       테이블 크기가 큰 경우 통계 수집에 소요되는 시간이 클 수 있으므로, 빈번한 통계 수집이 필요한 테이블들은
       파티션화 하여 해당 파티션에 대한 통계 수집을 할 수 있도록 유도
      
   다. 인덱스 힌트 사용
       bind peeking을 하더라도 힌트가 포함되는 경우 힌트를 사용하여 실행계획이 생성됨.
       따라서 해당 작업이 원하는 실행계획으로 수행될 가능성이 상대적으로 높아짐.
      
5. BIND PEEKING 문제 발생시 응급 대처 방법
   BIND PEEKING의 경우 처음 SQL이 수행될때(HARD PARSIN이 일어날때)에만 수행되고,
   SOFT PARSING인 경우에는 일어나지 않는다.
   따라서 해당 문장에 대한 HARD PARSING을 유발시키는 것이 유일한 방법이다.
  
   HARD PARSING이 유발되는 경우
   1) database shutdown/restart
   2) 커서가 LRU 알고리즘에 의해 AGED OUT 되는 경우
   3) 관련 객체에 대한 통계가 변경 되는 경우
   4) 관련 객체에 대한 구조가 변경 되는 경우
   5) 해당 객체에 대한 권한이 변경 되는 경우
   6) SHARED POOL FLUSH (ALTER SYSTEM FLUSH SHARED_POOL;)
   7) DBMS_SHARED_POOL.PURGE (10.2.0.4, Patch 5614566)

   EX> ALTER SYSTEM FLUSH SHARED_POOL ;      --> 활성 세션이 많은 경우는 불리
  
   HARD PARSING 유발 후에도 동일한 현상이 발생한다면 통계 수집을 통해 통계를 변경해야 하며,
   경우에 따라 힌트를 사용해야 할때도 있다.

[출처] BIND PEEKING (Database Advanced Knowledge) |작성자 열리기