오라클 bind 변수 길이에 따른 bind mismatch

오라클은 입력된 바인드 값이 32, 128, 2000, 4000 바이트를 넘을 때마다 새로운 Child 커서를 생성한다.

즉, bind 변수를 사용한 SQL 이라도 bind 변수값의 길이가 위의 구간에 따라 새로운 Child 커서가 생성된다.

아래의 테스트 케이스로 확인해 본다.

 

varchar2(4000) 컬럼을 가진 테스트 테이블을 생성한다.

create table tab1 ( col1 varchar2(4000));

 

아래 프로시저를 수행한 후 결과값을 보면 정확히 32, 128, 2000, 4000 바이트 구간에서 child 커서가 생성되는 것을 볼 수 있다.

set serveroutput on

declare
  l_cnt number;
  l_child_cnt number;
  l_prev_child_cnt number;
  l_bind_value varchar2(4000);
  l_sql_id varchar2(13);
begin

  l_prev_child_cnt := 0;

  for c in 1..4000
  loop
    l_bind_value := lpad('A', c, '0');  
    select count(*) into l_cnt from tab1 where col1 = l_bind_value;

    if c = 1 then
      select prev_sql_id into l_sql_id -- sql_id 를 찾음
      from   v$session
      where  sid = userenv('sid')
      and    username is not null
      and    prev_hash_value <> 0;
      dbms_output.put_line('sql_id --> ' || l_sql_id);
    end if;

    select count(*) into l_child_cnt from v$sql where sql_id = l_sql_id;

    if l_prev_child_cnt < l_child_cnt then -- 새 Child 커서 생길 때마다
      dbms_output.put_line(c);
      l_prev_child_cnt := l_child_cnt;
    end if;

  end loop;
end;
/

sql_id --> 4bp0vskw058t3
1
33
129
2001

PL/SQL procedure successfully completed.

 

child 커서가 공유되지 못한 이유를 보기위해서는 V$SQL_SHARED_CURSOR 뷰를 조회해 본다.

위의 테스트 결과는 hash_match_failed 컬럼과 bind_lentgh_upgradeable 컬럼값이 'Y' 인 것을 볼 수 있다.

즉 child 커서를 공유하지 못한 이유가 hash_match_fail, bind_lentgh 때문이라는 것을 알 수 있다. hash_match_fail 은 histogram이 생성되어 있지 않아서 발생하는 것 같다.

 

 

 

HASH_MATCH_FAILED

Hash mismatch. Set to "Y" if sharing fails due to a hash mismatch, such as the case with mismatched histogram data or a range predicate marked as unsafe by literal replacement (See Bug 3461251)


BIND_LENGTH_UPGRADEABLE Bind length upgradeable and could not be shared because a bind variable size was smaller than the new value being inserted
(marked as BIND_MISMATCH in earlier versions).

 

reson 컬럼에는 Bind mismatch로 표시된다.

select
    sh.sql_id,
    sh.child_number, sh.hash_match_failed, sh.bind_length_upgradeable,
    xt.r1
from
    v$sql_shared_cursor sh
    inner join xmltable (
        '/ChildNode'
        passing xmlparse(content sh.reason)
        columns
            r1 varchar2(40) path 'reason'
    ) xt on ( 1 = 1 )
where sh.sql_id = '4bp0vskw058t3';


SQL_ID	CHILD_NUMBER	HASH_MATCH_FAILED	BIND_LENGTH_UPGRADEABLE	R1
4bp0vskw058t3	0	N	N	Bind mismatch(22)
4bp0vskw058t3	1	Y	Y	Bind mismatch(22)
4bp0vskw058t3	2	Y	Y	Bind mismatch(22)
4bp0vskw058t3	3	Y	Y	Bind mismatch(22)

 

* 참고 : child 커서가 공유되지 않는 이유

• USE_FEEDBACK_STATSCardinality feedback. Cardinality feedback is being used and therefore a new plan could be formed for the current execution.

BIND_EQUIV_FAILUREThe bind value's selectivity does not match that used to optimize the existing child cursor. When adaptive cursor sharing is used and the cursor is bind aware, then if the selectivity is outside of the current ranges and a new plan is desirable then a new child is raised with this as the reason code for non-sharing of the previous plan. For an example, see Document 836256.1. After each execution in the example, run:

select sql_id, address, child_address, child_number, BIND_EQUIV_FAILURE 
from v$sql_shared_cursor where sql_id='19sxt3v07nzm4';

once the cursor is marked as bind aware and a second plan is seen then the following will be the resultant output:  

SQL_ID        ADDRESS          CHILD_ADDRESS    CHILD_NUMBER B
------------- ---------------- ---------------- ------------ -
19sxt3v07nzm4 000000007A1C0DE0 000000007A1BF980            0 N
19sxt3v07nzm4 000000007A1C0DE0 000000007A10DDB0            1 Y

SQL_ID        ADDRESS          CHILD_ADDRESS    CHILD_NUMBER B
------------- ---------------- ---------------- ------------ -
19sxt3v07nzm4 000000007A1C0DE0 000000007A1BF980            0 N
19sxt3v07nzm4 000000007A1C0DE0 000000007A10DDB0            1 Y

As can be seen, the new version is created due to BIND_EQUIV_FAILURE