Performance of the disk access_based process depends on factors such as the number of I/O controller cards, the software, the speed of the disk, and so forth.

When a process requests data, the following brief events occur to retrieve the requested data:

• The I/O controller locates the address of the data on the right disk; this is known as the queuing delay. Typically, there would be other outstanding requests of data to be read from the disk at the given point of time. The time spent in the queue depends on the OS and the system load on the disk. The outstanding requests on the disk are sorted in such a way that the disk head does not have to do too much back-and-forth movement. This sorting of requests may result in the requests spending more time in the queue, but the seek time is reduced.
  
  
• When the location of the data is found, the disk arm—or, to be precise, the disk head—must be positioned over the correct disk on the right track. The time delay that occurs here is called the seek time.
  
  
• The disk head will then read the data of the disk as the disk rotates below the disk head. After the disk head is positioned over the right track, it has to wait for the disk to rotate so that the relevant sectors of the data are right under the disk head and the read/write can be completed. This period is commonly referred to as the rotational latency.
  
  
• The data read in by the disk head is then transferred to the controller. This final part of the I/O is referred to as the data transfer time. Finally, the data is transferred over the I/O back plane and is routed to the process that made the request for the data.

The total time required to retrieve data from the disk can roughly said to be

Queue Time + Seek Time + Rotational Latency + Data Transfer Time

As can be clearly seen, disk access involves mechanical and rotational motion of the disk and can be a bottleneck on the system if there are frequent I/O calls on the same disk concurrently by multiple users. Finally, the I/O rate of the disk will govern the total throughput of the disk, resulting in users queuing on the disk.

As in the CPU usage section, this section shows you how to identify users doing a lot of file access, after you take an overview of the system.

Taking an I/O Overview

Before you proceed to find heavy I/O users, you must determine whether any of the file systems are heavily accessed currently. If this is true, only then do you proceed to locate these users.

Listing 22.14 can be run to take a quick overview of the system.

As before, take a snapshot of the system for a small period of time and then obtain the interval output. By looking at the output, it will be very easy to evaluate whether there is any heavy I/O going on.

In this listing, there are two tables, stat$begin_file and stat$end_file, which are used to store the start and end file I/O statistic details. By using the difference between the two snapshots and the time between the two, you can compute the rate of file access.

Run Listing 22.14 to store the file I/O statistics at the start of the snapshotting interval.

REM
REM Start IO : Script to Capture File I/O Statistics at the beginning of
the Âinterval.
REM
Drop View stats$file_view;
Create View stats$file_view As
 Select ts.name    ts,
 i.name     name,
 x.phyrds pyr,
 x.phywrts pyw,
 x.readtim prt,
 x.writetim pwt,
 x.phyblkrd pbr,
 x.phyblkwrt pbw,
 sysdate     st_time
From v$filestat x, ts$ ts, v$datafile i,file$ f
Where i.file#=f.file#
And ts.ts#=f.ts#
And x.file#=f.file#;

Drop Table stats$begin_file;

Create Table stats$begin_file   Tablespace tools
Storage(Initial 10K Next 10K Pctincrease 10 ) As
Select *
From stats$file_view
Where 0 = 1;

Drop Table stats$end_file;
Create Table stats$end_file   Tablespace tools
Storage(Initial 10K Next 10K Pctincrease 10 )As
Select *
From stats$begin_file;

Insert Into stats$begin_file
Select *
From stats$file_view;

After the desired interval when the performance of the system is to be analyzed is over, run the end I/O script to capture the statistics at the end of the interval. The end I/O script will capture and store statistics in stat$end_file and will generate the I/O report. Listing 22.15 is the script for end I/O and to obtain the I/O usage report.

REM
REM End IO : Script to Capture File I/O Statistics at the end of the interval.
REM
Set Term Off
Insert Into stats$end_file
Select *
From stats$file_view;

