- 最后登录
- 2014-3-6
- 在线时间
- 36 小时
- 威望
- 0
- 金钱
- 109
- 注册时间
- 2012-6-12
- 阅读权限
- 10
- 帖子
- 45
- 精华
- 0
- 积分
- 0
- UID
- 494
|
1#
发表于 2013-6-13 17:57:28
|
查看: 3742 |
回复: 3
本帖最后由 霍俊路 于 2013-6-13 18:01 编辑
您好:
我这边有个SQL语句,生产系统使用的绑定变量,其执时间约40S。如果直接带入数值,执行时间4S. 相差10倍。请帮忙指点一下:
oracle 版本: 10.2.0.4
绑定变量的sql执行计划:
-------------------------------------------------------------------------------------------------------------------------------
Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------
0 | SELECT STATEMENT | | 44367 | 5242K| | 98017 (23)| | |
1 | SORT ORDER BY | | 44367 | 5242K| 11M| 98017 (23)| | |
* 2 | HASH JOIN RIGHT OUTER | | 44367 | 5242K| | 96767 (23)| | |
3 | VIEW | | 1126 | 21394 | | 27483 (1)| | |
4 | SORT GROUP BY | | 1126 | 55174 | | 27483 (1)| | |
5 | VIEW | | 1126 | 55174 | | 27483 (1)| | |
6 | SORT UNIQUE | | 1126 | 159K| | 27483 (1)| | |
* 7 | FILTER | | | | | | | |
* 8 | HASH JOIN | | 1126 | 159K| | 27481 (1)| | |
9 | TABLE ACCESS FULL | LOCATION | 144 | 3456 | | 3 (0)| | |
* 10 | HASH JOIN | | 1128 | 133K| | 27477 (1)| | |
* 11 | TABLE ACCESS FULL | SITE | 209 | 16511 | | 36 (6)| | |
12 | TABLE ACCESS BY INDEX ROWID| SITE_PRIZE_DAILY_REPORT | 3 | 93 | | 7 (0)| | |
13 | NESTED LOOPS | | 21109 | 865K| | 27439 (1)| | |
* 14 | INDEX FAST FULL SCAN | SYS_C0074372 | 7969 | 87659 | | 117 (4)| | |
* 15 | INDEX RANGE SCAN | PK_SITE_PRIZE_DAILY_REPORT | 4 | | | 3 (0)| | |
16 | MERGE JOIN | | 8235 | 820K| | 69283 (32)| | |
17 | VIEW | | 8235 | 747K| | 69279 (32)| | |
18 | SORT GROUP BY | | 8235 | 579K| | 69279 (32)| | |
* 19 | HASH JOIN | | 8235 | 579K| | 69274 (32)| | |
20 | VIEW | | 7969 | 47814 | | 121 (7)| | |
21 | SORT UNIQUE | | 7969 | 87659 | | 121 (7)| | |
* 22 | FILTER | | | | | | | |
* 23 | INDEX FAST FULL SCAN | SYS_C0074372 | 7969 | 87659 | | 117 (4)| | |
* 24 | HASH JOIN | | 8235 | 530K| | 69151 (32)| | |
* 25 | HASH JOIN | | 208 | 7072 | | 40 (8)| | |
26 | TABLE ACCESS FULL | LOCATION | 144 | 2448 | | 3 (0)| | |
* 27 | TABLE ACCESS FULL | SITE | 209 | 3553 | | 36 (6)| | |
28 | PARTITION RANGE ITERATOR | | 97070 | 3033K| | 69103 (32)| KEY | KEY |
* 29 | TABLE ACCESS FULL | SITE_DAILY_REPORT | 97070 | 3033K| | 69103 (32)| KEY | KEY |
* 30 | SORT JOIN | | 144 | 1296 | | 4 (25)| | |
31 | TABLE ACCESS FULL | LOCATION | 144 | 1296 | | 3 (0)| | |
-------------------------------------------------------------------------------------------------------------------------------
redicate Information (identified by operation id):
--------------------------------------------------
2 - access("X"."SITE_NO"="Y"."SITE_NO"(+))
7 - filter(TO_DATE(:V9,'yyyy-mm-dd:hh24:mi:ss')<=TO_DATE(:V10||' 23:59:59','yyyy-mm-dd:hh24:mi:ss'))
8 - access("B"."LOCATION_ID"="C"."LOCATION_ID")
10 - access("A"."SITE_ID"="B"."SITE_ID")
11 - filter("B"."SITE_NO" LIKE :V8)
14 - filter(TO_CHAR("GAME_ID") LIKE DECODE(:V6,'All','%',:V7))
15 - access("A"."DRAW_ID"="DRAW_ID" AND "A"."REPORT_DATE">=TO_DATE(:V9,'yyyy-mm-dd:hh24:mi:ss') AND
"A"."REPORT_DATE"<=TO_DATE(:V10||' 23:59:59','yyyy-mm-dd:hh24:mi:ss'))
19 - access("A"."DRAW_ID"="D"."DRAW_ID")
22 - filter(TO_DATE(:V4,'yyyy-mm-dd:hh24:mi:ss')<=TO_DATE(:V5||' 23:59:59','yyyy-mm-dd:hh24:mi:ss'))
23 - filter(TO_CHAR("GAME_ID") LIKE DECODE(:V1,'All','%',:V2))
24 - access("A"."SITE_ID"="B"."SITE_ID")
25 - access("B"."LOCATION_ID"="C"."LOCATION_ID")
27 - filter("B"."SITE_NO" LIKE :V3)
29 - filter("A"."REPORT_DATE">=TO_DATE(:V4,'yyyy-mm-dd:hh24:mi:ss') AND "A"."REPORT_DATE"<=TO_DATE(:V5||'
23:59:59','yyyy-mm-dd:hh24:mi:ss'))
30 - access("X"."LOCATION_ID"="W"."LOCATION_ID")
filter("X"."LOCATION_ID"="W"."LOCATION_ID")
|
|