oracle下一条SQL语句的优化过程(比较详细)
来源:asp之家 发布时间:2010-04-22 16:58:00
原来的语句是这样的:
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
两张表的表结构如下所示:
SQL> desc xstfxps1
Name Type Nullable Default Comments
------ ------------ -------- ------- --------
DHAO00 NUMBER(8)
LHDH00 NUMBER(8) Y
FLDH00 NUMBER(8) Y
FPLB00 VARCHAR2(2) Y
YWRQ00 DATE Y
YWRY00 VARCHAR2(8) Y
SHRQ00 DATE Y
XSQRRQ DATE Y
XSQRRY VARCHAR2(8) Y
KHDM00 VARCHAR2(12)
XKZH00 VARCHAR2(12)
CKDM00 VARCHAR2(2) Y
THCKDM VARCHAR2(2) Y
XSFSDM VARCHAR2(2) Y
FXRYDM VARCHAR2(4) Y
SHRYDM VARCHAR2(4) Y
SHBJ00 VARCHAR2(1) 'N'
FXBJ00 VARCHAR2(1) 'N'
SKBJ00 VARCHAR2(2) Y
FKDM00 VARCHAR2(2) Y
SQL> desc xstfxps2
Name Type Nullable Default Comments
------ ------------ -------- ------- --------
DHAO00 NUMBER(8)
SPDM00 VARCHAR2(8)
DJIA00 NUMBER(7,2) 0
FXSL00 NUMBER Y 0
SL0000 NUMBER Y 0
THSL00 NUMBER Y 0
JE0000 NUMBER Y 0
SE0000 NUMBER Y
FPBBH0 VARCHAR2(11) Y
FPHAO0 VARCHAR2(10) Y
RBDH00 NUMBER(8) Y
其中XSTFXPS1的客户订单的表头,保存订单的客户信息、订货日期等信息。XSTFXPS2是订单的表体,详细记录了客户订单的商品、价格、数量等信息。
调整的第一步是把子查询提取出来,再看语句的执行计划。通常来说,如果语句能够避免子查询的使用,就尽量不用子查询。因为子查询的开销是相当昂贵的。改写后的语句如下:
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次,语句的执行计划也符合我们的调整目标,创建的索引产生了比较大的效果。这条语句的调整至此告一段落。
猜你喜欢
- 如何侦测HTTP表头信息?可用下列办法侦测并显示所有的HTTP HEADERS:<HTML><HEAD><TI
- CSS 文件的大小和所引起的 HTTP 的请求数是 CSS 性能的最关键因素回流(reflow)和渲染时间(非常!)没那么重要副本(dupl
- 网上广泛流传的取 object 的绝对位置的做法是:var getAbsPos = function(pTar
- 页面加载loading效果, 这个挺好玩的!用setTimeout实现的!可以和服务端整合弄一些生成HTML或者上传文件等应用!
- 代码如下:--根据MAX(MIN)ID CREATE PROC [dbo].[proc_select_id] @pageindex int=
- 阅读Chapter 1 清单Chapter 2 标题总览:不但所有网页都需要有标题,而且如果标记正确的话,他们能为网页设计和易用性
- 本月第一天日期SELECT FirstDayOfCurrentMonth = dateadd(mm,datediff(mm,0,getdat
- 如何做一个可以让人家申请使用的计数器? 好了,我们来做一个与页面分离的计数器,是文本型的啦。这也很简单,
- 1)用这个方法,把虚拟主机的默认页只设置成一个比如index.asp 在index.asp的最上面加入以下代码:<%if R
- 近段时间看了一些论坛上面关于分页的ASP程序依然有许多的关注者,但里面只有代码,没有详细的解释,对于初学者来说,这样总是得不到真正的掌握,此
- 七夕节简介每年农历七月初七这一天是我国汉族的传统节日七夕节。因为此日活动的主要参与者是少女,而节日活动的内容又是以乞巧为主,故而人们称这天为
- icon可以用多个软件制作,也可以通过一些网站把普通图片转换为.ico文件,但通常存在的问题是图片本该透明的地方经转换后变为了黑色或者白色,
- 代码如下:url="http://www.cidianwang.com/" wstr=getHTTPPage(
- MySQL是一个非常流行的小型关系型数据库管理系统,2008年1月16号被Sun公司收购。目前MySQL被广泛地应用在Internet上的中
- 有助于效率的类型选择1、使你的数据尽可能小最基本的优化之一是使你的数据(和索引)在磁盘上(并且在内存中)占据的空间尽可能小。这能给出巨大的改
- asp连接mysql的问题ASP连接Mysql数据库的问题。下了一个MySql 的ODBC驱动。做了个小测试。顺利通过。先记录下来,中间还有
- 木马原理:入侵者使用诸如ASP图片木马生成器之类的工具将一张正常的图片与一个ASP木马文件合并成一个图片文件(即将对网站有害的ASP代码插在
- 在Internet上我们每天都会遇到数不清的表单,也看到其中大部分并没有限制用户多次提交同一个表单。缺乏这种限制有时候会产生某些预料不到的结
- 二是什么时候CPU是空闲的?空闲是一个相对的标准。有时会CPU使用率30%以下可以定义为空闲;而有时候CPU使用率只有不到60%,就是空闲。
- 1:在终端下:mysql -V。 以下是代码片段:[shengting@login ~]$ mysql -Vmysql Ver 14.7 D