Oracle11g RAC下真的要关闭DRM

骑猪看日落 2022-06-18 07:26 661阅读 0赞

以前从来没有遇到过如此的问题，特别是gcs drm freeze in enter server mode等待事件，是DRM特性。在影响用户的角度分析，执行频繁的insert语句很慢，我想原因与两个，一是插入的时候需要读取待插入数据块，二是需要读取待插入的索引块，还有索引的分裂。数据块在节点之间来回传输导致等待。

Top 10 Foreground Events by Total Wait Time

<table> 
 <tbody> 
 <tr> 
 <th style="font-size:8pt; color:white; padding-left:4px; padding-right:4px; padding-bottom:2px; background:rgb(0,102,204)"> Event</th> 
 <th style="font-size:8pt; color:white; padding-left:4px; padding-right:4px; padding-bottom:2px; background:rgb(0,102,204)"> Waits</th> 
 <th style="font-size:8pt; color:white; padding-left:4px; padding-right:4px; padding-bottom:2px; background:rgb(0,102,204)"> Total Wait Time (sec)</th> 
 <th style="font-size:8pt; color:white; padding-left:4px; padding-right:4px; padding-bottom:2px; background:rgb(0,102,204)"> Wait Avg(ms)</th> 
 <th style="font-size:8pt; color:white; padding-left:4px; padding-right:4px; padding-bottom:2px; background:rgb(0,102,204)"> % DB time</th> 
 <th style="font-size:8pt; color:white; padding-left:4px; padding-right:4px; padding-bottom:2px; background:rgb(0,102,204)"> Wait Class</th> 
 </tr> 
 <tr> 
 <td style="font-size:8pt; vertical-align:top; background:rgb(255,255,204)"> gc buffer busy acquire</td> 
 <td align="right" style="font-size:8pt; vertical-align:top; background:rgb(255,255,204)"> 266,461</td> 
 <td align="right" style="font-size:8pt; vertical-align:top; background:rgb(255,255,204)"> 225.8K</td> 
 <td align="right" style="font-size:8pt; vertical-align:top; background:rgb(255,255,204)"> 847</td> 
 <td align="right" style="font-size:8pt; vertical-align:top; background:rgb(255,255,204)"> 33.1</td> 
 <td style="font-size:8pt; vertical-align:top; background:rgb(255,255,204)"> Cluster</td> 
 </tr> 
 <tr> 
 <td style="font-size:8pt; vertical-align:top">DB CPU</td> 
 <td align="right" style="font-size:8pt; vertical-align:top"> </td> 
 <td align="right" style="font-size:8pt; vertical-align:top">71.6K</td> 
 <td align="right" style="font-size:8pt; vertical-align:top"> </td> 
 <td align="right" style="font-size:8pt; vertical-align:top">10.5</td> 
 <td style="font-size:8pt; vertical-align:top"> </td> 
 </tr> 
 <tr> 
 <td style="font-size:8pt; vertical-align:top; background:rgb(255,255,204)"> gc cr block congested</td> 
 <td align="right" style="font-size:8pt; vertical-align:top; background:rgb(255,255,204)"> 30,065</td> 
 <td align="right" style="font-size:8pt; vertical-align:top; background:rgb(255,255,204)"> 57K</td> 
 <td align="right" style="font-size:8pt; vertical-align:top; background:rgb(255,255,204)"> 1897</td> 
 <td align="right" style="font-size:8pt; vertical-align:top; background:rgb(255,255,204)"> 8.4</td> 
 <td style="font-size:8pt; vertical-align:top; background:rgb(255,255,204)"> Cluster</td> 
 </tr> 
 <tr> 
 <td style="font-size:8pt; vertical-align:top">gcs drm freeze in enter server mode</td> 
 <td align="right" style="font-size:8pt; vertical-align:top">101,845</td> 
 <td align="right" style="font-size:8pt; vertical-align:top">50.3K</td> 
 <td align="right" style="font-size:8pt; vertical-align:top">493</td> 
 <td align="right" style="font-size:8pt; vertical-align:top">7.4</td> 
 <td style="font-size:8pt; vertical-align:top">Other</td> 
 </tr> 
 <tr> 
 <td style="font-size:8pt; vertical-align:top; background:rgb(255,255,204)"> enq: TX - row lock contention</td> 
 <td align="right" style="font-size:8pt; vertical-align:top; background:rgb(255,255,204)"> 9,538</td> 
 <td align="right" style="font-size:8pt; vertical-align:top; background:rgb(255,255,204)"> 49.4K</td> 
 <td align="right" style="font-size:8pt; vertical-align:top; background:rgb(255,255,204)"> 5179</td> 
 <td align="right" style="font-size:8pt; vertical-align:top; background:rgb(255,255,204)"> 7.2</td> 
 <td style="font-size:8pt; vertical-align:top; background:rgb(255,255,204)"> Application</td> 
 </tr> 
 <tr> 
 <td style="font-size:8pt; vertical-align:top">gc current block congested</td> 
 <td align="right" style="font-size:8pt; vertical-align:top">40,354</td> 
 <td align="right" style="font-size:8pt; vertical-align:top">43.9K</td> 
 <td align="right" style="font-size:8pt; vertical-align:top">1087</td> 
 <td align="right" style="font-size:8pt; vertical-align:top">6.4</td> 
 <td style="font-size:8pt; vertical-align:top">Cluster</td> 
 </tr> 
 <tr> 
 <td style="font-size:8pt; vertical-align:top; background:rgb(255,255,204)"> gc current grant busy</td> 
 <td align="right" style="font-size:8pt; vertical-align:top; background:rgb(255,255,204)"> 958,406</td> 
 <td align="right" style="font-size:8pt; vertical-align:top; background:rgb(255,255,204)"> 35.8K</td> 
 <td align="right" style="font-size:8pt; vertical-align:top; background:rgb(255,255,204)"> 37</td> 
 <td align="right" style="font-size:8pt; vertical-align:top; background:rgb(255,255,204)"> 5.3</td> 
 <td style="font-size:8pt; vertical-align:top; background:rgb(255,255,204)"> Cluster</td> 
 </tr> 
 <tr> 
 <td style="font-size:8pt; vertical-align:top">gc cr block 2-way</td> 
 <td align="right" style="font-size:8pt; vertical-align:top">531,320</td> 
 <td align="right" style="font-size:8pt; vertical-align:top">26.1K</td> 
 <td align="right" style="font-size:8pt; vertical-align:top">49</td> 
 <td align="right" style="font-size:8pt; vertical-align:top">3.8</td> 
 <td style="font-size:8pt; vertical-align:top">Cluster</td> 
 </tr> 
 <tr> 
 <td style="font-size:8pt; vertical-align:top; background:rgb(255,255,204)"> gc current block 2-way</td> 
 <td align="right" style="font-size:8pt; vertical-align:top; background:rgb(255,255,204)"> 864,948</td> 
 <td align="right" style="font-size:8pt; vertical-align:top; background:rgb(255,255,204)"> 12.4K</td> 
 <td align="right" style="font-size:8pt; vertical-align:top; background:rgb(255,255,204)"> 14</td> 
 <td align="right" style="font-size:8pt; vertical-align:top; background:rgb(255,255,204)"> 1.8</td> 
 <td style="font-size:8pt; vertical-align:top; background:rgb(255,255,204)"> Cluster</td> 
 </tr> 
 <tr> 
 <td style="font-size:8pt; vertical-align:top">gc buffer busy release</td> 
 <td align="right" style="font-size:8pt; vertical-align:top">24,042</td> 
 <td align="right" style="font-size:8pt; vertical-align:top">11.8K</td> 
 <td align="right" style="font-size:8pt; vertical-align:top">491</td> 
 <td align="right" style="font-size:8pt; vertical-align:top">1.7</td> 
 <td style="font-size:8pt; vertical-align:top">Cluster </td> 
 </tr> 
 </tbody> 
