同一个SQL语句，相同查询条件，查询赋值不一样，执行计划却有很大的差别？

显示全部楼层 · 2008-1-2 17:35:53

同一条SQL语句
select max(pdh) from ration_data where scrq_ps=20101213；
只是查询条件的赋值不一样，执行计划差别很大，
当scrq_ps=20101213
scrq_ps=20101214
scrq_ps=20101215
的时候，执行全表扫描
Rows Row Source Operation
----------------------------------------------------------
1SORT AGGREGATE (cr=241590 pr=153520 pw=0 time=102883702 us)
32496 TABLE ACCESS FULL RATION_DATA (cr=241590 pr=153520 pw=0 time=157761824 us)
********************************************************************************
当scrq_ps=20101216
scrq_ps=20101208
或者其他的值的时候，却使用了索引
Rows Row Source Operation
----------------------------------------------------------
1SORT AGGREGATE (cr=16154 pr=0 pw=0 time=898428 us)
246893 TABLE ACCESS BY INDEX ROWID RATION_DATA (cr=16154 pr=0 pw=0 time=740878 us)
246893INDEX RANGE SCAN IDX_RATION2 (cr=665 pr=0 pw=0 time=294 us)(object id 106642)
什么原因呢，困惑中。。。
以下是SQL TRACE 的结果：

TKPROF: Release 10.2.0.4.0 - Production on Thu Dec 16 20:23:02 2010
Copyright (c) 1982, 2007, Oracle.All rights reserved.
Trace file: caskdb1_ora_696546.trc
Sort options: default
********************************************************************************
count= number of times OCI procedure was executed
cpu= cpu time in seconds executing
elapsed= elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query= number of buffers gotten for consistent read
current= number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************
select max(pdh)
from
ration_data where scrq_ps=20101213

call count cpuelapsed diskquerycurrentrows
------- -------------- ---------- ---------- ---------- --------------------
Parse10.00 0.00
0
0
0 0
Execute10.00 0.00
0
0
0 0
Fetch2 25.21 102.88 153520 241590
0 1
------- -------------- ---------- ---------- ---------- --------------------
total4 25.21 102.88 153520 241590
0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 76
Rows Row Source Operation
----------------------------------------------------------
1SORT AGGREGATE (cr=241590 pr=153520 pw=0 time=102883702 us)
32496 TABLE ACCESS FULL RATION_DATA (cr=241590 pr=153520 pw=0 time=157761824 us)
********************************************************************************
select max(pdh)
from
ration_data where scrq_ps=20101212

call count cpuelapsed diskquerycurrentrows
------- -------------- ---------- ---------- ---------- --------------------
Parse10.00 0.00
0
0
0 0
Execute10.00 0.00
0
0
0 0
Fetch20.00 0.00
0
3
0 1
------- -------------- ---------- ---------- ---------- --------------------
total40.00 0.00
0
3
0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 76
Rows Row Source Operation
----------------------------------------------------------
1SORT AGGREGATE (cr=3 pr=0 pw=0 time=122 us)
0 TABLE ACCESS BY INDEX ROWID RATION_DATA (cr=3 pr=0 pw=0 time=93 us)
0INDEX RANGE SCAN IDX_RATION2 (cr=3 pr=0 pw=0 time=64 us)(object id 106642)
********************************************************************************
select max(pdh)
from
ration_data where scrq_ps=20101208

call count cpuelapsed diskquerycurrentrows
------- -------------- ---------- ---------- ---------- --------------------
Parse10.00 0.00
0
0
0 0
Execute10.00 0.00
0
0
0 0
Fetch20.16 0.18 75 5861
0 1
------- -------------- ---------- ---------- ---------- --------------------
total40.16 0.18 75 5861
0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 76
Rows Row Source Operation
----------------------------------------------------------
1SORT AGGREGATE (cr=5861 pr=75 pw=0 time=184695 us)
30753 TABLE ACCESS BY INDEX ROWID RATION_DATA (cr=5861 pr=75 pw=0 time=139517 us)
30753INDEX RANGE SCAN IDX_RATION2 (cr=76 pr=75 pw=0 time=15812 us)(object id 106642)
********************************************************************************
select max(pdh)
from
ration_data where scrq_ps=20101216

