删除私有同义词期间AWR报告显示的SQL性能问题

由于用户比较多，私有同义词也比较多（66万条），删除操作非常缓慢，经过分析查看AWR报告，发现以下SQL耗时较多
Begin
  Begin
    If (Xdb.Dbms_Xdbz0.Is_Hierarchy_Enabled_Internal(Sys.Dictionary_Obj_Owner, Sys.Dictionary_Obj_Name,
                                                     Sys.Dictionary_Obj_Owner)) Then
      Xdb.Xdb_Pitrig_Pkg.Pitrig_Truncate(Sys.Dictionary_Obj_Owner, Sys.Dictionary_Obj_Name);
    End If;
  Exception
    When Others Then
      Null;
  End;
  Begin
    If (Xdb.Dbms_Xdbz0.Is_Hierarchy_Enabled_Internal(Sys.Dictionary_Obj_Owner, Sys.Dictionary_Obj_Name,
                                                     Sys.Dictionary_Obj_Owner, Xdb.Dbms_Xdbz.Is_Enabled_Resmetadata)) Then
      Xdb.Xdb_Pitrig_Pkg.Pitrig_Dropmetadata(Sys.Dictionary_Obj_Owner, Sys.Dictionary_Obj_Name);
    End If;
  Exception
    When Others Then
      Null;
  End;
End;

该SQL在一小时内执行了6万5千次，还有一个相关的SQL执行了13万次
Select /*+ ALL_ROWS */
 Count(*)
From All_Policies V
Where v.Object_Owner = :B3 And v.Object_Name = :B2 And (Policy_Name Like '%xdbrls%' Or Policy_Name Like '%$xd_%') And
      v.Function = :B1

查到Oracle的以下文章
SQL IN XDB.DBMS_XDBZ0 LINE 637 IS MAJOR CONSUMER OF CPU (文档 ID 972671.1)
原因是：
The cause of this problem has been identified and verified in an unpublished Bug 4766344. It is caused by XDB's use of Sys.DBMS_Sys_SQL.
The code for this trigger kicks in only for VPD enabled tables and can be avoided by selecting directly from the All_Policies view.

打补丁Patch 4766344. 可解决
影响的版本有：10.2.0.1 to 10.2.0.4 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/117319/viewspace-1839900/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/117319/viewspace-1839900/