CURSOR_SHARING (FORCE, SIMILAR, EXACT)

• 정의 : SQL 조건절에 있는 상수 값들을 변수로 전환시켜 parsing 함으로써 cursor를 공유할 수 있도록 해줌
  
  
• Default value=exact
  
  
• 적용 방법 : alter session(system) set cursor_sharing = value;
  
  
• 특징
  1. EXACT : literal 값을 모두 hard paring. 대소문자, 공백, 비교 상수값이 조금만 달라도 공유할 수 없음
  2. SIMILAR
     1. literal을 bind로 변환하면서 모든 literal값에 대한 실행계획을 확인하므로 동일한 sql문이라 하더라도 여러 개의 실행계획이 수립될 수 있음
     2. similar가 적용되면 동일한 sql문에 대한 확인뿐만 아니라 bind값에 대한 확인까지 수행
     3. shared pool에서 확인할 수 있는 sql 문의 실제 실행 계획의 수를 줄이거나 그렇지 못할 수도 있음
  3. FORCE
     1. literal값을 bind값으로 바꾸면서 동일한 sql 문에 대해서 단일계획 (one-size-fits-all-plan)만을 수립
     2. rownum 사용 : rownum = 1 과 같이 프로그램 작성시 cursor_sharing=force를 설정할 경우 rownum = :b1 과 같이 Oracle 내부적으로 변경되므로 전체범위 처리후에 해당 1건의 row를 추출하게 된다. 이때에는 rownum <= 1과 같이 변경 후 cursor_sharing=force 를 설정!!
  4. SIMILAR & FORCE의 공통점
     1. literal 값을 ":SYS_B_n" 형태의 bind값으로 변형
     2. 조건절의 상수 값을 변수로 전환시켜 실행계획을 수립하므로 실행계획의 공유비율이 높음
     3. 해당 cursor가 Memory에서 Aging out 되지 않을 경우 : 이후 수행되는 literal value에 대해서는 peek at the bind로 수행 됨
     4. peek at the bind로 해석 = "_optim_peek_user_bind"=TRUE 로 해석
        ☞ 위와 같이 해석될 경우 system level에서 cursor_sharing를 설정하는 것은 상당히 위험해 질 수 있으므로, 해당 파라미터 세팅(FORCE, SIMILAR) 시에는 필히 SESSION LEVEL OR SQL LEVEL에서 제어를 할 필요가 있음.
        
        _optim_peek_user_binds =TRUE
        Bind Value에 인덱스가 있고, 해당 SQL이 Bind Value를 이용해서 해석이 되어야 하는 경우 Query Optimizer가 Bind Value의 Column Histogram을 참조하여 SQL문을 해석 하고 실행하게 된다.
        
        
        이때, 평상시 조회가 되지 않는 조건으로 Binding이 될 경우 평상시의 PLAN과 상이하게 해석될 수 있으며, 해당 SQL의 Cursor가 Aging Out 되지 않는 동안에는 PLAN이 유지되게 된다.
        
        
        Aging Out 된 이후에는 또다시 Column Histogram을 참조하여 PLAN을 세우게 되어 이전과는 상이한 실행계획 이 수립되어 SQL이 실행될 수 있다.
        
        
        False일 경우에는 일반적인 Query Optimizing 을 하게 된다. (필수적용사항)
        
        
        alter session set "_optim_peek_user_binds"=false ;
        

문서에 대하여

• 최초작성자 : 박혜은
• 최초작성일 : 2009년 11월 19일
• 이 문서에 있는 테스트 결과는 DBMS버전과 구성된 환경에 따라 다를 수 있습니다.