I/O Tuning

     Note: This article ( named I/O Tuning ) was taken from www.in-oracle.com.

     If an application is not well tuned, the I/O time will be longer and the CPU will wait for the disk. This has a big impact over the database performance.  To have a tuned application at the I/O level means not to have more I/O activity than we need or we can have. Here are the ways we can reduce the I/O activity: 

     1. Tuning SQL statements: this has a big impact on the I/O activity. It is obvious that an unnecessary full-table scan will generate more I/O then an index range scan. The effect is seen on that statement and also could be seen on the whole database. Fore more information about SQL tuning click here.

     2. Tuning SGA memory: when we increase the shared_pool, large_pool or db_cache_size, we can see a reduction in disk I/O. (Writing one time more is better then writing many time less). Fore more information about SGA tuning click here. 

    3. Using a proper data block size for the data objects: 

• Small blocks for tables with small rows which are accessed in a random fashion must be placed in tablespaces with small block sizes;

• Large blocks for tables with large rows which often experience full table scan.

    4. Table reorganizations: when the table is reorganized the data is not fragmented and that generate less I/O.

    Even if some tasks don't modify the I/O activity, these tasks could have a positive impact on the database performance. Here are the main 2 tasks from this category:

 

1. Using faster disks: in this case even the cost of a full table scan is smaller and over all performance is increased.   

 

2. Distribute the load on the datafiles: In order to have less waits for the disk to finish the I/O job, also we have to take care of the data files distributions over the disks. 

    Here is the select which show the data files distributions and the I/O activity of each:

 

   SELECT NAME, PHYRDS,PHYWRTS

   FROM V$DATAFILE DF, V$FILESTAT FS 

   WHERE DF.FILE#=FS.FILE#;

 

 

Note: This article ( named I/O Tuning ) was taken from www.in-oracle.com.

 

 

There are two types of data block access in Oracle Type of Data Block Access Number of blocks read Example
db file sequential read A single-block read index fetch by ROWID
db file scattered read A multiblock read a full-table scan, OPQ, sorting


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.

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.

 

More information you can get from here.