how to tell if th io of the database is slow

LeeWen2020

627

9页

1次

2019-10-10

5墨值下载

2019/4/30 文档 1275596.1

https://support.oracle.com/epmos/faces/DocumentDisplay?_adf.ctrl-state=my278iyqm_269&id=1275596.1 1/9

How to Tell if the I/O of the Database is Slow (文档 ID 1275596.1)

In this Document

Goal

Ask Questions, Get Help, And Share Your Experiences With This Article

Solution

Introduction

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

References

APPLIES TO:

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 9.2.0.1 and later

Oracle Database - Personal Edition - Version 9.2.0.1 and later

Information in this document applies to any platform.

GOAL

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

https://support.oracle.com/epmos/faces/DocumentDisplay?_adf.ctrl-state=my278iyqm_269&id=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.

SOLUTION

Introduction

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.

Throughput

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.