每秒执行6000的简单SQL优化（二） (r10笔记第65天)

jeanron100

发布于 2018-03-20 11:12:12

8470

发布于 2018-03-20 11:12:12

继续前几天的一次性能调优，这次调优难度不小，而且空间很小，看起来简直就是绝处逢生的感觉。下面的两条SQL语句执行频率极高，每秒达到6000次，希望能够优化。 select companyname from license

select supdepid from hrmdepartment where id='' 前几天分析了一下，也尝试了很多种方法，但是始终无法启用索引，最后采用IOT的形式才看到效果，这是其一。可以参见每秒执行6000的简单SQL优化（一）（r10笔记第62天）还有一点很多明眼人看出来了，为什么创建了唯一性索引，表license中存在1行记录，但是却无法启用，一个根本原因就是唯一性索引的列没有非空约束。为此我又进行了一次测试，添加了主键，那么就会是非空约束和唯一性主键。

create table license tablespace users as select * from  USERV6.LICENSE ;
alter table license modify(companyname primary key);
exec dbms_stats.gather_table_stats(ownname=>null,tabname=>'LICENSE');测试结果如下：

可以看到达到了预期的改进效果。但是改进幅度到底有多大呢。我们抓取了一个awr的报告来看看。改进前，是全表扫描，每次执行的buffer gets是7

而改进之后的情况如下:

第一条语句的优化暂且到这里，如果是在11g中，可能result cache还有有一些改进之处。再来看看第二条语句。 select supdepid from hrmdepartment where id='' 表hrmdepartment这种id是主键列，但是查询使用了id='' 查看执行计划可以很明显看到Filter的处理，里面的条件很微妙NULL IS NOT NULL

这样一个语句怎么优化呢，一个很明显的处理方式就是在SQL中做检查，尽可能调整逻辑。但是让人尴尬的是这个应用是闭源的，无法直接修改里面的逻辑。怎么改进，或者说有什么是DBA能想办法缓解的。首先使用唯一性索引是最高效的索引扫描方式。

能够沾沾索引的光，其实还真有点门路，那就是创建一个复合索引，基于列(supdepid,id)

create unique index ind_hrm_id on HRMDEPARTMENT(id,supdepid);      
exec dbms_stats.gather_table_stats(ownname=>null,tabname=>'HRMDEPARTMENT',cascade=>true);这个语句的效果如下：

如果运行已有的主键id条件的查询，就会发现原来的唯一性索引变为了下面的区间扫描。

其实可以在继续改进，就是建立复合索引，基于列（supdepid，id）这样的好处在于不会影响已有的唯一性主键索引。查询id=''的效果如下：

而根据id的值来查询，这个是还是走原来的唯一性索引扫描。

这样做可能看起来效果不大，毕竟扫描返回的行数都是全表的行数。我们把数据量增大到500万

create table test_obj as select level object_id ,'obj'||level 
object_name,'TABLE' object_type from dual connect by level<=5000000;
alter table test_obj modify(object_id primary key);

这个时候Ojbect_type为char类型，修改为varchar2 SQL> ALTER TABLE TEST_OBJ MODIFY(OBJECT_TYPE VARCHAR2(10)); 然后把数据简单的处理一下，让数据的分布基本打散。

update test_obj set object_type='VIEW' where object_id>200000 and object_id<1430000;
update test_obj set object_type='SYNONYM' where object_id>2000000 and object_id<3430000;
update test_obj set object_type='SEQUENCE' where object_id>4000000 and object_id<4743000;

数据的分布情况如下：

SQL> select object_type,count(*)from test_obj group by object_type;
OBJECT_TYP   COUNT(*)
---------- ----------
VIEW          1229999
SYNONYM       1429999
SEQUENCE       742999
TABLE         1597003

如果我们创建复合索引基于列（object_type,object_id）

create index ind_test_obj_mx on test_obj(object_type,object_id);
exec dbms_stats.gather_table_stats(ownname=>null,tabname=>'TEST_OBJ');

全表扫描的cost为4342，则索引扫描的cost为3765，相比还是有不小的提高。而对于这个问题的持续跟进结果就是，应用那边也做了一些努力，我可以看到的是语句的执行频率从原来的每小时2000万降到了200万。这个改进的效果有多大。可以参见下面的图。横轴是时间，纵轴是数据库的负载绿色的部分是每小时2000万的时候数据库的负载曲线红色的部分是每小时200万的时候数据库的负载曲线蓝色的部分是优化之后，每小时200万的时候数据库的负载曲线。