Sql*Plus的set autotrace用法学习笔记[@more@]Sql*Plus中Autotrace使用小结：
我们都习惯使用一些界面非常好的工具，毫无疑问对于初学者和提高工作效率
都有帮，但对于那些高手来说都是使用用命令窗口。我以前长期使用pl/sql
developer进行过程、查询语句等工作，非常方便，表名、常用命令、函数只
要输入几个字母，系统就会自动带出来，我用了半年后发现对一个系统的表名
能够正确地书写出来还不至三分之一，当然常用命令也好不了多少。从那天开
始我就决心要转移到命令窗口和文本编辑工具中来工作，提高对系统了解的熟
练程度。

set autotrace off --关闭所有的跟踪报表，默认为off

set autotrace语句的使用主要分为两种情况：
1、既要显示查询结果，又要显示执计划或统计信息;
2、只显示执行计划或统计信息，无需反馈和显示查询结果;
二者的区别主要是通过on和traceonly来控制的，如果set autotace后跟on，则
为第一种情况，如果set auotrace后跟traceonly，则为第二种情况，下面为具
体示例：

ttx@TTX>set autotrace off
ttx@TTX>set autotrace on
ttx@TTX>select count(1) from ttx_temp;

COUNT(1)
----------
50182

Execution Plan
----------------------------------------------------------
Plan hash value: 2082060479

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

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
694 consistent gets
0 physical reads
0 redo size
413 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

ttx@TTX>set autotrace off
ttx@TTX>set autotrace on explain
ttx@TTX>select count(1) from ttx_temp;

COUNT(1)
----------
50182

Execution Plan
----------------------------------------------------------
Plan hash value: 2082060479

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

ttx@TTX>set autotrace off
ttx@TTX>set autotrace on statistics
ttx@TTX>select count(1)
2 from ttx_temp;

COUNT(1)
----------
50182

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
694 consistent gets
0 physical reads
0 redo size
413 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

ttx@TTX>set autotrace off
ttx@TTX>set autotrace traceonly
ttx@TTX>select count(1) from ttx_temp;

Execution Plan
----------------------------------------------------------
Plan hash value: 2082060479

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

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
694 consistent gets
0 physical reads
0 redo size
413 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

ttx@TTX>set autotrace off
ttx@TTX>set autotrace traceonly exaplain
SP2-0158: unknown SET option "exaplain"
ttx@TTX>set autotrace traceonly explain
ttx@TTX>select count(1)
2 from ttx_temp;

Execution Plan
----------------------------------------------------------
Plan hash value: 2082060479

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

ttx@TTX>set autotrace off
ttx@TTX>set autotrace traceonly statistics
ttx@TTX>select count(1)
2 from ttx_temp;

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
694 consistent gets
0 physical reads
0 redo size
413 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

ttx@TTX>set autotrace off
ttx@TTX>spool off

ITPub博客

Sql*Plus的set autotrace用法学习笔记

最新文章