又是数据库服务器IO高引起的问题

我不是女神ヾ 2022-08-18 00:53 136阅读 0赞

昨天整个系统慢，weblogic日志有大量stuck，数据库服务器IO负载高。取了数据库报告后发现了问题。

**负载很高，平常DB Time最多1000.**

<table> 
 <tbody> 
  <tr> 
   <th>Host Name</th> 
   <th>Platform</th> 
   <th>CPUs</th> 
   <th>Cores</th> 
   <th>Sockets</th> 
   <th>Memory (GB)</th> 
  </tr> 
  <tr> 
   <td>XXXXXX</td> 
   <td>AIX-Based Systems (64-bit)</td> 
   <td align="right">128</td> 
   <td align="right">32</td> 
   <td align="right">&nbsp;</td> 
   <td align="right">123.50</td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <tbody> 
  <tr> 
   <th>&nbsp;</th> 
   <th>Snap Id</th> 
   <th>Snap Time</th> 
   <th>Sessions</th> 
   <th>Cursors/Session</th> 
   <th>Instances</th> 
  </tr> 
  <tr> 
   <td>Begin Snap:</td> 
   <td align="right">8940</td> 
   <td align="middle">01-12月-15 13:59:29</td> 
   <td align="right">1263</td> 
   <td align="right">114.2</td> 
   <td align="right">2</td> 
  </tr> 
  <tr> 
   <td>End Snap:</td> 
   <td align="right">8943</td> 
   <td align="middle">01-12月-15 16:19:14</td> 
   <td align="right">1648</td> 
   <td align="right">96.0</td> 
   <td align="right">2</td> 
  </tr> 
  <tr> 
   <td>Elapsed:</td> 
   <td>&nbsp;</td> 
   <td align="middle">139.75 (mins)</td> 
   <td>&nbsp;</td> 
   <td>&nbsp;</td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td>DB Time:</td> 
   <td>&nbsp;</td> 
   <td align="middle">30,337.56 (mins)</td> 
   <td>&nbsp;</td> 
   <td>&nbsp;</td> 
   <td>&nbsp;</td> 
  </tr> 
 </tbody> 
</table>

**TOP等待事件都是IO**

Top 10 Foreground Events by Total Wait Time

<table> 
 <tbody> 
  <tr> 
   <th>Event</th> 
   <th>Waits</th> 
   <th>Total Wait Time (sec)</th> 
   <th>Wait Avg(ms)</th> 
   <th>% DB time</th> 
   <th>Wait Class</th> 
  </tr> 
  <tr> 
   <td>db file sequential read</td> 
   <td align="right">19,395,915</td> 
   <td align="right">512.6K</td> 
   <td align="right">26</td> 
   <td align="right">28.2</td> 
   <td>User I/O</td> 
  </tr> 
  <tr> 
   <td>read by other session</td> 
   <td align="right">9,753,486</td> 
   <td align="right">385.8K</td> 
   <td align="right">40</td> 
   <td align="right">21.2</td> 
   <td>User I/O</td> 
  </tr> 
  <tr> 
   <td>direct path read temp</td> 
   <td align="right">4,205,461</td> 
   <td align="right">310.7K</td> 
   <td align="right">74</td> 
   <td align="right">17.1</td> 
   <td>User I/O</td> 
  </tr> 
  <tr> 
   <td>direct path read</td> 
   <td align="right">1,886,691</td> 
   <td align="right">133.4K</td> 
   <td align="right">71</td> 
   <td align="right">7.3</td> 
   <td>User I/O</td> 
  </tr> 
  <tr> 
   <td>DB CPU</td> 
   <td align="right">&nbsp;</td> 
   <td align="right">86.1K</td> 
   <td align="right">&nbsp;</td> 
   <td align="right">4.7</td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td>direct path write temp</td> 
   <td align="right">840,182</td> 
   <td align="right">61.9K</td> 
   <td align="right">74</td> 
   <td align="right">3.4</td> 
   <td>User I/O</td> 
  </tr> 
  <tr> 
   <td>gc buffer busy acquire</td> 
   <td align="right">3,103,568</td> 
   <td align="right">58.2K</td> 
   <td align="right">19</td> 
   <td align="right">3.2</td> 
   <td>Cluster</td> 
  </tr> 
  <tr> 
   <td>db file scattered read</td> 
   <td align="right">1,721,381</td> 
   <td align="right">55.1K</td> 
   <td align="right">32</td> 
   <td align="right">3.0</td> 
   <td>User I/O</td> 
  </tr> 
  <tr> 
   <td>db file parallel read</td> 
   <td align="right">859,955</td> 
   <td align="right">33.5K</td> 
   <td align="right">39</td> 
   <td align="right">1.8</td> 
   <td>User I/O</td> 
  </tr> 
 </tbody> 
</table>

**表XX\_PURCHASE\_CONTRACT\_ITEM\_MV物理读非常高，284623132\*8/1024/1024=2171.5G的数据，在磁盘上读了这么多数据，当然，这个数字需要\*2, 因为是RAC的数据库。**

### Segments by Physical Reads ###

*  Total Physical Reads: 620,000,121
 *  Captured Segments account for 52.3% of Total

<table> 
 <tbody> 
  <tr> 
   <th>Owner</th> 
   <th>Tablespace Name</th> 
   <th>Object Name</th> 
   <th>Subobject Name</th> 
   <th>Obj. Type</th> 
   <th>Physical Reads</th> 
   <th>%Total</th> 
  </tr> 
  <tr> 
   <td>XXXXX</td> 
   <td>Www</td> 
   <td>XX_PURCHASE_CONTRACT_ITEM_MV</td> 
   <td>&nbsp;</td> 
   <td>TABLE</td> 
   <td align="right">284,623,132</td> 
   <td align="right">45.91</td> 
  </tr> 
  <tr> 
   <td>XXXXX</td> 
   <td>Www</td> 
   <td>XX_PURCHASE_ITEM</td> 
   <td>P0308</td> 
   <td>TABLE PARTITION</td> 
   <td align="right">7,707,543</td> 
   <td align="right">1.24</td> 
  </tr> 
  <tr> 
   <td>XXXXX</td> 
   <td>Www</td> 
   <td>XX_PURCHASE_ITEM</td> 
   <td>P0314</td> 
   <td>TABLE PARTITION</td> 
   <td align="right">6,211,667</td> 
   <td align="right">1.00</td> 
  </tr> 
  <tr> 
   <td>XXXXX</td> 
   <td>Www</td> 
   <td>XX_PURCHASE_ITEM</td> 
   <td>P0313</td> 
   <td>TABLE PARTITION</td> 
   <td align="right">5,502,512</td> 
   <td align="right">0.89</td> 
  </tr> 
  <tr> 
   <td>XXXXX</td> 
   <td>Www</td> 
   <td>XX_PURCHASE_ITEM</td> 
   <td>P0318</td> 
   <td>TABLE PARTITION</td> 
   <td align="right">3,820,804</td> 
   <td align="right">0.62</td> 
  </tr> 
 </tbody> 
</table>