autotrace使用详解!

本文介绍如何使用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

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值