[펌]Statement vs. PreparedStatement

PreparedStatement를 사용하는 경우 분포도
이 부분은 preparedStatement를 사용하는 경우 값의 분포도에 따라 오히려 낮은 성능을 보이는 경우로 자바서비스넷(www.javaservice.net)에서 김주현씨가 답변한 내용을 일부 수정한 것으로 상당히 이해가 쉽게 정리가 되어 있습니다.

 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이 저장되어있어서 재사용되겠지요.  

 

Statement vs. PreparedStatement의 성능 차이 

이 부분은 자바서비스넷(www.javaservice.net) 에서 statement와 preparedstatement 중 어떤 것이 나은지 논쟁 중에 박영록(poci)씨가 “static sql과 dynamic sql의 성능 차이”라는 제목으로 preparedstatement를 사용하는 경우 발생할 수 있는 문제에 대해 정리한 내용을 일부 수정한 것으로 소모적인 논쟁 중에서 빛을 발하는 내용입니다.

 prepared statement에서는 보통 변수를 설정하고 바인딩하는 static sql이 사용되고 statement에서는 쿼리 자체에 조건이 들어가는 dynamic sql이 사용됩니다. (주 - 여기서 언급하고 있는 static/dynamic은 일반적으로 얘기하는 동적 sql과는 다른 의미로, 실행계획이 매번 새로 생성되는지 아닌지에 관한 것입니다.)  preparedStatement가 파싱 타임을 줄여주는 것은 분명히 중요한 장점이지만, static sql을 사용하는데 따르는 퍼포먼스 저하를 생각하지 않을 수가 없습니다.  이를테면 이런 예가 있을 수 있습니다.  dynamic sql을 사용하는 경우 자바에서 다음과 같은 SQL을 생성했다고 가정해봅시다. 

 

SELECT ... FROM   TAB1 WHERE  COL1 LIKE '%' AND    COL2 LIKE '%' AND    COL3 LIKE 'ABC%'  그리고 인덱스가 COL1 + COL2 + COL3 로 걸려 있습니다.  그렇다면 실행계획은 당연히 인덱스에서 COL3 컬럼만을 액세스하게 설정될 것입니다.  그런데 다음과 같은 static sql을 봅시다.  SELECT ... FROM   TAB1 WHERE  COL1 LIKE :A || '%' AND    COL2 LIKE :B || '%' AND    COL3 LIKE :C || '%'  이런 식으로 SQL을 작성했다고 합시다.

 

A, B, C에 어떤 값이 들어올지 모르니까 인덱스의 COL1, COL2, COL3 컬럼을 차례대로 스캔하도록 처리 경로가 잡힙니다. 그런데 조건으로 A, B는 NULL, C에는 'ABC'라는 값이 들어왔습니다. COL3 컬럼만 랜덤으로 액세스해서 테이블로 가면 될 것을 인덱스 풀스캔을 해버립니다.  이와 같이 static sql은 변수에 어떤 값이 바인딩 되는지를 알 수 없기 때문에 가장 일반적인 형태로 실행 계획을 작성합니다. 때문에 통계 자료를 제대로 활용할 수 없고 인덱스 활용 등에서 비효율이 발생할 확률이 높아집니다.  이런 예는 정말로 많습니다. 이를테면 TABLE1의 COL1의 분포도가 값이 'A'인 게 100건, 'B'인 게 100만 건 있다고 할 경우, COL1 = :VAR 와 같이 바인딩을 시켜놓으면 늘 풀스캔을 하게 실행계획을 잡습니다.

 

그런데 :VAR에 'A'가 들어가는 경우 인덱스를 타면 금방 결과가 나올 것을 풀스캔 후에 결과를 리턴하게 되죠.  static sql의 활용으로 파싱 타임을 절약해서 매번 실행 계획을 작성하는 것을 피하는 대가로 좀더 비효율적인 실행 계획이 수립된다는 것이죠. 일반적으로 SQL의 실행과정에서 파싱과 실행 계획 수립 과정은 전체 SQL 수행 시간에서 큰 비중을 차지하지 않습니다. 가장 큰 비중으로 차지하는 것은 테이블에서 로우를 가져오는 과정이고 파싱 시간은 이의 10분의 1에 불과합니다. 그런데, 이 10%의 시간을 줄이자고 90%의 시간에서 비효율이 발생하게 만드는 일이 생길 수 있습니다. 

 

물론 쿼리 생성 단계에서부터 어느 정도는 해결할 수 있는 문제입니다만, 그로 인해 자바 코드와 SQL이 복잡하게 얽히는 문제도 결코 작은 문제가 아니죠.  얼마전 대용량 데이터베이스 I, II를 모두 수강했습니다. 강사분께서 계속 강조하는 말씀이 두 가지가 있었습니다. 첫째는 원리를 이해하고 이론적으로 따져보는 것이 중요하다는 것, 그리고 둘째는 이론적으로 따져서 작성한 SQL에 대해 반드시 실행 계획을 떠보라는 것이었습니다. 상반되는 이야기인 것처럼도 보이고 일반적인 이론과 경험의 조화를 말하는 것처럼도 보이지만 저에게는 이론적인 체계를 먼저 잡아나가고 원리를 따져본 다음에 그 이론을 검증해보라는 말로 들렸습니다.  

 

출처 : http://helols.tistory.com/13

[출처] Statement vs. PreparedStatement|작성자 써니