[이창훈]수많은 생각보다 한 번의 시도를 하자

오라클 교육과정을 거치면서 이정도면 충분하지 않을까? 생각해 본 적이 있다. 그러나 머지않아 그 한계를 느끼게 되고, ‘이제부터 시작이구나’ 라는 생각을 다시금 하곤 한다.

 Dynamic Performance View (V$), 막연하게 Database Performance 관련 정보를 수집하여 V$로 명명된 View 라는 것만 알고 있었지, 정작 직접 쿼리해 보며, 레퍼런스 문서를 뒤지고 각각의 뷰들을 조인해 본 적이 없었다. 아마도 내가 무엇을 원하는지 몰랐고, 구현해야 될 목표가 없어서 한번 읽어 보기만 하고, ‘아 이렇구나’ 하고 이해만 하고 만 것이다. 그 결과 어느정도 시간이 흐른 후, 내용은 머리에서 금방 잊혀졌다.

 때 마침 Maxgauge 성능 모니터링 툴을 사용하면서,

                      여기서 보여 지는 수많은 지표들을 SQL 쿼리로 구현할 수 있을까?

 라는 의문을 가지게 되었다. 오라클은 V$에 모든 성능 지표를 저장하기 때문에 당연히 가져오는 정보도 모두 V$에 저장되어 있을 것 이라는 생각 때문이었다.
 

                                 그림 (1) Maxgauge 화면     

"그러나 과연 구현할 수 있을까?"

 처음에는 망설였다. 그러나 이번에는 목표가 있으니, 해야 되는 방향도 있으니, 실천 해야겠다 라는 마음을 먹었고, 우선 시도나 해보자 라고 생각했다. 드디어 한번 읽어봐야 했음에도 미뤄 두었던 Reference 문서를 한부 뽑아 성능 모니터링 쿼리 작성에 들어갔다.

       그림 (2) Maxgauge Lock Tree 화면

가장 만만해 보이는(?) Lock Tree를 시도해본다. 가장 눈에 띄는 지표는 각각의 Lock을 잡고 Holder, Waiter가 누군지 보여주는 Type Held, Mode Requested와 어떤 Sql 구문으로 Lock이 발생하는지 알려주는 Sql Text 그리고 세션의 정보를 나타내는 schema, machine, sid 등, 이런 정보들을 보며 V$SESSION, V$LOCK, V$SQLAREA 등의 뷰를 이용하면 되겠구나 라는 생각을 하며 조인을 하게 된다.

 레퍼런스 문서를 뒤져 각각의 v$ 컬럼에 대해 참고해 보고 하나하나 쿼리를 구현해 보았다. 어느 정도 시간이 지났을까? 문제는 금방 나타났다. Wait에 빠져있는 세션을 구하기는 V$SESSION의 lockwait 컬럼으로 쉽게 구할 수 있지만, Holder 세션을 구하는 게 문제였다.

/* Lock Waiter 구현 쿼리 */
select  lpad(' ',3,'-')||b.sid "SID" , d.spid,
        decode(a.lmode,6,a.type,'--') "Type Held",
        decode(a.lmode,0,'---',1,'null',2,'row-S', 3,'row-X', 4,'share',5,'S/Row-X',6,'exclusive') "Mode      
        Held",
        decode(a.request,6,a.type,'--') "Type Request",
        decode(a.request,0,'---', 1,'null',2,'row-S', 3,'row-X', 4,'share',5,'S/Row-X',
                                  6,'exclusive') "Mode request",b.status,b.event,c.SQL_TEXT,
                                  round(c.elapsed_time/1000000,0) "Elapsed Time",
b.PROGRAM,b.MODULE,b.ACTION,b.SCHEMANAME,b.MACHINE,b.OSUSER,
                                     b.LOGON_TIME,b.SERIAL#,b.USERNAME
from v$lock a,v$session b,v$sqlarea c,v$process d
where a.kaddr=b.lockwait
and b.sql_address=c.address
and b.paddr=d.addr;

어떻게 하면 Holder를 구할 수 있을까?

 고심 끝에 컬럼 lockwait가 null인 세션 중에 v$lock의 TYPE이 "TX"을 거는 세션만 뽑아 보기로 했다. 아무래도 lockwait 컬럼이 아닌 것이, Holder일 가능성이 크고 그중에  v$lock의 type이 존재한다면, 그것은 holder라고 생각했기 때문이다. 그러나 생각과 달리 값이 나오지 않았다.

왜 그런 것일까? 조인의 연결고리가 잘못된 것인가?

