Search BC Sites

Free Oracle Tips   HTML Text

Free Oracle App Server Tips   HTML Text

Privacy Policy

Redneck

Dress Code

Oracle tuning

Oracle training

Oracle support

Remote Oracle

Oracle Tips by Burleson

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$sesstattables 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)/&divide_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)/&divide_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$filestat and 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:

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 iostat command 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

For more details and scripts, see my new book " Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot.