Tuning Disk I/O Operations in Oracle
Database
By Ahmed Baraka
Oracle© Database Performance Tuning - a course by Ahmed Baraka
Objectives
In this lecture, you should learn how to perform the following:
• List the memory components involved in the database I/O operations
• Describe the buffer cache I/O waits events
• Describe the difference between I/O through Buffer Cache and PGA
• Understand I/O performance tuning hierarchy
• Obtain details on the current I/O events
• Interpret I/O statistics in AWR reports
• Describe the guideline about handling I/O bottlenecks
• Describe the impact of changing data block sizes on I/O operations
• Set I/O mode
• Perform I/O calibration
• Understand the best practices on managing the tablespaces
Oracle© Database Performance Tuning - a course by Ahmed Baraka
Memory Components Involved in I/O Operations
• Memory components that have communication with the storage:
- Buffer cache
- PGA
- Redo Log buffer
- Flash Cache
• I/O wait events belong to wait class 'User I/O'
Oracle© Database Performance Tuning - a course by Ahmed Baraka
SQL Buffer Cache I/O Waits
Wait Event Description
db file sequential read Wait for a single block to be delivered from the disk to the
memory to satisfy the statement. Common in OLTP.
db file scattered read Wait for multi-block I/O to complete, like FTS, RS, and IFF.
Common in warehouse databases.
db file parallel read Wait for parallel reads from multiple data files to non-
contiguous buffers (PGA or buffer cache), like in buffer
prefetching or recovery operations
• They are normal outcomes from healthy systems
• They indicate I/O issue, when they are in the top wait events of the
performance issue scope
Oracle© Database Performance Tuning - a course by Ahmed Baraka
The difference between I/O through Buffer Cache and
PGA
• FTS might be processed by:
- Going through the buffer cache: 'db file scattered read'
Slower because more latches involved
Has impact on the buffer cache lru
Fetched blocks are shared
- Going through the PGA: 'direct path read'
Faster
Does not affect the buffer cache lru
Fetch blocks are not shared
• Depends on the size of the table, buffer cache size, and other statistics.
Oracle© Database Performance Tuning - a course by Ahmed Baraka
I/O Performance Tuning Hierarchy
high number
SQL of waits
buffer busy waits
Buffer Cache or CBC latches
average wait
I/O Subsystem time > 20 ms
Oracle© Database Performance Tuning - a course by Ahmed Baraka
I/O Issue Example 1
Oracle© Database Performance Tuning - a course by Ahmed Baraka
I/O Issue Example 1
Oracle© Database Performance Tuning - a course by Ahmed Baraka
I/O Issue Example 1
Oracle© Database Performance Tuning - a course by Ahmed Baraka
I/O Issue Example 2
Oracle© Database Performance Tuning - a course by Ahmed Baraka
I/O Issue Example 2
Oracle© Database Performance Tuning - a course by Ahmed Baraka
I/O Issue Example 2
Oracle© Database Performance Tuning - a course by Ahmed Baraka
Obtaining Details on Current I/O Events
SELECT EVENT, P1TEXT, P1, P2TEXT,P2, WAIT_CLASS, .. FROM V$SESSION ..
EVENT P1TEXT P1 P2TEXT
---------------------- ----------- ----- ----------
db file scattered read file number 201 first dba
P2 WAIT_CLASS SECONDS_IN_WAIT WAIT_TIME_MICRO STATE
----- ---------- --------------- --------------- -------------------
14844 User I/O 0 3352 WAITED SHORT TIME
SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE
FROM DBA_EXTENTS
WHERE FILE_ID=201 AND ( 14844 BETWEEN BLOCK_ID AND (BLOCK_ID+ BLOCKS ))
OWNER SEGMENT_NAME SEGMENT_TYPE
-------------------- -------------------- -------------
SOE CUSTOMERS_PK INDEX
Oracle© Database Performance Tuning - a course by Ahmed Baraka
Obtaining Details on Current I/O Events (cont..)
SELECT EVENT, P1TEXT, P1, P2TEXT,P2, ROW_WAIT_OBJ#,.. FROM V$SESSION ..
EVENT P1TEXT P1 P2TEXT
---------------------- ----------- ----- ----------
db file scattered read file number 201 first dba
P2 WAIT_CLASS ROW_WAIT_OBJ# WAIT_TIME_MICRO STATE
----- ---------- --------------- --------------- -------------------
14844 User I/O 73859 3352 WAITED SHORT TIME
SELECT OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_TYPE
FROM DBA_OBJECTS WHERE DATA_OBJECT_ID =73859 ;
OWNER OBJECT_NAME SUBOBJECT_ OBJECT_TYPE
--------------- -------------------- ---------- -----------------------
SOE CUSTOMERS_PK INDEX
Oracle© Database Performance Tuning - a course by Ahmed Baraka
IO Stats in AWR Reports
Oracle© Database Performance Tuning - a course by Ahmed Baraka
IO Stat by Function summary
Oracle© Database Performance Tuning - a course by Ahmed Baraka
IO Stat by Filetype Summary
Oracle© Database Performance Tuning - a course by Ahmed Baraka
Tablespace / File IO Stats in AWR in Oracle 12.2
• Tablespace IO Stats and File IO Stats are empty in AWR report in
Oracle 12.2
• According to Document ID 2333859.1, set STATISTICS_LEVEL to
ALL or apply the patch 25416731 to address the issue
Oracle© Database Performance Tuning - a course by Ahmed Baraka
Tablespace / File IO Stats in AWR
Oracle© Database Performance Tuning - a course by Ahmed Baraka
Tablespace / File IO Stats in AWR
Oracle© Database Performance Tuning - a course by Ahmed Baraka
Handling I/O Bottlenecks
• SQL Tuning:
- SQL Tuning
- Gather and manage optimizer statistics
• Database tuning to reduce I/O
- Resolve undersized memory areas
- Use server cache result or client cache
• Physical I/O Tuning:
- Resolve I/O subsystem Issues
- Use Automatic Storage Management.
- Add more disks to reduce the number of I/Os on each disk
Oracle© Database Performance Tuning - a course by Ahmed Baraka
Choosing Data Block Size
• Has an impact on the entire database I/O operations
• Default: 8 KB. OLTP: 2 KB or 4 KB. DSS: 16 or 32 KB.
Block Size Pros. Cons.
Smaller • Efficient with small rows that are • Large space needed for block
randomly accessed headers
• Reduces block contention • Increase the possibility of row
chaining for large rows
Larger • Efficient with large rows • Waste space with small rows
• Allows reading more rows into the • Increase contention of index leaf
buffer cache with a single I/O blocks
• Efficient with sequential access
Oracle© Database Performance Tuning - a course by Ahmed Baraka
Setting I/O Mode
• Set by setting the parameter FILESYSTEMIO_OPTIONS parameter:
Value Description
ASYNCH Use asynchronous I/O (if available)
DIRECTIO Use direct I/O (if available)
SETALL Use asynchronous I/O and direct I/O
NONE Do not use asynchronous I/O and direct I/O
Oracle© Database Performance Tuning - a course by Ahmed Baraka
I/O Calibration from inside the Database
• Aims at obtaining the attached I/O subsystem performance capacity
specifications
• Used in:
- assessing the performance of the storage subsystem
- verifying that I/O performance problems are caused by the storage
subsystem
- setting the automatic degree of parallelism (DOP)
• Executed by DBMS_RESOURCE_MANAGER.CALIBRATE_IO (can only be
executed by sys)
- Should be used when the database is placed in a quiesced state
Oracle© Database Performance Tuning - a course by Ahmed Baraka
Using DBMS_RESOURCE_MANAGER.CALIBRATE_IO
DECLARE
V_LAT INTEGER; /* actual latency in ms */
V_IOPS INTEGER; /* i/o rate per second */
V_MBPS INTEGER; /* throughput, mb per second */
BEGIN
DBMS_RESOURCE_MANAGER.CALIBRATE_IO(
1 /* number of disks */
, 10 /* maximum tolerable latency in ms */
, V_IOPS
, V_MBPS
, V_LAT );
DBMS_OUTPUT.PUT_LINE('Max IOPS = ' || V_IOPS);
DBMS_OUTPUT.PUT_LINE('Actual Latency (ms) = ' || V_LAT);
DBMS_OUTPUT.PUT_LINE('Max MBPS = ' || V_MBPS);
END;
/
Oracle© Database Performance Tuning - a course by Ahmed Baraka
About I/O Calibration using Oracle Orion
• An external utility that works without having to have a database created
• Simulates database I/O workloads
• Does not support devices with filesystems
• Tested on DAS, SAN, and NAS
Oracle© Database Performance Tuning - a course by Ahmed Baraka
Managing Tablespaces Best Practices
• User tablespaces:
- Use locally managed tablespaces
- Use automatic segment-space management (ASSM)
• Undo tablespaces:
- Use automatic undo management
- Sizing the undo tablespace:
Set its initial size to small value (100 MB) and the enable the AUTOEXTEND
Use the Undo Advisor, add 20% margin, then disable the AUTOEXTEND
Oracle© Database Performance Tuning - a course by Ahmed Baraka
Summary
In this lecture, you should have learnt how to perform the following:
• List the memory components involved in the database I/O operations
• Describe the buffer cache I/O waits events
• Describe the difference between I/O through Buffer Cache and PGA
• Understand I/O performance tuning hierarchy
• Obtain details on the current I/O events
• Interpret I/O statistics in AWR reports
• Describe the guideline about handling I/O bottlenecks
• Describe the impact of changing data block sizes on I/O operations
• Set I/O mode
• Perform I/O calibration
• Understand the best practices on managing the tablespaces
Oracle© Database Performance Tuning - a course by Ahmed Baraka
