ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 稳定执行计划的使用

稳定执行计划的使用

原创 Linux操作系统 作者:anchen211 时间:2009-06-11 19:55:04 0 删除 编辑

数据库版本:10.2.0.1

C:\Documents and Settings\an.chen>sqlplus ca/ca001

SQL*Plus: Release 10.2.0.1.0 - Production on 星期四 6月 11 19:45:15 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> set autotrace on
SQL> CREATE OR REPLACE OUTLINE my_demo ON select count(object_id) from t;

大纲已创建。

SQL>
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 |
---------------------------------------------------------------------------


统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        690  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
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> create index t_index on t(object_id);

索引已创建。

SQL> select count(object_id) from t;

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


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

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

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

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

|   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

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

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

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

|   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


统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        117  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
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>
SQL> ALTER SESSION SET USE_STORED_OUTLINES = TRUE;

会话已更改。

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 |
---------------------------------------------------------------------------

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


统计信息
----------------------------------------------------------
         33  recursive calls
        125  db block gets
        711  consistent gets
          1  physical reads
        560  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
          2  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> select /*+ index(t_index) +/ count(object_id) from t;
select /*+ index(t_index) +/ count(object_id) from t
       *
第 1 行出现错误:
ORA-01742: 备注错误终止


SQL> select /*+ index(t_index) */ count(object_id) from t;

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


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

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

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

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

|   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
          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
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> CREATE OR REPLACE OUTLINE my_demo ON select /*+ index(t_index) */ count(object_id) from t;

大纲已创建。

SQL> select count(object_id) from t;

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


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

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

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

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

|   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
|

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

 

统计信息
----------------------------------------------------------
          4  recursive calls
          0  db block gets
        118  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
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>

解释说明:

我在表T未建索引之前先存储了他的执行计划,毫无疑问,应该是全表扫描.

然后创建一个索引.

这时再执行select count(object_id) from t;执行计划就变为快速全索引扫描.

这时如果使用存储的执行计划,再查询时仍然是执行全表扫描.

最后修改存储执行计划为快速全索引扫描.再执行同样的语句,执行计划也作了相应的改变.

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

请登录后发表评论 登录
全部评论

注册时间:2008-11-04

  • 博文量
    129
  • 访问量
    175593