Home » RDBMS Server » Enterprise Manager » Create sql scripts same as OEM chart scripts
Create sql scripts same as OEM chart scripts [message #27609] Fri, 17 October 2003 06:55
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

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;


create or replace procedure hist_proc

as begin

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;


if hist_pac.v_tot_secs is null then




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;


select value
into v_cpu_time
from v$sysstat
where name = 'CPU used by this session';

hist_pac.v_cpu_time := v_cpu_time;


select value
into v_user_calls
from v$sysstat
where name = 'user calls';

hist_pac.v_user_calls := v_user_calls;


select value
into v_recur_calls
from v$sysstat
where name = 'recursive calls';

hist_pac.v_recur_calls := v_recur_calls;



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

when too_many_rows then
when no_data_found then
raise_application_error (-20005, '"' ||'" xxxxx (' ||v_tot_secs || ')');


Any help on this will be greatly appreciated!
Previous Topic: intelligent agent Jobs & patch set
Next Topic: PO Costs
Goto Forum:

Current Time: Thu May 26 22:26:01 CDT 2022