Custom Scripts
Generally speaking, custom scripts utilize the
v$ series of views to
generate reports showing I/O distribution, timing data and wait
statistics. For data and temp file related statistics, the
v$filestatand v$tempstat
tables are utilized. For wait interface information, the
v$waitstat,
v$sysstatand v$sesstat tables can be
utilized. The following is an example script for generating I/O
related data:
--
*************************************************
-- 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
-- *************************************************
column inst_id format 999 heading 'Ins'
column sum_io1 new_value st1 noprint
column sum_io2 new_value st2 noprint
column sum_io new_value divide_by noprint
column Percent format 99.9 heading 'Perc|Of I/O'
column brratio format 999.99 heading 'Blck|Read|Rat'
column bwratio format 999.99 heading 'Blck|Write|Rat'
column phyrds heading 'Phys|Reads'
column phywrts heading 'Phys|Writes'
column phyblkrd heading 'Phys|Block|Reads'
column phyblkwrt heading 'Phys|Block|Writes'
column name format a45 heading 'File|Name'
column file# format 9999 heading 'File'
set feedback off verify off lines 132 pages 60 sqlbl on trims on
rem
select
nvl(sum(a.phyrds+a.phywrts),0) sum_io1
from
sys.gv_$filestat a;
select nvl(sum(b.phyrds+b.phywrts),0) sum_io2
from
sys.gv_$tempstat b;
select &st1+&st2 sum_io from dual;
rem
ttitle 'File I/O Statistics Report'
spool fileio
select
a.inst_id, a.file#,b.name, a.phyrds, a.phywrts,
(100*(a.phyrds+a.phywrts)/÷_by) Percent,
a.phyblkrd, a.phyblkwrt, (a.phyblkrd/greatest(a.phyrds,1))
brratio,
(a.phyblkwrt/greatest(a.phywrts,1)) bwratio
from
sys.gv_$filestat a, sys.gv_$dbfile b
where
a.file#=b.file#
and a.inst_id=b.inst_id
union
select
c.inst_id, c.file#,d.name, c.phyrds, c.phywrts,
(100*(c.phyrds+c.phywrts)/÷_by) Percent,
c.phyblkrd, c.phyblkwrt,(c.phyblkrd/greatest(c.phyrds,1))
brratio,
(c.phyblkwrt/greatest(c.phywrts,1)) bwratio
from
sys.gv_$tempstat c, sys.gv_$tempfile d
where
c.file#=d.file#
and c.inst_id=d.inst_id
order by 1,2;
spool off
pause Press enter to continue
set feedback on verify on lines 80 pages 22
clear columns
ttitle off
In this script, both the
gv$filestatand gv$tempstat tables are utilized, and the results are compared to
a total I/O figure so each datafile and tempfile’s I/O is
captured. In RAC, the gv$
views contain statistics cumulative since the database started.
For Oracle RAC environments, the
gv$ version of these
tables needs to be utilized so that total I/O across all instances
is captured. The v$ only
captures statistics for the single instance. The following is an
example of the output from the script for the TPCH environment
with ATA drives:
Mon Jul
19
page 1
File I/O Statistics Report
Phys Phys Blck Blck
File Phys Phys
Perc Block Block Read Wrt
Ins File Name Reads Writes
I/O Reads Writes Rat Rat
--- ---- --------------------------------------- -------- ------
---- -------- ------ ---- -----
1 1 /u08/oracle/oradata/dss/system01.dbf 12857 1201
.0 47975 1201 3.73 1.00
1 1 /u12/oracle/oradata/dss/tem101.dbf 8733 25480
.0 80127 338717 9.18 13.29
1 2 /u09/oracle/oradata/dss/undotbs101.dbf 50 2979
.0 50 2979 1.00 1.00
1 2 /u12/oracle/oradata/dss/tem102.dbf 4 0
.0 4 0 1.00 .00
1 3 /u12/oracle/oradata/dss/temp05.dbf 4 0
.0 4 0 1.00 .00
1 3 /u12/oracle/oradata/dss/undotbs103.dbf 50 397
.0 50 397 1.00 1.00
1 4 /u01/oracle/oradata/dss/temp021.dbf 0 0
.0 0 0 .00 .00
1 4 /u11/oracle/oradata/dss/undotbs102.dbf 313 3157
.0 313 3157 1.00 1.00
1 5 /u02/oracle/oradata/dss/temp022.dbf 0 0
.0 0 0 .00 .00
1 5 /u08/oracle/oradata/dss/dss_data01.dbf 33688717 3
35.6 34889652 3 1.04 1.00
1 6 /u09/oracle/oradata/dss/dss_data02.dbf 29910924 3
31.6 31075321 3 1.04 1.00
1 7 /u10/oracle/oradata/dss/dss_data03.dbf 30345274 3
32.0 31504507 3 1.04 1.00
1 8 /u11/oracle/oradata/dss/dss_index01.dbf 182678 3
.1 243693 3 1.33 1.00
1 9 /u13/oracle/oradata/dss/dss_index02.dbf 181979 3
.1 245391 3 1.35 1.00
1 10 /u14/oracle/oradata/dss/dss_index03.dbf 173160 3
.1 233697 3 1.35 1.00
1 11 /u09/oracle/oradata/dss/drsys01.dbf 5 3
.0 5 3 1.00 1.00
1 12 /u09/oracle/oradata/dss/tools01.dbf 5 3
.0 5 3 1.00 1.00
1 13 /u10/oracle/oradata/dss/xdb01.dbf 11 3
.0 23 3 2.09 1.00
1 14 /u13/oracle/oradata/dss/dss_index3.dbf 11554 3
.0 11554 3 1.00 1.00
1 15 /u10/oracle/oradata/dss_index05.dbf 5 3
.0 5 3 1.00 1.00
1 16 /u09/oracle/oradata/dss_index06.dbf 5 3
.0 5 3 1.00 1.00
1 17 /u08/oracle/oradata/dss_index07.dbf 5 3
.0 5 3 1.00 1.00
1 18 /u03/oracle/oradata/dss/undo021.dbf 0 3
.0 0 3 .00 1.00
1 19 /u04/oracle/oradata/dss/uno022.dbf 0 3
.0 0 3 .00 1.00
In this listing, the majority, a total of 98%,
of I/O is being directed to the
dss_data tablespace datafiles with the second largest
contribution being the dss_index
datafiles with 0.580 percent, and the temporary tablespace at
0.036 percent. From this report, one can see that the major stress
is being placed on the data
datafiles.
A look at the operating system
iostatcommand confirms that the I/O subsystem is undergoing
an extreme amount of stress. The listing below shows the results
of an iostat command
revealing the first set of data, which consists of the averages
since system startup, have been omitted:
avg-cpu:
%user %nice %sys %idle
0.20 0.00 0.60 99.20
Device: tps Blk_read/s Blk_wrtn/s Blk_read
Blk_wrtn
dev3-0 59.04 854.62 269.88 2128
672
dev8-0 0.00 0.00 0.00
0 0
dev8-1 1795.18 14361.45 0.00
35760 0
avg-cpu:
%user %nice %sys %idle
0.40 0.00 1.39 98.21
Device: tps Blk_read/s Blk_wrtn/s Blk_read
Blk_wrtn
dev3-0 55.16 736.51 60.32 1856
152
dev8-0 0.00 0.00 0.00
0 0
dev8-1 1796.03 14374.60 0.00
36224 0
This listing shows that device 8-1, the ATA
drive array, is doing the majority of the work in the system. CPU
time is registering as 98% idle, so this might lead an observer to
think that the system was idling along without much stress;
however, a look at the top command shows the error in this
assumption:. The output of the top command for the same time
period is shown below:
21:01:35
up 14 days, 3:32, 1 user, load average: 2.00, 2.00, 2.00
65 processes: 64 sleeping, 1 running, 0 zombie, 0 stopped
CPU states: cpu user nice system irq softirq iowait
idle
total 0.4% 0.0% 0.0% 0.4% 0.0% 49.5%
49.5%
cpu00 0.0% 0.0% 0.0% 0.9% 0.0% 0.0%
99.0%
cpu01 0.9% 0.0% 0.0% 0.0% 0.0%
99.0% 0.0%
Mem:
4067556k av, 4048420k used, 19136k free, 0k shrd, 281432k
buff
3063424k actv, 798872k in_d, 20108k in_c
Swap: 2040244k av, 577540k used, 1462704k free 3555660k cached
This listing shows just the header from an I/O
stat taken during the ATA query runs. The system is 98% idle with
the CPU because it is 100% I/O bound on the processor that is
handling the I/O for the query. The VMSTAT
command also confirms this as is shown below:
procs
memory swap io system cpu
r b swpd free buff cache si so bi bo in cs us
sy id wa
1 1 574436 18864 281432 3557252 13 1 17 7 16 2
3 2 1 6
0 2 575580 18688 281432 3557580 142 61 3770 82 628 811
1 4 46 49
1 2 575164 18736 281432 3557728 167 311 3808 337 638 843
0 1 50 49
0 2 574740 19496 281432 3556400 188 0 3923 16 639 838
0 1 50 49
1 1 577084 18724 281432 3557972 195 118 3684 142 615 797
0 1 50 50
2 0 576552 18752 281432 3557452 223 0 3487 36 588 773
0 5 48 47
0 2 577120 19048 281432 3557268 234 34 3763 48 621 809
0 0 49 51
1 1 576584 18704 281432 3557072 243 430 3638 454 605 775
1 1 50 49
0 2 576124 18772 281432 3556524 202 184 3682 210 614 795
1 1 49 49
1 1 575896 19372 281432 3558408 169 120 3769 134 622 820
0 1 49 49