</table>

由于需要等几天才能重启数据库，所以临时的处理方案是将weblogic指向一个实例，从效果上来看，非常不错。

**在10g中，可以采用如下方式禁用DRM（当然你也可以只禁用其中的一个模块object affinity或者undo affinity）**  
\--disable object affinity  
alter system set "\_gc\_affinity\_time"=0 scope=spfile ;  
\--disable undo affinity  
alter system set "\_gc\_undo\_affinity"=FALSE  scope=spfile;  
然后同时重启所有实例生效。  
**如果暂时无法重启实例，可以使用如下命令“事实上”禁用DRM：（以下两个参数可以动态调整）**  
alter system set “\_gc\_affinity\_limit”=10000000 sid='\*';  
alter system set “\_gc\_affinity\_minimum”=10000000 sid='\*';  
**在11g中，同样可以使用如下方式禁用DRM，强烈建议关闭：**  
alter system set "\_gc\_policy\_time"=0 scope=spfile;  
然后同时重启所有实例生效。如果不想完全禁用DRM，但是需要禁用read-mostly locking或者reader bypass的机制。可以使用如下命令：  
\--disable read-mostly locking  
alter system set "\_gc\_read\_mostly\_locking"=false scope=spfile sid='\*';  
\--disable reader-bypass  
alter system set "\_gc\_bypass\_readers"=false scope=spfile sid='\*';

