我这稳定执行计划为啥不行?-CSDN博客

基本做法参照BITI的文章,http://blog.itpub.net/category/330/831

可结果发现并不对.过程如下:

SQL> set autotrace on
SQL> drop index t_index;

索引已删除。

SQL> alter session set create_stored_outlines = my_demo;

会话已更改。

SQL> select count(object_id) from t;

COUNT(OBJECT_ID)
----------------
49811

执行计划
----------------------------------------------------------
Plan hash value: 2966233522

---------------------------------------------------------------------------
| Id | Operation          | Name | Rows | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     5 |   159   (2)| 00:00:02 |
|   1 | SORT AGGREGATE    |      |     1 |     5 |            |          |
|   2 |   TABLE ACCESS FULL| T    | 49811 |   243K|   159   (2)| 00:00:02 |
---------------------------------------------------------------------------

统计信息
----------------------------------------------------------
        175 recursive calls
          0 db block gets
        707 consistent gets
          0 physical reads
          0 redo size
        418 bytes sent via SQL*Net to client
        385 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          4 sorts (memory)
          0 sorts (disk)
          1 rows processed

SQL> alter session set create_stored_outlines = false;

会话已更改。

SQL> create index t_index on t(object_id);

索引已创建。

SQL> select count(object_id) from t;

COUNT(OBJECT_ID)
----------------
49811

执行计划
----------------------------------------------------------
Plan hash value: 789015712

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

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

| 0 | SELECT STATEMENT | | 1 | 5 | 27 (4)| 00:00:01
|

| 1 | SORT AGGREGATE | | 1 | 5 | |
|

| 2 | INDEX FAST FULL SCAN| T_INDEX | 49811 | 243K| 27 (4)| 00:00:01
|

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

统计信息
----------------------------------------------------------
          1 recursive calls
          0 db block gets
        117 consistent gets
        110 physical reads
          0 redo size
        418 bytes sent via SQL*Net to client
        385 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          1 rows processed

SQL> alter session set use_stored_outlines = my_demo;

会话已更改。

SQL> select count(object_id) from t;

COUNT(OBJECT_ID)
----------------
49811

执行计划
----------------------------------------------------------
Plan hash value: 789015712

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

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

| 0 | SELECT STATEMENT | | 1 | 5 | 27 (4)| 00:00:01
|

| 1 | SORT AGGREGATE | | 1 | 5 | |
|

| 2 | INDEX FAST FULL SCAN| T_INDEX | 49811 | 243K| 27 (4)| 00:00:01
|

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

Note
-----
- outline "SYS_OUTLINE_09061117273662511" used for this statement

统计信息
----------------------------------------------------------
          9 recursive calls
          3 db block gets
        118 consistent gets
          0 physical reads
        520 redo size
        418 bytes sent via SQL*Net to client
        385 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          1 rows processed

SQL>

说明:

我的数据库版本是10g,我发现10g在select count(*)时,不论是否建索引,也不论是否作统计数据收集,执行计划都是做全表扫描.所以只能换一种测试方法.存储做全表扫描的执行计划,在建立索引后,select count(索引列)也希望他走全表扫描来验证稳定执行计划.

但测试结果发现并非如预期一样.原因未知.

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

转载于:http://blog.itpub.net/9375/viewspace-605931/