Previous | Table of Contents | Next

Page 550

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 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.

Page 551

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.

Listing 22.14 Scripts to Create Temporary Tables and Insert Values into stat$begin_file Table
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.

Page 552

Listing 22.15 Script to Populate stat$file_end Table and Generate 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,

Page 553

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.

Previous | Table of Contents | Next