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

요즘 시간이 엄청 많아지면서(프로젝트 끝나고 마땅한 일감이 없다보니 백수가 되었음..공실기간이 커져서 걱정이..ㅠㅠ..) 한동안 욕심이 있었던 좀더 심층있는 Oracle 공부를 할 시간이 주어지게 되었다. 그러다보니 거기에 나온 예제들을 Spring을 이용하여 연동해보고 싶어진 충동이 생겨서 삽질아닌 삽질의 결과를 정리해보고자 한다

 

사실 지금 쓰는 내용은 우리가 프로젝트를 하는데 있어서 개발하는 기간에는 필요한 내용이 아니다. 적어도 실제 운영단위 레벨의 부하를 줘가며 하는 테스트를 하는 시점 전까지는 지금 쓰는 내용을 적용할 상황이 없다. 우리가 SQL을 Trace 하는 이유는 WAS가 실행하는 SQL 중 어떤 SQL이 부하를 많이 주는지에 대한 것을 확인하고자 함인데 그러한 부분은 우리가 코딩을 하는 시점에 이미 실행계획을 봐가면서 자신의 SQL을 검증하기 때문에 적어도 코딩하는 시점에서는 이렇게 SQL을 Trace 하는 상황이 발생하진 않는다. 그러나 시스템 오픈 전의 운영 단위 테스트에서는 얘기가 달라진다. 테스트 툴로 WAS에 여러명이 동시접속을 하는 상황을 만들며 부하테스트를 하기 때문에 이 과정에서 WAS의 어떤 페이지가 응답이 늦는지 확인할수가 있고 또 그 페이지에 사용된 여러 SQL에서 어떤 SQL이 문제가 있는지 확인해야 할 상황이 발생하기 때문이다. 그리고 이런 작업은 통상 DBA랑 같이 수행하기 일수인데 이럴 경우 적어도 DBA가 SQL을 분석할수 있는 환경을 제공하게끔은 해줘야 DBA도 작업을 할 수 있을것이다. 때문에 지금 당장은 알아둘 상황이 아니더라도 차후에 발생할 경우를 대비해 이러한 내용을 정리해두고자 한다. 그리고 이 글에서는 Spring에서의 Oracle SQL Trace에 대한 내용 위주이므로 SQL Trace에 대한 상세적인 내용은 적질 않는다. 그런 내용은 관련 서적을 보길 바란다. 그리고 여기서 설명하는 내용은 Spring Framework 3.0.7, Oracle 11g, mybatis 3.1.1, mybatis-spring 1.1.1을 maven을 이용하여 테스트했으며 SQL Trace에 관한 내용은 오라클 성능 고도화 원리와 해법 I ((주)비투엔컽설팅, 조시형 지음) 책에서 인용했음을 먼저 밝혀둔다

 

우리가 Spring에서 DB 연동을 할때 가장 먼저 해야 할 것은 바로 DataSource를 만드는 작업이다. 흔히 WAS의 것을 쓰든 자체적으로 하든 Connection Pool을 만들어야 하는데 이 Connection Pool을 만드는 시점에 SQL Trace를 시작한다고 설정해주는 작업이 해줘야 SQL Trace가 가능하다. 다음의 Spring DataSource 설정을 보자.

 

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close"> <property name="driverClassName" value="oracle.jdbc.driver.OracleDriver" /> <property name="url" value="jdbc:oracle:thin:@localhost:1521:orcl" /> <property name="username" value="study" /> <property name="password" value="study" /> <property name="defaultAutoCommit" value="false" /> <property name="initialSize" value="10"/> <property name="maxActive" value="10"/> <property name="maxIdle" value="10"/> <property name="connectionInitSqls"> <list> <value>ALTER SESSION SET SQL_TRACE=TRUE</value> <value>ALTER SESSION SET TRACEFILE_IDENTIFIER='terry_ora'</value> <value>CALL DBMS_APPLICATION_INFO.SET_MODULE('Spring Connection', 'SamplePrj')</value> </list> </property> </bean>

 

흔히 Spring에서 DataSource를 만들때 많이 보는 xml 구문이다. Connection Pool을 만들기 위해 Apache Commons-DBCP 1.4 를 사용하였다(버전을 얘기한 이유가 있는데 왜 그런지는 다음 글에서 설명하겠다) 여기서 우리가 눈여겨 봐야 할 프로퍼티가 있는데 그것은 connectionInitSqls 프로퍼티이다. 이 프로퍼티가 하는 역할은 Connection Pool에서 Connection을 만든뒤에 추가적으로 실행해줘야 할 SQL문, 프로시저, 또는 함수 실행하는 구문을 적어주어 Connection을 만든뒤에 이러한 구문들을 실행시켜 Connection을 초기화 해주는데 있다. 이제부터 각각 적어놓은 의미에 대해 설명하고자 한다.

 

ALTER SESSION SET SQL_TRACE=TRUE 는 Oracle의 현재 세션(Oracle 세션을 말한다)에 SQL Trace를 하겠다는 의미이다. Toad, SQL Gate, SQL Developer와 같은 쿼리툴에서는 하나의 접속에 여러 세션을 맺을수 있고 이러한 각각의 세션에 이와 같이 SQL Trace를 지정할 수 있다. 그러나 WAS에서는 Connection과 Database Session은 동일한 개념이므로 Connection Pool에 10개의 Connection을 만든것은 곧 Oracle 세션 10개를 만들었다는 것과 같은 의미가 된다.그래서 개개의 Connection(=Oracle 세션)에 SQL Trace를 하겠다는 의미가 된다

 