Drop Table stats$files;
Create Table stats$files  Tablespace tools
Storage(Initial 10K Next 10K Pctincrease 0)As
Select b.ts table_space,
b.name file_name,
e.pyr-b.pyr phys_reads,
e.pbr-b.pbr phys_blks_rd,
e.prt-b.prt phys_rd_time,
e.pyw-b.pyw phys_writes,
          e.pbw-b.pbw phys_blks_wr,
e.pwt-b.pwt phys_wrt_tim,
      (e.st_time -b.st_time)*24*60*60 tot_tim
From stats$begin_file b, stats$end_file e
Where b.name=e.name;

Drop Table stats$begin_file;
Drop Table stats$end_file;
Set Term On
Clear Screen
Set Pause On

Set Pagesize 24
Set Feedback off
Col table_space Format a17
Col file_name Format a55
Col phys_writes Format 9999999 Heading `PHY_WRS'
Col phys_reads  Format 9999999 Heading `PHY_RDS'
Col phys_rd_time Format 999999 Heading  `PHY_RDT'
Col phys_wrt_tim Format 999999999  Heading `PHY_WRT'
Col phys_blks_rd Format 999999999 Heading `BLKS_RD'
Col phys_blks_wr Format 9999999   Heading `BLKS_WR'
Col ios_sec Format 999 Heading `IOS_SEC'




REM
REM Generate IO Overview Report
REM
Select table_space,
      file_name,
phys_reads,
phys_writes,
phys_blks_rd,
phys_blks_wr,
phys_rd_time,

phys_wrt_tim,
(phys_reads + phys_writes)/tot_tim ios_sec
From stats$files
Order By ios_sec Desc

TABLE_SPACE       FILE_NAME
---------------- ------------------------------------------------------
PHY_RDS  PHY_WRS    BLKS_RD  BLKS_WR PHY_RDT    PHY_WRT IOS_SEC
-------- -------- ---------- -------- ------ ---------- ------
ARMSLIVEDB04_TS   /u03/oradata/ora7/armslive/armslivedbu04.dbf
  23100       34      23100       34   17644        106      93

ARMSLIVEIDX02_TS  /da11/oradata/ora7/armslive/armsliveidxa07.dbf
  16886        0      16886        0     912          0      68

ARMSLIVEIDX11_TS  /u14/oradata/ora7/armslive/armsliveidx19.dbf
   3461      542       3461      542    1288       2946      16

ARMSLIVEDB04_TS   /u03/oradata/ora7/armslive/armslivedbu03.dbf
   2521       38       2521       38    2131        255      10

RBS2              /db07/oradata/ora7/rbs4ora7.dbf
      1      982          1      982       3       7890       4

ARMSLIVEIDX03_TS  /db04/oradata/ora7/armslive/armliveidxb01.dbf
    870       51        870       51     323        124       4

ARMSLIVETMP01_TS  /db06/oradata/ora7/armslive/armslivetmpb01.dbf
    866        1      24729        1    1402          2       3

ARMSLIVEDB12_TS   /u03/oradata/ora7/armslive/armslivedbu19.dbf
    157      323        157      323      32       1065       2

ARMSLIVEDB11_TS   /u09/oradata/ora7/armslive/armslivedbu16.dbf
     87      331        273      331      30       1693       2

ARMSLIVEIDX10_TS  /u16/oradata/ora7/armslive/armsliveidx17.dbf
    137       25        137       25      69         66       1

SYSTEM            /u01/oradata/ora7/syst1ora7.dbf
     63       23         79       23      31        204       0

SYSTEM            /u01/oradata/ora7/syst2ora7.dbf
     34       12         37       12      11         90       0

ARMSLIVEDB02_TS   /db06/oradata/ora7/armslive/armslivedb06.dbf
      7       32          7       32       1        203       0

From this I/O overview report, it is evident that there is some I/O activity on the system and the tablespace most affected by this is ARMSLIVEDB04_TS. The tablespace has an I/O rate of around 93 read/writes per second. Depending on the speed of the disk or the I/O rate the disks can support, it can be ascertained whether the value obtained is high or not. Having observed that there is a high activity on the system, you can now proceed to identify the user causing this high I/O to be generated.