그때부터 하나씩 컬럼을 제거하고 조인의 연결고리를 바꿔 보며 실행을 해봤다.
어느 정도 시간이 지났을까... 마침내 그 이유를 알아냈다.
Holder 세션의 status 컬럼이  “inactive" 상태였기 때문에, 세션의 sql_address값이 존재하지 않았던 것이다. 그 때문에 결국 v$sqlarea에 address나 hash_value가 일치하는 값이 없었고 당연히 결과가 나올 수 없었던 것이다.

그럼 어떻게 이전에 사용했던 sql_address나 hash_value를 구할 수 있을까?

 다행히 v$session에서 prev_hash_value 컬럼을 제공해 이전 값을 누적하고 있었고 쉽게 Holder 세션의 sql_address나 hash_value을 구할 수 있었다.

from v$lock a,v$session b,v$sqlarea c,v$process d
where a.sid=b.sid
and b.lockwait is null
and a.type='TX'
and b.PREV_HASH_VALUE=c.hash_value
and b.paddr=d.addr

 

이제야 잘나온다. 어떻게 보면 간단한 조인 조건이지만, 이 결과를 찾기 위해 수많은 실험을 해봐야 했다.

     그림 (3) SQL Used 화면

이번에는 SQL Used를 구현해본다. SQL Used 는 세션 모니터링을 시작한 이후부터 실행된 모든 SQL들을 확인 할수 있는 영역으로 각 사용된 SQL 문장마다의 자세한 통계정보를 제공한다.

 이제 컬럼 구조를 파악해 본다. Reference를 살펴보니 값의 대부분을 v$sqlarea에서 가져올 수 있었다. average부분만 약간의 연산이 필요할 것 같다.

자 구현 해보자!

Lcok Tree 보다는 수월하게 쿼리가 작성되었다. 컬럼을 나열하고 average값은 각 누적값을 실행 횟수로 나눈 1회 평균값이기 때문에 Buffer Gets / Executions처럼 나누어 주면 간단히 나올 것 같았다.

그런데 웬걸 ORA-01476 ERROR에 봉착했다.
제수가 0이란다. ‘ 아 실행 횟수가 0인 것도 있겠구나.’

즉 Executions 값이 0일 때가 있기 때문에 발생 하게 된 것이다.

간단히 DECODE 함수를 썼다.

decode(EXECUTIONS ,0,0, BUFFER_GETS/EXECUTIONS)

값이 0일때는 그냥 0값을 반환하고, 0이 아닐 때 연산을 하게 만들었다.

/* Sql Used 구현 쿼리 */
select
b.SQL_TEXT,a.module,b.FIRST_LOAD_TIME,b.VERSION_COUNT,b.OPTIMIZER_MODE,b.EXECUTIONS,
       b.BUFFER_GETS,round(decode(EXECUTIONS ,0,0, BUFFER_GETS/EXECUTIONS),0) "BUFFER_GETS AVG",
       b.DISK_READS,round(decode(EXECUTIONS ,0,0, DISK_READS/EXECUTIONS),0) "DISK_READS AVG",
       b.FETCHES "Rows",round(decode(EXECUTIONS ,0,0, FETCHES/EXECUTIONS),0) "Rows AVG",
       b.SORTS,round(decode(EXECUTIONS ,0,0, SORTS/EXECUTIONS),0) "SORTS AVG",
       round(b.CPU_TIME/1000,2) "CPU Time",round(decode(EXECUTIONS ,0,0, CPU_TIME/1000/EXECUTIONS),2)"CPU_TIME AVG",
       round(b.ELAPSED_TIME/1000,2) "Elapsed Time",round(decode(EXECUTIONS ,0,0, ELAPSED_TIME/1000/EXECUTIONS),0) "ELAPSED_TIME AVG"
from v$session a,v$sqlarea b
where a.sql_address(+)=b.address;

쿼리 마지막 부분 ELAPSED_TIME과 CPU_TIME을 1000으로 나눈 건 원 컬럼의 값이microsecond, us ( 1/100만 초)로 표현되고 Sql Used가 보여주는 값은 millisecond, ms (1/1000 초) 이기 때문이다.

아직 신입인 나에게는 정말 값진 경험이었다. 그동안 접해보기 힘든 V$ View를 이해하는데 많은 도움이 되었기 때문이다. 그동안 성능을 참조하기 위해 V$ View를 뒤져보는 일이 필요하다는 “생각”은 했지만 한번의 “시도”도 하지 않았던 것이 사실이다.
 
무엇이든지 한 번의 실행이 힘들다. 일이든, 사랑(?)이든.. 이번 일을 토대로 내가 생각하는 바를 꼭 실천해 봐야겠다는 생각이 든다.

마음속에 있는 것으로는 결과를 만들어 낼 수 없다.
행동으로 보이는 것, 그 자체가 결과를 보이는 것이다.