Q : 특정 쿼리가 PreparedStatement로 실행하면 너무 느린데 같은 쿼리를 Statement 로 실행시는 1초도 안걸립니다. 이유는?
두번째 생각할 것은, prepared statement는 실행계획을 한번 만들고 계속 재사용한다는 것입니다. 따라서 값의 분포등이 바뀌었어도 이런걸 고려 안해주고 예전에 만든 실행계획을 계속 사용합니다. 따라서 새로이 인덱스를 만들었거나 해도 무조건 예전의 실행계획만 쓰게 되죠. 그래서 이걸 제대로 반영하려면 매번 실행 계획을 작성하는 statement를 쓰시거나 아니면 shared pool이라는 오라클내 공유메모리를 비워주면 되는데 alter system flush shared_pool; 이란 명령을 씁니다. ('_'를 빼야하는건지도 모르겠네요. 잘 기억이 안나서.. 죄송.)
세번째 생각할 내용은, 실행 계획이 매번 잘 바뀌도록 비용을 제대로 산정해주고 있는가의 문제인데, 비용 산정을 위해서는 값의 분포(히스토그램)나 전체 행의 수같은 정보가 필요합니다. 이것을 분석하는 명령은 자체 제공하는 dbms_stats 패키지가 있고, analyze명령이 있고, monitoring 옵션이 있습니다. 제대로 해주려면 이런 비용 측정 정책도 잘 정립해야됩니다. 자세한 내용은 굉장히 많으니 서적이나 asktom.oracle.com에서 검색을 통해서 참고하시기 바랍니다. 마지막으로 말씀드리고 싶은건, 자주 실행하는 명령도 아니고 그러면 그 문장 하나 Statement로 한다고 해서 큰일날 일도 없단 것입니다. 몽땅 다 Statement로 코딩했다면 모르되, 한두개 쿼리가 Statement라고 심각한 성능저하가 생기고 하지는 않습니다.
-------------------------------------------------------------------------------------------------
A2 : BIND 를 쓸 경우 실행계획이 틀어져서 느려지는 그런 경우를 unsafe literal 이라고 합니다. 예를 들어...
select * from emp where empno = :v1
위의 SQL에서 empno 는 emp의 PK입니다. 따라서 preparedStatement를 써서 BIND변수를 사용하든지 그렇지 않든지 실행계획은 똑 같이 나옵니다.
이런 경우를 safe literal 이라고 합니다. 하지만,
select * from emp where deptno > :v1
위의 경우... :v1이 ... 1 일 경우와 1000일 경우는 분포도가 전혀 다릅니다. 1일 경우에는
대부분의 deptno가 1보다는 크기 때문에... 분포도가 넓습니다. 즉, 인덱스를 타지 않는게
더 좋습니다. 하지만 deptno가 1000보다 큰 경우는 거의 없습니다. 즉, 분포도가 좁습니다. 이 경우 인덱스를 타야 합니다.
그러니가 히스토그램이라는 것은 이처럼 실재 :v1 값이 무엇이냐에 따라서 분포도가 달라지는 분포도 정보입니다. 어느 value가 어느 정도의 분포도를 가지고 있는지 그 정보를
히스토그램이라고 합니다.
문제는 BIND를 쓰면... :v1 이 1이 들어올지 1000이 들어올지에 무관하게 무조건 단 하나의 실행계획만을 만들고 일괄적으로 적용하다가 보니... 1000이 들어오는데도 불구하고 인덱스를 타지 않고 Full Scan하는 상황이 나올 수 있습니다.
즉, BIND 변수를 활용하며... 히스토그램 정보를 활용할 수 없습니다.
그렇다고면? prepatedStatement를 쓰지 말아야 할까요??? 아닙니다. 써야 합니다.
위와 같이 불충분한 통계정보(히스토그램)이 없어서 실행계획을 잘못 수립해서 느려지는 경우는 어쩔 수 없는 현재의 옵티마이져의 한계로 보아야 합니다. 이 경우 똑똑한 사람이
힌트 등을 통해서 SQL에게 올바른 실행계획을 가르쳐줘야 합니다.
아주 특수한 경우 위의 이유로 튜닝을 위해 Statement를 쓰는 경우도 있습니다.
예를 들어... 성별이 남, 여 두가지일 경우... 남자가 전체의 2%이며 아주 극소수라고 가정하면.. 남자일 경우에는 인덱스르 타야하고... 여자일 경우 인덱스를 타지 말아야 합니다.
이 경우... 어차피 distinct 값의 종류가 딱 2가지 이므로... BIND를 쓰지 않아서
하드파싱을 해봤자... 2개의 SQL 종류 밖에는 나오지 않으므로 문제가 없습니다.
select * from user where sex = 'm'
select * from user where sex = 'f'
오라클의 Shared pool의 Library cache에는 위와 같이 딱 두종류의 SQL이 저장되어있어서 재사용되겠지요. 답변이 길었는데... 위의 이야기는 DBMS에 대해서 잘 아시는 분이 아니라면 어려울 수도 있습니다.
간단하게 이야기해서 실행계획을 비교해보십시오.
BIND변수를 썼을 때와 그렇지 않았을 때를... 분명이 위의 경우 실행계획이 전혀 다르게 나올겁니다. 그러니까. Unsafe literal 이죠.
따라서 ... BIND 쓰지 않았을 경우와 똑같이 실행계획이 나오도록 힌트를 사용해서 조정해 주십시오.
반드시 preparedStatement를 쓰시구요. 어쩌다 일주일에 한번쯤 돌리는 SQL이 아니라... 수시로 똑 같은 SQL이 반복적으로 계속 들어온다면 반드시 preparedStatement로 작성하셔야합니다. 즉, OLTP에서는 무조건 써야 합니다.