弹冠相庆

October 10, 2010

一个CBO错用索引的例子

Filed under: Uncategorized — Corey @ 21:20
Tags:

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’)<TO_DATE(’12/06/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’)<TO_DATE(’12/06/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的问题

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: