由rownum引起的执行计划变化，谁能解析下？

显示全部楼层 · 2010-1-4 13:39:56

由rownum引起的执行计划变化，谁能解析下？
第一个语句执行3 秒钟能出结果，
第二个语句执行10分钟才能出结果；
请高手解析下，只是对结果进行选择下，怎么会这么久呢?
pro_bbossbpm@BL1> SELECT C.XMLDATA, A.CUSTOMERORDERMEMBERASSOCID
2FROM ORDERINFOSYNCINSTANCE A
3INNER JOIN CUSTOMERORDERMEMBERASSOC B
4ON A.CUSTOMERORDERMEMBERASSOCID =B.CUSTOMERORDERMEMBERASSOCID
5INNER JOIN ORDERINFO C
6ON B.ORDERINFOID = C.ORDERINFOID
7WHERE C.CUSTOMERNUMBER = '00020069000803' AND A.COMPANYID = '000' AND B.TYPE = 2
8AND C.PRODUCTOFFERINGID = '1242' AND C.OPERATIONSUBTYPEID = 1
9ORDER BY A.SYNCID ASC;
已用时间:00: 00: 00.00
执行计划
----------------------------------------------------------
Plan hash value: 1580940903
----------------------------------------------------------------------------------------------------
| Id| Operation
| Name
| Rows| Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT
|
|3087 | 449K| 14457 (1)| 00:02:54 |
| 1 |SORT ORDER BY
|
|3087 | 449K| 14457 (1)| 00:02:54 |
| 2 | TABLE ACCESS BY INDEX ROWID | ORDERINFOSYNCINSTANCE| 1 |16 | 3 (0)| 00:00:01 |
| 3 |NESTED LOOPS
|
|3087 | 449K| 14456 (1)| 00:02:54 |
|*4 | HASH JOIN
|
|2588 | 336K|6875 (2)| 00:01:23 |
| 5 |TABLE ACCESS BY INDEX ROWID| ORDERINFO
|2342 | 269K|1978 (1)| 00:00:24 |
|*6 | INDEX SKIP SCAN | SYS_C0027014
|2342 | | 776 (0)| 00:00:10 |
|*7 |TABLE ACCESS FULL
| CUSTOMERORDERMEMBERASSOC |1404K|20M|4887 (2)| 00:00:59 |
|*8 | INDEX RANGE SCAN
| SYS_C0027017
| 1 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("B"."ORDERINFOID"="C"."ORDERINFOID")
6 - access("C"."PRODUCTOFFERINGID"='1242' AND "C"."OPERATIONSUBTYPEID"=1 AND

"C"."CUSTOMERNUMBER"='00020069000803')
filter("C"."OPERATIONSUBTYPEID"=1 AND "C"."CUSTOMERNUMBER"='00020069000803' AND

"C"."PRODUCTOFFERINGID"='1242')
7 - filter("B"."TYPE"=2)
8 - access("A"."CUSTOMERORDERMEMBERASSOCID"="B"."CUSTOMERORDERMEMBERASSOCID" AND

"A"."COMPANYID"='000')

