Monitoring Script 만들기

 

출처 :  http://ukja.tistory.com/176

 

 

다음과 같은 요구사항이 있다.

 

v$sysstat 뷰에서 Data를 읽으면서 초당 Delta 값을 Monitoring하고 싶다

 

 

즉, Monitoring Tool에서 하는 일을 직접 Query로 수행하고 싶다는 것이다.
(1초 단위의 Monitoring Tool이 있긴 한가???)

가장 쉬운 방법은 다음과 같이 PL/SQL Block을 작성하는 것이다.
dbms_lock.sleep을 이용해서 1초 간격의 Data를 얻는 것이 핵심이다.
50초 동안 Monitoring을 수행한다.

set serveroutput on

declare
type sysstat_type is table of v$sysstat%rowtype;
stat1 sysstat_type;
stat2 sysstat_type;

begin

for idx in 1 .. 50 loop
select * bulk collect into stat1
from v$sysstat
where name in ('session logical reads', 'execute count');

dbms_lock.sleep(1);

select * bulk collect into stat2
from v$sysstat
where name in ('session logical reads', 'execute count');

for idx2 in 1 .. stat1.count loop
dbms_output.put_line(idx || ': ' || stat1(idx2).name || ' = ' ||
(stat2(idx2).value - stat1(idx2).value));
end loop;

end loop;
end;
/

1: session logical reads = 360
1: execute count = 46
2: session logical reads = 360
2: execute count = 46
3: session logical reads = 364
3: execute count = 48
...
50: session logical reads = 352
50: execute count = 45

PL/SQL procedure successfully completed.

Elapsed: 00:00:50.20

이 방법의 문제는? DBMS_OUTPUT.PUT_LINE을 이용한 출력은 PL/SQL Block의 수행이 종료된 후에나 출력된다는 것이다. 즉, 50초가 지난 다음에야 결과를 볼 수 있다. 분명히 우리가 원하는 것은 아니다.

이 문제를 해결하는 방법은 DBMS_OUTPUT.PUT_LINE 대신 DBMS_SYSTEM.KSDWRT를 이용하는 것이다. Trace File이나 Alert Log 등에 기록을 한다. 따라서 tail 명령을 이용하면 실시간으로 Montirong하는 효과를 얻을 수 있다.

declare
type sysstat_type is table of v$sysstat%rowtype;
stat1 sysstat_type;
stat2 sysstat_type;

begin

for idx in 1 .. 50 loop
select * bulk collect into stat1
from v$sysstat
where name in ('session logical reads', 'execute count');

dbms_lock.sleep(1);

select * bulk collect into stat2
from v$sysstat
where name in ('session logical reads', 'execute count');

for idx2 in 1 .. stat1.count loop
sys.dbms_system.ksdwrt(1, idx || ': ' || stat1(idx2).name || ' = ' ||
(stat2(idx2).value - stat1(idx2).value));
end loop;

end loop;
end;
/

1: session logical reads = 360
1: execute count = 46
2: session logical reads = 360
2: execute count = 46
3: session logical reads = 360
3: execute count = 46
...
*** 2008-11-14 16:50:15.203
...
50: session logical reads = 360
50: execute count = 46

한가지 재미있는 것은 위의 요구사항을 PL/SQL Block이 아닌 Pure SQL로도 구현할 수 있다는 것이다. SQL로 수행할 수 있으면 화면에 바로 출력이 될 것이고 Handling이 더욱 쉽다.

우선 다음과 같이 1초를 쉬는 함수를 만든다.

create or replace function fsleep(v1 int, sleep int)
return number
authid current_user
is
begin
dbms_lock.sleep(sleep);
return v1;
end;
/

그리고 v$sysstat에서 통계값을 얻는 함수를 만든다.
Read Consistency 문제때문에 일부러 함수를 사용한다.

create or replace function fget(v_name in varchar2)
return number
authid current_user
is
v_value number;
begin
select value into v_value
from v$sysstat
where name = v_name;

return v_value;
end;
/

이제 다음과 같이 Query를 수행한다.
USE_NL, LEADING, USE_HASH, NO_MERGE 등의 Hint와 fsleep, fget 함수 등을 사용해 마치 PL/SQL에서 1초 간격으로 Data를 가져오는 것과 같은 효과를 얻는 기법에 유의해야 한다.

set arraysize 2

col name format a30
col value format 999,999,999

select
/*+ use_nl(x y) leading(x y) */
x.x, y.name,
y.value2,
y.value1,
y.diff
from
(select /*+ no_merge */ level as x
from dual
connect by level <= 50) x,
(select /*+ use_hash(s1 s2) leading(s1 s2) no_merge */
s1.name as name,
(s2.value - s1.value) as diff,
s2.value as value2,
s1.value as value1
from
(select /*+ no_merge */ name, fget(name) as value
from v$sysstat
where name in ('session logical reads', 'execute count')
) s1,
(select /*+ no_merge */ name, fget(name) as value
from v$sysstat
where name in ('session logical reads', 'execute count')
and fsleep(1, 1) = 1
) s2
where s1.name = s2.name
) y
;

다음과 같은 결과가 초 단위로 점진적으로 출력된다.

        X  NAME                               VALUE2     VALUE1       DIFF
---------- ------------------------------ ---------- ---------- ----------
        1  session logical reads           327851893  327851467        426
        1  execute count                    31700340   31700278         62
        2  session logical reads           327852373  327851893        480
        2  execute count                    31700413   31700342         71
...

        X  NAME                               VALUE2     VALUE1       DIFF
---------- ------------------------------ ---------- ---------- ----------
        49 execute count                    31702858   31702804         54
        50 session logical reads           327869889  327869569        320
        50 execute count                    31702904   31702860         44

100 rows selected.

위의 Query가 어떻게 해서 초 단위의 Delta 값을 얻는지 이해한다면 Oracle이 Query를 수행하는 절차에 대해 어느 정도 이해를 하고 있다고 볼 수 있을 것이다.

이 방법을 동료에게 이야기했더니 "이게 집합적 사고인거 같군요!"라고 한다. 집합적 사고라... 그게 뭔지는 모르겠지만. ^^

하지만 어설픈 집합적 사고보다는 논리적인 PL/SQL Block이 더 나은 경우가 많다는 거!!!

 

---- Appended 2008/11/15

PS) 또 하나의 방법은 다음과 같이 Pipelined Table Function을 이용하는 것이다. Pipelined Table Function은 Oracle이 제공하는 가장 혁명적인 기능 중 하나이다.

create or replace type object_sysstat_type as object (
name varchar2(100),
value2 number,
value1 number,
diff number
);
/

create or replace type object_sysstat_table_type
as table of object_sysstat_type;
/

create or replace function fget_sysstat
return object_sysstat_table_type
pipelined
is
type sysstat_type is table of v$sysstat%rowtype;
stat1 sysstat_type;
stat2 sysstat_type;

begin

for idx in 1 .. 50 loop
select * bulk collect into stat1
from v$sysstat
where name in ('session logical reads', 'execute count');

dbms_lock.sleep(1);

select * bulk collect into stat2
from v$sysstat
where name in ('session logical reads', 'execute count');

for idx2 in 1 .. stat1.count loop
pipe row(object_sysstat_type(stat1(idx2).name, stat2(idx2).value,
stat1(idx2).value, stat2(idx2).value - stat1(idx2).value));
end loop;
end loop;

return;

end;
/

set arraysize 2

select * from table(fget_sysstat);