Q : 특정 쿼리가 PreparedStatement로 실행하면 너무 느린데 같은 쿼리를 Statement 로 실행시는 1초도 안걸립니다. 이유는?
A1 : 첫번째 생각할 것은 최적화기의 문제입니다. 오라클의 경우엔 규칙기반 최적화와, 비용기반 최적화기가 있는데 각각을 rule-based optimizer와 cost-based optimzer가 있습니다. 그리고 이런 최적화기를 선택하는 기준을 설정하기 위한 파라미터로 optimizer_goal 인가하는 파라미터가 있는데 이 값이 all_rows인 경우 쿼리를 olap성으로 파악해서 throughput을 최대화하고, first_rows인 경우에는 oltp성으로 파악해서 response time을 최소화 합니다. 둘다 maximize되는 모드는 없습니다. 예를 드는게, 테이블을 읽을 때 어떻게 읽을까 하는 전략인데 all_rows의 경우에는 full table scan을 지향하고, first_rows에서는 index scan을 사용합니다. 룰 기반 최적화기는 이와 달리, 각종 스캔방식에 점수를 미리 매겨놓고 이 점수대로 최적화하는 것입니다. 가령, 어떤 행을 인덱스로 읽는게 10점이면 풀 테이블 스캔은 5점 이런식입니다. 따라서 인덱스가 있다면 인덱스를 무조건 씁니다. (점수가 높으니까요.) 하지만 인덱스라는게 반환하는 행의 개수가 전체 10~15%일때 인덱스 사용의 효과가 나타나는게 일반적인데, 이 것까지는 룰 기반 최적화에서는 고려하지 않습니다. 다시 말해 옛날 방식이란 거죠. 별다른 설정을 하지 않았다면 아마도 최적화 모드가 all_rows일 텐데 반응시간을 높이고 싶다면 이것을 first_rows로 바꿔주어야합니다. 바꾸는 방법은 oracle 8i 이하에서는 init{SID}.ora (여기서 {SID}자리에는 실제 SID를 적어주면 됩니다. 예를들어 ORCL이라면 initORCL.ora가 됩니다)를 열어서 수정하고 데이터베이스를 재시작하면 됩니다. 오라클 9이상부터는 이렇게만 하면 안되고 방식이 조금 바뀌었는데, 자세한건 저도 기억이 안나네요.. 메뉴얼이나 otn에 물어보세요. (otn.oracle.co.kr 가셔서 좌측에 포럼 클릭하시면 됩니다.)
두번째 생각할 것은, 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에서는 무조건 써야 합니다.
두번째 생각할 것은, 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에서는 무조건 써야 합니다.
