oracle 11.2.0.4暴重大bug!!-->sysauth$ 存在登录查询的bug:High CPU/IO for dictionary SQL against SYSAUTH$

今天在查看awr的自动诊断建议的时候，无意中发现：sysauth$占比消耗资源挺大的。即使是11.2.0.3和11.2.0.4也存在该bug，希望尽快下载修复！！

具体见如下:

   

SQL 优化

   估计的收益为 .03 个活动会话, 占总活动的6.84\%。

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

  操作

      对SELECT 语句 (SQL_ID 为"cm5vu20fhtnq1") 运行 SQL 优化指导。

      相关对象

         SQL_ID 为 cm5vu20fhtnq1 的 SQL 语句。

         select /*+ connect_by_filtering */privilege#,level from sysauth$

         connect by grantee#=prior privilege#and privilege#>0 start with

         grantee#=:1 and privilege#>0

  操作

      从SQL_ID 为 "cm5vu20fhtnq1" 的 SELECT 语句提取结果时使用更大的提取数组。

      相关对象

         SQL_ID 为 cm5vu20fhtnq1 的 SQL 语句。

         select /*+ connect_by_filtering */privilege#,level from sysauth$

         connect by grantee#=prior privilege#and privilege#>0 start with

         grantee#=:1 and privilege#>0

  原理

      SQL 在CPU, I/O 和集群等待上花费的时间占其数据库时间的 88%。这部分数据库时间可通过 SQL 优化指导进行改善。

  原理

      此SQL 的数据库时间由以下部分构成: SQL 执行占 89%,语法分析占 11%, PL/SQL 执行占 0%, Java 执行占 0%。

  原理

      SQL_ID 为 "cm5vu20fhtnq1" 的 SQL 语句执行了 21785 次, 每次执行平均用时0.00084 秒。

 

 

感觉有点莫名其妙，这个很少会造成很大的资源消耗。

经查确实是一个bug

Bug 14283239 - High CPU/IO for dictionary SQL against SYSAUTH$ (文档 ID 14283239.8)

转到底部

---

 

 

 

具体见如下:

Bug 14283239  High CPU/IO for dictionary SQL against SYSAUTH$  This note gives a brief overview of bug 14283239.   The content was last updated on: 18-OCT-2016  Click here for details of each of the sections below. Affects: Product (Component) Oracle Server (Rdbms) Range of versions believed to be affected Versions BELOW 12.2 Versions confirmed as being affected 12.1.0.2 (Server Patch Set) 11.2.0.4 11.2.0.3 Platforms affected Generic (all / most platforms affected) Fixed: The fix for 14283239 is first included in 12.2 (Future Release) 12.1.0.2.160719 (Jul 2016) Database Patch Set Update (DB PSU) 12.1.0.2.160119 (Jan 2016) Bundle Patch for Engineered Systems / DB In-Memory (DBBP) 12.1.0.2.160719 (Jul 2016) Bundle Patch for Windows Platforms Interim patches may be available for earlier versions - click here to check. Symptoms: Related To: Excessive CPU Usage Performance Of Query/ies Affected Execution plan may include SYSAUTH$ Execution plan may include TABLE ACCESS FULL OPTION$ Description Performance issue for a specific dictionary SQL that queries SYSAUTH$ .   Rediscovery Notes  If high CPU / IO is observed to be due to sessions running dictionary SQL  like below then it is likely due to this bug:    select max(nvl(option$,0))      from sysauth$ where privilege#=99    connect by grantee#=prior privilege# and privilege#>0      start with (grantee#=25 or grantee#=1) and privilege#>0      group by privilege#;    In particular the execution plan is likely to show a FULL TABLE SCAN  of SYSAUTH$.      受影响范围：

你们新打补丁的用户，颤动吧。话不多说，测试一下然后小补丁修复一下。