SQL> SELECT MIN(UNIQUE_ID) FROM sms.VIEW_Q_MT_107710 A WHERE UNIQUE_ID>(SELECT B.UNIQUE_ID_END FROM sms.Q_INFO B WHERE B.Q_KEY='MT_107710')
2 /Elapsed: 00:00:37.25Execution Plan
----------------------------------------------------------
Plan hash value: 755455310--------------------------------------------------------------------------------
------------------| Id | Operation | Name | Rows | Bytes | Cost
(%CPU)| Time |--------------------------------------------------------------------------------
------------------| 0 | SELECT STATEMENT | | 1 | 14 | 10273
(2)| 00:02:04 || 1 | SORT AGGREGATE | | 1 | 14 |
| ||* 2 | VIEW | index$_join$_003 | 48673 | 665K| 10272
(2)| 00:02:04 ||* 3 | HASH JOIN | | | |
| ||* 4 | INDEX FAST FULL SCAN | IDX_AREA_ID_Q_MT | 48673 | 665K| 3777
(2)| 00:00:46 || 5 | INDEX FAST FULL SCAN | PK_Q_MT_PART | 48673 | 665K| 3773
(2)| 00:00:46 || 6 | TABLE ACCESS BY INDEX ROWID| Q_INFO | 1 | 15 | 1
(0)| 00:00:01 ||* 7 | INDEX UNIQUE SCAN | PK_Q_INFO | 1 | | 0
(0)| 00:00:01 |--------------------------------------------------------------------------------
------------------
Predicate Information (identified by operation id):
--------------------------------------------------- 2 - filter("AREA_ID"='107710' AND "UNIQUE_ID"> (SELECT "B"."UNIQUE_ID_END" FR
OM "SMS"."Q_INFO" "B" WHERE "B"."Q_KEY"='MT_107710'))
3 - access(ROWID=ROWID)
4 - filter("AREA_ID"='107710')
7 - access("B"."Q_KEY"='MT_107710')
Statistics
----------------------------------------------------------
307 recursive calls
2 db block gets
6987 consistent gets
20832 physical reads
132 redo size
421 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
1 rows processed两个SQL语句都是通过索引查询的,而且两个select分开执行每个所需的时间都不到1s,和起来执行居然要37s。看了下执行计划,CBO选择了hash join,而且所选择的基表是大的那个表,问题应该就是出在了这里。
询问一下这个应该改写优化
2 /Elapsed: 00:00:37.25Execution Plan
----------------------------------------------------------
Plan hash value: 755455310--------------------------------------------------------------------------------
------------------| Id | Operation | Name | Rows | Bytes | Cost
(%CPU)| Time |--------------------------------------------------------------------------------
------------------| 0 | SELECT STATEMENT | | 1 | 14 | 10273
(2)| 00:02:04 || 1 | SORT AGGREGATE | | 1 | 14 |
| ||* 2 | VIEW | index$_join$_003 | 48673 | 665K| 10272
(2)| 00:02:04 ||* 3 | HASH JOIN | | | |
| ||* 4 | INDEX FAST FULL SCAN | IDX_AREA_ID_Q_MT | 48673 | 665K| 3777
(2)| 00:00:46 || 5 | INDEX FAST FULL SCAN | PK_Q_MT_PART | 48673 | 665K| 3773
(2)| 00:00:46 || 6 | TABLE ACCESS BY INDEX ROWID| Q_INFO | 1 | 15 | 1
(0)| 00:00:01 ||* 7 | INDEX UNIQUE SCAN | PK_Q_INFO | 1 | | 0
(0)| 00:00:01 |--------------------------------------------------------------------------------
------------------
Predicate Information (identified by operation id):
--------------------------------------------------- 2 - filter("AREA_ID"='107710' AND "UNIQUE_ID"> (SELECT "B"."UNIQUE_ID_END" FR
OM "SMS"."Q_INFO" "B" WHERE "B"."Q_KEY"='MT_107710'))
3 - access(ROWID=ROWID)
4 - filter("AREA_ID"='107710')
7 - access("B"."Q_KEY"='MT_107710')
Statistics
----------------------------------------------------------
307 recursive calls
2 db block gets
6987 consistent gets
20832 physical reads
132 redo size
421 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
1 rows processed两个SQL语句都是通过索引查询的,而且两个select分开执行每个所需的时间都不到1s,和起来执行居然要37s。看了下执行计划,CBO选择了hash join,而且所选择的基表是大的那个表,问题应该就是出在了这里。
询问一下这个应该改写优化
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货