【20150628】SQL调优实战
来源:互联网 发布:pon网络光纤监测系统 编辑:程序博客网 时间:2024/06/06 18:30
语句显示为:某一客户当天产品的总销量,开发写的SQL的语句是这样的:
select sum(sl0000) from xstfxps2 where
dhao00 in (
select dhao00 from xstfxps1 where trunc(ywrq00)=trunc(sysdate)
and khdm00='500000003913');
已用时间: 00: 02: 49.04
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 NESTED LOOPS
3 2 TABLE ACCESS (FULL) OF 'XSTFXPS2'
4 2 TABLE ACCESS (BY INDEX ROWID) OF 'XSTFXPS1'
5 4 INDEX (UNIQUE SCAN) OF 'XSTFXPS1_PK' (UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
17355138 consistent gets
34141 physical reads
2912 redo size
198 bytes sent via SQL*Net to client
275 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
我们看到统计信息里面进行了17355138次逻辑读,34141次物理IO,这是相当吓人的数字。在执行计划里面我们看到表XSTFXPS2来了一次全表扫描。
我们首先看一下这两张表总的数据量:
SQL> select count(*) from xstfxps2;
COUNT(*)
----------
5585018
我们这里看到XSTFXPS2这张表有5585018条记录。
SQL> select count(*) from xstfxps1;
COUNT(*)
----------
702121
调整的第一步
把子查询提取出来,再看语句的执行计划。
通常来说,如果语句能够避免子查询的使用,就尽量不用子查询。因为子查询的开销是相当昂贵的。
改写后的语句如下:
select sum(sl0000)
from xstfxps2 a,(select dhao00 from xstfxps1 where trunc(ywrq00)=trunc(sysdate)
and khdm00='500000003913') b
where a.dhao00=b.dhao00;
已用时间: 00: 00: 03.05
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'XSTFXPS2'
3 2 NESTED LOOPS
4 3 TABLE ACCESS (FULL) OF 'XSTFXPS1'
5 3 INDEX (RANGE SCAN) OF 'XSTFXPS2_PK' (UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
11974 consistent gets
225 physical reads
832 redo size
211 bytes sent via SQL*Net to client
275 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
我们可以看到逻辑IO由原来的17355138次下降到11974次,有了数量级的提升。执行时间也有原来将近3分钟下降到现在的3秒多一些。很显然性能有了大幅的提升。
不过我们看到执行计划里面表XSTFXPS1还是有一个全表扫描存在。
通常来说我们应该尽量避免全表扫描的存在,尤其对于大表,应该建立合适的索引以避免FTS的产生。
我们来看这两张表的索引信息:
select index_name,column_name from dba_ind_columns where table_name like 'XSTFXPS%'
INDEX_NAME COLUMN_NAME
------------------------------ -----------------------------------
XSTFXPS1_PK DHAO00
XSTFXPS2_PK DHAO00
XSTFXPS2_PK SPDM00
我们看到这两张表除了主键约束外都没有建另外的索引。根据语句的查询情况,我们建立了如下的复合索引:
create index idx_xstfxps1_khdm00_ywrq00 on xstfxps1(khdm00,ywrq00) tablespace indx;
为了使用索引,我们必须对原来的日期字段的条件进行一些调整。
因为有个trunc()函数的存在,语句将不会使用到索引。
我们只要明白trunc(ywrq00)=trunc(sysdate)事实上等同于ywrq00大于trunc(sysdate),小于trunc(sysdate+1)减去一秒,我们就有了比较好的办法来处理这个条件。
最终改写后的语句如下:
select sum(sl0000)
from xstfxps2 a, xstfxps1 b
where a.dhao00=b.dhao00
and b.khdm00='500000003913'
and b.ywrq00 between trunc(sysdate)
and trunc(sysdate)+1-1/(24*60*60);
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'XSTFXPS2'
3 2 NESTED LOOPS
4 3 TABLE ACCESS (BY INDEX ROWID) OF 'XSTFXPS1'
5 4 INDEX (RANGE SCAN) OF 'IDX_XSTFXPS1_KHDM00_YWRQ00'
(NON-UNIQUE)
6 3 INDEX (RANGE SCAN) OF 'XSTFXPS2_PK' (UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
210 bytes sent via SQL*Net to client
275 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
我们这时候看逻辑IO已经降为3次,语句的执行计划也符合我们的调整目标,创建的索引产生了比较大的效果。
这条语句的调整至此告一段落。
总结:
①避免大表的全表扫描
②正确的组合索引建立
- 【20150628】SQL调优实战
- SQL实战
- 动态SQL实战
- SQL行列转换实战
- SQL行列转换实战
- Oracle PL/SQL实战
- 实战:oracle sql profile
- PL/SQL实战积累
- SQL语句实战
- SQL总结查询实战
- 数据库SQL实战
- 数据库SQL实战
- 数据库SQL实战
- 数据库SQL实战
- 数据库SQL实战
- 数据库SQL实战
- 数据库SQL实战
- 数据库SQL实战
- 进程互斥(三)Peterson算法
- 利用Web Browser创建简单的浏览器
- 程序调试技巧
- python遇到的问题解决方法的记录
- R语言基础:管理工作空间的函数
- 【20150628】SQL调优实战
- [php] UEditor编辑器及常见问题
- 1010--映射一对多关联关系
- 黑马程序员——Java中 多线程笔记
- ViewPager,OnPageChangeListener及PageTransformer
- 黑马程序员---java字符串常用类
- python __init__.py
- 微信开发 注意 js接口安全域名 invail url domain
- SQL优化案例