Oracle資料庫非同步IO導致查詢響應緩慢

尛樣兒發表於2015-09-02
Oracle資料庫非同步

    客戶的環境是兩臺HP-UX ia64 B.11.31部署的一套Oracle 11.2.0.4.4 RAC Database，儲存是一套EMC，一套HDS，透過賽門鐵克storage foundation將兩套儲存做成映象，實現節點之間的共享儲存。前期只有一套HDS在使用，在將EMC加入到storage foundation之後，RAC的第一個節點出現查詢操作緩慢的情況，包括sqlplus本地登陸緩慢，查詢只有兩條資料的臨時表耗時12秒，資料庫例項啟動也非常的慢。

    最終該問題透過檢查等待事件的方式得以解決，下面簡單描述一下處理過程：

會話1：
在伺服器本地使用sqlplus登陸資料庫例項，

1).執行下面的SQL語句確定本會話的SID：
SQL> SELECT DISTINCT SID FROM V$MYSTAT;

2).執行查詢2條資料的臨時表（固定耗時12秒）。

會話2：
在伺服器本地使用sqlplus登陸資料庫例項，

執行下面的SQL語句，查詢會話1在查詢2條資料的臨時表時發生的等待事件：
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

    該等待事件的WAIT_CLASS為Other，比較異常，從EVENT可以大概瞭解該等待和非同步IO有一定的關係，透過在MOS上檢索該EVENT找到如下一篇文章：

Bug 9829397  Excessive CPU and many "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 affected Versions >= 11.2 but BELOW 12.1
Versions confirmed as being affected
Platforms affected Generic (all / most platforms affected)

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

Fixed:

This issue is fixed in

Symptoms:

Related To:

Description 

Some queries in 11.2 may exhibit higher CPU usage than earlier
releases with many "asynch descriptor resize" waits occurring
compared 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 many resizes of the number of AIO 
 descriptors unnecessarily wasting CPU. 
 
Workaround Disable async IO. 
 eg: Set DISK_ASYNCH_IO = false    <<<< 禁用非同步IO
 
References:
 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 included) 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.


References

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

從文章可以看出該問題可能是個bug，透過在資料庫例項關閉非同步IO即可解決問題，根據文章執行如下的操作：

SQL> show parameter io


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
......
disk_asynch_io                       boolean     TRUE
......

SQL> alter system set disk_asynch_io=false scope=spfile sid='scrk1';

System altered.

重啟RAC第一個節點資料庫例項之後問題即得到解決。

--end--

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/23135684/viewspace-1789767/，如需轉載，請註明出處，否則將追究法律責任。

相關文章