 |
|
Oracle Disk I/O
Oracle Tips by Burleson Consulting |
Disk I/O and Oracle
Oracle tuning techniques are continuously being modified to match
changes in the hardware technology. Having current tuning tools is
especially important with data intensive applications like Oracle.
The cost and speed of disk devices have had a considerable impact on
Oracle tuning activities.
In 1985, a 1.2 gigabyte disk sold for more than $250,000. Today,
users can buy 100 gigabytes disks for $200 and 100 gigabytes of
RAM-disk for $100,000. The following statements show how storage
trends change over time:
§
Disk storage improves tenfold every year.
§
Storage media becomes obsolete every 25 years.
§
RAM-SAN will replace disks by 2006.
In Oracle, physical disk I/O can be measured by querying STATSPACK
and the AWR for the physical disk reads information that is captured
inside the stats$filestatxs and
dba_hist_filestatxs tables.
For example, the following Oracle10g script detects all files with
physical reads over 10,000 during the snapshot period:
break on begin_interval_time skip 2
column phyrds format 999,999,999
column begin_interval_time format a25
select
begin_interval_time,
filename,
phyrds
from
dba_hist_filestatxs
natural join
dba_hist_snapshot
where
phyrds > 10000
;
The results yield a running total of Oracle physical reads. The
snapshots are collected every hour in this example, and many DBAs
will increase the default collection frequency of AWR snapshots.
Starting from this script, users could easily add a WHERE
clause criteria and create a unique time-series exception report.
SQL> @phys_reads
BEGIN_INTERVAL_TIME FILENAME PHYRDS
-------------------------
---------------------------------------- -------
24-FEB-04 11.00.32.000 PM
E:\ORACLE\ORA92\FSDEV10G\SYSTEM01.DBF 164,700
E:\ORACLE\ORA92\FSDEV10G\UNDOTBS01.DBF 26,082
E:\ORACLE\ORA92\FSDEV10G\SYSAUX01.DBF 472,008
E:\ORACLE\ORA92\FSDEV10G\USERS01.DBF 21,794
E:\ORACLE\ORA92\FSDEV10G\T_FS_LSQ.ORA 12,123
24-FEB-04 12.00.32.000 PM
E:\ORACLE\ORA92\FSDEV10G\SYSTEM01.DBF 164,700
E:\ORACLE\ORA92\FSDEV10G\UNDOTBS01.DBF 26,082
This concept is particularly true when we consider Moore’s Law,
which essentially states that processor capacity increases steadily
while hardware costs fall and while Moore’s law does not apply to
RAM chip characteristics., RAM is steadily falling in cost, but the
speed has remained the same for more than 30 years, hovering at
about 50 nanoseconds.
SEE CODE DEPOT FOR FULL SCRIPTS
 |
This is an excerpt from my latest book "Oracle
Tuning: The Definitive Reference".
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts: |
http://www.rampant-books.com/book_1002_oracle_tuning_definitive_reference_2nd_ed.htm
|
