sqlplus得到执行计划的统计信息

以前在sqlplus得到执行计划都是用

 

set autot on exp

 

这种方式得到的执行计划没有统计信息，而且用explain plan for 也没有统计信息

 

后来在网上查到用

set autotrace on 不仅可以得到执行计划还用统计信息，所有演示下。

 

sys@PRIMARY> conn scott/tiger@primary

已连接。

scott@PRIMARY> set autotrace on;

SP2-0618: 无法找到会话标识符。启用检查 PLUSTRACE 角色

SP2-0611: 启用 STATISTICS 报告时出错

 

--这是没用plustrace这样角色，用sys用户登录执行[ORACLE_HOME]/sqlplus/admin/plustrace.sql文件

查看下这个文件的内容

set echo on

drop role plustrace;
create role plustrace;

grant select on v_$sesstat to plustrace;
grant select on v_$statname to plustrace;
grant select on v_$mystat to plustrace;
grant plustrace to dba with admin option;

set echo off

 

--蓝色字体为脚本里的语句

sys@PRIMARY> @D:/app/263370/product/11.1.0/db_1/sqlplus/admin/plustrce.sql

sys@PRIMARY>

sys@PRIMARY> drop role plustrace;

drop role plustrace

          *

第 1 行出现错误:

ORA-01919: 角色 'PLUSTRACE' 不存在

 

 

sys@PRIMARY> create role plustrace;

 

角色已创建。

 

sys@PRIMARY>

sys@PRIMARY> grant select on v_$sesstat to plustrace;

 

授权成功。

 

sys@PRIMARY> grant select on v_$statname to plustrace;

 

授权成功。

 

sys@PRIMARY> grant select on v_$mystat to plustrace;

 

授权成功。

 

sys@PRIMARY> grant plustrace to dba with admin option;

 

授权成功。

 

sys@PRIMARY>

sys@PRIMARY> set echo off

sys@PRIMARY> grant plustrace to scott;

 

授权成功。

 

sys@PRIMARY> conn scott/tiger@primary

已连接。

 

scott@PRIMARY> set autotrace on;

scott@PRIMARY> select * from dept;

 

    DEPTNO DNAME          LOC

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

        10 ACCOUNTING     sz

        20 RESEARCH       DALLAS

        30 SALES          CHICAGO

 

 

执行计划

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

Plan hash value: 3383998547

 

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

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

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

|   0 | SELECT STATEMENT  |      |     3 |    90 |     3   (0)| 00:00:01 |

|   1 |  TABLE ACCESS FULL| DEPT |     3 |    90 |     3   (0)| 00:00:01 |

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

 

Note

-----

   - dynamic sampling used for this statement

 

 

统计信息

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

          0  recursive calls

          0  db block gets

          8  consistent gets

          0  physical reads

          0  redo size

        623  bytes sent via SQL*Net to client

        416  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          3  rows processed

 

scott@PRIMARY> explain plan for select * from dept;

 

已解释。

 

scott@PRIMARY> select plan_table_output from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

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

 

Plan hash value: 3383998547

 

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

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

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

|   0 | SELECT STATEMENT  |      |     3 |    90 |     3   (0)| 00:00:01 |

|   1 |  TABLE ACCESS FULL| DEPT |     3 |    90 |     3   (0)| 00:00:01 |

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

 

Note

-----

 

PLAN_TABLE_OUTPUT

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

 

   - dynamic sampling used for this statement

 

已选择12行。

 

 

执行计划

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

Plan hash value: 2137789089

 

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

 

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

 

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

 

)| Time     |

 

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

 

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

 

|   0 | SELECT STATEMENT                  |         |  8168 | 16336 |    29   (0

 

)| 00:00:01 |

 

|   1 |  COLLECTION ITERATOR PICKLER FETCH| DISPLAY |       |       |

 |          |

 

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

 

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

 

 

 

统计信息

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

         13  recursive calls

         12  db block gets

         65  consistent gets

          0  physical reads

          0  redo size

       1050  bytes sent via SQL*Net to client

        416  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          1  sorts (memory)

          0  sorts (disk)

         12  rows processed