本文共 2688 字,大约阅读时间需要 8 分钟。
继续前几天的一次性能调优,这次调优难度不小,而且空间很小,看起来简直就是绝处逢生的感觉。下面的两条SQL语句执行频率极高,每秒达到6000次,希望能够优化。
select companyname from license select supdepid from hrmdepartment where id ='' 前几天分析了一下,也尝试了很多种方法,但是始终无法启用索引,最后采用IOT的形式才看到效果,这是其一。 还有一点很多明眼人看出来了,为什么创建了唯一性索引,表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万的时候数据库的负载曲线。 可以看到蓝色的曲线还是略微要低于红色的部分,但是改进的空间在200万的执行频率下效果被缩小了。 改进最大的是应用的调整,幅度可不是简单的几倍几十倍,而是根本性的改变。来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/23718752/viewspace-2127254/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/23718752/viewspace-2127254/