PURPOSE
Purpose
Scope and Application
- AWR (or Statspack) reports show I/O wait events in the "Top 5 Wait/Timed Events" section.
- SQL Tracing with wait events of a database session shows it is limited mainly by I/O wait events.
- Operating System tools show very high utilization or saturation of disks used for storage of database files.
TROUBLESHOOTING STEPS
Troubleshooting I/O-related waits
Time 'Service Time'
'Wait Time'Document 190124.1Document 250655.1### Determining the Real Significance of I/O Wait Events _Legacy information: Prior to Oracle9i Release 2 Statspack reports contain this information in a section called "Top 5 Wait Events". The "Top 5 Wait Events" section has been renamed to "Top 5 Timed Events" where 'Service Time' as measured by the statistic 'CPU used by this session' is listed as 'CPU time' (this started in Oracle9i Release 2). This means that it is now easier to accurately measure the impact of Wait Events in overall 'Response Time' and to correctly target the subsequent tuning effort._### Misinterpreting the Impact of Wait Events: Examples
Example 1: Statspack before Oracle9i Release 2
Top 5 Wait Events ~~~~~~~~~~~~~~~~~ Wait % Total Event Waits Time (cs) Wt Time ——————————————– ———— ———— ——- direct path read 4,232 10,827 52.01 db file scattered read 6,105 6,264 30.09 direct path write 1,992 3,268 15.70 control file parallel write 893 198 .95 db file parallel write 40 131 .63
------------------------------------------------------------- Statistic Total per Second per Trans ——————————— —————- ———— ———— CPU used by this session 358,806 130.5 12,372.6
CPU time = 94.52% direct path read = 2.85% db file scattered read = 1.65% direct path write = 0.86% control file parallel write = 0.05% db file parallel write = 0.03%
Example 2: AWR after Oracle10i Release 2
Top 5 Timed Foreground Events ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Avg
wait % DB Event Waits Time(s) (ms) time Wait Class —————————— ———— ———– —— —— ———- DB CPU 33,615 82.0 db file sequential read 3,101,013 7,359 2 18.0 User I/O log file sync 472,958 484 1 1.2 Commit read by other session 46,134 291 6 .7 User I/O db file parallel read 91,982 257 3 .6 User I/O ### General Approaches for Handling I/O Problems
Reduce the general IO traffic being generated by the database
- Tune SQL doing the most IO (or logical reads). Even tuning SQL not doing "db file scattered read" might lighten any general IO congestion.
- Size the SGA buffer cache based on the advisors
- Tune the "schema" e.g. investigate if partitioning can reduce IO
- Look for objects that can be shrunk via online shrink (leveraging Segment Advisor) / perhaps in conjunction with purging old data.
- Investigate the use of materialized views to save both onlybe processing time and IO in general (and possibly of offload processing to a remote DB).
- Result caching allows you to store the results of queries, query fragments and function result sets. For details see:
http://docs.oracle.com/database/121/CNCPT/memory.htm#CNCPT1919
- Investigate the Advanced Compression option (to reduce the amount of IO required)
- Cache specific objects into the SGA buffer cache
Document 262687.1#### Reduce the I/O requirements of the database by tuning instance parameters
- Using memory caching to limit I/O:
The amount of I/O required by the database is limited by the use of a number of memory caches e.g. the Buffer Cache, the Log Buffer, various Sort Areas etc. Increasing the Buffer Cache, up to a point, results in more buffer accesses by database processes (logical I/Os) being satisfied from memory instead of having to go to disk (physical I/Os). With larger Sort Areas in memory, the likelihood of them being exhausted during a sorting operation and having to use a temporary tablespace on disk is reduced. The other caches also work according to similar concepts.
- Tuning the size of multiblock I/O (relevant to pre 10g)
The size of multiblock I/O is controlled by the db_file_multiblock_read_count parameter which became self-tuning in Oracle 10g Release 2 . From Oracle 10g Release 2, this parameter is set automatically to a function of buffer cache size and the sessions parameter. Adjustment is not recommended. See:
_Legacy information (pre 10g):_
_The size of individual multiblock I/O operations can be controlled by instance parameters. Up to a limit, multiblock I/Os are executed faster when there are fewer larger I/Os than when there are more smaller I/Os. For example, transferring 100Mb of data will complete faster if it is done in 100 requests of size 1Mb each than if it is done in 1,000 requests of size 100Kb each or 10,000 requests of 10Kb each. After this limit is reached, the difference is no longer important: transferring 1Gb of data in 100 requests of size 10Mb each (if allowed by limits on maximum I/O transfer size of Operating Systems) would be almost as efficient as a single transfer of size 1Gb. This is because the time taken to service an I/O involves two main components: _
- _I/O Setup Time: tends to be fairly constant across different I/O sizes and for small I/O sizes tends to dominate the total service time._
- _I/O Transfer Time: tends to increase in proportion to the size of the I/O and for small I/O sizes is usually less than the I/O Setup Time._
_The consequence of the above is that before 10g Release 2, it was usually better to configure instance so that the database issues larger and fewer multiblock I/Os by setting DB_FILE_MULTIBLOCK_READ_COUNT._
Make the remaining IO as efficient as possible
- Asynchronous I/O: Asynchronous I/O does not reduce traffic but allows processes to do other things while waiting for IO to complete.
- Direct I/O (bypassing the Operating System's File Caches) : Direct IO does not reduce traffic but may use a shorter code path / fewer CPU cycles to perform the IO.
Balancing the database I/O by usage of Oracle ASM (Automatic Storage Manager)
Document 249992.1Document 1153664.1
http://docs.oracle.com/cd/E11882_01/server.112/e18951/asmdiskgrps.htm#OSTMG13790 #### Balancing the database I/O by usage of Striping, RAID, SAN or NAS
Document 30286.1#### Redistribute database I/O by manual placement of database files across different filesystems, controllers and physical devices
- Reducing the data volumes of the current database by moving older data out.
- Investing in more and/or faster hardware
Managing Runaway IO Using Resource Manager
Effective Resource Management Using Oracle Database Resource Manager
Avoid External IO contention
- Look out for oversubscribing IO bandwidth across too many virtual environments (Exadata has IORM)
- Schedule backups to avoid competing with important batch jobs (avoid potentially IO heavy operations running simultaneously)
- What other systems are attached to the same NAS storage? How much IO are they generating?
- Is the storage doing any resilvering work (replacement of bad disk mirrors)? ASM rebalancing activity?
Use Faster Hardware
- Faster disks (10K rpm vs. 7200 rpm, more disk level cache, no energy saving – idling / spin reduction)
- Use of outside tracks vs. inside tracks (intelligent data placement)
- Flash storage / intelligent storage array caches
- Exadata storage (leveraging HCC, storage indexes)
- Infiniband networks to reduce network latency of IO (and/or?) higher bandwidth
Leverage New Technology
https://www.oracle.com/storage/san/fs1/index.html
http://www.oracle.com/us/products/oracle-fs1-2-ds-2301395.pdf ### Datafile I/O related Wait Events
'db file sequential read'
Document 34559.1Document 1475825.1
- Find the Top SQL statements in Physical Reads (from a Statspack or AWR report
in the section titled "SQL ordered by Reads" or from the view V$SQL)
and tune them in order to reduce their I/O requirements:
- If Index Range scans are involved, more blocks than necessary could be being visited if the index is unselective.
By forcing or enabling the use of a more selective index, we can access the same table data by visiting fewer index blocks (and doing fewer physical I/Os).
- If indexes are fragmented, again we have to visit more blocks because there is less index data per block.
In this case, rebuilding the index will compact its contents into fewer blocks. Indexes can be (online) rebuilt, shrunk, or coalesced.
- If the index being used has a large Clustering Factor, then more table data blocks have to be visited in order to get the rows in each index block: by rebuilding the table with its rows sorted by the particular index columns we can reduce the Clustering Factor and hence the number of table data blocks that we have to visit for each index block.
For example, if the table has columns A, B, C & D and the index is on B, D then we can rebuild the table as:
See:
- Use Partitioning to reduce the number of index and table data blocks to be visited for each SQL statement by usage of Partition Pruning.
- If there are no particular SQL statements with bad execution plans doing more physical I/Os than necessary, then one of the following may be happening:
- I/Os on particular datafiles may be being serviced slower due to excessive activity on their disks. In this case, looking at the Statspack "File I/O Statistics" section (or V$FILESTAT) will help us find such hot disks and spread out the I/O by manually moving datafiles to other storage or by making use of Striping, RAID and other technologies to automatically perform I/O load balancing for us.
- Starting with Oracle 9.2, we can also find which segments (tables or indexes) have the most Physical Reads being performed against them by using the new Segment Statistics data from view V$SEGMENT_STATISTICS.We can then look in detail at such segments and see if e.g. indexes should be rebuilt or Partitioning could be used to reduce I/O on them.
Statspack also generates a "Segment Statistics" report starting at level 7.
- If there is no SQL with suboptimal execution plans and I/O is evenly spread out with similar response times from all disks then a larger Buffer Cache may help:
- In Oracle8i experiment with gradual increments of DB_BLOCK_BUFFERS followed
by measurements of the Buffer Cache Hit Ratio from Statspack until there is no further improvement to it.
- In Oracle9i and above use the Buffer Cache Advisory facility (also available in the Statspack report) to tune the size of the Buffer Cache.
For details please refer to the manual:
- In Oracle10g and above Automatic Shared Memory Management (ASMM) can be used to enable the database to automatically determine the optimal size for the Buffer Cache according to recent workload. For more information see
- For hot segments, usage of Multiple Buffer Pools can be explored: place
such hot indexes and tables in the KEEP Buffer Pool. For details refer to
- Finally, you can consider reducing the data held in the most frequently accessed segments (by moving older unneeded data out of the database) or moving these segments to new faster disks to reduce the response time on their I/Os.
'db file scattered read'
- Find which SQL statements perform Full Table or Fast Full Index scans and tune them to make sure these scans are necessary and not the result of a suboptimal plan.
Starting with Oracle9i the new view V$SQL_PLAN view can help: (ignore data dictionary SQL in the output of these queries)
For Full Table scans:
For Fast Full Index scans:
Otherwise a possible approach is to find sessions performing multiblock reads by querying V$SESSION_EVENT for this Wait Event and then SQL Tracing them. Alternatively, the Top SQL statements for Physical Reads can be investigated to see if their execution plans contain Full Table or Fast Full Index scans.
- In cases where such multiblock scans occur from optimal execution plans it is possible to tune the size of multiblock I/Os issued by Oracle by setting the instance parameter DB_FILE_MULTIBLOCK_READ_COUNT so that
DB_BLOCK_SIZE x DB_FILE_MULTIBLOCK_READ_COUNT = max_io_size of system
For more information refer to
As stated previously, starting with Oracle10g Release 2 DB_FILE_MULTIBLOCK_READ_COUNT initialization parameter is now automatically tuned to use a default value when this parameter is not set explicitly. This default value corresponds to the maximum I/O size that can be performed efficiently. This value is platform-dependent and is 1MB for most platforms. Because the parameter is expressed in blocks, it will be set to a value that is equal to the maximum I/O size that can be performed efficiently divided by the standard block size.
- As blocks read using Full Table and Fast Full Index scans are placed on the least recently used end of the Buffer Cache replacement lists, sometimes it may help to use Multiple Buffer Pools and place such segments in the KEEP pool.
For more information please refer to
- Partitioning can also be used to reduce the amount of data to be scanned as Partition Pruning can restrict the scan to a subset of the segment's partitions.
- If the activity is mostly from reports or other data warehouse like activity then consider offloading that activity to a read only standby database or Active Data Guard instance to separate it from OLTP activity on the production system.
- With Database 12c is to load such tables into the IM column store (particularly if a good compression ratio can be achieved). See:
[
- Finally, you can consider reducing the data held in the most frequently accessed segments (by moving older unneeded data out of the database) or moving these segments to new faster disks to reduce the response time on their I/Os.
'db file parallel read'
Direct Path Reads and Writes
'direct path read'Document 50415.1Document 1476089.1
Document 1475655.1'direct path write'Document 50416.1Document 1477235.1'direct path read (lob)''direct path write (lob)'
- Usage of Asynchronous I/O is recommended where available.
- In Oracle8i, minimize the number of I/O requests by setting the DB_FILE_DIRECT_IO_COUNT instance parameter so that DB_BLOCK_SIZE x DB_FILE_DIRECT_IO_COUNT = max_io_size of system
In Oracle8i the default for this is 64 blocks.
(In Oracle9i, it is replaced by _DB_FILE_DIRECT_IO_COUNT which governs the size of direct I/Os in BYTES (not blocks). The default is 1Mb but will be sized down if the max_io_size of the system is smaller.)
- Tune memory Sort areas so that disk I/O for Sorting is minimized:
In 9i and above use Automated SQL Execution Memory Management.
In 8i tune the various Sort areas manually.
- For LOB segments, store them on filesystems where an Operating System File
Buffer Cache can provide some memory caching.
- Identify sessions performing direct I/Os by querying V$SESSION_EVENT for these Wait Events or V$SESSTAT for statistics:
'physical reads direct', 'physical reads direct (lob)', 'physical writes direct' & 'physical writes direct (lob)'and tune their SQL statements.
- Identify datafiles on bottlenecked disk storage and move elsewhere using V$FILESTAT or Statspack's "File IO Statistics" section.
Controlfile I/O related Wait Events
'control file parallel write'
- Reduce the number of controlfile copies to the minimum that ensures that not all copies can be lost at the same time.
- Use Asynchronous I/O if available on your platform.
- Move the controlfile copies to less saturated storage locations.
'control file sequential read' and 'control file single write'
- Move the problematic controlfile copy to a less saturated storage location.
- Use Asynchronous I/O if available on your platform.
Redo Logging I/O-Related Wait Events
'log file parallel write'
- Make use of UNRECOVERABLE/NOLOGGING options.
- Reduce the number of redo group members to the minimum necessary to ensure
not all members can be lost at the same time.
- Do not leave tablespaces in BACKUP mode for longer than necessary.
- Only use the minimal level of Supplemental Logging required to achieve the required functionality e.g. in LogMiner, Logical Standby or Streams.
- Place redo group members on storage locations so that parallel writes do not contend with each other.
- Do not use RAID-5 for redo logfiles.
- Use Raw Devices for redo logfiles.
- Use faster disks for redo logfiles.
- If archiving is being used setup redo storage so that writes for the current redo group members do not contend with reads for the group(s) currently being archived.
'log file sync'
- If they are almost similar, then redo logfile I/O is causing the delay and the guidelines for tuning it should be followed.
- If 'log file parallel write' is significantly different i.e smaller, then the delay is caused by the other parts of the Redo Logging mechanism that occur during a COMMIT/ROLLBACK (and are not I/O-related).
Sometimes there will be latch contention on redo latches, evidenced by 'latch free' or 'LGWR wait for redo copy' wait events.
'log file sequential read' and 'log file single write'
'switch logfile command' ,'log file switch completion' and 'log file switch (clearing log file)'
'log file switch (checkpoint incomplete)'
Document 76713.1#### 'log switch/archive' and 'log file switch (archiving needed)'
Buffer Cache I/O-Related Wait Events
'db file parallel write' , 'db file single write', 'write complete waits', 'free buffer waits'
Document 34416.1Document 62172.1
Document 147468.1### Footnote
Who to contact for more information?
Document 1383594.1Document 1275596.1
- Tune SQL doing the most IO (or logical reads). Even tuning SQL not doing "db file scattered read" might lighten any IO congestion.
- Size the SGA buffer cache based on the advisors
- Tune the "schema" e.g. investigate if partitioning can reduce IO.
- Look for objects that can be shrunk via online shrink (leveraging whatever advisor does this) / perhaps in conjunction with purging old data.
- Investigate the Advanced Compression option
- Cache specific objects into the SGA buffer cache
Use Faster Hardware
REFERENCES
NOTE:109907.1 – How to Determine an Optimal SORT_AREA_SIZE
NOTE:76713.1 – 8i Parameters that Influence Checkpoints
NOTE:1398860.1 – How is Parameter DB_FILE_MULTIBLOCK_READ_COUNT Calculated?
NOTE:1037322.6 – WHAT IS THE DB_FILE_MULTIBLOCK_READ_COUNT PARAMETER?
NOTE:50416.1 – WAITEVENT: "direct path write" Reference Note
NOTE:50415.1 – WAITEVENT: "direct path read" Reference Note
NOTE:432854.1 – Asynchronous I/O Support on OCFS/OCFS2 and Related Settings: filesystemio_options, disk_asynch_io
NOTE:45042.1 – Archiver Best Practices
NOTE:249992.1 – New Feature on ASM (Automatic Storage Manager).
NOTE:1275596.1 – How to Tell if the I/O of the Database is Slow
NOTE:47324.1 – Init.ora Parameter "DB_FILE_DIRECT_IO_COUNT" Reference Note
NOTE:147806.1 – Oracle9i New Feature: Automated SQL Execution Memory Management
NOTE:262687.1 – Using the DBMS_SQLTUNE package to Run the Sql Tuning Advisor
NOTE:34558.1 – WAITEVENT: "db file scattered read" Reference Note
NOTE:34559.1 – WAITEVENT: "db file sequential read" Reference Note
NOTE:34592.1 – WAITEVENT: "log file sync" Reference Note
NOTE:147468.1 – Checkpoint Tuning and Troubleshooting Guide
NOTE:34583.1 – WAITEVENT: "log file parallel write" Reference Note
NOTE:62172.1 – Understanding and Tuning Buffer Cache and DBWR
NOTE:34412.1 – WAITEVENT: "control file parallel write" Reference Note
NOTE:1153664.1 – Comparing ASM to Filesystem in benchmarks
NOTE:190124.1 – The COE Performance Method
NOTE:30286.1 – I/O Tuning with Different RAID Configurations
NOTE:30712.1 – Init.ora Parameter "DB_FILE_MULTIBLOCK_READ_COUNT" Reference Note
NOTE:250655.1 – How to use the Automatic Database Diagnostic Monitor
NOTE:34416.1 – WAITEVENT: "db file parallel write" Reference Note
NOTE:39836.1 – Clustering Factor
NOTE:76374.1 – Multiple Buffer Pools
NOTE:257643.1 – Oracle Database Automated SGA Memory Tuning