 |
|
Oracle Tips by Burleson |
I/O Timing Data
Of course, the I/O timing data from inside Oracle
is also a critical component of the analysis. The script below shows
an example query that can be used to get the I/O timing data for the
various data and temp files:
--
*************************************************
-- Copyright © 2005 by Rampant TechPress
-- This script is free for non-commercial purposes
-- with no warranties. Use at your own risk.
--
-- To license this script for a commercial purpose,
-- contact info@rampant.cc
-- *************************************************
Col file#
format 99999 heading 'File#'
col name format a45 heading 'Name'
col phywrts heading 'Phys.|Writes'
col phyrds heading 'Phys.|Reads'
col read_rat heading 'Avg.|Read|Time' format 990.000
col write_rat heading 'Avg.|Write|Time' format 990.000
set lines 132 pages 45
ttitle 'I/O Timing Analysis'
spool io_time
select f.FILE#
,d.name,PHYRDS,PHYWRTS,READTIM/greatest(PHYRDS,1) read_rat,WRITETIM/greatest(PHYWRTS,1)
write_rat
from v$filestat f, v$datafile d
where f.file#=d.file#
union
select f.FILE# ,d.name,PHYRDS,PHYWRTS,READTIM/greatest(PHYRDS,1)
read_rat,WRITETIM/greatest(PHYWRTS,1) write_rat
from v$tempstat f, v$tempfile d
where f.file#=d.file#
order by 5 desc;
spool off
ttitle off
clear columns
set lines 80 pages 22
The script queries the
v$tempfile and v$datafileviews for timing data and shows the average time per I/O
operation on the files. An example report is shown below:
Mon Jul
19 page 1
I/O Timing Analysis
Avg. Avg.
Phys. Phys.
Read Write
File#
Name Reads Writes Time
Time
-----
--------------------------------------- -------- ------ -------
-------
3
/u12/oracle/oradata/dss/undotbs103.dbf 50 397 1.400
0.073
13
/u10/oracle/oradata/dss/xdb01.dbf 11 3 1.363
0.333
1
/u08/oracle/oradata/dss/system01.dbf 12857 1201 1.127
0.087
4
/u11/oracle/oradata/dss/undotbs102.dbf 313 3157 0.785
0.069
2
/u09/oracle/oradata/dss/undotbs101.dbf 50 2979 0.760
0.064
2
/u12/oracle/oradata/dss/tem102.dbf 4 0 0.500
0.000
1
/u12/oracle/oradata/dss/tem101.dbf 8733 25480 0.480
0.599
5
/u08/oracle/oradata/dss/dss_data01.dbf 33688717 3 0.448
0.000
7
/u10/oracle/oradata/dss/dss_data03.dbf 30345274 3 0.444
0.000
6
/u09/oracle/oradata/dss/dss_data02.dbf 29910924 3 0.441
0.000
11
/u09/oracle/oradata/dss/drsys01.dbf 5 3 0.400
0.333
3
/u12/oracle/oradata/dss/temp05.dbf 4 0 0.250
0.000
12
/u09/oracle/oradata/dss/tools01.dbf 5 3 0.200
0.000
15
/u10/oracle/oradata/dss_index05.dbf 5 3 0.200
0.000
16
/u09/oracle/oradata/dss_index06.dbf 5 3 0.200
0.000
17
/u08/oracle/oradata/dss_index07.dbf 5 3 0.200
0.000
10
/u14/oracle/oradata/dss/dss_index03.dbf 173160 3 0.110
0.000
9
/u13/oracle/oradata/dss/dss_index02.dbf 181979 3 0.106
0.000
8
/u11/oracle/oradata/dss/dss_index01.dbf 182678 3 0.102
0.333
14
/u13/oracle/oradata/dss/dss_index3.dbf 11554 3 0.017
0.000
4
/u01/oracle/oradata/dss/temp021.dbf 0 0 0.000
0.000
5
/u02/oracle/oradata/dss/temp022.dbf 0 0 0.000
0.000
18
/u03/oracle/oradata/dss/undo021.dbf 0 3 0.000
0.000
19
/u04/oracle/oradata/dss/uno022.dbf 0 3 0.000
0.000
24 rows
selected.
It is apparent that the I/O times are actually
quite acceptable for a disk I/O subsystem. All I/O times for the
filesystems of concern are fractional milliseconds. However, when the
volume of I/O operations is large, even a fractional time multiplies
rapidly. In this case, moving to SSD assets is indicated.
So far the use of scripts to determine I/O rates
and timings has been covered. From this data, the files that are
experiencing the most I/O should be placed on the SSD drive. However,
this is not the total picture. It is also necessary to look at the
wait interface to see if the I/O is actually generating wait related
issues. While self generated scripts can be used, the STATSPACK
scripts do a great job on reporting against the wait interface.
The
above book excerpt is from:
Oracle
Solid State Disk Tuning
High Performance Oracle
tuning with RAM disk
ISBN
0-9744486-5-6
Donald K. Burleson & Mike Ault
http://www.rampant-books.com/book_2005_1_ssd.htm
 |
For more details and scripts, see my new book "
Oracle
Tuning: The Definitive Reference", over 900 pages
of BC's favorite tuning tips & scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot. |
|
