쿼리 파싱 시간 측정 - query parsing time

db 프로그래밍을 짜실 때 꼭 한가지를 기억하세요.. 저는 자바로 설명하겠지만 이건 꼭 언어를 따지는
건 아닙니다.

가령 이런 SQL문이 있다고 하죠.

select * from tab where id = 1;

그리고 시스템에서 id 를 입력받아서 매번 각 id에 대해 위의 쿼리를 실행한다고 하죠.

그러면 다음과 같이 쿼리를 생성해서는 안됩니다.

Connection con = null;
Statement stmt = null;

String query = "select * from tab where id = " + id;

stmt = con.createStatement(stmt);
ResultSet rs = stmt.executeQuery();

이와같이 코딩하면 SQL문장이 DBMS에 개별적으로 들어갑니다.
즉,
select * from tab where id = 1;
select * from tab where id = 2;
select * from tab where id = 3;
select * from tab where id = 4;
select * from tab where id = 5;

가 전부다 다른 문장으로 처리된다는 말이죠. 다른 문장으로 처리된다는 말은 매번 저 문장이
파싱되야한다는 말입니다.

그러나 쿼리를

select * from tab where id = ?

와 같이 만들고 ? 자리에 값을 넣는 방법이 어떤 언어든지 있습니다.

이럴때 ? 를 바인드 변수라고 하는데, 바인드 변수를 사용하면 위의쿼리는

select * from tab where id = :id_val;

이렇게 치환되죠..

그리고 개별 쿼리시마다 동일한 sql문을 수행하되 id_val의 값이 바뀌게 됩니다.

자바의 경우는 이것이 PreparedStatement입니다. 또 어떤분은 PreparedStatement를 쓰긴
하는데 정말 이상하게 씁니다. 가령 다음과 같은 식이죠.

String query = "select * from tab where id = " + id;
PreparedStatement pstmt = con.prepareStatement(query);

이런식이죠... 이렇게 해서야 바인드 변수가 쓰이지 않습니다.

String query = "select * from tab where id = ?"
PreparedStatement pstmt = con.prepareStatement(query);
pstmt.setInt(1, id);

이런식으로 코딩해야합니다..

그러면 DBMS에는 하나의 쿼리인 select * from tab where id = ? 만 들어가고
DBMS는 이 문장만 파싱한뒤에 바인드 변수값 바뀌는 처리는 파싱을 제외하고 해줄 수 있습니다..

파싱이 뭐 대단하겠냐 하겠지만 파싱이 많으면 CPU점유율이 높아집니다.. 매일 CPU한계치에
도달해서 뺑뺑이 치는 DBMS에서 이와같이 PreparedStatement를 쓰고 안쓰고의 차이는
심지어 CPU 차지율을 20% 이상 차이나게 합니다..

(말이 20% 지 평상시에 40% 의 CPU가 사용되는 시스템은 60% CPU를 먹게 만든단 뜻입니다..)

Statement로만 도배를 해놓은 경우 급하면 급한대로 오라클의 init{SID}.ora파일에서
CURSOR_SHARING=FORCE로 선언해주면 급하게 해결을 볼 수 있습니다..

그러나 오라클에서는 DSS나 QUERY REWRITE를 사용하는 환경에서는
이 매개변수를 FORCE로 고치지 말라고 경고하고 있습니다.

얼핏 생각해봐도 아시겠지만, CURSOR_SHARING=FORCE로 해주면 대부분의 쿼리를 오라클이 임의로 수정하여
바인드 변수를 쓰게 만들어 실행계획이 매번 동일하게 돌아가는 효과를 낳습니다.
따라서 QUERY REWRITE와 DSS에서 필요한 그때 그때에 맞는 정확한 플랜이 안나오게
될 수 있다는 것이죠.

QUERY REWRITE를 쓰는지 안쓰는지 모르겠다고 그러시면 안쓰는 것입니다. 제가 알기로는
QUERY REWRITE가 8i 이하에서는 Materialized View외에서는 사용되지 않습니다..
9i 부터는 모르겠군요.

만약 자신의 데이터베이스에서 얼마나 많은 재파싱이 일어나고 싶은지를 알고 싶다면,
다음 스크립트를 sys로 접근한뒤 실행해보세요.

prompt
prompt * SQL문 parsing time 구하기
prompt
SELECT         NAME, 
        VALUE 
FROM V$SYSSTAT
WHERE NAME = 'parse time cpu'
or NAME = 'parse time elapsed'
or NAME like 'parse count%';

결과는 대략 다음과 같이 나타납니다.

NAME                                  VALUE
------------------------------ ------------
parse time cpu                            0
parse time elapsed                        0
parse count (total)                 322,128
parse count (hard)                      662

SQL> 

여기서 total 은 전체 파싱된 sql문장의 수 이며 (hard)는 하드 파싱, 즉 새로
파싱된 문장의 수를 의미합니다.

이 비율과 parse time cpu 에 나타난 parsing 에 소요된 cpu 시간을 사용해
접근하시면 됩니다.