estd_interconnect_traffic check procedure

set echo off
set feed off
ACCEPT startt PROMPT'Please enter start time (MM/DD/YYYY):'
ACCEPT endt PROMPT'Please enter end time (MM/DD/YYYY):'
ACCEPT instid PROMPT'Please enter instance number:'
drop table interconnect_traffic;

create table interconnect_traffic (stime varchar2(100), svalue varchar2(100)) tablespace users;

create or replace procedure interconnect_traffic_pr1 (stime varchar2,etime varchar2,instid number)is
intraffic varchar2(100);
databaseid number;
ssnapid number;
esnapid number;
display_date varchar2(100);
i number;
begin
select min(snap_id) into ssnapid from dba_hist_snapshot where to_char(begin_interval_time,'yyyymmdd') = stime and instance_number = instid;

select max(snap_id)-1 into esnapid from dba_hist_snapshot where to_char(begin_interval_time,'yyyymmdd') = etime and instance_number = instid;

select dbid into databaseid from v$database ;

for i in ssnapid..esnapid loop

select replace(output,'Estd Interconnect traffic (KB)','') into intraffic from table(dbms_workload_repository.awr_report_text( databaseid,instid,i, i+1,0 )) where output like '%Interconnect%';

select to_char(begin_interval_time,'MM/DD/YYYY HH24:MI') into display_date from dba_hist_snapshot where snap_id = i+1 and instance_number = instid;

insert into interconnect_traffic values (display_date,intraffic);

commit;
end loop;
exception
when others then
dbms_output.put_line(i);
end;
/

execute interconnect_traffic_pr1('20090415','20090415',1);

execute interconnect_traffic_pr1('&startt','&endt','&instid');

drop procedure interconnect_traffic_pr1;

set echo on
set feed on
column stime format a30
column svalue format a30
select stime,svalue from interconnect_traffic;

 

spreport.sql shows forumlae of Interconnect traffic as follows:

Estd Interconnect traffic (KB):' hd2, round( ( ((:gccrrv+:gccurv +:gccrsv+:gccusv) :bs) + ((:dpms+:dnpms+:pmrv+:npmrv) 200) * )/&&btokb/:ela,2)

where
gccrrv = gc cr blocks received
gccurv = gc current blocks received
gccrsv = gc cr blocks served
gccusv = gc current blocks served
bs = block_size
dpms = gcs messages sent
dnpms = ges messages sent
pmrv = gcs msgs received
npmrv = ges msgs received
btokb = 1024
ela = elapsed time between two snapshots in seconds.

According to above formulae, script to calculate interconnect traffic is as shown below. Interconnect traffic caculated by below script is close to one calcuated by AWR but not same.

------------------------------------------------------------------------------------------------

set echo off
set feed off
ACCEPT startt PROMPT'Please enter start time (MM/DD/YYYY):'
ACCEPT endt PROMPT'Please enter end time (MM/DD/YYYY):'
ACCEPT instid PROMPT'Please enter instance number:'
drop table interconnect_traffic2;
create table interconnect_traffic2 (instid number,stime varchar2(100), svalue varchar2(100)) tablespace mig_data;

create or replace procedure interconnect_traffic_pr2 (stime varchar2,etime varchar2,instid number)is
ssnapid number;
esnapid number;
ogcstats number;
ngcstats number;
ogmstats number;
ngmstats number;
blksize number;
start_time date;
end_time date;
intraffic number;
cnt number :=2;
display_date varchar2(100);
begin
select min(snap_id) into ssnapid from dba_hist_snapshot where to_char(begin_interval_time,'yyyymmdd') = stime and instance_number = instid;

select max(snap_id) into esnapid from dba_hist_snapshot where to_char(begin_interval_time,'yyyymmdd') = etime and instance_number = instid;

select value into blksize from v$parameter where name ='db_block_size';
for i in ssnapid..esnapid loop
select sum(value) into ogcstats from dba_hist_sysstat where stat_name in ('gc cr blocks received','gc current blocks received','gc cr blocks served','gc current blocks served')and snap_id = i and instance_number = instid;
select sum(value) into ngcstats from dba_hist_sysstat where stat_name in ('gc cr blocks received','gc current blocks received','gc cr blocks served','gc current blocks served')and snap_id = i+1 and instance_number = instid;
select to_char(begin_interval_time,'YYYY-MM-DD HH24:MI') into display_date from dba_hist_snapshot where snap_id = i and instance_number = instid;
select sum(value) into ogmstats from dba_hist_sysstat where stat_name in ('gcs messages sent','ges messages sent','gcs msgs received','ges msgs received')and snap_id = i and instance_number = instid;
select sum(value) into ngmstats from dba_hist_sysstat where stat_name in ('gcs messages sent','ges messages sent','gcs msgs received','ges msgs received')and snap_id = i+1 and instance_number = instid;
select (to_date(to_char(begin_interval_time,'YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS')) into start_time from dba_hist_snapshot where snap_id = i and instance_number = instid;
select (to_date(to_char(begin_interval_time,'YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS')) into end_time from dba_hist_snapshot where snap_id = i + 1 and instance_number = instid;
intraffic := (((ngcstats - ogcstats)*blksize ) + ((ngmstats - ogmstats)*200))/1024/((end_time-start_time)*1440*60);
insert into interconnect_traffic2 values (instid,display_date,intraffic);
commit;
end loop;
end;
/

execute interconnect_traffic_pr2('&startt','&endt','&instid');

execute interconnect_traffic_pr2('20090416','20090416',1);

execute interconnect_traffic_pr2('20090416','20090416',2);

execute interconnect_traffic_pr2('20090417','20090417',1);

execute interconnect_traffic_pr2('20090417','20090417',2);

drop procedure interconnect_traffic_pr1;
set echo on
set feed onc
olumn stime format a30
column svalue format a30
select stime,svalue from interconnect_traffic;
-------------------------------------------------------------------------------------------------
Posted by Vishal Desai at 11:36 AM