| |
 |
|
Oracle Tips by Burleson |
PL/SQL to Calculate I/O per Second Data
Knowing the value for total I/O per second for the database allows
the DBA to do resource planning for the number of disks utilized.
Generally speaking, a modern disk drive can only support around 100
I/O’s per second of non-linear activity. If the value for average
I/O’s per second exceeds 100 times the number of disks in use,
chances are the disks are being over-taxed and more disks should be
added.
This report calculates an average since the database was started;
therefore, the actual value for peak I/O’s per second could be
double or more this average value.
* io_sec.sql
-- *************************************************
-- Copyright © 2005 by Rampant TechPress
-- This script is free for non-commercial purposes
-- with no warranties. Use at your own risk.
--
-- To license this script for a commercial purpose,
-- contact info@rampant.cc
-- *************************************************
rem
rem NAME: io_sec.sql
rem
rem FUNCTION: PL/SQL to calculate IO/sec data
rem Mike Ault
rem
set serveroutput on
declare
cursor get_io is select
nvl(sum(a.phyrds+a.phywrts),0) sum_io1,to_number(null) sum_io2
from sys.gv_$filestat a
union
select
to_number(null) sum_io1, nvl(sum(b.phyrds+b.phywrts),0) sum_io2
from
sys.gv_$tempstat b;
now date;
elapsed_seconds number;
sum_io1 number;
sum_io2 number;
sum_io12 number;
sum_io22 number;
tot_io number;
tot_io_per_sec number;
fixed_io_per_sec number;
temp_io_per_sec number;
begin
open get_io;
for i in 1..2 loop
fetch get_io into sum_io1, sum_io2;
if i = 1 then sum_io12:=sum_io1;
else
sum_io22:=sum_io2;
end if;
end loop;
select sum_io12+sum_io22 into tot_io from dual;
select sysdate into now from dual;
select ceil((now-max(startup_time))*(60*60*24)) into elapsed_seconds
from gv$instance;
fixed_io_per_sec:=sum_io12/elapsed_seconds;
temp_io_per_sec:=sum_io22/elapsed_seconds;
tot_io_per_sec:=tot_io/elapsed_seconds;
dbms_output.put_line('Elapsed Sec :'||to_char(elapsed_seconds,
'9,999,999.99'));
dbms_output.put_line('Fixed IO/SEC:'||to_char(fixed_io_per_sec,'9,999,999.99'));
dbms_output.put_line('Temp IO/SEC :'||to_char(temp_io_per_sec,
'9,999,999.99'));
dbms_output.put_line('Total IO/SEC:'||to_char(tot_io_Per_Sec,
'9,999,999.99'));
end;
/
The following sample output from io_sec.sql displays the total
elapsed seconds since the database was started, the I/O per second
for both temporary I/O and fixed I/O as well as the total I/O per
second. The report should be run periodically to determine if I/O
rates are fluctuating. A version of this report that uses STATSPACK
data is also available so that interval I/O rates can be determined.
Elapsed Sec : 2,183,399.00
Fixed IO/SEC: 190.96
Temp IO/SEC : .00
Total IO/SEC: 190.96
PL/SQL procedure successfully completed.
The above book excerpt is from:
Oracle
Tuning Power Scripts
With 100+ High Performance
SQL Scripts
Oracle In-Focus Series
ISBN
0-9744486-7-2
Mike Ault, Donald K.
Burleson. Harry Conway
http://www.rampant-books.com/book_2005_1_power_tuning.htm |
