昨天工程师反映客户一个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
-----------------------------------
进行计划分析
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优化,望大家讨论
相关推荐
AR人脸库数据集(2600张图片).zipAR人脸库数据集(2600张图片).zipAR人脸库数据集(2600张图片).zipAR人脸库数据集(2600张图片).zipAR人脸库数据集(2600张图片).zipAR人脸库数据集(2600张图片).zipAR人脸库...
Convo (康沃) 变频器 VFC 2600 技术数据pdf,Convo (康沃) 变频器 VFC 2600 技术数据
1、水果刀检测数据集,从COCO2017数据集中提取得到,并分别转成了txt和xml两种格式的标签,可用于YOLO 水果刀检测; 2、目标类别名:knife; 3、数量:2647 4、...
该数据集还包含来自270,000位用户的全部45,000部电影的2600万个分级的文件。等级为1-5,可从GroupLens官方网站获得。 该数据集包含以下文件: films_metadata.csv:主电影元数据文件。包含完整电影镜头数据集中的45...
Carbon Trust投资英商康桥半导体2600万美金成为新的投资者.pdf
ast2600 bmc芯片_Aspeed发布新一代的BMC芯片AST2600数据手册 AST2600 Integrated Remote Management Processor A3 Datasheet ASPEED Technology Inc. Version 0.9 May 6, 2021
TMT每日资讯速递:工信部:国内5G手机出货量累计超2600万部每日资讯速递.pdf
TMT每日资讯速递:工信部:国内5G手机出货量累计超2600万部每日资讯速递
这个驱动是我使用过的,适合市场上买的数据线。
1、键盘检测测数据集,从COCO2017数据集中提取得到,并分别转成了txt和xml两种格式的标签,可用于YOLO键盘检测; 2、目标类别名:keyboard; 3、数量:2647 4、...
1、长颈鹿检测数据集,从COCO2017数据集中提取得到,并分别转成了txt和xml两种格式的标签,可用于YOLO长颈鹿检测; 2、目标类别名:giraffe; 3、数量:2647 4、...
1、酒杯检测数据集,从COCO2017数据集中提取得到,并分别转成了txt和xml两种格式的标签,可用于YOLO酒杯检测; 2、目标类别名:wine glass; 3、数量:2643 4、...
这些文件包含完整 MovieLens 数据集中列出的所有 45,000 部电影的元数据。...该数据集还包含包含 270,000 名用户对所有 45,000 部电影的 2600 万个评分的文件。评分范围为 1-5,并且是从 GroupLens 官方网站获得的。
超过45000部电影的元数据集 数据说明: 这些文件包含完整MovieLens数据集中列出的所有45.000部电影的元数据...该数据集还包含270,000用户对所有45000部电影的2600万个评分。评分是1-5分,并已从官方网站GroupLens获得。
1、棒球棒检测数据集,从COCO2017数据集中提取得到,并分别转成了txt和xml两种格式的标签,可用于YOLO等算法棒球棒检测; 2、目标类别名:baseball bat; 3、数量:2603 4、...
matlab开发-适用于2600系列USB数据采集设备数据采集的Matlabadaptor。这使您能够使用Matlab和Matlab的数据采集工具箱与u2600a系列DAQ设备。
AST2600 Datasheet
价值2600元的新云 v3.0.0.518 高级企业版(SQLACCESS)(所有组件已经彻底破解)无需要解压密码
BES2600全系列的datasheet, BES资料挺不好找的, 辛苦搬运, 非常不错的资料, 对该芯片型号感兴趣的朋友可以研究研究. 包含BES2600Z/Y, BES2600YA/YP, BES2600IUC
有超过2600万个家庭需要能源供应商提供能源,目标是到2020年每家每户都有智能电表。 此次智能电表的推出由欧盟牵头,欧盟要求所有成员国政府将智能电表作为升级能源供应和应对气候变化措施的一部分。经过初步研究,...