一个CBO错用索引的例子

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

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

| Id  | Operation                            | Name         | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |

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

|   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

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

| Id  | Operation                  | Name     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |

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

|   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博客 ” ，链接：http://blog.itpub.net/441887/viewspace-663610/，如需转载，请注明出处，否则将追究法律责任。

0

0

分享到：

上一篇： Linux IO性能测试

下一篇： Oracle10gR2在RHEL 5下开启异步IO

请登录后发表评论 登录

全部评论

<%=items[i].createtime%>

<%=items[i].content%>

<%if(items[i].items.items.length) { %>

<%for(var j=0;j

<%=items[i].items.items[j].createtime%> 回复

<%=items[i].items.items[j].username%>   回复   <%=items[i].items.items[j].tousername%>： <%=items[i].items.items[j].content%>

<%}%> <%if(items[i].items.total > 5) { %>

还有<%=items[i].items.total-5%>条评论 ) data-count=1 data-flag=true>点击查看

<%}%>

<%}%>

<%}%>

转载于:http://blog.itpub.net/441887/viewspace-663610/