ITPub博客

Oracle数据库异步IO导致查询响应缓慢

原创 作者:尛样儿 时间:2015-09-02 21:37:27 0 删除 编辑
<span style="font-size:16px;font-family:'Courier New';"><br /> &nbsp; &nbsp; 客户的环境是两台HP-UX ia64&nbsp;B.11.31部署的一套Oracle 11.2.0.4.4 RAC Database,存储是一套EMC,一套HDS,通过赛门铁克storage foundation将两套存储做成镜像,实现节点之间的共享存储。前期只有一套HDS在使用,在将EMC加入到storage foundation之后,RAC的第一个节点出现查询操作缓慢的情况,包括sqlplus本地登陆缓慢,查询只有两条数据的临时表耗时12秒,数据库实例启动也非常的慢。<br /> </span><br /> <span style="font-size:16px;font-family:'Courier New';">&nbsp; &nbsp; 最终该问题通过检查等待事件的方式得以解决,下面简单描述一下处理过程:</span><br /> <br /> <span style="font-size:16px;font-family:'Courier New';"><strong>会话1:</strong></span><br /> <span style="font-size:16px;font-family:'Courier New';">在服务器本地使用sqlplus登陆数据库实例,</span><br /> <br /> <span style="font-size:16px;font-family:'Courier New';">1).执行下面的SQL语句确定本会话的SID:</span><br /> <span style="font-size:16px;font-family:'Courier New';">SQL&gt; SELECT DISTINCT SID FROM V$MYSTAT;</span><br /> <br /> <span style="font-size:16px;font-family:'Courier New';">2).执行查询2条数据的临时表(<span style="color:#E53333;">固定耗时12秒</span>)。</span><br /> <br /> <span style="font-size:16px;font-family:'Courier New';"><strong>会话2:</strong></span><br /> <span style="font-size:16px;font-family:'Courier New';">在服务器本地使用sqlplus登陆数据库实例,</span><br /> <br /> <span style="font-size:16px;font-family:'Courier New';">执行下面的SQL语句,查询会话1在查询2条数据的临时表时发生的等待事件:</span><br /> <span style="font-size:16px;font-family:'Courier New';">SQL&gt;&nbsp;set linesize 200</span><br /> <span style="white-space:normal;font-size:16px;font-family:'Courier New';">SQL&gt;&nbsp;</span><span style="font-size:16px;font-family:'Courier New';">set pagesize 200</span><br /> <span style="white-space:normal;font-size:16px;font-family:'Courier New';">SQL&gt;&nbsp;</span><span style="font-size:16px;font-family:'Courier New';">col program format a30</span><br /> <span style="white-space:normal;font-size:16px;font-family:'Courier New';">SQL&gt;&nbsp;</span><span style="font-size:16px;font-family:'Courier New';">col machine format a30</span><br /> <span style="white-space:normal;font-size:16px;font-family:'Courier New';">SQL&gt;&nbsp;</span><span style="font-size:16px;font-family:'Courier New';">col wait_class format a30</span><br /> <span style="font-size:16px;font-family:'Courier New';">SQL&gt; select username,program,machine,event,wait_class from v$session where wait_class &lt;&gt;'Idle' and sid=572</span><br /> <br /> <br /> <span style="font-size:16px;font-family:'Courier New';">USERNAME &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; PROGRAM &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;MACHINE &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;EVENT &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;WAIT_CLASS</span><br /> <span style="font-size:16px;font-family:'Courier New';">------------------------------ ------------------------------ ------------------------------ ---------------------------------------------------------------- ------------------------------</span><br /> <span style="font-size:16px;font-family:'Courier New';">SYS &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;sqlplus@rx9900a (TNS V1-V3) &nbsp; &nbsp;rx9900a &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;<span style="color:#E53333;">asynch descriptor resize</span> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; <span style="color:#E53333;">Other</span></span><br /> <div style="white-space:nowrap;"> <br /> <span style="font-size:16px;font-family:'Courier New';">&nbsp; &nbsp;&nbsp;该等待事件的WAIT_CLASS为Other,比较异常,从EVENT可以大概了解该等待和异步IO有一定的关系,通过在MOS上检索该EVENT找到如下一篇文章:</span><br /> <span style="font-size:16px;font-family:'Courier New';"></span> <h3> <u><span style="font-size:16px;font-family:'Courier New';">Bug 9829397&nbsp;&nbsp;Excessive CPU and many "asynch descriptor resize" waits for SQL using Async IO</span></u> </h3> <span style="font-size:16px;font-family:'Courier New';">&nbsp;This note gives a brief overview of bug 9829397. </span><br /> <span style="font-size:16px;font-family:'Courier New';"> &nbsp;The content was last updated on: 28-JUN-2013</span><br /> <span style="font-size:16px;font-family:'Courier New';"> &nbsp;</span><i><span style="font-size:16px;font-family:'Courier New';">Click </span><a href="https://support.oracle.com/epmos/faces/DocumentDisplay?parent=DOCUMENT&amp;sourceId=9829397.8&amp;id=245840.1"><span style="font-size:16px;font-family:'Courier New';">here</span></a><span style="font-size:16px;font-family:'Courier New';"> for details of each of the sections below.</span></i><br /> <h3> <u><span style="font-size:16px;font-family:'Courier New';">Affects:</span></u> </h3> <blockquote> <table border="1"> <tbody> <tr> <td> <b><span style="font-size:16px;font-family:'Courier New';">Product (</span><i><span style="font-size:16px;font-family:'Courier New';">Component</span></i><span style="font-size:16px;font-family:'Courier New';">)</span></b> </td> <td> <span style="font-size:16px;font-family:'Courier New';">Oracle Server (Rdbms)</span> </td> </tr> <tr> <td> <b><span style="font-size:16px;font-family:'Courier New';">Range of versions </span><i><span style="font-size:16px;font-family:'Courier New';">believed</span></i><span style="font-size:16px;font-family:'Courier New';"> to be affected</span></b> </td> <td> <span style="font-size:16px;font-family:'Courier New';">Versions &gt;= 11.2 but BELOW 12.1</span> </td> </tr> <tr> <td> <b><span style="font-size:16px;font-family:'Courier New';">Versions </span><i><span style="font-size:16px;font-family:'Courier New';">confirmed</span></i><span style="font-size:16px;font-family:'Courier New';"> as being affected</span></b> </td> <td> <ul compact="compact"> <li> <a href="https://support.oracle.com/epmos/faces/DocumentDisplay?parent=DOCUMENT&amp;sourceId=9829397.8&amp;id=245840.1#AFFECTS_11.2.0.2"><span style="font-size:16px;font-family:'Courier New';">11.2.0.2</span></a> </li> <li> <a href="https://support.oracle.com/epmos/faces/DocumentDisplay?parent=DOCUMENT&amp;sourceId=9829397.8&amp;id=245840.1#AFFECTS_11.2.0.1"><span style="font-size:16px;font-family:'Courier New';">11.2.0.1</span></a> </li> </ul> </td> </tr> <tr> <td> <b><span style="font-size:16px;font-family:'Courier New';">Platforms affected</span></b> </td> <td> <span style="font-size:16px;font-family:'Courier New';">Generic (all / most platforms affected)</span> </td> </tr> </tbody> </table> <br /> <span style="font-size:16px;font-family:'Courier New';">&nbsp;It is believed to be a </span><a href="https://support.oracle.com/epmos/faces/DocumentDisplay?parent=DOCUMENT&amp;sourceId=9829397.8&amp;id=245840.1#TAGS_REGRESSION"><span style="font-size:16px;font-family:'Courier New';">regression</span></a><span style="font-size:16px;font-family:'Courier New';"> in </span><b><span style="font-size:16px;font-family:'Courier New';">default</span></b><span style="font-size:16px;font-family:'Courier New';"> behaviour thus:</span><br /> <span style="font-size:16px;font-family:'Courier New';"> &nbsp;&nbsp;&nbsp;Regression introduced in 11.2.0.2</span><br /> </blockquote> <h3> <u><span style="font-size:16px;font-family:'Courier New';">Fixed:</span></u> </h3> <blockquote> <table border="1"> <tbody> <tr> <td> <b><span style="font-size:16px;font-family:'Courier New';">This issue is fixed in</span></b> </td> <td> <ul compact="compact"> <li> <a href="https://support.oracle.com/epmos/faces/DocumentDisplay?parent=DOCUMENT&amp;sourceId=9829397.8&amp;id=245840.1#FIXED_12.1.0.1"><span style="font-size:16px;font-family:'Courier New';">12.1.0.1 (Base Release)</span></a> </li> <li> <a href="https://support.oracle.com/epmos/faces/DocumentDisplay?parent=DOCUMENT&amp;sourceId=9829397.8&amp;id=245840.1#FIXED_11.2.0.3"><span style="font-size:16px;font-family:'Courier New';">11.2.0.3 (Server Patch Set)</span></a> </li> <li> <a href="https://support.oracle.com/epmos/faces/DocumentDisplay?parent=DOCUMENT&amp;sourceId=9829397.8&amp;id=13343424.8"><span style="font-size:16px;font-family:'Courier New';">11.2.0.2.5 Database Patch Set Update</span></a> </li> <li> <a href="https://support.oracle.com/epmos/faces/DocumentDisplay?parent=DOCUMENT&amp;sourceId=9829397.8&amp;id=13343447.8"><span style="font-size:16px;font-family:'Courier New';">11.2.0.2.5 Grid Infrastructure Patch Set Update (GI PSU)</span></a> </li> <li> <a href="https://support.oracle.com/epmos/faces/DocumentDisplay?parent=DOCUMENT&amp;sourceId=9829397.8&amp;id=13395584.8"><span style="font-size:16px;font-family:'Courier New';">11.2.0.2 Bundle Patch 13 for Exadata Database</span></a> </li> <li> <a href="https://support.oracle.com/epmos/faces/DocumentDisplay?parent=DOCUMENT&amp;sourceId=9829397.8&amp;id=1114533.1"><span style="font-size:16px;font-family:'Courier New';">11.2.0.2 Patch 13 on Windows Platforms</span></a> </li> <li> <a href="https://support.oracle.com/epmos/faces/DocumentDisplay?parent=DOCUMENT&amp;sourceId=9829397.8&amp;id=1114533.1"><span style="font-size:16px;font-family:'Courier New';">11.2.0.1 Patch 15 on Windows Platforms</span></a> </li> </ul> </td> </tr> </tbody> </table> </blockquote> <table align="top" width="90%"> <tbody> <tr> <td> <h3> <u><span style="font-size:16px;font-family:'Courier New';">Symptoms:</span></u> </h3> </td> <td> <h3> <u><span style="font-size:16px;font-family:'Courier New';">Related To:</span></u> </h3> </td> </tr> <tr> <td> <ul compact="compact"> <li> <a href="https://support.oracle.com/epmos/faces/DocumentDisplay?parent=DOCUMENT&amp;sourceId=9829397.8&amp;id=245840.1#TAGS_CPU"><span style="font-size:16px;font-family:'Courier New';">Excessive CPU Usage</span></a> </li> <li> <a href="https://support.oracle.com/epmos/faces/DocumentDisplay?parent=DOCUMENT&amp;sourceId=9829397.8&amp;id=245840.1#TAGS_QPERF"><span style="font-size:16px;font-family:'Courier New';">Performance Of Query/ies Affected</span></a> </li> <li> <a href="https://support.oracle.com/epmos/faces/DocumentDisplay?parent=DOCUMENT&amp;sourceId=9829397.8&amp;id=1081977.1"><span style="font-size:16px;font-family:'Courier New';">Waits for "asynch descriptor resize"</span></a> </li> </ul> </td> <td> <ul compact="compact"> <li> <a href="https://support.oracle.com/epmos/faces/DocumentDisplay?parent=DOCUMENT&amp;sourceId=9829397.8&amp;id=47328.1"><span style="font-size:16px;font-family:'Courier New';">DISK_ASYNCH_IO</span></a> </li> </ul> </td> </tr> </tbody> </table> <h3> <u><span style="font-size:16px;font-family:'Courier New';">Description</span></u> </h3> <blockquote> <pre>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.&nbsp;</pre> <pre><b><u> </u></b></pre> <pre><b><u>Workaround</u></b> Disable async IO. eg: Set DISK_ASYNCH_IO = false &lt;&lt;&lt;&lt; 禁用异步IO References: For more information about "asynch descriptor resize" see the following: <a href="https://support.oracle.com/epmos/faces/DocumentDisplay?parent=DOCUMENT&amp;sourceId=9829397.8&amp;id=1273748.1">Note:1273748.1</a> High Numbers of 'asynch descriptor resize' waits <a href="https://support.oracle.com/epmos/faces/DocumentDisplay?parent=DOCUMENT&amp;sourceId=9829397.8&amp;id=1081977.1">Note:1081977.1</a> Details of the "asynch descriptor resize" wait event. </pre> <pre><b><u>Getting a Fix</u></b> Use one of the "Fixed" versions listed above <i>(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)</i> or <a href="https://support.oracle.com/epmos/faces/DocumentDisplay?parent=DOCUMENT&amp;sourceId=9829397.8&amp;id=1373411.1&amp;ARGBUG=9829397&amp;ARGPROD=5&amp;ARGCOMP=RDBMS&amp;ARGFIX=V1102000205%20V11020002.BP13%20V11020002.GIPSU05%20V11020003%20V12010001%20CWIN:B201P15%20CWIN:B202P13&amp;ARGMIN=V1102&amp;ARGXMIN=V11020002&amp;ARGXBIT=8192&amp;ARGMAX=V1201&amp;ARGHOOK=&amp;ARGTAG=&amp;X" target="_blank">Click here for suggestions on how to get a fix for this issue</a> </pre> </blockquote> <p> <br /> </p> <table align="center" border="1" width="80%"> <tbody> <tr> <td> <b><span style="font-size:16px;font-family:'Courier New';">Please note:</span></b><span style="font-size:16px;font-family:'Courier New';"> 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.</span> </td> </tr> </tbody> </table> <p> <br /> </p> <h3> <u><span style="font-size:16px;font-family:'Courier New';">References</span></u> </h3> <blockquote> <a href="https://support.oracle.com/epmos/faces/BugDisplay?parent=DOCUMENT&amp;sourceId=9829397.8&amp;id=9829397" target="_blank"><span style="font-size:16px;font-family:'Courier New';">Bug:9829397</span></a><span style="font-size:16px;font-family:'Courier New';"> (This link will only work for PUBLISHED bugs)</span><br /> <a href="https://support.oracle.com/epmos/faces/DocumentDisplay?parent=DOCUMENT&amp;sourceId=9829397.8&amp;id=245840.1"><span style="font-size:16px;font-family:'Courier New';">Note:245840.1</span></a><span style="font-size:16px;font-family:'Courier New';"> Information on the sections in this article&nbsp;</span> </blockquote> <span style="font-size:16px;font-family:'Courier New';"></span><br /> <span style="font-size:16px;font-family:'Courier New';">从文章可以看出该问题可能是个bug,通过在数据库实例关闭异步IO即可解决问题,根据文章执行如下的操作:</span><br /> <span style="font-size:16px;font-family:'Courier New';"></span><br /> <span style="font-size:16px;font-family:'Courier New';"></span><span style="font-size:16px;font-family:'Courier New';">SQL&gt; show parameter io</span><br /> <br /> <br /> <span style="font-size:16px;font-family:'Courier New';">NAME &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; TYPE &nbsp; &nbsp; &nbsp; &nbsp;VALUE</span><br /> <span style="font-size:16px;font-family:'Courier New';">------------------------------------ ----------- ------------------------------</span><br /> <span style="font-family:'Courier New';"><span style="font-size:16px;line-height:24px;">......</span></span><br /> <span style="font-size:16px;font-family:'Courier New';">disk_asynch_io &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; boolean &nbsp; &nbsp; TRUE</span><br /> <span style="font-family:'Courier New';"><span style="font-size:16px;line-height:24px;">......<br /> </span></span><br /> <span style="font-size:16px;font-family:'Courier New';">SQL&gt; <span style="color:#E53333;">alter system set disk_asynch_io=false scope=spfile sid='scrk1';</span></span><br /> <br /> <span style="font-size:16px;font-family:'Courier New';">System altered.</span><br /> <span style="font-size:16px;font-family:'Courier New';"></span><br /> <span style="font-size:16px;font-family:'Courier New';">重启RAC第一个节点数据库实例之后问题即得到解决。</span><br /> <span style="font-size:16px;font-family:'Courier New';"></span><br /> <span style="font-size:16px;font-family:'Courier New';">--end--</span><br /> <br /> </div>
请登录后发表评论 登录
全部评论
Oracle数据库管理员,Oracle数据库系统构架员;2012年7月出版《构建最高可用Oracle数据库系统:Oracle 11gR2 RAC管理、维护与性能优化》一书;Oracle 10g OCM。

注册时间:2010-01-05

  • 博文量
    461
  • 访问量
    5178484