配置环境 - 关于AUTOTRACE

最新推荐文章于 2022-03-24 17:37:24 发布

ZhouZhiqiangFreesky

最新推荐文章于 2022-03-24 17:37:24 发布

阅读量546

点赞数

CC 4.0 BY-SA版权

分类专栏： Expert Oracle Database - Architecture 9i and 10g Programming Techniques and Solutions 文章标签： statistics sql access table disk oracle

本文链接：https://blog.youkuaiyun.com/ZhouZhiqiangFreesky/article/details/1787557

Expert Oracle Database - Architecture 9i and 10g Programming Techniques and Solutions 专栏收录该内容

29 篇文章

订阅专栏

本文详细介绍了Oracle数据库中AUTOTRACE系统变量的不同设置及其产生的效果，包括关闭AUTOTRACE、仅显示执行计划、仅显示执行统计信息、同时显示执行计划和统计信息等多种场景。

AUTOTRACE系统变量：

SET AUTOTRACE OFF：不生成AUTOTRACE报告。这是默认设置。

MUZIYU@MYDB> SET AUTOTRACE OFF;
MUZIYU@MYDB> SELECT * FROM Dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SET AUTOTRACE ON EXPLAIN：AUTOTRACE报告只显示优化器执行路径。

MUZIYU@MYDB> SET AUTOTRACE ON EXPLAIN;
MUZIYU@MYDB> SELECT * FROM Dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON


Execution Plan
----------------------------------------------------------
          0                    SELECT STATEMENT Optimizer=CHOOSE
          1                  0   TABLE ACCESS (FULL) OF 'DEPT'

SET AUTOTRACE ON STATISTICS：AUTOTRACE报告只显示SQL语句的执行统计信息。

MUZIYU@MYDB> SET AUTOTRACE ON STATISTICS;
MUZIYU@MYDB> SELECT * FROM Dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON


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

SET AUTOTRACE ON：报告显示优化器执行路径和SQL语句的执行统计信息。

MUZIYU@MYDB> SET AUTOTRACE ON;
MUZIYU@MYDB> SELECT * FROM Dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON


Execution Plan
----------------------------------------------------------
          0                    SELECT STATEMENT Optimizer=CHOOSE
          1                  0   TABLE ACCESS (FULL) OF 'DEPT'




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

SET AUTOTRACE TRACEONLY：这与SET AUTOTRACE ON类似，但不显示用户的查询输出（如果有的话）。

MUZIYU@MYDB> SET AUTOTRACE TRACEONLY;
MUZIYU@MYDB> SELECT * FROM Dept;

Execution Plan
----------------------------------------------------------
          0                    SELECT STATEMENT Optimizer=CHOOSE
          1                  0   TABLE ACCESS (FULL) OF 'DEPT'




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

软件环境：Windows XP SP3+Oracle 9i R2，[ORACLE_HOME]=C:/Oracle/Ora92