Oracle 一次疑似Bug 14283239 - High CPU/IO for dictionary SQL against SY...

巡检数据库时发现某实例存在过度的latch争用，怀疑有大量的硬解析；进一步查看有很多的系统认证语句，导致数据库share pool 没有发挥应有的作用。

这台数据库主要承担门店业务数据上传后的汇总，在进行进一步的业务分析操作。同步程序部署在每家门店，每隔5分钟就会有以一次上传操作，其中一项是收银流程，从刷卡读信息到计算费用，判断余额，扣减，写流水等一些列操作在一起的，很可能是很多小的查询组合在一个事务中。

分析AWR报告：

大量的硬解析，且每个事务平均执行378条SQL

latch：shared pool等待事件很严重

SQL分析，有大量的数据库认证SQL

select /*+ connect_by_filtering index(sysauth$ i_sysauth1) */ privilege#, bitand(nvl(option$, 0), 8), grantee#, level from sysauth$ connect by grantee#=prior privilege# and privilege#>0 start with grantee#=:1 and privilege#>0

初拿到这些SQL，就怀疑是不是数据库有什么BUG，查询MOS后，还真找到了Bug 14283239 - High CPU/IO for dictionary SQL against SYSAUTH$，于是对确定可维护时间后，打了BUG；但等到第二天业务上来后，问题重现了，并且确认前一晚操作没有问题，那会是什么问题呢？

首先看下发起这类SQL的会话信息，可以确认都是门店同步程序发起的会话，那么怀疑就是同步程序中对数据库的操作触发了对用户权限的递归查询，很有可能是有类似 set role的动作。

select count(*) c,a.MACHINE,PROGRAM  from DBA_HIST_ACTIVE_SESS_HISTORY a join dba_users b on a.user_id = b.user_id where sql_id= '7umy6juhzw766' group by a.MACHINE,PROGRAM

但程序都是编译好的，没有源码，不可能知道同步程序的详细信息，那么在数据库层面可不可以进行优化呢？

大量的硬解析就是发生在这几条执行次数较多的SQL，SQL类似，value可能不一样；为避免相似SQL不能共享执行计划，可以设置参数

alter system set cursor_sharing = force;

在线修改之后，查看数据库监控，硬解析数量明显下降

再次查看AWR报告，发现SYSAUTH相关SQL已经被正常业务SQL取代

并持续对暴漏出来的业务SQL进行分析，优化，CPU使用率下降明显


由于不能搞清楚同步程序到底做了什么，还不能彻底解决问题；
cursor_sharing默认是EXACT模式，即完全相同的SQL才会被认为是同一条SQL，修改前后请观察数据库性能的变化。