In Oracle : Oracle I/O tuning

Oracle Applications EBS 11i, R12      Oracle Database      Data Warehouse & BI      Oracle Middleware      PL/SQL      Linux ...

Oracle DBA

Real Application Cluster (RAC) Maintenance tasks Backup and Recovery Database Architecture Replication Oracle Performance Tuning Oracle DBA - Other articles Oracle scripts/ Selects for DBA Oracle Errors (ORA-nnnnn)

The last articles in the site (HOT)

The most visited articles in the site

In-Oracle.com  -> Oracle DBA -> Oracle Performance Tuning

-> Oracle I/O tuning

 

 Oracle I/O tuning

 

 

 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: Oracle SQL Tuning.

 

     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: Oracle SGA Tuning

 

    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#;

 


In-Oracle.com  -> Oracle DBA -> Oracle Performance Tuning

-> Oracle I/O tuning

Oracle Applications EBS 11i, R12      Oracle Database      Data Warehouse & BI      Oracle Middleware      PL/SQL      Linux ...

 

     Copyright (c) 2011-2015  www.in-oracle.com  |  Disclaimer: The views expressed on this web site are my own and do not reflect the views of Oracle Corporation. You may use the information from this site only at your risk.