诊断一次Oracle日志切换频繁的问题

今天药忘吃喽~ 2022-08-18 14:10 405阅读 0赞

日志切换，就是生成的日志太大，数据块的变化太频繁。

<table> 
 <tbody> 
 <tr> 
 <th> </th> 
 <th>Snap Id</th> 
 <th>Snap Time</th> 
 <th>Sessions</th> 
 <th>Cursors/Session</th> 
 </tr> 
 <tr> 
 <td>Begin Snap:</td> 
 <td align="right">14560</td> 
 <td align="middle">09-Dec-15 04:00:48</td> 
 <td align="right">59</td> 
 <td align="right">4.5</td> 
 </tr> 
 <tr> 
 <td>End Snap:</td> 
 <td align="right">14561</td> 
 <td align="middle">09-Dec-15 05:00:59</td> 
 <td align="right">54</td> 
 <td align="right">4.6</td> 
 </tr> 
 <tr> 
 <td>Elapsed:</td> 
 <td> </td> 
 <td align="middle">60.19 (mins)</td> 
 <td> </td> 
 <td> </td> 
 </tr> 
 <tr> 
 <td>DB Time:</td> 
 <td> </td> 
 <td align="middle">82.47 (mins)</td> 
 <td> </td> 
 <td> </td> 
 </tr> 
 </tbody> 
</table>

**  1s产生2M的日志。**

<table style="font-size:14px"> 
 <tbody> 
 <tr> 
 <th>&nbsp;</th> 
 <th>Per Second</th> 
 <th>Per Transaction</th> 
 <th>Per Exec</th> 
 <th>Per Call</th> 
 </tr> 
 <tr> 
 <td>DB Time(s):</td> 
 <td align="right">1.4</td> 
 <td align="right">3.3</td> 
 <td align="right">0.02</td> 
 <td align="right">0.47</td> 
 </tr> 
 <tr> 
 <td>DB CPU(s):</td> 
 <td align="right">1.1</td> 
 <td align="right">2.5</td> 
 <td align="right">0.01</td> 
 <td align="right">0.36</td> 
 </tr> 
 <tr> 
 <td>Redo size (bytes):</td> 
 <td align="right">2,315,310.3</td> 
 <td align="right">5,548,471.3</td> 
 <td>&nbsp;</td> 
 <td>&nbsp;</td> 
 </tr> 
 </tbody> 
</table>

**一个小时日志切换31次。**

<table style="font-size:14px"> 
 <tbody> 
 <tr> 
 <th>Statistic</th> 
 <th>Total</th> 
 <th>per Hour</th> 
 </tr> 
 <tr> 
 <td>log switches (derived)</td> 
 <td align="right">31</td> 
 <td align="right">30.90</td> 
 </tr> 
 </tbody> 
</table>

**重点来了，看segments by DB Blocks changes,看是什么对象造成的。**

### Segments by DB Blocks Changes ###

*  % of Capture shows % of DB Block Changes for each top segment compared
 *  with total DB Block Changes for all segments captured by the Snapshot

<table style="font-size:14px"> 
 <tbody> 
 <tr> 
 <th>Owner</th> 
 <th>Tablespace Name</th> 
 <th>Object Name</th> 
 <th>Subobject Name</th> 
 <th>Obj. Type</th> 
 <th>DB Block Changes</th> 
 <th>% of Capture</th> 
 </tr> 
 <tr> 
 <td>TC</td> 
 <td>TBS_TCI</td> 
 <td>VIEW_TICKET</td> 
 <td> </td> 
 <td>TABLE</td> 
 <td align="right">7,758,832</td> 
 <td align="right">36.44</td> 
 </tr> 
 <tr> 
 <td>TC</td> 
 <td>TBS_TCI</td> 
 <td>V_DATA_RANGE</td> 
 <td> </td> 
 <td>TABLE</td> 
 <td align="right">7,076,112</td> 
 <td align="right">33.23</td> 
 </tr> 
 <tr> 
 <td>TC</td> 
 <td>TBS_TCI</td> 
 <td>MV_TCM_WORKFORM</td> 
 <td> </td> 
 <td>TABLE</td> 
 <td align="right">3,593,664</td> 
 <td align="right">16.88</td> 
 </tr> 
 <tr> 
 <td>TBM</td> 
 <td>TBS_TBM</td> 
 <td>VIEW_DATAAUTH_ROLE_USER</td> 
 <td> </td> 
 <td>TABLE</td> 
 <td align="right">2,368,016</td> 
 <td align="right">11.12</td> 
 </tr> 
 <tr> 
 <td>TC</td> 
 <td>TBS_TC</td> 
 <td>RPT_ACCRUALS_DETAIL</td> 
 <td> </td> 
 <td>TABLE</td> 
 <td align="right">87,648</td> 
 <td align="right">0.41</td> 
 </tr> 
 </tbody> 
</table>

**通过这些对象找到相应的SQL语句，定位就是它们。对这种业务，有两种方法，1. delete改为truncate   2.加大redo log file的大小**  
36p9tcbryxnq4 delete from "VIEW\_TICKET"   
213rsbk7216pc delete from "V\_DATA\_RANGE"   
0mv6h8360myzu delete from "MV\_TCM\_WORKFORM"   
2u4qz8gfqs1z7 INSERT /\*+ BYPASS\_RECURSIVE\_CHECK \*/ INTO "MV\_TCM\_WORKFORM" select t.\*, sysdate as s from v\_tcm\_workform t