错误的转换绑定变量类型导致执行计划错误

最新推荐文章于 2021-05-27 14:52:10 发布

wujizhou2000

最新推荐文章于 2021-05-27 14:52:10 发布

阅读量575

点赞数 1

分类专栏： ORACLE SQL优化

本文链接：https://blog.csdn.net/wujizhou/article/details/22414465

版权

ORACLE SQL优化专栏收录该内容

3 篇文章 0 订阅

订阅专栏

昨天开发来问我个问题，一个业务sql执行缓慢，下面

SQL> SELECT A.ISSUE_OID,
(SELECT B.ISSUE_END_TIME FROM ISSUE_T B WHERE A.ISSUE_OID = B.IWOID) AS ISSUE_END_TIME,
(SELECT C.ENCASH_END_TIME FROM ISSUE_T C WHERE A.ISSUE_OID = C.IWOID) AS ENCASH_END_TIME,
A.OPERATOR_ID,
...
FROM SALE_DETAIL_T A
WHERE 1 = 1
AND A.TICKET_STATE = ?
AND A.SALE_TIME between ? and ?
......

SALE_DETAIL_T 数据600w，返回结果少量，SALE_TIME是索引列

带入参数，查看执行计划

----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 187 | 3 (34)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | ISSUE_T | 1 | 41 | 2 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_ISSUE_T | 1 | | 1 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID | ISSUE_T | 1 | 41 | 2 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | PK_ISSUE_T | 1 | | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID | ISSUE_T | 1 | 37 | 2 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | PK_ISSUE_T | 1 | | 1 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID | ISSUE_T | 1 | 36 | 2 (0)| 00:00:01 |
|* 8 | INDEX UNIQUE SCAN | PK_ISSUE_T | 1 | | 1 (0)| 00:00:01 |
| 9 | TABLE ACCESS BY INDEX ROWID | ISSUE_T | 1 | 36 | 2 (0)| 00:00:01 |
|* 10 | INDEX UNIQUE SCAN | PK_ISSUE_T | 1 | | 1 (0)| 00:00:01 |
| 11 | TABLE ACCESS BY INDEX ROWID | ISSUE_T | 1 | 37 | 2 (0)| 00:00:01 |
|* 12 | INDEX UNIQUE SCAN | PK_ISSUE_T | 1 | | 1 (0)| 00:00:01 |
| 13 | TABLE ACCESS BY INDEX ROWID | ISSUE_T | 1 | 37 | 2 (0)| 00:00:01 |
|* 14 | INDEX UNIQUE SCAN | PK_ISSUE_T | 1 | | 1 (0)| 00:00:01 |
| 15 | TABLE ACCESS BY INDEX ROWID | ISSUE_T | 1 | 37 | 2 (0)| 00:00:01 |
|* 16 | INDEX UNIQUE SCAN | PK_ISSUE_T | 1 | | 1 (0)| 00:00:01 |
| 17 | HASH GROUP BY | | 1 | 187 | 3 (34)| 00:00:01 |
|* 18 | TABLE ACCESS BY INDEX ROWID| SALE_DETAIL_T | 1 | 187 | 2 (0)| 00:00:01 |
|* 19 | INDEX RANGE SCAN | SALE_DETAIL_SALE_TIME_IDX | 1 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("B"."IWOID"=:B1)
4 - access("C"."IWOID"=:B1)
6 - access("E"."IWOID"=:B1)
8 - access("D"."IWOID"=:B1)
10 - access("E"."IWOID"=:B1)
12 - access("F"."IWOID"=:B1)
14 - access("G"."IWOID"=:B1)
16 - access("H"."IWOID"=:B1)
18 - filter("A"."TICKET_STATE"=1)
19 - access("A"."SALE_TIME">=TO_DATE('2014-03-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND
"A"."SALE_TIME"<=TO_DATE('2014-03-01 23:59:59', 'yyyy-mm-dd hh24:mi:ss'))
filter(SYS_OP_DESCEND("A"."SALE_TIME")<=HEXTORAW('878DFCFEF8FEF8FF') AND
SYS_OP_DESCEND("A"."SALE_TIME")>=HEXTORAW('878DFCFEE7C3C3FF') )

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
1419 bytes sent via SQL*Net to client
457 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

真实的执行计划如下

---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 125 | 23375 | 101K (2)| 00:20:15 |
| 1 | TABLE ACCESS BY INDEX ROWID| ISSUE_T | 1 | 41 | 2 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_ISSUE_T | 1 | | 1 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| ISSUE_T | 1 | 41 | 2 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | PK_ISSUE_T | 1 | | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| ISSUE_T | 1 | 37 | 2 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | PK_ISSUE_T | 1 | | 1 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID| ISSUE_T | 1 | 36 | 2 (0)| 00:00:01 |
|* 8 | INDEX UNIQUE SCAN | PK_ISSUE_T | 1 | | 1 (0)| 00:00:01 |
| 9 | TABLE ACCESS BY INDEX ROWID| ISSUE_T | 1 | 36 | 2 (0)| 00:00:01 |
|* 10 | INDEX UNIQUE SCAN | PK_ISSUE_T | 1 | | 1 (0)| 00:00:01 |
| 11 | TABLE ACCESS BY INDEX ROWID| ISSUE_T | 1 | 37 | 2 (0)| 00:00:01 |
|* 12 | INDEX UNIQUE SCAN | PK_ISSUE_T | 1 | | 1 (0)| 00:00:01 |
| 13 | TABLE ACCESS BY INDEX ROWID| ISSUE_T | 1 | 37 | 2 (0)| 00:00:01 |
|* 14 | INDEX UNIQUE SCAN | PK_ISSUE_T | 1 | | 1 (0)| 00:00:01 |
| 15 | TABLE ACCESS BY INDEX ROWID| ISSUE_T | 1 | 37 | 2 (0)| 00:00:01 |
|* 16 | INDEX UNIQUE SCAN | PK_ISSUE_T | 1 | | 1 (0)| 00:00:01 |
| 17 | HASH GROUP BY | | 125 | 23375 | 101K (2)| 00:20:15 |
|* 18 | FILTER | | | | | |
|* 19 | TABLE ACCESS FULL | SALE_DETAIL_T | 15932 | 2909K| 101K (2)| 00:20:15 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("B"."IWOID"=:B1)
4 - access("C"."IWOID"=:B1)
6 - access("E"."IWOID"=:B1)
8 - access("D"."IWOID"=:B1)
10 - access("E"."IWOID"=:B1)
12 - access("F"."IWOID"=:B1)
14 - access("G"."IWOID"=:B1)
16 - access("H"."IWOID"=:B1)
18 - filter(TO_TIMESTAMP('2014-03-01 00:00:00',:B1)<=TO_TIMESTAMP('2014-03-01
23:59:59',:B2))
19 - filter(INTERNAL_FUNCTION("A"."SALE_TIME")>=TO_TIMESTAMP('2014-03-01
00:00:00',:B1) AND INTERNAL_FUNCTION("A"."SALE_TIME")<=TO_TIMESTAMP('2014-03-01
23:59:59',:B2) AND "A"."TICKET_STATE"=1)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
455964 consistent gets
413600 physical reads
0 redo size
1419 bytes sent via SQL*Net to client
457 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed