一组统计SQL的优化的经典案例

一、现象
2010-3-31 网管项目组反映湖北全流程监控业务的SQL执行比较缓慢，无法满足频繁采样监控的时间要求，急需优化。
现场技术人员发来需要调整的问题SQL,具体见附件环节采数脚本.rar，其中EXCLE中需要进一步优化的语句见蓝色及浅蓝色处。
于是准备介入分析调查。（本案例主要讲述思想，所以这些大同小异的脚本就不贴上去了，文中举了一例SQL，可以参考）
二、分析
连上湖北环境后，查看所有标注需改进的SQL，居然有30多个，吓了一跳，看来工作量还不小。不过静下心来一看，发现这些SQL原来都有共同规律。
1、 几乎全部是COUNT(*)和SUM的统计查询
2、 涉及到的都是CRM和OSS的网管外系统的表，我们的SQL语句都是本地通过数据链连接到外系统中去的。
3、 绝大多数SQL是统计10分钟的数据（代码随处可见SysDate - b.exit_date) * 24 * 60 <= 10的写法说明了这点）

进一步分析这些SQL，发现写法集中在如下几张表中，并且记录数都在千万以上，其中ls65_crm2.cust_indent_tache_t记录过亿

1、 HBNEW97OSS3.oss_xml_hist ---记录数21965662条
2、hbnew97oss3.process_inst; ---记录数29357919条
3、HBNEW97OSS3.Bpm_Process_Inst ---记录数 24926324条
4、ls65_crm2.cust_indent_t; ---记录数 32931916条
5、ls65_crm2.cust_indent_tache_t ---记录数139427859条
从生活常识便可得知，这些数据的海洋中，最近10分钟的数据应该是占总数据量非常少的！实验如下
select count(*) from HBNEW97OSS3.oss_xml_hist
where (sysdate - acc_date) * 24 * 60 <= 10; --返回记录数134条
select count(*) from hbnew97oss3.process_inst
where sts_date>sysdate-10/24/60 ; --返回记录数203条
select count(*) from HBNEW97OSS3.Bpm_Process_Inst
where (sysdate - start_date) * 24 * 60 < 10; --返回记录数117条
select count(*) from ls65_crm2.cust_indent_tache_t b
where (sysdate - b.exit_date) * 24 * 60 < 10 ; --返回记录数1518条
由此可以得知，根据这个exit_date的条件将返回很少的一部分记录，占全库很小的比例，属于选择性很高的列，非常适合建立索引。其他列都不能与之比拟，比如
select count(*) from hbnew97oss3.process_inst
where sub_type = 'SBPMRA'返回了500多万行，而按10分钟的条件才返回前面的203行

经查询发现涉及到用到日期范围的字段如ls65_crm2.cust_indent_tache_t 表的exit_date 或HBNEW97OSS3.oss_xml_hist表的acc_date 都没有建索引！这一系列共性SQL监控语句中，放着如此高选择性的列未建索引，非常可惜，如果能有效的利用到该列的索引，应该性能脚本上能得

到十倍甚至百倍以上的提高！

当然，在本案例中特别要注意的一点是，即便建立了这些日期列的索引，由于写法有问题，最近也会用不到，比如
where (sysdate - b.exit_date) * 24 * 60 < 10 ; 这个写法其实根本就是在exit_date列建了索引，索引也根本不会被用到，因为把表的列进行四则运算后，ORACLE还是无法智能到还原公式，把谓词转化出来。有兴趣的人可以看我证明的小实验，具体见附件转化写法与索引.sql


三、解决方案
通过以上的分析我们知道，现在我们着手完成如下四步，即可调优成功，整体性能应该可以大幅度提升。
1、所有SQL中涉及到确定时间范围为最近10分钟的SQL语句的时间列，必须要索引
2、为了能有可能进行索引直接回答问题，建议上一步骤继续深入，把这些时间字段列标记为NOT NULL属性（我想这个时间字段列基本可以确认有这个属性吧，如果可以确认，请加上这个NOT NULL属性，对COUNT和SUM的聚合只有百利而无一害，因为增加了索引变身“瘦表”的可能）
3、凡是涉及到类似到where (sysdate - b.exit_date) * 24 * 60 < 10的写法，请修改为b.exit_date >sysdate-10/24/60 ，如果不这么改写，索引建了也没用！
4、连接两个远程表，比连接两个本地表要昂贵的多！所以建议先在远程环境做好连接后，在远程的机器上建立一个视图，我们本地再访问这个视图。(你可以想像要先把数据读过来，再连接)，这是以前在ASKTOM上看来的，TOM大师强调过的论点。

现在综合举一个例子如下：

比如如下SQL
select a.partition_id_region 本地网编码,
count(a.agreement_id) 十分钟内竣工环节处理量
from ls65_crm2.cust_indent_t@A2SVC2 a,
ls65_crm2.cust_indent_tache_t@A2SVC2 b
where a.indent_state = '100'
and a.agreement_id = b.agreement_id
and b.operate_tache = 6
and b.deal_flag = 1
and (sysdate - b.exit_date) * 24 * 60 < 10
group by a.partition_id_region
order by a.partition_id_region;

该如何调优呢
1、首先在CRM的机器上建索引

Create index idx_ exit_date on cust_indent_tache_t(exit_date);

2、由于大量的COUNT和SUM的聚合语句，建议为该列建NOT NULL属性，等于告诉ORACLE可以把索引当“瘦表”来用而不会出错！

alter table cust_indent_tache_t modify exit_date not null;

3、去掉数据链写法@A2SVC2,在远程环境直接建视图
Create or replace view v_cust_ident as
select a.partition_id_region 本地网编码,
count(a.agreement_id) 十分钟内竣工环节处理量
from ls65_crm2.cust_indent_t a,
ls65_crm2.cust_indent_tache_t b
where a.indent_state = '100'
and a.agreement_id = b.agreement_id
and b.operate_tache = 6
and b.deal_flag = 1
b.exit_date >sysdate-10/24/60
group by a.partition_id_reg

ion
order by a.partition_id_region;

4、我们网管监控系统直接用调用远程视图
如：
Select * from v_cust_ident@A2SVC2；
四、总结
1、对选择性高的列要敏感（什么样的查询能从大数据量返回少量值，这个查询列非常适宜建索引）
2、SQL的写法要注意列避免被运算，这个可作为SQL开发规范来规范。
3、远程多表连接开销很大，尽量用视图封装，TOM大师强调过，也可考虑将来做为开发规范。



本案例中，主要是高选择性的索引列未建，其次是注意SQL写法，否则即便建了索引也用不到，这个可以用一个简单的例子发出来，我构造的。


drop table test;
create table test (id int , day date default sysdate);

insert into test select rownum,sysdate-rownum from dual connect by rownum<=100000;
select * from test;
drop index idx_day;

--最近1万分钟以内的记录
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> connect ljb/ljb
已连接。
SQL> set autotrace on
SQL> select count(*) from test where (sysdate - day) * 24 * 60 < 10000;

COUNT(*)
----------
6


执行计划
----------------------------------------------------------
Plan hash value: 1950795681

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 87 (21)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | 9 | | |
|* 2 | TABLE ACCESS FULL| TEST | 3 | 27 | 87 (21)| 00:00:02 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter((SYSDATE@!-"DAY")*24*60<10000)

Note
-----
- dynamic sampling used for this statement


统计信息
----------------------------------------------------------
4 recursive calls
0 db block gets
365 consistent gets
0 physical reads
0 redo size
408 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> select count(*) from test where day>sysdate-10000/24/60;

COUNT(*)
----------
6


执行计划
----------------------------------------------------------
Plan hash value: 788368599

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------

--------------
| 0 | SELECT STATEMENT | | 1 | 9 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 9 | | |
|* 2 | INDEX RANGE SCAN| IDX_DAY | 6 | 54 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("DAY">SYSDATE@!-6.94444444444444444444444444444444444444)

Note
-----
- dynamic sampling used for this statement


统计信息
----------------------------------------------------------
7 recursive calls
0 db block gets
86 consistent gets
0 physical reads
0 redo size
408 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL>



最后再说明一点，在做本案例优化的时候，由于所给的用户权限极小，甚至连看执行计划的权限都没有。
本想第二天向局方要一个权限大的用户来分析，不过事实上很多情况下，不用看执行计划根据经验也能猜出问题在哪，
也能做一些优化！