제5 외국어 영역2007.11.21 17:42

펌 : http://cafe.naver.com/jspworld.cafe?iframe_url=/ArticleRead.nhn%3Farticleid=31
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에 대해 반드시 실행 계획을 떠보라는 것이었습니다. 상반되는 이야기인 것처럼도 보이고 일반적인 이론과 경험의 조화를 말하는 것처럼도 보이지만 저에게는 이론적인 체계를 먼저 잡아나가고 원리를 따져본 다음에 그 이론을 검증해보라는 말로 들렸습니다.  


Statement vs. PreparedStatement의 JDBC 드라이버별 속도 차이
이 부분은 O’Reilly 출판사에서 나온 “Java Programming with Oracle JDBC” 란 책의 19장 Performance 에서 statement와 preparedStatement를 JDBC 드라이버별로 속도를 비교한 자료이다. 결론적으로 실 프로젝트에서 여기 결과를 따라 프로그래밍을 할 수는 없겠지만 개념적으로라도 알고 있어야 할 내용이다.
 There's a popular belief that using a PreparedStatement object is faster than using a Statement object. After all, a prepared statement has to verify its metadata against the database only once, while a statement has to do it every time. So how could it be any other way? Well, the truth of the matter is that it takes about 65 iterations of a prepared statement before its total time for execution catches up with a statement. This has performance implications for your application, and exploring these issues is what this section is all about.   When it comes to which SQL statement object performs better under typical use, a Statement or a PreparedStatement, the truth is that the Statement object yields the best performance. When you consider how SQL statements are typically used in an application--1 or 2 here, maybe 10-20 (rarely more) per transaction--you realize that a Statement object will perform them in less time than a PreparedStatement object. In the next two sections, we'll look at this performance issue with respect to both the OCI driver and the Thin driver.  The OCI Driver Table 19-3 shows the timings in milliseconds for 1 insert and 1,000 inserts in the TESTXXXPERF table. The inserts are done first using a Statement object and then a PreparedStatement object. If you look at the results for 1,000 inserts, you may think that a prepared statement performs better. After all, at 1,000 inserts, the PreparedStatement object is almost twice as fast as the Statement object, but if you examine Figure 19-1, you'll see a different story.  Table 19-3: OCI driver timings (in milliseconds) Inserts Statement PreparedStatement  1 10 113  1,000 2,804 1,412   Figure 19-1 is a graph of the timings needed to insert varying numbers of rows using both a Statement object and a PreparedStatement object. The number of inserts begins at 1 and climbs in intervals of 10 up to a maximum of 150 inserts. For this graph and for those that follow, the lines themselves are polynomial trend lines with a factor of 2. I chose polynomial lines instead of straight trend lines so you can better see a change in the performance as the number of inserts increases. I chose a factor of 2 so the lines have only one curve in them. The important thing to notice about the graph is that it's not until about 65 inserts that the PreparedStatement object outperforms the Statement object. 65 inserts! Clearly, the Statement object is more efficient under typical use when using the OCI driver.   Figure 19-1. OCI driver timings      The Thin Driver If you examine Table 19-4 (which shows the same timings as for Table 19-3, but for the Thin driver) and Figure 19-2 (which shows the data incrementally), you'll see that the Thin driver follows the same behavior as the OCI driver. However, since the Statement object starts out performing better than the PreparedStatement object, it takes about 125 inserts for the PreparedStatement to outperform Statement.  Table 19-4: Thin driver timings (in milliseconds) Inserts Statement PreparedStatement  1 10 113  1,000 2,583 1,739    Figure 19-2. Thin driver timings      When you consider typical SQL statement usage, even with the Thin driver, you'll get better performance if you execute your SQL statements using a Statement object instead of a PreparedStatement object. Given that, you may ask: why use a PreparedStatement at all? It turns out that there are some reasons why you might use a PreparedStatement object to execute SQL statements. First, there are several types of operations that you simply can't perform without a PreparedStatement object. For example, you must use a PreparedStatement object if you want to use large objects like BLOBs or CLOBs or if you wish to use object SQL. Essentially, you trade some loss of performance for the added functionality of using these object technologies. A second reason to use a PreparedStatement is its support for batching.  Batching As you saw in the previous section, PreparedStatement objects eventually become more efficient than their Statement counterparts after 65-125 executions of the same statement. If you're going to execute a given SQL statement a large number of times, it makes sense from a performance standpoint to use a PreparedStatement object. But if you're really going to do that many executions of a statement, or perhaps more than 50, you should consider batching. Batching is more efficient because it sends multiple SQL statements to the server at one time. Although JDBC defines batching capability for Statement objects, Oracle supports batching only when Prepared-Statement objects are used. This makes some sense. A SQL statement in a PreparedStatement object is parsed once and can be reused many times. This naturally lends itself to batching.   The OCI Driver Table 19-5 lists Statement and batched PreparedStatement timings, in milliseconds, for 1 insert and for 1,000 inserts. At the low end, one insert, you take a small performance hit for supporting batching. At the high end, 1,000 inserts, you've gained 75% throughput.  Table 19-5: OCI driver timings (in milliseconds) Inserts Statement Batched  1 10 117  1,000 2,804 691   If you examine Figure 19-3, a trend line analysis of the Statement object versus the batched PreparedStatement object, you'll see that this time, the batched Prepared-Statement object becomes more efficient than the Statement object at about 50 inserts. This is an improvement over the prepared statement without batching.   Figure 19-3. OCI driver timings for batched SQL      WARNING:  There's a catch here. The 8.1.6 OCI driver has a defect by which it does not support standard Java batching, so the numbers reported here were derived using Oracle's proprietary batching.  Now, let's take a look at batching in conjunction with the Thin driver.  The Thin Driver The Thin driver is even more efficient than the OCI driver when it comes to using batched prepared statements. Table 19-6 shows the timings for the Thin driver using a Statement object versus a batched PreparedStatement object in milliseconds for the specified number of inserts.  Table 19-6: Thin driver timings (in milliseconds) Inserts Statement Batched  1 10 117  1,000 2,583 367   The Thin driver takes the same performance hit on the low end, one insert, but gains a whopping 86% improvement on the high end. Yes, 1,000 inserts in less than a second! If you examine Figure 19-4, you'll see that with the Thin driver, the use of a batched PreparedStatement object becomes more efficient than a Statement object more quickly than with the OCI driver--at about 40 inserts.  Figure 19-4. Thin driver timings for batched SQL      If you intend to perform many iterations of the same SQL statement against a database, you should consider batching with a PreparedStatement object.   

Posted by is윤군
TAG