Oracle bind mismatch

oracle에서는 sql문장 뿐 아니라 실행계획까지 같다고 하더라도 하드 파싱하는 경우가 있는데 다음과 같다.

1. 공백 문자 또는 줄바꿈

2. 대소문자 구분

3. 테이블 Owner명시

4. 주석

5. 힌트 사용

6. 조건절 비교값 (바인딩 처리 여부)

1 ~ 4번은 쉽게 말해 쿼리문의 스트링이 하나라도 틀어지면 오라클은 다른 문장으로 받아들인다고 볼 수 있다.

5번은 오라클 옵티마이저의 실행 계획자체를 사용자가 컨트롤 하므로 당연히 오라클 입장에서는 다른 sql문장이다.

이번에 다룰 내용은 6번 조건절 비교값에 대한 부분이다. 아래 두 쿼리를 보자.

SELECT * FROM test WHERE a=1;

SELECT * FROM test WHERE a=2;

조건에 대입되는 값만 다를 뿐이지만 오라클입장에서는 완전하게 다른 sql문이며 하드파싱을 실행한다.

우리는 이런 비효율(때에 따라서는 효율적일 수도 있다)과 sql injection등 여러 이유로 preparedStatement를 이용한

binding을 이용한다. 아래 쿼리를 다시보자.

SELECT * FROM test WHERE a=?;  pstmt.setInt(1, 1);

SELECT * FROM test WHERE a=?;  pstmt.setInt(1, 2);

위와 같이 preparedStatement를 이용하여 바인딩 처리를 하게되면 첫 sql문 실행 이후는 소프트파싱이 이루어 질 것이다.

바인딩 쿼리가 캐싱될 것이고 오라클은 인자값만 바꿔서 실행하면 되기때문이다. 물론 preparedStatement가 항상 최고의

퍼포먼스를 보장하는 것은 절대 아니다. 데이터 분포도, 상수처리 등 다양한 상황에 따라 동작은 다를 수 있다.

다시 본론으로 넘어와서, 위와 같이 바인딩 쿼리를 이용하였지만 여전히 하드파싱 하는 경우가 있다.

바로 bind mismatch이다. 아래 쿼리를 보자.

PreparedStatement ptmt = conn.prepareStatement("INSERT INTO test(name) VALUES(?)");  ptmt.setString(1, "a");  // Length = 1

ptmt.setString(1, "aaaaaaaaaa.........aa");  // Length = 33

ptmt.setString(1, "aaaaa...............................aaaaa");  // Length = 129

ptmt.setString(1, "aaaaaa...........................................aaaaaaa");  // Length = 2001

위에서 말한 대로라면 위 쿼리는 단 하나의 sql문장만 캐싱되어 있어야한다.

하지만 총 네개의 독립적인 sql문장으로 등록된다. 그 이유는 bind type이나 bind value의 크기 변화로 인해 bind변수가

공유되지 못하기 때문이다. oracle의 경우 아래와 같은 범위로 varchar2 타입의 bind 변수의 길이를 반올림 해서 사용한다.

⊙ 32

⊙ 128

⊙ 2000

⊙ 4000

따라서 위의 쿼리의 bind 변수들은 모두 bind mismatch가 발생되고 서로 다른 sql 문장으로 인식되어 버린다.

위의 경우는 최악의 경우 4번의 하드파싱이 일어나지만 바인딩 변수가 많아질수록 경우의 수는 2*n으로 증가하여

쿼리 수행과 캐시 메모리 관리에 악영향을 끼칠 수 있다.

해결 방법은 아래의 참조 링크를 참고한다.

[참조]

http://ukja.tistory.com/70

http://db.tips/pages/viewpage.action?pageId=5898371

http://helols.tistory.com/13