autotrace使用详解！

来源：互联网发布：海龟交易策略 python 编辑：程序博客网时间：2024/05/20 07:52

查看执行计划、统计信息、执行时间并且返回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_sizeNAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------db_block_size                        integer     8192SQL> set linesize 200SQL> 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