channels表，索引是channels_unq(svr_grp_id, channels_record_id, snapshot_time)

执行如下语句

SELECT channels.channel_record_id

,SUM(channels.max_viewers) AS viewer_sum

FROM channels

WHERE channels.snapshot_time > to_date('11/25/2008 23:50'

,'MM/DD/YYYY HH24:MI:SS')

AND channels.snapshot_time <= to_date('12/06/2008 23:50'

,'MM/DD/YYYY HH24:MI:SS')

GROUP BY channels.channel_record_id;

474 rows selected.

Elapsed: 02:06:21.62

Execution Plan

----------------------------------------------------------

Plan hash value: 443160641

---------------------------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 39 | 365 (1)| 00:00:05 | | |

| 1 | HASH GROUP BY | | 1 | 39 | 365 (1)| 00:00:05 | | |

|* 2 | FILTER | | | | | | | |

| 3 | PARTITION RANGE ITERATOR | | 1 | 39 | 364 (0)| 00:00:05 | KEY | KEY |

| 4 | TABLE ACCESS BY LOCAL INDEX ROWID| CHANNELS | 1 | 39 | 364 (0)| 00:00:05 | KEY | KEY |

|* 5 | INDEX SKIP SCAN | CHANNELS_UNQ | 1 | | 364 (0)| 00:00:05 | KEY | KEY |

---------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - filter(TO_DATE('11/25/2008 23:50','MM/DD/YYYY HH24:MI:SS')

5 - access("CHANNELS"."SNAPSHOT_TIME">TO_DATE('11/25/2008 23:50','MM/DD/YYYY HH24:MI:SS') AND "CHANNELS"."SNAPSHOT_TIME"<=TO_DATE('12/06/2008 23:50','MM/DD/YYYY HH24:MI:SS'))

filter("CHANNELS"."SNAPSHOT_TIME">TO_DATE('11/25/2008 23:50','MM/DD/YYYY HH24:MI:SS') AND "CHANNELS"."SNAPSHOT_TIME"<=TO_DATE('12/06/2008 23:50','MM/DD/YYYY HH24:MI:SS'))

Statistics

----------------------------------------------------------

8100 recursive calls

0 db block gets

577374676 consistent gets

4631561 physical reads

59904 redo size

11269 bytes sent via SQL*Net to client

833 bytes received via SQL*Net from client

33 SQL*Net roundtrips to/from client

91 sorts (memory)

0 sorts (disk)

474 rows processed

用了两个多小时，可以看到，在第5步不正确地走了索引 channels_unq

把查询语句加个hint，强制用全表扫描

SELECT /*+ FULL(channels) */ channels.channel_record_id

,SUM(channels.max_viewers) AS viewer_sum

FROM channels

WHERE channels.snapshot_time > to_date('11/25/2008 23:50'

,'MM/DD/YYYY HH24:MI:SS')

AND channels.snapshot_time <= to_date('12/06/2008 23:50'

,'MM/DD/YYYY HH24:MI:SS')

GROUP BY channels.channel_record_id;

474 rows selected.

Elapsed: 00:12:33.59

Execution Plan

----------------------------------------------------------

Plan hash value: 4197359631

-------------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 39 | 1770K (1)| 05:54:04 | | |

| 1 | HASH GROUP BY | | 1 | 39 | 1770K (1)| 05:54:04 | | |

|* 2 | FILTER | | | | | | | |

| 3 | PARTITION RANGE ITERATOR| | 1 | 39 | 1770K (1)| 05:54:04 | KEY | KEY |

|* 4 | TABLE ACCESS FULL | CHANNELS | 1 | 39 | 1770K (1)| 05:54:04 | KEY | KEY |

-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - filter(TO_DATE('11/25/2008 23:50','MM/DD/YYYY HH24:MI:SS')

4 - filter("CHANNELS"."SNAPSHOT_TIME">TO_DATE('11/25/2008 23:50','MM/DD/YYYY HH24:MI:SS') AND "CHANNELS"."SNAPSHOT_TIME"<=TO_DATE('12/06/2008 23:50','MM/DD/YYYY HH24:MI:SS'))

Statistics

----------------------------------------------------------

1 recursive calls

0 db block gets

2762409 consistent gets

2391028 physical reads

4236 redo size

11269 bytes sent via SQL*Net to client

833 bytes received via SQL*Net from client

33 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

474 rows processed

这次走全表了，用时12分钟左右。

没有时间仔细去找原因，猜想：

1. 可能是最新统计信息没收集

2. 由于使用了绑定变量(在这个例子中简化了，没体现出来)，可能是bind varible peeking的问题

来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/441887/viewspace-663610/ ，如需转载，请注明出处，否则将追究法律责任。

ITPub博客

一个CBO错用索引的例子

最新文章