oracle查看sql执行计划和统计信息       

        分类：            oracle 2011-08-30 11:16 960人阅读 评论(0) 收藏 举报 sql oracle access table disk --获取sql的执行计划以及统计信息，不显示查询信息

SQL> set autotrace traceonly;

SQL> select * from test;

执行计划

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

Plan hash value: 1357081020

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

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

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

|   0 | SELECT STATEMENT  |      |     2 |   130 |     3   (0)| 00:00:01 |

|   1 |  TABLE ACCESS FULL| TEST |     2 |   130 |     3   (0)| 00:00:01 |

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

Note

-----

   - dynamic sampling used for this statement

统计信息

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

          0  recursive calls

          0  db block gets

          8  consistent gets

          6  physical reads

          0  redo size

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

          2  rows processed

==============================================================

--只显示统计信息

SQL> set autot traceonly stat;

SQL> select * from test;

统计信息

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

          0  recursive calls

          0  db block gets

          8  consistent gets

          0  physical reads

          0  redo size

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

          2  rows processed

==============================================================

--只显示执行计划

SQL> set autot traceonly exp;

SQL> select * from test;

执行计划

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

Plan hash value: 1357081020

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

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

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

|   0 | SELECT STATEMENT  |      |     2 |   130 |     3   (0)| 00:00:01 |

|   1 |  TABLE ACCESS FULL| TEST |     2 |   130 |     3   (0)| 00:00:01 |

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

Note

-----

   - dynamic sampling used for this statement

==============================================================

--开启sql跟踪,显示查询结果和执行计划，以及统计信息

SQL> set autot on;

SQL> select * from test;

      T_ID   T_NAME

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

         1      t1

         2      t2

执行计划

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

Plan hash value: 1357081020

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

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

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

|   0 | SELECT STATEMENT  |      |     2 |   130 |     3   (0)| 00:00:01 |

|   1 |  TABLE ACCESS FULL| TEST |     2 |   130 |     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

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

          2  rows processed

==============================================================

--关闭sql跟踪,只显示查询结果

SQL> set autot off;

SQL> select * from test;

      T_ID    T_NAME

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

         1        t1

         2        t2 from: http://blog.csdn.net/jlds123/article/details/6731851