Oracle Database asynchronous IO causes slow query response

The customer's environment is a two-HP-UXia64B.11.31 deployment of Oracle11.2.0.4.4RACDatabase, storage is a set of EMC, a set of HDS, through Symantec storage

The customer's environment is a set of Oracle 11.2.0.4.4 RAC databases deployed on two HP-UX ia64 B .11.31, the storage is a set of EMC, a set of HDS, through Symantec storage

The customer's environment is two HP-UX ia64 B .11.31 deployment of a set of Oracle 11.2.0.4.4 RAC Database, storage is a set of EMC, a set of HDS, through Symantec storage foundation to the two sets of storage into an image, implements shared storage between nodes. Only one HDS is in use in the early stage. After EMC is added to the storage foundation, the query operation on the first node of RAC is slow, including slow local login of sqlplus, it takes 12 seconds to query a temporary table with only two data items, and the database instance starts very slowly.

Finally, the problem is solved by checking the wait event. The following describes the process:

Session 1:

Log on to the database instance using sqlplus locally on the server,

1) execute the following SQL statement to determine the SID of the session:

SQL> SELECT DISTINCT SID FROM V $ MYSTAT;

2). Execute the temporary table to query two data entries (fixed time: 12 seconds ).

Session 2:

Log on to the database instance using sqlplus locally on the server,

Run the following SQL statement to query the wait event that occurs when Session 1 queries a temporary table with two data records:

SQL> set linesize 200
SQL> set pagesize 200
SQL> col program format a30
SQL> col machine format a30
SQL> col wait_class format a30
SQL> select username, program, machine, event, wait_class from v $ session where wait_class <> 'idle' and sid = 572

Username program machine event WAIT_CLASS
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SYS sqlplus @ rx9900a (TNS V1-V3) rx9900a asynch descriptor resize Other

The WAIT_CLASS of the wait EVENT is Other, which is abnormal. From the EVENT, you can probably understand the relationship between the wait and asynchronous IO. You can search for the EVENT on the MOS and find it, as shown in the next article:

Bug 9829397 Excessive CPU and memory "asynch descriptor resize" waits for SQL using Async IO
This note gives a brief overview of bug 9829397.
The content was last updated on: 28-JUN-2013
Click here for details of each of the sections below.

Affects:

Product (Component) Oracle Server (Rdbms)

Range of versions believed to be affectedVersions> = 11.2 but BELOW 12.1

Versions confirmed as being affected

Platforms affectedGeneric (all/most platforms affected)

It is believed to be a regression in default behaviour thus:
Regression introduced in 11.2.0.2
Fixed:

Issue is fixed in

Symptoms: Related:

DISK_ASYNCH_IO

DescriptionSome queries in 11.2 may exhibit higher CPU usage than earlierreleases with tags "asynch descriptor resize" waits occurringcompared to the same SQL in earlier releases. rediscovery Notes: Async IO is in use. the total time waiting for "asynch descriptor resize" is typically very small but with very high counts. the high wait count indicates extends resizes of the number of AIO descriptors unnecessarily wasting CPU. workaround Disable async IO. eg: Set DISK_ASYNCH_IO = false <disable asynchronous IOReferences: For more information about "asynch descriptor resize" see the following: Note: 1273748.1 High Numbers of 'asynch descriptor resize' waits Note: 1081977.1 Details of the "asynch descriptor resize" wait event. getting a Fix Use one of the "Fixed" versions listed above (for Patch Sets/bundles use the latest version available as contents are cumulative-the "Fixed" version listed above is the first version where the fix is wrongly DED) or Click here for suggestions on how to get a fix for this issue

Please note: The above is a summary description only. actual symptoms can vary. matching to any symptoms here does not confirm that you are encountering this problem. for questions about this bug please consult Oracle Support.

ReferencesBug: 9829397 (This link will only work for PUBLISHED bugs)
Note: 245840.1 Information on the sections in this article

From the article, we can see that this problem may be a bug. By disabling asynchronous IO on the database instance, we can solve the problem. perform the following operations according to the article:

SQL> show parameter io