pro_bbossbpm@BL1> SELECT XMLDATA, CUSTOMERORDERMEMBERASSOCID
2FROM (
3SELECT C.XMLDATA, A.CUSTOMERORDERMEMBERASSOCID
4FROM ORDERINFOSYNCINSTANCE A
5INNER JOIN CUSTOMERORDERMEMBERASSOC B
6ON A.CUSTOMERORDERMEMBERASSOCID =B.CUSTOMERORDERMEMBERASSOCID
7INNER JOIN ORDERINFO C
8ON B.ORDERINFOID = C.ORDERINFOID
9WHERE C.CUSTOMERNUMBER = '00020069000803' AND A.COMPANYID = '000' AND B.TYPE = 2
10AND C.PRODUCTOFFERINGID = '1242' AND C.OPERATIONSUBTYPEID = 1
11ORDER BY A.SYNCID ASC
12)
13WHERE ROWNUM < 2;
已用时间:00: 00: 00.03
执行计划
----------------------------------------------------------
Plan hash value: 142012335
----------------------------------------------------------------------------------------------------
| Id| Operation
| Name
| Rows| Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT
|
| 1 |2015 |8383 (1)| 00:01:41 |
|*1 |COUNT STOPKEY
|
| | |
|
|
| 2 | VIEW
|
| 2 |4030 |8383 (1)| 00:01:41 |
| 3 |NESTED LOOPS
|
| 2 | 298 |8383 (1)| 00:01:41 |
| 4 | NESTED LOOPS
|
|1057 | 32767 |6304 (1)| 00:01:16 |
|*5 |TABLE ACCESS BY INDEX ROWID| ORDERINFOSYNCINSTANCE|3284K|50M|2158 (1)| 00:00
| 6 | INDEX FULL SCAN | SYS_C00110503
|4015 | |11 (0)| 00:00:01 |
|*7 |TABLE ACCESS BY INDEX ROWID| CUSTOMERORDERMEMBERASSOC | 1 |15 | 2 (0)| 00:00:01 |
|*8 | INDEX UNIQUE SCAN | SYS_C00110494
| 1 | | 1 (0)| 00:00:01 |
|*9 | TABLE ACCESS BY INDEX ROWID | ORDERINFO
| 1 | 118 | 2 (0)| 00:00:01 |
|* 10 |INDEX UNIQUE SCAN
| SYS_C00110502
| 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<2)
5 - filter("A"."COMPANYID"='000')
7 - filter("B"."TYPE"=2)
8 - access("A"."CUSTOMERORDERMEMBERASSOCID"="B"."CUSTOMERORDERMEMBERASSOCID")
9 - filter("C"."OPERATIONSUBTYPEID"=1 AND "C"."CUSTOMERNUMBER"='00020069000803' AND

"C"."PRODUCTOFFERINGID"='1242')
10 - access("B"."ORDERINFOID"="C"."ORDERINFOID")

千问 · 2010-1-4 13:39:56

这种问题找棉花

千问 · 2010-1-4 13:39:56

ROWNUM干的
COUNT STOPKEY 这玩意搞的

千问 · 2010-1-4 13:39:56

千问 · 2010-1-4 13:39:56

有解决方法么？
除了用Hint。

千问 · 2010-1-4 13:39:56

[ 本帖最后由 zergduan 于 2010-5-28 16:42 编辑 ]

千问 · 2010-1-4 13:39:56

optimizer_mode是否设置成first_rows了??
CUSTOMERORDERMEMBERASSOC 表的ORDERINFOID上加个索引试试

千问 · 2010-1-4 13:39:56

对头，rownum会导致执行计划改变。
如何禁止这种改变呢？

千问 · 2010-1-4 13:39:56

这种改变，需要了解优化器想法，其实很容易解释这种计划的变更。
为什么加了rownum就改变了计划呢？
我怀疑这些字段没有柱状图，但是表可能有点大，数量级（不是数据行，是数据级）应该在10W。ORACLE无法知道大概取出多少行？于是HASH变nested loop，为什么会变。其实ORACLE知道HASH比nested loop要好，变的原因是ORACLE还要考虑sort和index full scan,table full scan的代价。
从2个计划可以推算出，nested loop +INDEX UNIQUE SCAN+ INDEX Full SCAN+ sort < hash loop+table full scan+table skip scan
但是事实上很慢，说明ORACLE错了，错在哪里？因为ORACLE无法知道要得到多少行.
楼主是否可以告诉我们这个SQL得到多少行，我认为行会比较多。
是否有柱状图，没有的话，建立收集。

千问 · 2010-1-4 13:39:56

1）说明下SQL中用到的这3张表的大小：
ORDERINFOSYNCINSTANCE
8GB
ORDERINFO
7GB
CUSTOMERORDERMEMBERASSOC 150MB
to tom_fans ：
2) 这个SQL 大部分时间只返回1条；但是也有返回2条以上的时候，不过很少时候。所以才加了rownum。