| Create sql scripts same as OEM chart scripts [message #27609] |
Fri, 17 October 2003 06:55 |
Jaco
Messages: 23
Registered: August 2000
|
Junior Member |
|
|
Hi there,
I am having trouble creating scripts to get me the same data as what OEM's Performance manager gets, in specific, the response time per execute.
According to the OEM help, response time per execute = (deltatotalwait + deltacputime) / (deltausercalls + deltarecursivecalls).
I wrote a procedure that I execute and everytime it store the current values in a package and uses them to calculate the delta and so on, but my values are WAY out if I compare them to OEM's in the same time interval. I.e. if I set the interval to be 15 seconds in OEM and run my procecure also every 15 seconds the values differ. My values might be something anything between 1 and 100, and OEM's stays at around 0.025.
My package and procecure code:
create or replace package hist_pac
as
v_tot_secs number;
v_cmt_value number;
v_rb_value number;
v_tot_value number;
v_tot_wait number;
v_cpu_time number;
v_user_calls number;
v_recur_calls number;
end;
/
create or replace procedure hist_proc
as begin
declare
v_capture_date date;
v_cmt_value number;
v_rb_value number;
v_tot_value number;
v_tmp_value number;
v_cmt_secs number;
v_rb_secs number;
v_tot_secs number;
v_tmp_secs number;
v_lib_cache_reloads number;
v_dict_cache number;
v_buff_cache number;
v_resp_time number;
v_delta number;
v_delta_time number;
v_rate number;
v_tot_wait number;
v_tmp_wait number;
delta_wait number;
v_cpu_time number;
v_tmp_cpu_time number;
delta_cpu_time number;
v_user_calls number;
v_tmp_user_calls number;
delta_user_calls number;
v_recur_calls number;
v_tmp_recur_calls number;
delta_recur_calls number;
response_time_per_execution number;
n number;
begin
if hist_pac.v_tot_secs is null then
hist_pac.v_tot_secs:=0;
hist_pac.v_cmt_value:=0;
hist_pac.v_rb_value:=0;
hist_pac.v_tot_value:=0;
hist_pac.v_tot_wait:=0;
hist_pac.v_cpu_time:=0;
hist_pac.v_user_calls:=0;
hist_pac.v_recur_calls:=0;
end if;
v_tot_value := 0;
v_tot_secs := 0;
v_tmp_value := hist_pac.v_tot_value;
v_tmp_secs := hist_pac.v_tot_secs;
v_tmp_wait := hist_pac.v_tot_wait;
v_tmp_cpu_time := hist_pac.v_cpu_time;
v_tmp_user_calls := hist_pac.v_user_calls;
v_tmp_recur_calls := hist_pac.v_recur_calls;
select sysdate
into v_capture_date
from dual;
select round(1-(phy.value - lob.value - dir.value) / ses.value,2) * 100
into v_buff_cache
from v$sysstat ses, v$sysstat lob, v$sysstat dir, v$sysstat phy
where ses.name='session logical reads'
and dir.name='physical reads direct'
and lob.name='physical reads direct (lob)'
and phy.name='physical reads';
select round((sum(getmisses)/sum(gets)) * 100,2)
into v_dict_cache
from v$rowcache;
select round((sum(reloads) / sum(pins)) * 100,2)
into v_lib_cache_reloads
from v$librarycache;
----------------user commits-----------------------------
select value
into v_cmt_value
from v$sysstat
where name = 'user commits';
hist_pac.v_cmt_value := v_cmt_value;
----------------user rollbacks----------------------
select value
into v_rb_value
from v$sysstat
where name = 'user rollbacks';
hist_pac.v_rb_value := v_rb_value;
---------------Response Time Per Execute-------------------------
--------------delta waits---------------------------------------
select sum(time_waited)
into v_tot_wait
from v$system_event;
hist_pac.v_tot_wait := v_tot_wait;
-------------deltacputime--------------------------------------
select value
into v_cpu_time
from v$sysstat
where name = 'CPU used by this session';
hist_pac.v_cpu_time := v_cpu_time;
-----------------deltausercalls--------------------------------
select value
into v_user_calls
from v$sysstat
where name = 'user calls';
hist_pac.v_user_calls := v_user_calls;
-----------------deltarecursivecalls---------------------------
select value
into v_recur_calls
from v$sysstat
where name = 'recursive calls';
hist_pac.v_recur_calls := v_recur_calls;
--------------------------------------------------------------
-- DATE
select to_char(sysdate,'sssss')
into v_tot_secs
from dual;
---------TRANSACTION RATE--------------------------
v_tot_value := v_cmt_value + v_rb_value;
v_delta := v_tot_value - v_tmp_value;
hist_pac.v_tot_value := v_tot_value;
v_delta_time := v_tot_secs - v_tmp_secs;
hist_pac.v_tot_secs := v_tot_secs;
v_rate := round((v_delta / v_delta_time),2);
----------RESPONSE TIME--------------------------
delta_wait := v_tot_wait - v_tmp_wait;
delta_cpu_time := v_cpu_time - v_tmp_cpu_time;
delta_user_calls := v_user_calls - v_tmp_user_calls;
delta_recur_calls := v_recur_calls - v_tmp_recur_calls;
response_time_per_execution := (delta_wait + delta_cpu_time) / (delta_user_calls + delta_recur_calls);
response_time_per_execution := round((response_time_per_execution / v_delta_time),2);
-------------------------------------------------
-- dbms_output.put_line('PROC1');
-- dbms_output.put_line('V_TOT_VALUE --> '||v_tot_value);
-- dbms_output.put_line('V_CMT_VALUE --> '||v_cmt_value);
-- dbms_output.put_line('V_RB_VALUE --> '||v_rb_value);
dbms_output.put_line('V_TOT_SECS --> '||v_tot_secs);
-- dbms_output.put_line('V_DELTA --> '||v_delta);
dbms_output.put_line('V_DELTA_TIME --> '||v_delta_time);
-- dbms_output.put_line('V_RATE --> '||v_rate);
dbms_output.put_line('DELTA_WAIT --> '||delta_wait);
dbms_output.put_line('V_TOT_WAIT --> '||v_tot_wait);
dbms_output.put_line('V_TMP_WAIT --> '||v_tmp_wait);
dbms_output.put_line('DELTA_CPU_TIME --> '||delta_cpu_time);
dbms_output.put_line('V_CPU_TIME --> '||v_cpu_time);
dbms_output.put_line('V_TMP_CPU_TIME --> '||v_tmp_cpu_time);
dbms_output.put_line('DELTA_USER_CALLS --> '||delta_user_calls);
dbms_output.put_line('V_USER_CALLS --> '||v_user_calls);
dbms_output.put_line('V_TMP_USER_CALLS --> '||v_tmp_user_calls);
dbms_output.put_line('DELTA_RECUR_CALLS --> '||delta_recur_calls);
dbms_output.put_line('V_RECUR_CALLS --> '||v_recur_calls);
dbms_output.put_line('V_TMP_RECUR_CALLS --> '||v_tmp_recur_calls);
dbms_output.put_line('RESPONSE_TIME --> '||response_time_per_execution);
insert into sys.perf_tbl
values
(v_capture_date,
v_rate,
nvl(response_time_per_execution,0),
v_lib_cache_reloads,
v_dict_cache,
v_buff_cache);
exception
when too_many_rows then
null;
when no_data_found then
raise_application_error (-20005, '"' ||'" xxxxx (' ||v_tot_secs || ')');
end;
end;
/
Any help on this will be greatly appreciated!
Thanks,
Jaco
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
