小心，有bug：分区表收集统计信息导致library cache堵塞

编辑手记：在数据库中，有一些bug,可能并不会造成严重的故障，却在细节上影响系统的性能，而你可能并不知情。

情景描述

在某系统中，对分区表TBLONWIP进行统计信息收集时，如果使用FOR COLUMN的选项, 就有可能导致另一个节点上的大量 Library cache lock/library cache loadlock/library cache: mutex X等等待事件。

相关的统计信息搜集SQL为

DBMS_STATS.GATHER_TABLE_STATS
(OWNNAME => 'MES',TABNAME => 
'TBLONWIP',ESTIMATE_PERCENT => 
DBMS_STATS.AUTO_SAMPLE_SIZE,CASCADE =>
TRUE,METHOD_OPT => 
'FOR ALL COLUMNS SIZE REPEAT',degree =>10);

模拟测试

由于此故障可以重现。因此，在2017/1/1客户停机时间段内，进行了多次模拟测试。

• 节点2开启两个SESSION, 持续运行SELECT SQL.(select .. from MES.TBLONWIP where …)

• 节点1 运行统计信息收集命令DBMS_STATS.GATHER_TABLE_STATS.

• 收集相关进程的processstate dump. SQL>oradebug dump processstate level 10;

• 收集相关时段AWR/ASH及等待信息

模拟发现，当在节点1运行统计信息收集命令时，节点2上访问该表的SQL大量等待Librarycache lock/library cache load lock/ library cache: mutex X。反之亦然。

DUMP文件分析

根据等待事件的P1(0x6fbf5e0128)/P3(0x1917a00010002)及相应的dump文件,可以看到，相关SQL尝试以行级共享锁(S,2)获取MES.TBLONWIP 锁，同时，发现了比较怪异的情况，其中有个属性VERSION=527

节点2的process state dump如下：

LibraryObjectPin: Address=0x6f7ab4a910 Handle=0x6fbf5e0128 Mode=S Lock=0x6f7ab4aa10 ClusterLock=0x6eaeee0508 User=0x70473bf488 Session=0x70473bf488 Count=0 Mask=0341 Flags=[00] SavepointNum=0x1c1d325 

LibraryHandle:  Address=0x6fbf5e0128 Hash=92bbb928 LockMode=S PinMode=S LoadLockMode=X Status=VALD 

ObjectName:  Name=MES.TBLONWIP   

FullHashValue=3a48285e416414c371f8008e92bbb928 Namespace=TABLE/PROCEDURE(01) Type=TABLE(02) Identifier=102778 OwnerIdn=84

Counters:  BrokenCount=548 RevocablePointer=1 KeepDependency=0 Version=527 BucketInUse=35361 HandleInUse=35361 HandleReferenceCount=0

节点1的process state dump, 进行gather的时候，会申请独占锁(X,3).发现会有不同VERSION

LibraryObjectLock:  Address=0x7dc3c7c200 Handle=0x7d9b68ad28 RequestMode=X CanBeBrokenCount=2805 Incarnation=1 ExecutionCount=0             

ClusterLock=0x7c8c4b67f0 User=0x7cc62d61a8 Session=0x7cc62d61a8 ReferenceCount=0             

Flags=[0000] SavepointNum=d078 

LibraryHandle:  Address=0x7d9b68ad28 Hash=92bbb928 LockMode=0 PinMode=0 LoadLockMode=0 Status=VALD 

ObjectName:  Name=MES.TBLONWIP   

FullHashValue=3a48285e416414c371f8008e92bbb928 Namespace=TABLE/PROCEDURE(01) Type=TABLE(02) Identifier=102778 OwnerIdn=84 

Statistics:  InvalidationCount=0 ExecutionCount=0 LoadCount=86 ActiveLocks=0 TotalLockCount=232031217 TotalPinCount=1939302 

Counters:  BrokenCount=2805 RevocablePointer=1 KeepDependency=0 Version=148 BucketInUse=11548 HandleInUse=11548 HandleReferenceCount=0 

Concurrency:  DependencyMutex=0x7d9b68add8(0, 197030, 30, 0) Mutex=0x7d9b68ae68(0, 475120620, 3780802, 0)

检查 v$db_object_cache 里MES.TBLONWIP的相关信息，发现大量的NAMESPACE为“MULTI-VERSION OBJECT FOR TABLE”、NAME=TBLONWIP的对象。

MULTI-VERSION OBJECT是11.2.0.4 里面访问分区表的一个特性。特性名字叫 partition accessversion by number. 是为了以数字的形式访问library cache里的分区表对象的一种方式。

SQL>select count(*),inst_id,namespace

  from gv$db_object_cache where name = 'TBLONWIP'

group by inst_id,namespace

/

 

COUNT(*)  INST_ID NAMESPACE

------ ---------- ---------------------------------

   922          2 MULTI-VERSION OBJECT FOR TABLE

   765          1 MULTI-VERSION OBJECT FOR TABLE

     1          1 TABLE/PROCEDURE

     1          2 TABLE/PROCEDURE

里面的LOCKED_TOTAL异常高。

寻找对应BUG

从Oracle官方网站中寻找关于“partition access versionby number”的相关bug, 发现有一个bug和我们发生的现象极为吻合。其版本为11.2.0.4。

在官网中有个例子是这样的：

可以看到无论是版本，环境，还是现象都极其吻合。

解决方案

该bug 在11.2.0.4.0上没有现成patch, 只有基于11.2.0.4.6，11.2.0.4.160119, 11.2.0.4.160419上的patch. 而未经测试，不适合直接上补丁集。因此，建议通过关闭“partitionaccess version by number”的特性来解决问题。

Alter system set "_part_access_version_by_number"=false scope=spfile; 

并同时关闭两节点后重启。

经测试和后续观察，问题得以解决。