autotrace使用详解！

最新推荐文章于 2017-04-14 17:21:25 发布

事后诸葛亮

最新推荐文章于 2017-04-14 17:21:25 发布

阅读量1.2k

点赞数

CC 4.0 BY-SA版权

分类专栏： execution plan 文章标签： sql access table disk buffer integer

本文链接：https://blog.youkuaiyun.com/zq9017197/article/details/6966153

execution plan 专栏收录该内容

27 篇文章

订阅专栏

本文介绍如何使用Oracle SQL的setautotrace命令来查看SQL语句的执行计划、统计信息及执行时间，帮助优化数据库性能。

查看执行计划、统计信息、执行时间并且返回sql结果集：

SQL> set autotrace on;
SQL> set timing on;
SQL> select count(*) from t;

  COUNT(*)
----------
     50295

已用时间:  00: 00: 00.01

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

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


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

查看执行计划、统计信息、执行时间不返回sql结果集：

SQL> set timing on;
SQL> set autotrace traceonly;
SQL> select * from t2;

已选择402344行。

已用时间:  00: 00: 20.66

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

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   402K|    33M|  1240   (3)| 00:00:15 |
|   1 |  TABLE ACCESS FULL| T2   |   402K|    33M|  1240   (3)| 00:00:15 |
--------------------------------------------------------------------------


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

只看执行计划、执行时间不返回sql结果集：

SQL> set timing on;
SQL> set autotrace traceonly explain;
SQL> select * from t;
已用时间:  00: 00: 00.01

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

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 50295 |  4273K|   161   (3)| 00:00:02 |
|   1 |  TABLE ACCESS FULL| T    | 50295 |  4273K|   161   (3)| 00:00:02 |
--------------------------------------------------------------------------

只看统计信息、执行时间不返回sql结果集：

SQL> set timing on;
SQL> set autotrace traceonly statistics;
SQL> select * from t;

已选择50295行。

已用时间:  00: 00: 02.59

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

查看帮助信息：

SQL> set autotrace
用法: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]

如何开看统计信息：

SQL> show parameter db_block_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_size                        integer     8192
SQL> set linesize 200
SQL> alter system flush shared_pool;      --清空shared_pool

系统已更改。

SQL> alter system flush buffer_cache;    --清空buffer_cache

系统已更改。

SQL> set timing on;
SQL> set autotrace traceonly;
SQL> select * from t2 order by object_name;

已选择402344行。

已用时间:  00: 00: 23.79      --执行了23.79秒

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

-----------------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |   402K|    33M|       |  9361   (2)| 00:01:53 |
|   1 |  SORT ORDER BY     |      |   402K|    33M|    92M|  9361   (2)| 00:01:53 |
|   2 |   TABLE ACCESS FULL| T2   |   402K|    33M|       |  1240   (3)| 00:00:15 |
-----------------------------------------------------------------------------------


统计信息
----------------------------------------------------------
        525  recursive calls
         30  db block gets
       5606  consistent gets
      11129  physical reads
          0  redo size
   13462598  bytes sent via SQL*Net to client
     295442  bytes received via SQL*Net from client
      26824  SQL*Net roundtrips to/from client
          4  sorts (memory)
          1  sorts (disk)
     402344  rows processed

执行了23.79秒。

消耗的内存：5606*8192/1024/1024=43.7M

I/O消耗：11129*8192/1024/1024=86.9M

简而言之：
select * from ...........where ....... consistent gets
update * from ..........where........ db block gets