In 10g AWR package dbms_workload_repository is wrapped so if you want to find formulae for any statistics its difficult. If you want to plot interconnect traffic Vs Time in Oracle RAC environment you can use following script and create chart using excel:
Note: It will run full AWR report for range of snap id's so do not run during peak hours.-----------------------------------------------------------------------------------------------
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 date,etime date,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_date(to_char(begin_interval_time,'MM/DD/YYYY'),'MM/DD/YYYY')) = stime and instance_number = instid;
select max(snap_id)-1 into esnapid from dba_hist_snapshot where (to_date(to_char(begin_interval_time,'MM/DD/YYYY'),'MM/DD/YYYY')) = 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('&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_traffic;
create table interconnect_traffic (stime varchar2(100), svalue varchar2(100)) tablespace users;
create or replace procedure interconnect_traffic_pr1 (stime date,etime date,instid number)isssnapid 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_date(to_char(begin_interval_time,'MM/DD/YYYY'),'MM/DD/YYYY')) = stime and instance_number = instid;
select max(snap_id) into esnapid from dba_hist_snapshot where (to_date(to_char(begin_interval_time,'MM/DD/YYYY'),'MM/DD/YYYY')) = 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,'MM/DD/YYYY 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,'MM/DD/YYYY HH24:MI:SS'),'MM/DD/YYYY 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,'MM/DD/YYYY HH24:MI:SS'),'MM/DD/YYYY HH24:MI:SS')) into end_time from dba_hist_snapshot wheresnap_id = i + 1 and instance_number = instid;
intraffic := (((ngcstats - ogcstats)*blksize ) + ((ngmstats - ogmstats)*200))/1024/((end_time-start_time)*1440*60);
insert into interconnect_traffic values (display_date,intraffic);
commit;
end loop;
end;
/
execute interconnect_traffic_pr1('&startt','&endt','&instid');
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;
-------------------------------------------------------------------------------------------------
