ua_oraio
NAME
ua_oraio - Resummarize an oracle I/O report
DESCRIPTION
The ua_oraio command summarizes report(s) generated by the io_rates.sql
script. At the University of Alaska these reports are created by the
automated script ~sxdrpp/job/io_rates.ksh and deposited under
~sxdrpp/oracle_io/xxxx.yymmdd.hhmm.
OPTIONS
Note, ua_oraio utilizes the cci command parser utilized by non-UNIX operat-
ing systems instead of the traditional UNIX getopt() parsing. Options have
been defined to "look like" UNIX style options, but can be spelled out or
abbreviated. For example '-t' is the same as '-table'. Because of this
multiple options must be space separated and the hyphen is part of the
option name. This means -Tt would not be recognized. Instead, use '-T -t'
or '-Totals -table'.
-file filename(s)
Specify input file(s), multiple files can be specified by space
separating them enclosed in quotes, such as:
-file "prod.970603.0730 prod.970603.0930"
or -file "`ls prod.970603.*`"
-domain summarize by domain
Flag to summarize by disk domain. The io_rates.ksh script includes
a sed script to convert the identify domains based on University of
Alaska naming conventions.
-mount summarize by mount point.
Flag to summarize by unix mount point (first level name qualifier).
-table summarize by table
Flag to summarize by tablespace name.
-rates display rates (io's/sec) not values.
-Totals display Totals only.
-zero do not display fids with zero physical reads and writes.
-verbose
display more verbose informational messages.
-output filename
Specify optional output file (same as >filename).
-? terse help message.
EXAMPLES
Domain summary by raw counts:
# ua_oraio -f "`ls ~sxdrpp/oracle_io/prod.970603.????`" -d -T
97/06/03 97/06/03 ...
07:31:50 - 09:30:04 09:30:04 - 11:30:01 ...
Domain Mnt Table FID pRd 0.01:58:14 pWr pRd 0.01:59:57 pWr ...
------ --- ----- --- ------------------- ------------------- ...
d1f102 Total: 13 591533 11221 878209 12500 ...
d1f200 Total: 8 72849 6706 234028 20191 ...
d1f300 Total: 9 115083 6630 115254 16606 ...
d1f400 Total: 27 115502 2728 122070 4782 ...
d2f200 Total: 9 170091 4040 200531 4587 ...
d2f300 Total: 16 103676 20901 59304 24031 ...
Grand: 82 1168734 52226 1609396 82697 ...
Domain summary by rates:
# ua_oraio -f "`ls ~sxdrpp/oracle_io/prod.970603.????`" -d -T -rates
97/06/03 97/06/03 ...
07:31:50 - 09:30:04 09:30:04 - 11:30:01 ...
Domain Mnt Table FID pRd 0.01:58:14 pWr pRd 0.01:59:57 pWr ...
------ --- ----- --- ------------------- ------------------- ...
d1f102 Total: 13| 83.385 1.582| 122.024 1.737|...
d1f200 Total: 8| 10.269 0.945| 32.517 2.805|...
d1f300 Total: 9| 16.223 0.935| 16.014 2.307|...
d1f400 Total: 27| 16.282 0.385| 16.961 0.664|...
d2f200 Total: 9| 23.977 0.569| 27.863 0.637|...
d2f300 Total: 16| 14.615 2.946| 8.240 3.339|...
Grand: 82| 164.750 7.362| 223.620 11.490|...
Tablespace summary (over night) by rates:
# ua_oraio -f "prod.970603.1734 prod.970604.0730" -Totals -table -rate
97/06/03 97/06/04
17:30:00 - 07:30:12
Domain Mnt Table FID pRd 0.14:00:12 pWr us% Total Free ...
------ --- ----- --- ------------------- --- ----- ---- ...
Total: AR_CLOSE 1| 4.195 0.000| 80 1280 254 ...
Total: AUDITS 1| 10.006 0.893| 50 300 150 ...
Total: COMDATA 1| 0.306 0.003| 56 500 218 ...
Total: COMINDEX 1| 0.210 0.005| 53 1024 473 ...
Total: DBA 1| . . | 0 10 10 ...
Total: FINARC 2| . . | 43 150 85 ...
Total: FINDATA 3| 2.932 0.110| 78 1317 277 ...
Total: FININDEX 2| 0.288 0.045| 67 1117 362 ...
Total: FINLDATA 8| 0.163 0.055| 78 7448 1620 ...
Total: FINLINDEX 13| 0.210 0.274| 84 9100 1437 ...
Total: GENDATA 2| 0.025 0.023| 33 402 267 ...
Total: GENINDEX 2| 0.050 0.045| 48 302 156 ...
Total: HRSDATA 3| 1.496 0.039| 50 900 445 ...
Total: HRSINDEX 2| 0.277 0.022| 58 300 124 ...
Total: PATDATA 1| . . | 1 100 99 ...
Total: POSDATA 3| 1.291 0.078| 92 726 58 ...
Total: POSINDEX 3| 0.039 0.033| 57 476 200 ...
Total: RBS 2| 1.132 2.040| 24 1524 1148 ...
Total: RESDATA 3| 0.001 0.000| 64 3072 1090 ...
Total: RESINDEX 1| 0.000 0.000| 54 1024 468 ...
Total: SATDATA 2| 0.189 1.168| 58 2048 848 ...
Total: SATHDATA 3| . . | 57 3072 1309 ...
Total: SATHINDEX 3| 0.000 0.000| 58 3072 1281 ...
Total: SATINDEX 1| 0.033 0.007| 48 1024 531 ...
Total: SYSTEM 1| 0.036 0.010| 39 262 158 ...
Total: TASDATA 3| 1.349 0.053| 73 656 172 ...
Total: TASINDEX 4| 0.442 0.096| 91 706 57 ...
Total: TEMP 3| 0.006 0.156| 3 1610 1548 ...
Total: TOOLS 3| 0.004 0.000| 46 251 134 ...
Total: UA 1| 0.001 0.000| 45 51 28 ...
Total: UAINDEX 1| 0.000 0.000| 6 50 47 ...
Total: USERS 3| 0.027 0.047| 68 160 51 ...
Grand: 83| 24.710 5.202| 65 44034 15105 ...
RESTRICTIONS
Oracle reports "physical reads" and "physical writes", these may not really
be "physical". From an oracle perspective this is likely IO which is exter-
nal to its buffers, but may still be satisfied by UNIX UBC or otherwise
buffered. In theory the physical read plus write rates from oracle should
equal the transactions per second reported via uaio, but oracle is much
higher. The theory was validated in a one pass read of the database after
rebooting, in practice something is being buffered. Existing UBC tools
(like vmubc) do not give an adequate view of how these IO's are being
satisfied. Until there is some means to measure this (we are working on
it), you must look at both what Oracle thinks it is doing for IO and what
the system is actually doing.
ua_oraio is useful only for reporting from data collected from io_rates.ksh
which utilizes sql/io_rates.sql, the ksh functions defined under
sql/sql_plus_*, and the view created by sql/free_space_view.sql.
io_rates.ksh uses internal sed scripts to convert tablespace mount points
and rz* device names to truncated advfs domain names. This works fine
based on University of Alaska naming conventions, but may be inappropriate
for other sites with very different naming conventions.
RELATED INFORMATION
Commands: uaio(8).