Spring과 Oracle 연동시 SQL Trace 걸어두기... (2)

지난 글에서는 Spring과 Oracle DB를 연동할때 Oracle의 SQL Trace를 하기 위한 설정 내용에 대해 설명하였다. 지난 글 말미에도 얘기했지만 그 내용만으로는 SQL Trace가 실제 수행이 되지 않는다. SQL Trace를 수행하기 위한 사전 설정 작업일뿐이다. 그럼 이렇게 설정한 내용을 가지고 SQL Trace를 하는 방법에 대해 설명하도록 하겠다.

 

지난 글에서 Oracle 세션의 Module과 Action에 각각 Spring Connection과 SamplePrj로 설정한 것을 알고 있을 것이다. 이제 이 값을 가지고 SQL Trace를 걸어야 하는데 다음의 내용을 Toad 같은 쿼리툴에서 실행하면 된다

 

begin
      dbms_monitor.serv_mod_act_trace_enable(
            service_name => 'orcl'
          , module_name => 'Spring Connection'
          , action_name => 'SamplePrj'
          , waits => true
          , binds => true
      );
end;
/

 

service_name엔 Oracle의 Service_Name을, module_name과 action_name엔 이전 글에서 사용했던 dbms_application_info 패키지의 set_module 함수 실행했을때 넣었던 값들인 module 값과 action 값을 넣어주면 된다. 이것을 실행하고 난 뒤에 WAS에서 실행되는 SQL들이 추적되기 시작한다. SQL Trace를 종료하고자 할땐 다음과 같이 하면 종료가 된다

 

begin
      dbms_monitor.serv_mod_act_trace_disable(
            service_name => 'orcl'
          , module_name => 'Spring Connection'
          , action_name => 'SamplePrj'
      );
end;
/

 

마찬가지로 service_name엔 Oracle의 Service_Name을, module_name과 action_name엔 이전 글에서 사용했던 dbms_application_info 패키지의 set_module 함수 실행했을때 넣었던 값들인 module 값과 action 값을 넣어주면 된다.

 

dbms_monitor 패키지의 serv_mod_act_trace_enable을 Connection Pool을 만들때 실행시키지 않은 데는 이유가 있다. 한번 실행된뒤 같은 내용으로 다시 실행하면 이미 설정되어 있다고 하면서 에러가 발생한다. 그러면서 DataSource 만드는 작업이 실패하게 되기 때문에 이 작업은 Toad 같은 쿼리툴에서 한번만 실행해주면 된다.

 

이렇게 SQL Trace를 하면 관련 내용이 trc 파일에 기록이 되는데 trc 파일을 그대로 보기에는 우리가 정보성으로 이 내용을 알아볼수가 없다. 다음과 같이 Oracle의 TKProf 유틸리티를 이용하여 trc 파일을 알아보기 쉬운 내용으로 변환작업을 해주어야 한다(이것은 운영체제의 Command 창에서 해야한다)

 

tkprof orcl_ora_6500_terry_ora.trc report.prf sys=no

 

이렇게 변환작업을 해준뒤에 변환된 파일(위의 예를 인용하게 되면 report.prf)을 Editplus나 Notepad++ 같은 문서 편집기로 변환된 파일을 열어보면 다음과 같은 형태의 SQL Trace에 대한 결과들이 있게 된다

 

 

SELECT CUSTNO, PAYMONTH, JIRO, AUTOPAY, CREDITCARD, HANDPHONE, INTERNET

        FROM MONTH_PAY1

        WHERE PAYMONTH = '201301'

 

call       count       cpu     elapsed         disk         query        current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse           1         0.01          0.07              0              1               0               0

Execute        1         0.00          0.00              0              0               0               0

Fetch       3001         0.09          0.06              0          3129              0          30000

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total         3003         0.10          0.14              0          3130              0          30000

 

Misses in library cache during parse: 1

Optimizer mode: ALL_ROWS

Parsing user id: 91  

 

Rows     Row Source Operation

-------  ---------------------------------------------------

  30000  TABLE ACCESS FULL MONTH_PAY1 (cr=3129 pr=0 pw=0 time=44041 us cost=43 size=2918210 card=30718)

 

위와 같이 쿼리문을 실행할때의 실행 계획이나 거기에 들어간 각종 비용등을 볼수 있게 된다. 

 

이제 지난글에서 설명을 미뤄뒀던 내용에 대해 설명하고자 한다. 지난글에서 DataSource 사용시Apache Commons-DBCP 라이브러리를 썼다고 설명하면서 버전을 1.4를 썼다고 밝힌바가 있다. 버전을 밝힌 이유는 connectionInitSqls 프로퍼티가 Apache Commons-DBCP 라이브러리의 모든 버전에 존재하는 프로퍼티가 아니기 때문이다. 항상 플젝을 했을때 1.2 버전을 사용했는데 이 기능을 알아보기 위해 구글링을 하던중connectionInitSqls 프로퍼티의 존재를 알게 되었고 그래서 바로 적용해보았으나 프로퍼티가 없다는 에러가 나왔었다. 다시 구글링을 해보니 이 프로퍼티는 1.3 버전부터 추가된 버전이었다(Apache Commons-DBCP API 문서에서 BasicDataSource의 connectionInitSqls 프로퍼티 설명에 보면 나와있다) 그래서 혹시 1.2 이하 버전을 사용하고 있다면 버전을 올려서 테스트해보길 바란다. 그리고 덧붙여서 꼭 Apache Commons-DBCP 라이브러리가 아닌 다른 Connection Pool 라이브러리(예를 들면 c3p0 라이브러리..) 또는 WAS에서 제공하는 Connection Pool을 사용해도 되지만 단 Apache Commons-DBCP 라이브러리의 connectionInitSqls 프로퍼티와 같은 기능을 하는 것이 있는지 먼저 확인하고 적용하길 바란다.

 

마지막으로 이 작업의 단점 또는 불편함을 설명하겠다. 일단 가장 불편한 것은 Connection Pool에서 생성한 Connection 갯수만큼 trc 파일이 생성되는것이 가장 불편했다. 파일의 갯수가 많아지는 것도 문제이지만 특정 쿼리가 항상 특정 Connection에서만 실행되는 것이 아니기 때문에 같은 쿼리라도 생성된 모든 trc 파일에서 존재할수 있다. 또 상황에 따라 결과가 약간 다를수도 있다. 예를 들면 처음 쿼리를 실행할땐 parse 과정을 거쳐 Oracle의 라이브러리 캐시에 올려놓지만 그 쿼리를 두번째 실행할때부터는 parse 과정을 거치지 않고 라이브러리 캐시에 있는 것을 그대로 사용하기 때문에 parse 부분 결과값이 다를수도 있다. 그래서 이것을 하나의 파일로 통합해서 봐야 할 필요성을 느꼈다. 그러나 이 부분은 DBA와 협의하여 조절해야 할 성격이어서 여기서는 어떻게 결론을 내릴수가 없었다. 내가 인용한 책에서도 java 코드를 사용해서 이렇게 추적하는 부분을 설명했지만 그 코드란것이 프레임워크가 적용이 안된 순수 jdbc 코드(Connection, Statement, Result 클래스 객체를 사용하여 Database를 이용하는 방법)로 지금의 내용을 설명하고 Connection Pool을 활용한 내용으로 설명한 것이 아니기때문에 더는 부가 설명을 얻을수가 없었다. 그렇다고 세션 단위 추적이 아닌 시스템 단위로 추적의 레벨을 올려버리면 하나의 파일로 나올수는 있어도 시스템에 부하를 주기 때문에 바람직하지가 않다.