ALTER SESSION SET TRACEFILE_IDENTIFIER='terry_ora' 는 SQL TRACE를 시작하게 되면 관련 내용이 파일로 기록되는데 이 파일의 이름 뒤에 terry_ora 라는 것을 붙인다는 의미이다. 그러므로 작업 성격의 필요에 따라서 terry_ora 라는 부분을 다른 값을 주어도 된다. SQL Trace를 시작하면 Trace 관련 내용이 파일로 저장이 된다(Windows에 오라클을 설치했을 경우 관련 내용이 Oracle이 설치된 디렉토리\diag\rdbms\Oracle의 Service_Name\Oracle의 Service_Name\trace 디렉토리에 파일이 만들어진다. 나의 경우는 Oracle의 Service_Name이 orcl 이어서 d:\oracle\diag\rdbms\orcl\orcl\trace 였다) 이것을 설정하지 않을 경우 Oracle은 Oracle Service_Name_ora_숫자.trc 형태(예를 들면 orcl_ora_6604,trc)로 파일을 만들기 때문이다. Toad같은 쿼리툴에서 현재 세션을 SQL Trace 할 경우 기록되는 파일명이 어떤건지는 다음의 쿼리를 실행하면 알 수 있다

 

select r.value || '/' || lower(t.instance_name) || '_ora_'  
    || ltrim(to_char(p.spid)) || '.trc' trace_file          
from   v$process p, v$session s, v$parameter r, v$instance t
where  p.addr = s.paddr                                     
and    r.name = 'user_dump_dest'                            
and    s.sid = (select sid from v$mystat where rownum = 1) ;

 

다음의 그림은 이렇게 했을 경우 trace 파일이 어떻게 생성되는지를 알 수 있는 윈도우 탐색기를 캡춰한 화면이다(Oracle을 D:\Oracle에 설치했다)

 

 

Oracle Service_Name이 orcl이어서 orcl_ora_숫자_terry_ora.trc 형태의 파일이 Connection Pool에서 만들어지는 Connection 갯수만큼 만들어진다. 근데 지금 보면 파일이 중복되어 만들어진다. 예를 들면 orcl_ora_6164.trc도 있고 orcl_ora_6164_terry_ora.trc 파일도 만들어진다. 하지만 나중에 분석할때는 별칭이 붙어있는 trc 파일을 이용해서 분석하면 된다. 이렇게 별칭을 사용해서 내가 SQL Trace을 하는 내용이 어느 파일에 기록되는지를 알기 쉽게 해준다.

 

CALL DBMS_APPLICATION_INFO.SET_MODULE('Spring Connection', 'SamplePrj')는 Module과 Action의 이름을 각각 Spring Connction과 SamplePrj로 바꾸겠다는 의미이다. Module과 Action에 대한 이해를 하기에 앞서 먼저 다음의 그림을 보도록 한다

 

 

이 그림은 SQL Developer를 실행한뒤의 세션 모니터 화면을 캡춰한 것이다. 원래는 한줄만 나오는게 아니지만 Username 컬럼을 STUDY로 필터링을 해서 한줄만 보이게 했다. 여기 화면을 보면 Module엔 SQL Developer가 있고 Action엔 null이 있는데 Module엔 이 세션을 열게한 프로그램(여기서는 SQL Developer) 이름이 보이게 된다. SQL Gate나 Toad를 실행해본뒤 세션 모니터 화면에서 Module 값을 확인해보면 해당 프로그램명이나 또는 실행 파일 이름이 들어가 있는 것을 확인할수 있다.

근데 만약 우리가 Spring을 DB와 연동하여 Connection Pool을 만들면 세션 모니터에서 어떻게 보일까? 다음의 그림과 같은 화면이 나타난다

 

 

세션이 Connection Pool에서 지정한 Connection 갯수만큼 만들어지지만 이 세션들의 Module과 Action이 각각 null로 들어가 있게 된다. 그럼 Module과 Action을 왜 사용하는 것일까? Oracle 10g 이전에는 WAS에서 맺은 Oracle 세션만 따로 찾아 이를 SQL Trace를 할 수 있는 방법이 없었다. 왜냐면 어떤 세션이 WAS에서 맺은 세션인지를 알수 있는 방법이 없었다. 그러나 Oracle 10g부터 service, module, action을 사용한 trace 설정이 가능해짐으로써 WAS에서 맺은 Connection의 Module과 Action 이름을 달리 주어 WAS에서 맺은 Connection에 대한 구분이 가능해졌고 그로 인해 WAS에서 맺은 Connection만을 대상으로 따로 SQL Trace를 할수 있게 되었다. 그래서 WAS에서 Connection Pool을 만들때 dbms_application_info 패키지의 set_module 함수를 실행하면 세션 모니터에 다음과 같이 나타나게 된다.

 

 

그림에서 보듯이 Module과 Action이 각각 Spring Connection과 SamplePrj로 설정된 것을 알수 있다. 그래서 WAS에서 맺은 세션에 대한 구분이 가능해져 그 세션을 대상으로 한  SQL Trace를 할수 있게 된다.

 

지금까지의 내용은 SQL Trace를 할수 있게끔 환경 조성만 한것이지 실제 SQL Trace를 시작한 것은 아니다. 다음 글에서는 이렇게 설정한 내용을 가지고 어떻게 SQL Trace를 하는지를 설명하겠다