**Oracle给出的Oracle11g下的调整：**

alter system set "\_gc\_policy\_time"=0 scope=spfile sid='\*';

alter system set "\_gc\_undo\_affinity"=false scope=spfile sid='\*';

**关闭DRM之后的数据库报告的情况：**

Top 10 Foreground Events by Total Wait Time

<table> 
 <tbody> 
 <tr> 
 <th style="font-size:8pt; color:white; padding-left:4px; padding-right:4px; padding-bottom:2px; background:rgb(0,102,204)"> Event</th> 
 <th style="font-size:8pt; color:white; padding-left:4px; padding-right:4px; padding-bottom:2px; background:rgb(0,102,204)"> Waits</th> 
 <th style="font-size:8pt; color:white; padding-left:4px; padding-right:4px; padding-bottom:2px; background:rgb(0,102,204)"> Total Wait Time (sec)</th> 
 <th style="font-size:8pt; color:white; padding-left:4px; padding-right:4px; padding-bottom:2px; background:rgb(0,102,204)"> Wait Avg(ms)</th> 
 <th style="font-size:8pt; color:white; padding-left:4px; padding-right:4px; padding-bottom:2px; background:rgb(0,102,204)"> % DB time</th> 
 <th style="font-size:8pt; color:white; padding-left:4px; padding-right:4px; padding-bottom:2px; background:rgb(0,102,204)"> Wait Class</th> 
 </tr> 
 <tr> 
 <td style="font-size:8pt; vertical-align:top; background:rgb(255,255,204)"> DB CPU</td> 
 <td align="right" style="font-size:8pt; vertical-align:top; background:rgb(255,255,204)"> </td> 
 <td align="right" style="font-size:8pt; vertical-align:top; background:rgb(255,255,204)"> 21.1K</td> 
 <td align="right" style="font-size:8pt; vertical-align:top; background:rgb(255,255,204)"> </td> 
 <td align="right" style="font-size:8pt; vertical-align:top; background:rgb(255,255,204)"> 79.0</td> 
 <td style="font-size:8pt; vertical-align:top; background:rgb(255,255,204)"> </td> 
 </tr> 
 <tr> 
 <td style="font-size:8pt; vertical-align:top">db file sequential read</td> 
 <td align="right" style="font-size:8pt; vertical-align:top">201,429</td> 
 <td align="right" style="font-size:8pt; vertical-align:top">1541.2</td> 
 <td align="right" style="font-size:8pt; vertical-align:top">8</td> 
 <td align="right" style="font-size:8pt; vertical-align:top">5.8</td> 
 <td style="font-size:8pt; vertical-align:top">User I/O</td> 
 </tr> 
 <tr> 
 <td style="font-size:8pt; vertical-align:top; background:rgb(255,255,204)"> db file scattered read</td> 
 <td align="right" style="font-size:8pt; vertical-align:top; background:rgb(255,255,204)"> 121,867</td> 
 <td align="right" style="font-size:8pt; vertical-align:top; background:rgb(255,255,204)"> 1017.8</td> 
 <td align="right" style="font-size:8pt; vertical-align:top; background:rgb(255,255,204)"> 8</td> 
 <td align="right" style="font-size:8pt; vertical-align:top; background:rgb(255,255,204)"> 3.8</td> 
 <td style="font-size:8pt; vertical-align:top; background:rgb(255,255,204)"> User I/O</td> 
 </tr> 
 <tr> 
 <td style="font-size:8pt; vertical-align:top">direct path read temp</td> 
 <td align="right" style="font-size:8pt; vertical-align:top">175,993</td> 
 <td align="right" style="font-size:8pt; vertical-align:top">494.2</td> 
 <td align="right" style="font-size:8pt; vertical-align:top">3</td> 
 <td align="right" style="font-size:8pt; vertical-align:top">1.9</td> 
 <td style="font-size:8pt; vertical-align:top">User I/O</td> 
 </tr> 
 <tr> 
 <td style="font-size:8pt; vertical-align:top; background:rgb(255,255,204)"> SQL*Net message from dblink</td> 
 <td align="right" style="font-size:8pt; vertical-align:top; background:rgb(255,255,204)"> 69,387</td> 
 <td align="right" style="font-size:8pt; vertical-align:top; background:rgb(255,255,204)"> 458.1</td> 
 <td align="right" style="font-size:8pt; vertical-align:top; background:rgb(255,255,204)"> 7</td> 
 <td align="right" style="font-size:8pt; vertical-align:top; background:rgb(255,255,204)"> 1.7</td> 
 <td style="font-size:8pt; vertical-align:top; background:rgb(255,255,204)"> Network</td> 
 </tr> 
 <tr> 
 <td style="font-size:8pt; vertical-align:top">enq: TX - row lock contention</td> 
 <td align="right" style="font-size:8pt; vertical-align:top">108</td> 
 <td align="right" style="font-size:8pt; vertical-align:top">389.1</td> 
 <td align="right" style="font-size:8pt; vertical-align:top">3602</td> 
 <td align="right" style="font-size:8pt; vertical-align:top">1.5</td> 
 <td style="font-size:8pt; vertical-align:top">Application</td> 
 </tr> 
 <tr> 
 <td style="font-size:8pt; vertical-align:top; background:rgb(255,255,204)"> log file sync</td> 
 <td align="right" style="font-size:8pt; vertical-align:top; background:rgb(255,255,204)"> 112,847</td> 
 <td align="right" style="font-size:8pt; vertical-align:top; background:rgb(255,255,204)"> 356.2</td> 
 <td align="right" style="font-size:8pt; vertical-align:top; background:rgb(255,255,204)"> 3</td> 
 <td align="right" style="font-size:8pt; vertical-align:top; background:rgb(255,255,204)"> 1.3</td> 
 <td style="font-size:8pt; vertical-align:top; background:rgb(255,255,204)"> Commit</td> 
 </tr> 
 <tr> 
 <td style="font-size:8pt; vertical-align:top">read by other session</td> 
 <td align="right" style="font-size:8pt; vertical-align:top">40,569</td> 
 <td align="right" style="font-size:8pt; vertical-align:top">222.2</td> 
 <td align="right" style="font-size:8pt; vertical-align:top">5</td> 
 <td align="right" style="font-size:8pt; vertical-align:top">.8</td> 
 <td style="font-size:8pt; vertical-align:top">User I/O</td> 
 </tr> 
 <tr> 
 <td style="font-size:8pt; vertical-align:top; background:rgb(255,255,204)"> db file parallel read</td> 
 <td align="right" style="font-size:8pt; vertical-align:top; background:rgb(255,255,204)"> 11,173</td> 
 <td align="right" style="font-size:8pt; vertical-align:top; background:rgb(255,255,204)"> 157.7</td> 
 <td align="right" style="font-size:8pt; vertical-align:top; background:rgb(255,255,204)"> 14</td> 
 <td align="right" style="font-size:8pt; vertical-align:top; background:rgb(255,255,204)"> .6</td> 
 <td style="font-size:8pt; vertical-align:top; background:rgb(255,255,204)"> User I/O</td> 
 </tr> 
 <tr> 
 <td style="font-size:8pt; vertical-align:top">gc current block busy</td> 
 <td align="right" style="font-size:8pt; vertical-align:top">25,659</td> 
 <td align="right" style="font-size:8pt; vertical-align:top">155</td> 
 <td align="right" style="font-size:8pt; vertical-align:top">6</td> 
 <td align="right" style="font-size:8pt; vertical-align:top">.6</td> 
 <td style="font-size:8pt; vertical-align:top">Cluster </td> 
 </tr> 
 </tbody> 
