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.
IO Tuning
---------------------------
OS Level
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.
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:
- A user process issues an I/O call (read or write).
- 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.
- 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.
- 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.
- The operating system (device driver) sends an I/O request through system bus to I/O controller (host bus adapter).
- 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.
- 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.
- 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.
- 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.
- The target controller reconnects with the I/O bus to transfer the data (with reads).
- 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.
