ORACLE MONITORING SESSION STAT
from
/*+ 이 양식도 사용한지 꽤나 시간이
흘렀군요. 가끔씩 혹은 종종 이 작은 문서에서도 방향을 잃고 헤메이기를 수십번!!! 양식을 개조할 필요가 있는 것 같습니다…*/
1.
v$sesstat 으로 할 수 있는 것들
v$sesstat view 는 말 그대로 Session 의 Statistics 정보를
조회할 수 있는 View 이다.
이 정보는 Session 이 종료될 때까지 유지되며 모든 Session 이 동일한 수의 Statistics 종류를 가진다. 이는 v$sesstat 에서
Sid 로 Count 해 보면 알 수 있다.
개인적으로 이곳에서 유용하다고 생각하는 정보는 session
logical reads 와 physical reads 이다. 추가적으로 parse count (hard), execute count 가
있겠다.
전자의 두 지표는 시스템의 I/O 를 가리키는 정보로 CPU 사용량과 깊은 연관을 가지고 있다. 물론 I/O 발생량이 낮으면서 CPU 사용량이 높은 흔하지 않은 케이스
– Parsing Time 이 긴 경우 – 도 있지만 일반적으로 I/O 는 CPU 사용량과 직결된다.
이 I/O 를 가리키는 지표를 통해 현재 경합을 많이 발생시키는
부하유발 세션을 찾을 수 있다.
후자의 parse count (hard), execute count 의
경우는 덤(?) 같은 느낌이다. parse count (hard) 의
경우는 특정 세션이 Literal SQL 수행을 하는 경우 쉽게 알아볼 수 있다. 뭐 찾아서 어떻게 할건데? 라고 생각한다면 할 말은 없지만 프로그램과 Schema, SQL 정도는 확인이 가능하고 후에 Bind 사용을
권고할 수도 있다.
execute count 는 DB 활동성을 보기위한 지표로… System Level 로
보면 목적에 부합하는 느낌이지만 Session Level 로는 무언가 이상하다.
/*+ 다른 괜찮은
지표는 무엇이 있지? */
여기까지 읽으면 왜 CPU 는 없지? 라고 생각하겠지만 ORACLE 내에 CPU 사용량을 % 로 맵핑할 수 있는 정보는 없다. 사용할 수 있는 CPU 관련 값은
time 기반이다. %로 맵핑하기 위해서는 CPU 를
조회할 수 있는 OS 명령어의 사용과 ORACLE 의 SPID 와 맵핑하는 방법이다.
(SPID 는 SERVER PROCESS ID 로 ps 명령어로 Process 를 조회했을 때 볼 수 있는 Process 의 Number 와 동일하다.)
2.
Global Temporary Table 을 이용한 모니터링 방법
앞서 이야기 한 것과 같이 v$sesstat 정보는 누적정보이고 Session 이 종료하기 전까지
사라지지 않는다. 그리고 statistic# 에 대한 v$sesstat.value 는 이 자체만으론 큰 의미가 없다.
Monitoring 이라는 것은 현재 상황에 대한 정보이다. 또 그 시각 시각마다 변화하는 값이 필요하다. 이는 Delta 값을 의미하는데 Delta 값이라 함은 특정 시간에 변화한
값을 의미한다. 변화한 값을 구하기 위해서는 과거와 현재의 Data 를
가지고 있어야 한다. 추가로 시간정보도 마찬가지이다.
이 과거 Data 를
보관하기 위한 방법으로는 v$sysstat 때 사용했었던 Host 변수
/*+ 이 표현이 맞는지 모르겠다 -_-; */
의 사용이 있다.
var variable_name
value_type
위와 같은 사용을 말한다.
SQL Prompt 에서 var 를 입력하면 사용에 대한 정보를 볼 수 있다.
그리고 이번에 이야기할
Global Temporary Table 의 사용이 있다.
왜 하필 Global
Temporary Table 인가 생각할 수 있는데 과거 Data 를 손쉽게 날려버릴 수
있기 때문이다. (추가 설명은 생략한다.)
사용에 대한 간단한 언급을 하자면 다음과 같다.
-
과거의 데이터를 Global Temporary Table 에 입력
-
현재 데이터와 과거 데이터를 가지고 가공
-
commit ( Flush Global Temporary Table )
-
처음의 과정을 반복
결과적으로 Query 는 입력,가공 두개가 된다. 여기서 시간을 구하기 위해 하나가 더 추가된다.
아래의 예제로 넘어가기에 앞서 Table 이란 형태를 택한
이유가 또 있다. 그것은 Join 형태를 다양하게 가져갈
수 있다는 것에 있다.
여기서의 조인형태는 Hash, Merge, Nested Loop 와
같은 데이터 가공방법을 말하는게 아니라 Inner Join, Outer Join 을 말함이다.
Session 의 System
Statistics 는 System 의 그것과 다르게 휘발된다. Session 이 종료되면 Statistics 도 같이 사라진다. 그렇기 때문에 Sid 라는 기준에 대해 비교대상이 없다가 생길수도
있고 있다가 사라질수도 있다. 이에 대해 PL/SQL 로는
상상이 안간다.
(아직 해보지도 않았다.)
하지만 Join 을 사용하면 간단하게 구현이 가능하다. (예제 참고)
3.
Monitoring Script 예제
<Global Temporary Table 의 생성>
SQL> create global temporary table
vsesstat
2 as select * from v$sesstat
where rownum < 2;
테이블이 생성되었습니다.
CTAS 를 이용해 테이블 형태만 생성하였다.
SQL> commit;
커밋이 완료되었습니다.
SQL> select * from vsesstat;
선택된 레코드가 없습니다.
10.2.0.2.0 에서 Session Logical
Reads, Physical Reads, Execute Count, Parse Count (hard) 를 구하는데 사용했던 Statistic# 번호를 사용한 그림이다. -_-
전 버전에 사용했던 Statistic# 을
사용하지 못함을 알 수 있다.
(예제는 11g R2 에서 진행하고
있다.)
STATISTIC# NAME
----------
------------------------------
9 recursive cpu usage
54 spare statistic 3
331 segment total chunk allocation
334 TBS Extension: files extended
아래가 11g R2 에서 내가 원했던
지표들이다. Script 를 Version 호환성? 을 생각해 만든다면 v$sesstat 과 v$statname 의 Join 이 불가피하다.
STATISTIC# NAME
----------
------------------------------
11 session logical reads
72 physical reads
550 parse count (hard)
554 execute count
<Script 제작>
이렇게 결과만 붙이면 간단해 보여도 나름 시간이 많이 들었다. 스크립트에는
바인드 변수의 선언을 제거하였다.
var hsecs number
그 이유는 Script 를 실행할 때마다 값이 초기화 되기
때문이었다;;
처음에는 그 문제를 못 찾아 많은 시간을 보냈다.
대안으로는 Script 를 바꾸는 방법과 (아직 해결안이 없으니 대안이라 하기도 뭐하다) Package Header 에
변수를 만들어 사용하는 방법이 있다.
---tmp2.sql : 아직 이름을 짓지 못했다 ㅋ---
col sid for 999
col value for 9999999
col module for a20
col program for a35
col event for a30
/*With 절의 Select 에는 ordered hint 를 사용해 Access 순서를 고정하였다. v$statname 에서 실질적으로 내가 원하는 지표를 뽑아낸다. 이
과정을 밟지 않으면 300여개가 넘어가는 Stat 이 모두
조인에 참여하는 비효율이 발생한다. v$sesstat 역시 300여개가
넘어가는 Stat (위의 Statistics 수와 동일) 을 가지고 있기에 v$sesstat 과 조인하며 처리범위를 줄였다.
그리고 다음으로 vsesstat 과
조인하였는데 이 과거의 값을 보관하는 Global Temporary Table 이 뒤에 오게 한 이유는 Outer Join 을 위해서이다. New 와 Old 를 비교할 때 특정 세션이 양쪽에 존재하면 문제가 안된다. 문제가
되는 건 어느 한쪽에 있는 경우이다. New 값이 있는 경우 New 를 Driving 으로 Outer Join 을 하면 New 의 Value 를 살릴 수 있다. 물론 Old 에는 Null 에
대한 처리가 필요하다. – nvl(value,0) 과 같은 – 값이 Old 에만 있고 New 에 없는 경우에는 해당 값을 버리면 된다. 어차피 세션이 종결되었기 때문에 모니터링 대상이 아니다. New 를 Driving 으로 Outer Join 하면서 위의 상황의 Sid 는 자연스레 사라진다.
With 를 사용한 것은 Delta 값을 만들어놓은
집합이 반복적으로 사용되기 때문에 속도 개선을 위해 사용하였다.
추후 개선사항으로는 tmp 값을 불러올
때 v$statname 을 다시 사용해 가지고 오는데 with 절에
미리 조인해 놓고 불러오는게 나은지 지금의 경우가 나은지는 Trace 를 떠 확인할 필요가 있다. Dictionary View 정보는 메모리에 있기 때문에 굉장히 빠르지만 사용자 입장에서는 그 뒷면에 읽어들이는
수많은 Logical Reads 에 대해 간과하기 쉽다…
*/
with tmp as (
select /*+ ordered */
new.sid sid,
new.statistic#,
(new.value - nvl(old.value,0)) * 100 /
(select hsecs - :hsecs from v$timer) value,
vss.program,
vss.module,
substr(vss.event,1,30) event
from v$statname vst, v$sesstat new, vsesstat
old, v$session vss
where new.statistic# = vst.statistic#
and new.sid = vss.sid(+)
and vst.name in (
'session logical reads',
'physical reads',
'execute count',
'parse count (hard)')
and new.statistic# = old.statistic#(+)
and new.sid = old.sid(+)),
tmp2 as (select :hsecs tmpp from dual)
select * from (
select /*+ ordered
*/sid,value,program,module,event from v$statname v, tmp t
where v.statistic# = t.statistic#
and v.name = 'session logical reads'
order by value desc)
where rownum < 6
union all
select * from (
select /*+ ordered */sid
sid,value,program,module,event from v$statname v,tmp t
where v.statistic# = t.statistic#
and v.name = 'physical reads'
order by value desc)
where rownum < 6
union all
select * from (
select /*+ ordered */sid sid,value,program,module,event
from v$statname v,tmp t
where v.statistic# = t.statistic#
and v.name = 'execute count'
order by value desc)
where rownum < 6
union all
select * from (
select /*+ ordered */sid
sid,value,program,module,event from v$statname v,tmp t
where v.statistic# = t.statistic#
and v.name = 'parse count (hard)'
order by value desc)
where rownum < 6
/
/* 여기에서 Delta 값의 Select Query 가 종결되었다. Query 를 보면 각 지표에
대해 Union All 을 사용해 해당 지표의 Top 5 Ordered
Result 를 반환한다. 위의 과정을 마치고
v$timer 정보를 이용해 Time 을 저장한다.
v$timer.hsecs 는 mili second 값으로
100 을 나눠야 해당
변화 값이 초가 된다. */
begin
select hsecs into :hsecs from v$timer;
end;
/
/*vsesstat table 을 flush 시킨다. 사후 데이터를 남길 목적이라면 당연히 Permanent Table 을
사용해야 한다.*/
commit
/
insert into vsesstat
select /*+ leading(vst vss) */vss.* from
v$sesstat vss,v$statname vst
where vss.statistic# = vst.statistic#
and vst.name in (
'session logical reads',
'physical reads',
'execute count',
'parse count (hard)')
/
/*데이터를 입력한다. Statistics 종류를
늘리고 싶다면 Sub Query 에 항목을 추가하면 된다. */