每秒执行6000的简单SQL优化（一）（r10笔记第62天）

最近看到一个系统的负载比较高，引起了我的注意，查看AWR报告发现，竟然是因为两条很简单的SQL语句导致。 语句有多简单呢，就是下面的两个SQL语句。 select companyname from license select supdepid from hrmdepartment where id='' 突然发现以前也发现了这个问题，但是最后也是不了了之，还是因为单纯从数据库的层面调整要灵活快捷的多，从业务层面来推动还是有一定的难度和阻力。之前的分析：关于CPU使用率高的awr分析

表License的数据只有1行，表hrmdepartment对的数据有2000多行，id是主键，含有非空约束。 为什么这么小数量的表，含有主键，怎么还会导致严重的性能问题呢。 看执行计划第一个语句是全表扫描，里面只有1行记录，全表和索引扫描应该差别很小。

就是这样一个语句，在一个小时的时间里竟然执行了近2000多万次。

这样一条SQL的影响被无限放大，就导致了数据库的负载很高。 如此来看，每秒钟的执行频率极高，1秒钟差不多是6000多次的频率。什么系统有如此之高的业务需求。 和开发的同学交涉，原以为分析已经到位，剩下的就是快刀斩乱麻似的解决问题了。但是发现问题比我想象的要糟糕很多。 目前的情况是，大家都认为这是一个问题，但是让人很无奈的情况是这个系统是一个外部系统，目前还没有源码，所以也就意味着这是一个黑盒的环境了。 我看了下出问题的用户的结构信息，真让我大跌眼镜，大量的存储过程和触发器，表竟然有1万多个，我倒吸一口冷气，这个问题的情况确实比我想的难，准确的说是糟糕。 sh findobj.sh USERV6|grep TABLE|wc -l 11904 $ sh findobj.sh USERV6|grep TRIGG|wc -l 1580 $ sh findobj.sh USERV6|grep INDEX|wc -l 1542 $ sh findobj.sh USERV6|grep PROC|wc -l 2149 换句话说，从应用层面来调整SQL的可能性极小。碰到这种情况真是无语了。但是抱怨和牢骚解决不了问题。我一边和开发的同学沟通，一边想数据库层面能不能做点什么。 select companyname from license这样一个语句，不能动SQL还有什么优化空间了。目前来看有一个改进之处是索引，表里有10多个字段，输出只有一个字段，表里存在一行记录。所以也就尽可能提高访问的效率，10%的改进被无限放大也是一个很理想的值。 而且如果SQL语句能够修改的情况下，我有什么好的办法来改进，这都是我需要考虑的问题。 我用一个新的用户来做了一番测试。 create table license tablespace users as select * from USERV6.LICENSE ; exec dbms_stats.gather_table_stats(ownname=>null,tabname=>'LICENSE'); 可以看到执行计划是走了全表扫描 SQL> set autot trace exp stat

尝试1： 因为这个表只存在1行记录，而且从表结构信息来看数据是唯一的，于是我尝试创建一个唯一性索引。 create unique index index_lic_companyname on license(companyname); 重新收集统计信息 SQL> exec dbms_stats.gather_table_stats(ownname=>null,tabname=>'LICENSE'); 再次查看，发现CBO还是走了全表扫描。执行计划没有发生改变。

尝试2： 尝试添加hint,设置cardinality都是一样的结果，没有任何改变。 select /*+index( license index_lic_companyname)*/ companyname from license select /*+cardinality( license,1)*/ companyname from license 尝试3： 我想到了一个新的改进方法，就是sample,可以根据随机算法得到数据。但是尝试结果依旧没有任何变化 select companyname from license sample(1);

尝试4： 尝试rownum的形式，结果依旧。 SQL> select companyname from license where rownum<=1; 尝试5： 启用cache选项，把数据牢牢放入cache里面，减少被换出的概率，但是在这个极端场景下，还是没有任何的改进。 SQL> alter table license cache; 尝试6： 如果我启用了Unique Index的时候，即SQL语句改为下面的形式，结果不言而喻，肯定是非常理想了。 select companyname from license where companyname='xxxx'

但是改动成为上面的情况的可能性我几乎为0，想想挺美好，总是有巨大的差距。 尝试7： 既然在这个特殊场景中，我们需要查询的是companyname这个字段，有什么办法把索引和表结合起来呢。一种方式就是IOT,即索引组织表了。 重新创建表 CREATE TABLE "CYDBA"."LICENSE_IOT" ( "COMPANYNAME" VARCHAR2(100) primary key, ... "DOCUMENT" CHAR(1) ) organization index TABLESPACE "USERS" ; 插入1行数据。 SQL> insert into LICENSE_IOT select * from license; 1 row created. SQL> commit; 收集统计信息。 SQL>exec dbms_stats.gather_table_stats(ownname=>null,tabname=>'LICENSE_IOT'); 可以看到还是有了不小的改进。

这个结果让我还是充满信息，准备近期部署上去对比一下，希望看到鲜明的差距。第2条SQL继续优化，还是一块不好啃的骨头。