Previous | Table of Contents | Next

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.

Finding Heavy I/O Users

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_IO
Column 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 Sort
Select 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 Scans
col 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 Buffers
set 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 ,

Previous | Table of Contents | Next