Where did all that I/O go?

10gR2 Note:  Starting in Oracle 10g release2, Oracle recommends not setting the db_file_multiblock_read_count parameter, allowing Oracle to empirically determine the optimal setting.  For more details, see my notes on 10gR2 automatically tuned multi-block reads. Also, see my related important notes on tuning full-scan I/O.

Because all Oracle databases retrieve and store data, the relative cost of physical disk access is an important topic. In Oracle, we see two types of data block access:

Physical disk speed is an important factor in weighing these costs. Faster disk access speeds can reduce the costs of a full-table scan vs. single block reads to a negligible level.

For example, the new solid state disks provide up to 100,000 I/Os per second, six times faster than traditional disk devices. In a solid-state disk environment, disk I/O is much faster and multiblock reads become far cheaper than with traditional disks.

The standard STATSPACK report can be generated when the database is processing a peak load, and you can get a super-detailed report of all elapsed-time metrics. The most important of these metrics is the STATSPACK top-five timed events. This report is critical because it shows the database events that constitute the bottleneck for the system. We can also see the same phenomenon where a system is disk I/O bound. In the STATSPACK report in Listing A, we see that the system is clearly constrained by disk I/O.

Read more about Oracle sequential read disk I/O tuning here:



Need a Health Check?

Oracle is the worlds most complex and robust database and there are hundreds of sub-optimal setting that can cripple your database performance.

Burleson Consulting has a great Oracle health check where we identify all database bottlenecks to ensure that your mission-critical system is running at optimal speeds.

Just call 800-766-1884 to schedule your health check.


Need Oracle Training?

The very best Oracle training comes from Burleson Consulting, where you get an on-site visit by an experienced Oracle expert and author.  Whether it's one-on-one mentoring or getting a customized on-site Oracle training class, there is no substitute for BC Oracle training.  Just call 800-766-1884 for details, and check-out our on-site Oracle training catalog at the following link: