昨天工程师反映客户一个sql的统计等的n慢，我plsql跑了一下，需要1110秒，近20分钟，这个现状肯定无法忍受。后进行分析调整之后查询速度在6-12秒之内，调整步骤如下：
现状:linux, oracle 10g ,4G内存，sga1.5G, 调整shared_pool300M,这个shared_pool的调整有些怀疑。先搁置。
表BCM_MONTHGASFEE 数据=26494361
原sql
-————————————————————————
select substr(f.dataenddate, 0, 7) gasdate,
?????? min(o.name) officename,
?????? to_char(sum(f.gasmonthcost)) cost
? from BCM_MONTHGASFEE f, OPM_ORGAN O
?where f.officecode = o.code
?? and f.ChargeMethodCode = ‘1‘
?? and (f.bcharge = 0 or
?????? (f.bcharge = 1 and
?????? f.ChargeTime >
?????? to_timestamp(‘2010-08-04 00:00:00‘, ‘yyyy-mm-dd hh24:mi:ss‘)))
?? and (f.officecode like ‘110%‘
?? and f.dataenddate between ‘2005-01-01‘ and ‘2010-07-31‘)??
?group by substr(f.dataenddate, 0, 7), o.code
?order by substr(f.dataenddate, 0, 7), o.code
OPM_ORGAN O=90条
——————————————————————————
进行sql语句分析，得结果如下：
select substr(f.dataenddate, 0, 7) gasdate,
??????????? f.officecode,
??????????? to_char(sum(f.gasmonthcost)) cost
?????? from BCM_MONTHGASFEE f
????? where?
???????? ((f.ChargeTime > to_timestamp(‘2009-08-04 00:00:00‘, ‘yyyy-mm-dd hh24:mi:ss‘)
??????? and f.bcharge = 1) or f.bcharge = 0 )????
??????? and f.ChargeMethodCode = ‘1‘
??????? and f.dataenddate between ‘2005-01-01‘ and ‘2010-07-31‘
??????? and f.officecode like ‘110%‘??
????? group by substr(f.dataenddate, 0, 7), f.officecode
-----------------------------------
?
SpringMVC+mybatis HTML5 全新高大尚后台框架_集成代码生成器
进行计划分析
?explain plan for
???....{sql语句}
?select * from table(dbms_xplan.display());
是全表扫描，后来建立索引1：officecode， dataenddate ；索引2：bcharge， ChargeTime 进行逐步分析，仍然是全表扫描。
后删除索引1和索引2，建立索引3：OFFICECODE, DATAENDDATE, BCHARGE, CHARGETIME, ChargeMethodCode, 到最后仍然是全表扫描，后来发现,原因在
sum(f.gasmonthcost)这个条语句上，怀疑是sum，去掉sum仍然全表，后来看因为gasmonthcost列未在索引范围之内，后把该索引加上，最终索引是：create index IDX_BCM_MONTHGASFEE_OFF1
?on BCM_MONTHGASFEE (OFFICECODE, DATAENDDATE, BCHARGE, CHARGETIME, ChargeMethodCode, GASMONTHCOST) local;
语句调整如下：
select a.gasdate, b.name, a.cost
?? from (select substr(f.dataenddate, 0, 7) gasdate,
??????????? f.officecode,
??????????? to_char(sum(f.gasmonthcost)) cost
?????? from BCM_MONTHGASFEE f
????? where?
???????? ((f.ChargeTime > to_timestamp(‘2009-08-04 00:00:00‘, ‘yyyy-mm-dd hh24:mi:ss‘)
??????? and f.bcharge = 1) or f.bcharge = 0 )????
??????? and f.ChargeMethodCode = ‘1‘
??????? and f.dataenddate between ‘2005-01-01‘ and ‘2010-07-31‘
??????? and f.officecode like ‘110%‘??
????? group by substr(f.dataenddate, 0, 7), f.officecode
????? )a, opm_organ b
? where b.officecode=a.code
? order by a.gasdate, a.officecode;
执行Sql,查询出结果25秒.
经过以上的Sql调整逻辑读和物理读已经大大缩小了
但是逻辑读还是特别大
?? 112514? consistent gets
?? 72207? physical reads
下面进行调整逻辑读
调整sql如下:
--------------------------------------------
select a.gasdate, b.name, a.cost
?? from (select gasdate, officecode,? to_char(sum(cost)) cost
??????? from (select substr(f.dataenddate, 0, 7) gasdate,
???????????????? f.officecode,
???????????????? f.gasmonthcost cost
??????????? from BCM_MONTHGASFEE f
?????????? where?
????????????? (f.ChargeTime > to_timestamp(‘2009-08-04 00:00:00‘, ‘yyyy-mm-dd hh24:mi:ss‘)
???????????? and f.bcharge = 1)?????
???????????? and f.ChargeMethodCode = ‘1‘
???????????? and f.dataenddate between ‘2005-01-01‘ and ‘2010-07-31‘
???????????? and f.officecode like ‘110%‘?? ?????
?????????? union
?????????? select substr(f.dataenddate, 0, 7) gasdate,
???????????????? f.officecode,
???????????????? f.gasmonthcost cost
??????????? from BCM_MONTHGASFEE f
?????????? where f.bcharge = 0
???????????? and f.ChargeMethodCode = ‘1‘
???????????? and f.dataenddate between ‘2005-01-01‘ and ‘2010-07-31‘
???????????? and f.officecode like ‘110%‘??
???????????? ) x???????
?????group by x.gasdate, x.officecode
????)a, opm_organ b
? where a.officecode=b.code
? order by a.gasdate, a.officecode;
经调整之后：
?54533? consistent gets
? 8392? physical reads
执行Sql,查询出结果9秒.
达到预期。
但是数据和原sql查询出来的数据有出入，调整之后的数据512条，原sql查询是477条，有些疑惑！！！
但是查询数的数据一样的啊，奇怪！！
?---一下查询结果一样 26494957条数据
? select count(1)
? from BCM_MONTHGASFEE f, OPM_ORGAN O
?where f.officecode = o.code? ;
?
? select count(1)
? from BCM_MONTHGASFEE f;
---关于sql优化，望大家讨论
?
SpringMVC+mybatis HTML5 全新高大尚后台框架_集成代码生成器
原文：http://qq-22530757.iteye.com/blog/2176959