SQL Tuning 基础概述01 - Autotrace的设定

1.autotrace的设定

SQL> set autotrace

Usage: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]

set autot on

--打开autotrace，之后执行的sql，会显示sql执行结果、执行计划、统计信息

set autot on exp

--会显示sql执行结果、执行计划

set autot on stat

--会显示sql执行结果、统计信息

set autot trace

--只显示执行计划、统计信息

set autot trace exp

--只显示执行计划（可能不准,sql查询并没有真正执行）

set autot trace stat

--只显示统计信息

set autot off

--关闭autotrace

2.实验验证 set autot trace exp 没有真正执行查询类sql：

SQL> set autot trace exp

SQL> select * from t_jingyu;

Elapsed: 00:00:00.04

Execution Plan

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

Plan hash value: 2809386205

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

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

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

|   0 | SELECT STATEMENT  |          |  1864K|    69M|   981   (2)| 00:00:12 |

|   1 |  TABLE ACCESS FULL| T_JINGYU |  1864K|    69M|   981   (2)| 00:00:12 |

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

Note

-----

   - dynamic sampling used for this statement (level=2)

SQL> set autot trace

SQL> select * from t_jingyu;

2097152 rows selected.

Elapsed: 00:00:24.89

Execution Plan

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

Plan hash value: 2809386205

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

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

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

|   0 | SELECT STATEMENT  |          |  1864K|    69M|   981   (2)| 00:00:12 |

|   1 |  TABLE ACCESS FULL| T_JINGYU |  1864K|    69M|   981   (2)| 00:00:12 |

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

Note

-----

   - dynamic sampling used for this statement (level=2)

Statistics

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

          0  recursive calls

          0  db block gets

     143066  consistent gets

       3484  physical reads

          0  redo size

   51171186  bytes sent via SQL*Net to client

    1538429  bytes received via SQL*Net from client

     139812  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

    2097152  rows processed

SQL> --试验表明set autot trace exp不真正执行sql显示的执行计划，set autot trace 执行了sql显示的执行计划。