how to tell if th io of the database is slow
2019/4/30 文档 1275596.1 1/9
版权所有 (c) 2019,Oracle。保留所有权利。Oracle 机密。
How to Tell if the I/O of the Database is Slow (文档 ID 1275596.1)
In this Document
Ask Questions, Get Help, And Share Your Experiences With This Article
What is "Slow"?
Response time
Types of IO
Read or Write
Single Block or MultiBlock
Synchronous or Asynchronous.
Expected thresholds for response time
IO Wait Outliers (Intermittent Short IO Delays)
Identifying IO Response Time
Sources in Oracle identifying Response Time
10046 Trace File
System State Dump
Statspack and AWR reports
Discuss Troubleshooting I/O Contention
Oracle Database Backup Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Oracle Database Cloud Service - Version N/A and later
Oracle Database - Enterprise Edition - Version and later
Oracle Database - Personal Edition - Version and later
Information in this document applies to any platform.
This document outlines some of the thresholds whereby RDBMS Support may consider IO to be slow and thus a
potential reason for a performance problem. It also looks into how to collect the supporting evidence from the
RDBMS perspective. It does not aim to provide diagnostics to understand why the IO is slow nor does it provide
any detailed explanation as to why slow IO may be occurring.
If the underlying cause for slow performance is found to be a result of slow IO at the OS level, then the
appropriate vendor responsible for the IO subsystem (hardware and software) should be engaged to diagnose
and correct the situation.
Ask Questions, Get Help, And Share Your Experiences With This Article
Would you like to explore this topic further with other Oracle Customers, Oracle Employees, and
Industry Experts?
2019/4/30 文档 1275596.1 2/9
Click here to join the discussion where you can ask questions, get help from others, and share your
experiences with this specific article.
Discover discussions about other articles and helpful subjects by clicking here to access the main
page for Database Tuning.
The efficiency of IO may be measured in 2 ways :
Response Time
Measured in milliseconds an operation takes to complete. This statistic is gathered by Oracle.
Measured as the number of operations per unit of time. This is calculated using OS tools for example iostat
on Unix.
As this document concentrates on how to determine whether IO is slow from the perspective of Oracle, we will
not go into detail on throughput and how it is measured. The following document describes how to install and
use OS Watcher in order to collect and archive iostat information as well as other OS information:
Document 301137.1 OS Watcher User Guide
For help in troubleshooting see
Document 223117.1 Troubleshooting I/O-related waits
What is "Slow"?
"Slow" is a very subjective term and depends largely upon the expectations of the system and the hardware from
the user. Users with Enterprise Storage Systems may expect all IO requests to return in 10ms or less while
individuals with an old laptop computer using an external disk connected via an ancient USB 1.0 interface may
well have different expectations!
Additionally, end users will come to expect a certain level of response time for their OLTP requests and reports. If
the response time changes dramatically, it could be due to a dramatic change in average IO response time even
though both old and new response times are less than what may be deemed as the standard reasonable IO
response time (e.g. degradation from 3ms to 9ms may have a significant impact on application performance but
IO may not be considered 'slow' until time goes above 20ms). The reasons for the IO response time change are
varied, examples may include migrating from file system cache to shared disk where there is no file system
caching or changing of system backup schedules such that its IO traffic overlaps with a batch job.
One of the ways that you can detect such changes is to record performance statistics during periods of normal
performance for comparison purposes when performance issues are reported using a tool such as OS Watcher:
Document 301137.1 OS Watcher User Guide
Response time
Hardware does not necessarily respond in a uniform fashion for each IO request; there are always likely to be
peaks and troughs. It is therefore common to measure response time using an average.
Note: In order to mitigate the effects of high/low value anomalies, the size of the sampled data set needs to
be 'significant'. To this end, the number of samples should be at least 1000 operations per hour in order to be
considered reliable and usable for decision making.
of 9


Top250 周榜 月榜