Page 554
You can also have a sample overview at the operating-system level on a UNIX system using the sar command, as shown in Listing 22.16. The sar command shown next samples the disk activity for an interval of 5 seconds for 3 times.
Listing 22.16 sar Command Used for Monitoring Disk I/O Statistics$ sar -d 5 3 HP-UX arms1 B.10.10 U 9000/819 08/30/97 19:43:00 device %busy avque r+w/s blks/s avwait avserv 19:43:05 c0t5d0 76.05 1.94 169 2711 8.76 5.94 c0t4d0 9.58 0.95 12 192 8.86 9.04 c0t3d0 8.18 0.50 6 102 4.77 12.28 c3t6d0 7.58 0.50 28 447 5.20 3.12 c4t6d1 15.77 1.30 28 450 8.44 14.35 c4t5d0 6.19 0.50 5 77 5.02 12.64 c3t4d0 7.19 0.50 9 150 5.27 7.68 c3t5d0 0.40 0.50 0 6 3.80 9.32 19:43:10 c0t6d0 0.20 0.50 0 1 6.42 11.56 c0t5d0 72.60 1.79 186 2980 8.59 5.09 c0t4d0 6.20 0.50 7 115 5.48 8.48 c0t3d0 8.60 0.50 7 112 5.19 13.32 c3t6d0 6.20 0.50 27 438 5.12 2.42 c4t6d1 15.40 1.52 25 394 9.70 16.09 c4t5d0 3.60 0.50 3 42 4.40 14.15 c3t4d0 5.00 0.50 6 99 5.63 7.49 19:43:15 c0t5d0 73.20 1.86 184 2947 9.44 5.38 c0t4d0 12.00 0.50 14 221 5.05 9.26 c0t3d0 8.60 0.50 6 99 5.39 12.99 c3t6d0 6.40 0.50 27 435 5.31 2.33 c4t6d1 14.40 1.33 23 365 10.21 14.72 c4t5d0 4.20 0.50 3 51 5.38 13.08 c3t4d0 9.60 0.50 13 202 4.76 7.91 Average c0t5d0 73.95 1.86 180 2879 8.93 5.46 Average c0t4d0 9.26 0.66 11 176 6.53 9.01 Average c0t3d0 8.46 0.50 7 104 5.11 12.88 Average c3t6d0 6.73 0.50 28 440 5.21 2.63 Average c4t6d1 15.19 1.38 25 403 9.38 15.03 Average c4t5d0 4.66 0.50 4 56 4.97 13.14 Average c3t4d0 7.26 0.50 9 150 5.12 7.74 Average c3t5d0 0.13 0.50 0 2 3.80 9.32 Average c0t6d0 0.07 0.50 0 0 6.42 11.56
This sar command was fired at the same time the I/O overview snapshot script was executed. In this command, disk c0t5d0 has a read/write rate of 180 and clearly indicates the heavy I/O access on that disk. The sar output is detailed; it shows the avwait, which is the time spent in the queue and the avserv, which is the sum of the seek time plus the rotational latency plus the disk transfer time. This information can be pretty useful. Using both of these techniques, you can identify the disk and files that are heavily accessed.
Page 555
Now let's find users using a lot of I/O resources. At the operating-system level, it is difficult to find the users doing a lot of I/O; therefore, you will derive your information by using Oracle system tables.
To identify heavy resource users, you can use the V$SESS_IO view. This view stores information about the I/O statistics for the sessions. For every session, there is one row corresponding to it in this view. The explanation of the columns in the view V$SESS_IO is given in Table 22.2.
Table 22.2 Column Description of View V$SESS_IOColumn Name | Description |
SID | The session identifier |
BLOCK_GETS | The number of block gets for the session |
CONSISTENT_GETS | The number of consistent gets for this session using the consistent get mechanism |
PHYSICAL_READS | The number of physical reads for this session |
BLOCK_CHANGES | The number of block changes for this session |
CONSISTENT_CHANGES | The number of consistent changes for this session |
As shown in the previous section, you can use the snapshotting technique on this view, storing the start and end snapshot values captured during a monitoring interval in a temporary table and then using the difference to find the users doing a lot of file access. Use the difference in the physical_read column to find users doing lots of data access from the disk. The difference in the block_gets column can be used to identify users doing a lot of buffer reads. Similar scripts can be developed as shown in the CPU section earlier.
The most common events in the database that might cause heavy disk access are sorts to disk (which can cause bottlenecks on the temp tablespace), full table scans, and heavy index reads.
You can identify sessions currently doing a sort by using the script in Listing 22.17.
Listing 22.17 Script to Identify Sessions Currently Doing a SortSelect sid From v$session_wait Where event = `db file scattered read' And p1 = <file id>;
The p1 column in v$session_wait will contain the file ID on which the event is
performed. Now, you can replace file id by the
file# value found from dba datafiles for files belonging
to the temporary tablespace. Select the file# column from
dba_data_files for temporary tablespaces in the database and replace
file id variable in Listing 22.17.
Page 556
After the sid value is found, the current SQL statement executed by the session can be found by using V$SQLTEXT view and the script in Listing 22.17. If excessive disk sorts are performed on the database, consider increasing the value of the init.ora parameter SORT_AREA_SIZE.
To identify a session currently doing a full table scan and waiting for a multiblock read call to return or whose last wait was for a multiblock read, you can use the script in Listing 22.18. Listing 22.18 will immediately identify all sessions doing full table scans.
Listing 22.18 Script to Find Sessions Currently Doing Full Table Scanscol sql_text format a40 select sid,sql_text From v$session a, v$sqlarea b Where a.sql_hash_value = b.hash_value And sid in (Select sid From v$session_wait Where event like `db file scattered read') / SID SQL_TEXT ---------- ---------------------------------------- 100 select ord_id from cust_ord where crea_u ser_id like `BSMJG%' 102 select emp_id,name from employee where salary > 1003;
To identify SQL statements that are doing a lot of physical reads and accessing a large number of buffers, you can use the V$SQLAREA view, as in Listing 22.19 You can replace the value of buffer_gets and disk_reads in Listing 22.19 by a value that you think is appropriate.
Listing 22.19 Script to Identify Sessions Doing Excessive Physical Reads or Accessing a Large Number of Buffersset pagesize 100 col sql_text format a50 select substr(sql_text,1,200) sql_text, buffer_gets, disk_reads from v$sqlarea where buffer_gets > 10000 or disk_reads > 10000000 order by disk_reads desc SQL_TEXT BUFFER_GETS DISK_READS -------------------------------------------------- ---------- ---------- Select RTrim ( cust_ord.brand_id ) ,RTrim ( ord_id 20488669 13025204 ) ,Nvl ( cust_ord.pmt _mthd_id ,'12' ) ,Nvl ( card _num ,'0' ) ,To_Char ( Nvl ( card_exp_dt ,sysdate ) ,'mmyy' ) ,Nvl ( pp_amt ,0.00 ) ,Nvl ( ord_amt ,