首页 > 代码库 > SQL Tuning 基础概述01 - autotrace的设定

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

2024-07-12 10:36:42   222人阅读

1.autotrace的设定

SQL> set autotraceUsage: 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 expSQL> select * from t_jingyu;Elapsed: 00:00:00.04Execution 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.89Execution 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 processedSQL> --试验表明set autot trace exp不真正执行sql显示的执行计划，set autot trace 执行了sql显示的执行计划。


联系
我们