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