</table>

本次出问题的数据库SGA设置为1T，所以安装的时候需要初始化一些特殊的参数。

**Best Practices and Recommendations for RAC databases with SGA size over 100GB (文档 ID 1619155.1)**

## APPLIES TO: ##

Oracle Database - Enterprise Edition - Version 11.2.0.3 and later  
Information in this document applies to any platform.

## PURPOSE ##

The goal of this note is to provide best practices and recommendations to users of Oracle Real Application Clusters (RAC) databases using very large SGA (e.g. 100GB) per instance (note that RAC assumes homogeneously sized SGAs across the cluster). This document is compiled and maintained based on Oracle's experience with its global RAC customer base.  
  
This is not meant to replace or supplant the Oracle Documentation set, but rather, it is meant as a supplement to the same. It is imperative that the Oracle Documentation be read, understood, and referenced to provide answers to any questions that may not be clearly addressed by this note.  
  
All recommendations should be carefully reviewed by your own operations group and should only be implemented if the potential gain as measured against the associated risk warrants implementation. Risk assessments can only be made with a detailed knowledge of the system, application, and business environment.

As every customer environment is unique, the success of any Oracle Database implementation, including implementations of Oracle RAC, is predicated on a successful test environment. Oracle Support has identified 100 GB as a baseline for large SGA's that would benefit from the recommendations provided in this note. However, this is just a baseline, and it is possible for similar(but smaller) SGA's to benefit from these recommendations. It is thus imperative that any recommendations from this note are thoroughly tested and validated using a testing environment that is a replica of the target production environment before being implemented in the production environment to ensure that there is no negative impact associated with the recommendations that are made

