Oracle set autotrace 功能理解对比

1930阅读　0评论2013-07-10　快跑蚂蚁
分类：Oracle

AUTOTRACE是一个SQL*Plus工具,用于跟踪SQL的执行计划,收集执行时所耗用资源的统计信息,是SQL优化工具之一，是分析SQL的执行计划，执行效率的一个非常简单方便的工具，在绝大多数情况下，也是非常有用的工具。利用AutoTrace工具提供的SQL执行计划和执行状态可以为我们优化SQL的时候提供优化的依据，以及优化效果的明显的对比效果。

Autotrace几个常用选项的说明:
SET AUTOTRACE OFF ---------------- 不生成AUTOTRACE 报告，这是缺省模式
SET AUTOTRACE ON EXPLAIN ------ AUTOTRACE只显示优化器执行路径报告
SET AUTOTRACE ON STATISTICS -- 只显示执行统计信息
SET AUTOTRACE ON ----------------- 包含执行计划和统计信息
SET AUTOTRACE TRACEONLY ------ 同set autotrace on，但是不显示查询输出 

OFF模式不用多说了，这里我们举例说明 SET AUTOTRACE ON 和 SET AUTOTRACE TRACEONLY的区别，让大家加深AUTOTRACE的理解

在OFF的情况下执行如下语句，输入下面结果：

点击(此处)折叠或打开

  1. SQL> select * from hr.jobs;

  3. JOB_ID JOB_TITLE MIN_SALARY MAX_SALARY
  4. ---------- ----------------------------------- ---------- ----------
  5. AD_PRES President 20080 40000
  6. AD_VP Administration Vice President 15000 30000
  7. AD_ASST Administration Assistant 3000 6000
  8. FI_MGR Finance Manager 8200 16000
  9. FI_ACCOUNT Accountant 4200 9000
  10. AC_MGR Accounting Manager 8200 16000
  11. AC_ACCOUNT Public Accountant 4200 9000
  12. SA_MAN Sales Manager 10000 20080
  13. SA_REP Sales Representative 6000 12008
  14. PU_MAN Purchasing Manager 8000 15000
  15. PU_CLERK Purchasing Clerk 2500 5500

  17. JOB_ID JOB_TITLE MIN_SALARY MAX_SALARY
  18. ---------- ----------------------------------- ---------- ----------
  19. ST_MAN Stock Manager 5500 8500
  20. ST_CLERK Stock Clerk 2008 5000
  21. SH_CLERK Shipping Clerk 2500 5500
  22. IT_PROG Programmer 4000 10000
  23. MK_MAN Marketing Manager 9000 15000
  24. MK_REP Marketing Representative 4000 9000
  25. HR_REP Human Resources Representative 4000 9000
  26. PR_REP Public Relations Representative 4500 10500
然后设置SET AUTOTRACE ON后，同样执行上列SQL语句

点击(此处)折叠或打开

  1. SQL> set autotrace on
  2. SQL> select * from hr.jobs;

  4. JOB_ID JOB_TITLE MIN_SALARY MAX_SALARY
  5. ---------- ----------------------------------- ---------- ----------
  6. AD_PRES President 20080 40000
  7. AD_VP Administration Vice President 15000 30000
  8. AD_ASST Administration Assistant 3000 6000
  9. FI_MGR Finance Manager 8200 16000
  10. FI_ACCOUNT Accountant 4200 9000
  11. AC_MGR Accounting Manager 8200 16000
  12. AC_ACCOUNT Public Accountant 4200 9000
  13. SA_MAN Sales Manager 10000 20080
  14. SA_REP Sales Representative 6000 12008
  15. PU_MAN Purchasing Manager 8000 15000
  16. PU_CLERK Purchasing Clerk 2500 5500

  18. JOB_ID JOB_TITLE MIN_SALARY MAX_SALARY
  19. ---------- ----------------------------------- ---------- ----------
  20. ST_MAN Stock Manager 5500 8500
  21. ST_CLERK Stock Clerk 2008 5000
  22. SH_CLERK Shipping Clerk 2500 5500
  23. IT_PROG Programmer 4000 10000
  24. MK_MAN Marketing Manager 9000 15000
  25. MK_REP Marketing Representative 4000 9000
  26. HR_REP Human Resources Representative 4000 9000
  27. PR_REP Public Relations Representative 4500 10500

  29. 19 rows selected.


  32. Execution Plan
  33. ----------------------------------------------------------
  34. Plan hash value: 944056911

  36. --------------------------------------------------------------------------
  37. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
  38. --------------------------------------------------------------------------
  39. | 0 | SELECT STATEMENT | | 19 | 627 | 3 (0)| 00:00:01 |
  40. | 1 | TABLE ACCESS FULL| JOBS | 19 | 627 | 3 (0)| 00:00:01 |
  41. --------------------------------------------------------------------------


  44. Statistics
  45. ----------------------------------------------------------
  46.           0 recursive calls
  47.           0 db block gets
  48.           9 consistent gets
  49.           0 physical reads
  50.           0 redo size
  51.        1700 bytes sent via SQL*Net to client
  52.         534 bytes received via SQL*Net from client
  53.           3 SQL*Net roundtrips to/from client
  54.           0 sorts (memory)
  55.           0 sorts (disk)
  56.          19 rows processed
可以看到和OFF情况下，多了Execution Plan和Statistics信息

然后我们再看下SET AUTOTRACE TRACEONLY情况下，执行同样ＳＱＬ语句的结果：

点击(此处)折叠或打开

  1. SQL> set autotrace traceonly
  2. SQL> select * from hr.jobs;

  4. 19 rows selected.


  7. Execution Plan
  8. ----------------------------------------------------------
  9. Plan hash value: 944056911

  11. --------------------------------------------------------------------------
  12. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
  13. --------------------------------------------------------------------------
  14. | 0 | SELECT STATEMENT | | 19 | 627 | 3 (0)| 00:00:01 |
  15. | 1 | TABLE ACCESS FULL| JOBS | 19 | 627 | 3 (0)| 00:00:01 |
  16. --------------------------------------------------------------------------


  19. Statistics
  20. ----------------------------------------------------------
  21.           0 recursive calls
  22.           0 db block gets
  23.           9 consistent gets
  24.           0 physical reads
  25.           0 redo size
  26.        1700 bytes sent via SQL*Net to client
  27.         534 bytes received via SQL*Net from client
  28.           3 SQL*Net roundtrips to/from client
  29.           0 sorts (memory)
  30.           0 sorts (disk)
  31.          19 rows processed
可以看到没有显示查询结果，只显示出来执行计划和统计信息。




上一篇：oracle手动建库
下一篇：理解DBA_blockers