call count cpuelapsed diskquerycurrentrows
------- -------------- ---------- ---------- ---------- --------------------
Parse10.00 0.00
0
0
0 0
Execute10.00 0.00
0
0
0 0
Fetch20.89 0.89
016154
0 1
------- -------------- ---------- ---------- ---------- --------------------
total40.89 0.90
016154
0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 76
Rows Row Source Operation
----------------------------------------------------------
1SORT AGGREGATE (cr=16154 pr=0 pw=0 time=898428 us)
246893 TABLE ACCESS BY INDEX ROWID RATION_DATA (cr=16154 pr=0 pw=0 time=740878 us)
246893INDEX RANGE SCAN IDX_RATION2 (cr=665 pr=0 pw=0 time=294 us)(object id 106642)
********************************************************************************
alter session set sql_trace=false

call count cpuelapsed diskquerycurrentrows
------- -------------- ---------- ---------- ---------- --------------------
Parse10.00 0.00
0
0
0 0
Execute10.00 0.00
0
0
0 0
Fetch00.00 0.00
0
0
0 0
------- -------------- ---------- ---------- ---------- --------------------
total20.00 0.00
0
0
0 0
Misses in library cache during parse: 0
Parsing user id: 76

********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpuelapsed diskquerycurrentrows
------- -------------- ---------- ---------- ---------- --------------------
Parse50.00 0.01
0
0
0 0
Execute50.00 0.00
0
0
0 0
Fetch8 26.26 103.96 153595 263608
0 4
------- -------------- ---------- ---------- ---------- --------------------
total 18 26.26 103.98 153595 263608
0 4
Misses in library cache during parse: 4

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpuelapsed diskquerycurrentrows
------- -------------- ---------- ---------- ---------- --------------------
Parse00.00 0.00
0
0
0 0
Execute00.00 0.00
0
0
0 0
Fetch00.00 0.00
0
0
0 0
------- -------------- ---------- ---------- ---------- --------------------
total00.00 0.00
0
0
0 0
Misses in library cache during parse: 0
5userSQL statements in session.
0internal SQL statements in session.
5SQL statements in session.
********************************************************************************
Trace file: caskdb1_ora_696546.trc
Trace file compatibility: 10.01.00
Sort options: default
1session in tracefile.
5userSQL statements in trace file.
0internal SQL statements in trace file.
5SQL statements in trace file.
5unique SQL statements in trace file.
70lines in trace file.
207elapsed seconds in trace file.
[ 本帖最后由 sudaemon 于 2010-12-16 21:16 编辑 ]

千问 · 2008-1-2 17:35:53

选择率不同，当查询的数据量超过一定比例，就会选择全表扫描

千问 · 2008-1-2 17:35:53

这很正常，谓词的值对查询性能影响很大，所以才会对table、index进行分析、统计。

千问 · 2008-1-2 17:35:53

可以参考对象统计，分析的知识，自己建个表，给一列建个索引（这一列进行一些极端情况的模拟，有些值可以有N多，有些值只有1个），按照不同的比例（estimate_percent）进行分析，然后进行谓词条件查询，做了这个实验就会知道为什么执行计划不同了！

千问 · 2008-1-2 17:35:53

认真读了下冰冻奶茶的blog
http://sunwgneuqsoft.itpub.net/post/34741/456500
开始有些认识了。
也谢谢以上各位的指点。

千问 · 2008-1-2 17:35:53

不是说oracle有个Cardinality Feedback机制,不管用?

千问 · 2008-1-2 17:35:53

看看每个值都有多少条记录？

千问 · 2008-1-2 17:35:53

把柱状图去了，就一样了

千问 · 2008-1-2 17:35:53

有直方图吧