实战课堂:系统CPU高消耗的SQL筛选和最佳索引优化

在一次客户系统性能优化项目中，经过第一阶段的优化之后，数据库的DB Time和物理读都明显降低，但是我们发现CPU并没有明显降低。

对此，我方针对占用CPU较高的SQL进行了分析，并继续寻找优化空间。

找出占用CPU高的CPU有很多办法，比如:

通过操作系统高CPU消耗的Oracle进程，通过其 PID 和数据库内部视图 v$process ，v$session 关联，找到相关SQL。 通过 AWR 的历史信息，获取TOP CPU消耗的SQL列表，再针对性的分析

从思路二出发，首先通过查询 DBA_HIST_SQLSTAT 字典表，获取 CPU 按照使用率的SQL列表：

这其中排在最前列的主要是2条SQL语句，通过 v$sql 可以找到其SQL文本：

那么接下来的问题，就是分析这两条SQL，找到提升其效率的办法。

两条SQL共占用CPU TIME 30%以上。这两条SQL基本一致，只是mod一个字段的值不同，一个筛选mod之后为1的数据，一个筛选mod之后为0的数据。经验证这两条SQL解决方法一致，以其中一条为例。

select * from GBORDDDRETURN_UP_SCAN t where mod(SEQ_ID, 2)=1 and status >0 and upload_state = 0 and work_type in('10', '11', '12', '13', '20') and rownum <= 200 order by done_date

该SQL的执行统计信息如下，单次执行需要接近 5s 时间，消耗逻辑读 125，887，而平均每次执行返回 0.01 行，也就是说绝大多数查询是不返回满足条件的结果的。而如果 1393 次执行，只返回 10 行记录，那么单次的逻辑读消耗就显得高的可怕。这也是高 CPU 消耗的原因。

考察一下执行计划，可以看到一个索引被使用到，很多时候我们认为走索引就问题不大，这个常规判断在这里显然不成立，这个效率不高的索引是高逻辑读的主要原因。

我们再来分析一下表的元数据，可以看到现有索引的效率不佳，过滤性极差：

那么我们继续分析一下查询中的其他条件，以期望尽快的筛选记录，减少逻辑读。

通过分析我们注意到，虽然status和upload_state字段单独的过滤性都很差，但是放在一起却是一个非常好的条件。这两个条件可以快速筛选：

在创建了新的索引之后，可以看到整个SQL的执行效率大大提升：

建立该索引之后，执行时间由4966 ms降低到10m秒以内。逻辑读由125887降低到10以下。系统的CPU消耗得以快速消减。

这个案例给我们的启示是：

有效的索引才是好的索引； 如果单行查询逻辑读过高，一定需要对SQL进行单独的审核和优化；

多看多知，这就是实战课堂。