Oracle: Reducing disk I/O with SSD
As RAM storage becomes cheaper than ever, many companies are
exploring the issue of fully cached Oracle databases. As noted in
an earlier Oracle tip, solid-state disk is changing the way that
Oracle professionals manage and tune their databases.
One of the issues is the relatively high cost of fetching an Oracle
data block from disk. In theory, RAM is 10,000 times faster than
disk with speeds in the milliseconds versus nanoseconds; however,
when the overhead of lock serialization and latches is added in, a
logical I/O might be less than a thousand times faster than a
physical disk I/O.
SSD is especially useful for Oracle undo logs, redo logs and the
TEMP tablespace, but it can be used with any Oracle data file for
high-speed access. In a real world setting, SSD has been used to
set a new record for table load rates, using SQL*Loader to get over 500,000 rows per second into a table!
Yes, that is 30 million rows per minute!
Oracle speeds are very high with SSD, and SSD is cheap too at only
$1k/gig USD. The existing TPC-C benchmark is always under challenge
in hopes of setting the new world record by exceeding one million
transactions per minute using Oracle9i with SSD.
Companies such as Texas Memory Systems are offering solid-state disk
replacement for the Oracle data buffer cache to speed up I/O at the
physical level. Companies such as UNISYS are getting blistering
performance from Oracle using 100 gigabyte
db_cache_size and
db_keep_cache_size .
The following section will provide scripts for real time Oracle disk
monitoring and then move on into information on the AWR and ASH
tables as they relate to Oracle disk I/O.
SEE CODE DEPOT FOR FULL SCRIPTS