## SCOPE ##

This article applies to all new and existing RAC implementations.

**This is for RAC databases only as most of the parameters listed in here are for RAC Database only.**

## DETAILS ##

Note that the recommendations presented in this note are a result of the experience from working on databases with SGA of 1 TB and 2.6 TB.

Also, the databases with SGA of 100GB and 300GB also benefited from the recommendations

**init.ora parameters:**

a.      ** Set \_lm\_sync\_timeout to 1200**   
           Setting this will prevent some timeouts during reconfiguration and DRM. It's a static parameter and rolling restart is supported.  
  
  
b.       **Set \_ksmg\_granule\_size to 134217728**  
           Setting this will cut down the time needed to locate the resource for a data block. It's a static parameter and rolling restart is supported.  
  
c.       **Set shared\_pool\_size to 15% or larger of the total SGA size.**  
        For example, if SGA size is 1 TB, the shared pool size should be at least 150 GB. It's a dynamic parameter.  
  
d.       **Set \_gc\_policy\_minimum to 15000**  
        There is no need to set \_gc\_policy\_minimum if DRM is disabled by setting \_gc\_policy\_time = 0. \_gc\_policy\_minimum is a dynamic parameter, \_gc\_policy\_time is a static parameter and rolling restart is not supported. To disable DRM, instead of \_gc\_policy\_time, \_lm\_drm\_disable should be used as it's dynamic.  
  
e.      ** Set \_lm\_tickets to 5000**  
        Default is 1000.   Allocating more tickets (used for sending messages) avoids issues where we ran out of tickets during the reconfiguration. It's a static parameter and rolling restart is supported. When increasing the parameter, rolling restart is fine but a cold restart can be necessary when decreasing.  
  
f.       **Set gcs\_server\_processes to the twice the default number of lms processes that are allocated.**  
        The default number of lms processes depends on the number of CPUs/cores that the server has,   
        so please refer to the gcs\_server\_processes init.ora parameter section in the Oracle Database Reference Guide   
        for the default number of lms processes for your server.  Please make sure that the total number of lms processes   
        of all databases on the server is less than the total number of CPUs/cores on the server.  Please refer to the  [Document 558185.1][]   
        It's a static parameter and rolling restart is supported.

[Document 558185.1]: https://support.oracle.com/epmos/faces/DocumentDisplay?parent=DOCUMENT&sourceId=1619155.1&id=558185.1