IO Tuning Tips

Scenario: In AWR report you see most of IO then you need to figure out which are queries which are directly using PGA and memory is not enough to hold all the blocks, Oracle used Temp Tablespaces.

Below are actiity which causes most of IO.

• Sort
• Group by
• Full table scan etc

IO Tuning
---------------------------
OS Level

• iostat
• vmstat

Analyzing Using Dynamic performance view

SELECT * FROM V$FILESTAT
SELECT * FROM V$SYSTEM_EVENT
SELECT NAME, PHYRDS, PHYWRTS
FROM V$DATAFILE df, V$FILESTAT fs
WHERE df.FILE# = fs.FILE#;


Separating Tables and Indexes

It is not necessary to separate a frequently used table from its index. During the course of a transaction, the index is read first, and then the table is read. Because these I/Os occur sequentially, the table and index can be stored on the same disk without contention. However, for very high OLTP systems, separating indexes from tables may be required.

Split indexes and tables into separate tablespaces to minimize disk head movement and parallelize I/O. Both reads happen faster because one disk head is on the index data and the other is on the table data.

The idea of separating objects accessed simultaneously applies to indexes as well. For example, if a SQL statement uses two indexes at the same time, then performance is improved by having each index on a separate disk.

Also, avoid having several heavily accessed tables on the same disk. This requires strong knowledge of the application access patterns.

The use of partitioned tables and indexes can improve performance of operations in a data warehouse. Divide a large table or index into multiple physical segments residing in different tablespaces. All tables that contain large object datatypes should be placed into a separate tablespace as well.

First, determine if DBWR is keeping up the write requests. Review the V$SYSTEM_EVENT view for significant numbers of 'free buffer' waits. Large values may indicate that users wants to read a buffer, but they cannot because there are too many dirty buffers in the cache. If you do not see free buffer waits, then DBWR is not a problem.

4 groups x 2 members each = 8 logfiles labeled: 1a, 1b, 2a, 2b, 3a, 3b, 4a, 4b.

This requires at least 4 disks, plus one disk for archived files.


Table creattion
---------------------

CREATE TABLESPACE stripedtabspace
   DATAFILE 'file_on_disk_1' SIZE 1GB,
      'file_on_disk_2' SIZE 1GB,
      'file_on_disk_3' SIZE 1GB,
      'file_on_disk_4' SIZE 1GB,
      'file_on_disk_5' SIZE 1GB;


CREATE TABLE stripedtab (
   col_1  NUMBER(2),
   col_2  VARCHAR2(10) )
   TABLESPACE stripedtabspace
   STORAGE ( INITIAL 1023MB  NEXT 1023MB
      MINEXTENTS 5  PCTINCREASE 0 );



Disk Access Minimum Stripe Size
Random reads and writes

The minimum stripe size is twice the Oracle block size.

Sequential reads

The minimum stripe size is twice the value of DB_FILE_MULTIBLOCK_READ_COUNT.

Table 20-15 Typical Stripe Size



SELECT NAME, VALUE
FROM V$SYSSTAT
WHERE NAME = 'recursive calls';

Oracle responds with something similar to the following:

NAME                                                         VALUE
------------------------------------------------------- ----------
recursive calls                                             626681


Misses on the data dictionary cache.
Firing of database triggers.
Execution of Data Definition Language (DDL) statements.
Execution of SQL statements within stored procedures, functions, packages, and anonymous PL/SQL blocks.
Enforcement of referential integrity constraints.



Using the DB_WRITER_PROCESSES initialization parameter, you can create multiple database writer processes (from DBW0 to DBW9). Database I/O slaves provide non-blocking, asynchronous requests to simulate asynchronous I/O.

First, determine if DBWR is keeping up the write requests. Review the V$SYSTEM_EVENT view for significant numbers of 'free buffer' waits. Large values may indicate that users wants to read a buffer, but they cannot because there are too many dirty buffers in the cache. If you do not see free buffer waits, then DBWR is not a problem.

Dissecting the I/O Path

This section explains the I/O path, so that you can analyze and tune I/O bottlenecks. The I/O path follows these steps:

1. A user process issues an I/O call (read or write).
2. The I/O is placed on an available CPU's dispatch queue. An available CPU picks up the request and context switches the user process.
3. The CPU checks the local cache to see if the requested data block is there. If it is, then it is a cache hit, and the I/O request is complete.
4. If the block does not exist in the cache or main memory, then it takes a major page fault (gets page from disk), and issues an I/O call against the appropriate device driver. The device driver builds a set of SCSI commands against an I/O unit.
5. The operating system (device driver) sends an I/O request through system bus to I/O controller (host bus adapter).
6. The host bus adapter (HBA) arbitrates for bus access. When the I/O request's device is ready, it is selected, and the I/O statement is prepared to be sent to the target.
7. If the target unit can satisfy the request from its cache, then it transfers the data back and disconnects. This is a disk cache hit. For a cache miss, the target disconnects and tries to service the request.
8. The I/O request is placed in the target's queue table on its adapter where it may be sorted and merged with other I/O requests. This is possible only if the disk unit supports tag queuing.
9. After the I/O is picked off the queue, it is serviced by computing the physical address and seeking to the correct sector, read or write. If it is a read operation, then data is placed in the target cache. Write operations signal a completion by sending an interrupt signal.
10. The target controller reconnects with the I/O bus to transfer the data (with reads).
11. The HBA sends an interrupt to the operating system, marking the end of the I/O request.

The following table explains where the wait components lay with respect to the I/O path.

Steps 1 - 5, 11	These steps are handled by the operating system. The time required for these operations is limited by access time to the HBA. Slowness can be attributed to CPU contention or I/O bus contention. A heavily loaded CPU is not able to service an I/O request. Review vmstat statistics for runnable process, high system time, and excessive context switch.
Steps 5, 6, 10, 11	These two steps involve the I/O adapter. A faster adapter propagates and manages I/O requests faster. An overloaded I/O bus may cause I/O requests to process slowly. Review IOSTAT statistics for high percent busy combined with large AVWAIT (or AVSERV, if available).
Steps 7 - 9	These two steps are handled by the disk drive. Limiting factors can be seek times, rotational delays, and data transfer times. These disk operations are mechanical in nature; therefore, they consume the largest chunk of time in an I/O call. Newer disks have improved seek times, rotational speeds, and data transfer rates. Mechanical time is considered wasted time, because no data (productive work) is transferred during this time. The goal is to minimize this time by acquiring disks with larger caches and by using disks with tag queuing. To minimize the mechanical overhead of an I/O, spread the I/O request across several disks using an appropriate stripe size under a RAID implementation. An incorrect stripe size can cause hot disks or multiple physical I/Os per logical I/O. Additionally, use a raw interface (raw devices) or direct I/O when possible. Raw devices allow unbuffered I/O and can utilize kernalized asynchronous I/O. Raw interfaces can also be implemented using a volume manager. Finally, check to see if your operating system provides direct I/O support on file system-based files. Direct I/O has proven to be helpful for I/Os that involve sequential reads and writes.