AUTOTRACE选项说明

csnd2020

关注

转载最新推荐文章于 2026-02-19 06:35:17 发布 313 阅读 AI 写同款· GEO 优化 ›

AUTOTRACE输出了一些报告，这都是什么意思？
------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 15 | 1560 | 10 (10)| 00:00:01 || 1 | VIEW | | 15 | 1560 | 10 (10)| 00:00:01 || 2 | UNION-ALL | | | | | || 3 | NESTED LOOPS OUTER | | 14 | 784 | 4 (0)| 00:00:01 || 4 | TABLE ACCESS FULL | EMP | 14 | 518 | 3 (0)| 00:00:01 || 5 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 19 | 1 (0)| 00:00:01 ||* 6 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 || 7 | MERGE JOIN ANTI | | 1 | 22 | 6 (17)| 00:00:01 || 8 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 76 | 2 (0)| 00:00:01 || 9 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 ||* 10 | SORT UNIQUE | | 14 | 42 | 4 (25)| 00:00:01 || 11 | TABLE ACCESS FULL | EMP | 14 | 42 | 3 (0)| 00:00:01 |------------------------------------------------------------------------------------------

不同数据库版本输出的东西是不一样的。

COST:CBO在执行查询的时候会为执行计划的每一步估计一个成本值，通常CBO会生成多个执行计划，从中选成本最小的来执行

CARD:是基数的缩写，这是对执行计划中每一步的输出记录数做的一个估计值，比如全表扫描EMP表这一步估计它的输出行数是14行。事实上EMP表总共也只有14行。

BYTES:对于执行计划的每一步，CBO会对它们估计一个返回数据量的大小值，单位是BYTE.

如果说一个执行计划中以上3个指标都没有出现，那么可以肯定该执行计选用的优化器不是CBO,是RBO

recursive calls：为了执行当前的SQL语句，ORACLE需要执行SQL语句的数量。英文翻译为递归调用

db block gets：从当前模式中的缓存区域中读取块的总数量

consistent gets：一致性读的次数，一致性读需要在缓冲区中申请块。一致性读也可能要去读取UNDO中的信息，读UNDO的次数也要计算在一致性读中。

physical reads：物理读的次数，从数据文件到缓冲区

redo size：执行语句期间产生的REDO的大小，单位为BYTES.

bytes sent via SQL*Net to client：从服务器发给客户端的字节数量

bytes received via SQL*Net from client：从客户端接收的字节数量

SQL*Net roundtrips to/from client：发给客户端和从客户端接收的SQL*NET信息，这包含了往返的信息从一个多行结果集中【this includes round-trips for fetchs from a multiple-row result set】

sorts (memory):代表在用户会话中进行的排序，它由SORT_AREA_SIZE参数控制

sorts (disk):当排序的大小超出了用户的排序大小所能承受的最大值时，会在磁盘上进行排序，这里说的磁盘通常指的是临时表空间。

rows processed：修改处理或者SELECT语句返回的行数

一些AUTOTRACE常用指令说明

set autotrace off---关掉AUTOTRACE

set autotrace on explain--不显示统计信息，显示执行计划SQL> select * from emp;
EMPNO ENAME JOB HIREDATE SAL COMM DEPTNO---------- ---------- --------- ------------------ ---------- ---------- ---------- 7369 SCOTT CLERK 17-DEC-80 800 20 7499 SCOTT SALESMAN 20-FEB-81 1600 300 30 7521 SCOTT SALESMAN 22-FEB-81 1250 500 30 7566 SCOTT MANAGER 02-APR-81 2975 20 7654 SCOTT SALESMAN 28-SEP-81 1250 1400 30 7698 SCOTT MANAGER 01-MAY-81 2850 30 7782 SCOTT MANAGER 09-JUN-81 2450 10 7788 SCOTT ANALYST 19-APR-87 3000 20 7839 SCOTT PRESIDENT 17-NOV-81 5000 10 7844 SCOTT SALESMAN 08-SEP-81 1500 0 30 7876 SCOTT CLERK 23-MAY-87 1100 20
EMPNO ENAME JOB HIREDATE SAL COMM DEPTNO---------- ---------- --------- ------------------ ---------- ---------- ---------- 7900 SCOTT CLERK 03-DEC-81 950 30 7902 SCOTT ANALYST 03-DEC-81 3000 20 7934 SCOTT CLERK 23-JAN-82 1500 10
14 rows selected.

Execution Plan----------------------------------------------------------Plan hash value: 3956160932
--------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 14 | 490 | 3 (0)| 00:00:01 || 1 | TABLE ACCESS FULL| EMP | 14 | 490 | 3 (0)| 00:00:01 |--------------------------------------------------------------------------
SQL> set autotrace on statistics--显示统计信息，不显示执行计划

SQL> set autotrace on statistics;

SQL> select * from emp;

EMPNO ENAME JOB HIREDATE SAL COMM DEPTNO

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

7369 SCOTT CLERK 17-DEC-80 800 20

7499 SCOTT SALESMAN 20-FEB-81 1600 300 30

7521 SCOTT SALESMAN 22-FEB-81 1250 500 30

7566 SCOTT MANAGER 02-APR-81 2975 20

7654 SCOTT SALESMAN 28-SEP-81 1250 1400 30

7698 SCOTT MANAGER 01-MAY-81 2850 30

7782 SCOTT MANAGER 09-JUN-81 2450 10

7788 SCOTT ANALYST 19-APR-87 3000 20

7839 SCOTT PRESIDENT 17-NOV-81 5000 10

7844 SCOTT SALESMAN 08-SEP-81 1500 0 30

7876 SCOTT CLERK 23-MAY-87 1100 20

EMPNO ENAME JOB HIREDATE SAL COMM DEPTNO

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

7900 SCOTT CLERK 03-DEC-81 950 30

7902 SCOTT ANALYST 03-DEC-81 3000 20

7934 SCOTT CLERK 23-JAN-82 1500 10

14 rows selected.

Statistics

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

0 recursive calls

0 db block gets

8 consistent gets

0 physical reads

0 redo size

1187 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)

14 rows processed

SQL>

set autotrace on---打开autotrace功能，能显示执行计划和统计信息

set autotrace traceonly--不显示SQL语句执行结果

SQL> set autotrace traceonly;

SQL> select * from dual;

Execution Plan

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

Plan hash value: 272002086

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

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

| 0 | SELECT STATEMENT | | 1 | 2 | 2 (0)| 00:00:01 |

| 1 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 |

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

Statistics

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

0 recursive calls

0 db block gets

3 consistent gets

0 physical reads

0 redo size

407 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

SQL>

set autotrace traceonly statistics--只显示统计信息

SQL> set autotrace traceonly statistics

SQL> select * from emp;

14 rows selected.

Statistics

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

0 recursive calls

0 db block gets

8 consistent gets

0 physical reads

0 redo size

1187 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)

14 rows processed

set autotrace traceonly explain --只显示执行计划

SQL> set autotrace traceonly explain

SQL> select * from emp;

Execution Plan

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

Plan hash value: 3956160932

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

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

| 0 | SELECT STATEMENT | | 14 | 490 | 3 (0)| 00:00:01 |

| 1 | TABLE ACCESS FULL| EMP | 14 | 490 | 3 (0)| 00:00:01 |

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

SQL>

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/15720542/viewspace-731672/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/15720542/viewspace-731672/

标签

#数据库

oracle执行计划方式一：Autotrace自动跟踪功能 4、physical reads 物理读：物理读从磁盘中读取数据块得数量，主要发生得原因：1、数据库高速缓存中不存在这些块。这个值越高说明从磁盘请求大量的数据到buffer cache总，通常意味着存在大量的全表扫描sql语句，会影响数据库的性能，尽量避免全表扫描，全表扫描的sql应添加相应索引，来优化sql；1、recursive calls：用户和系统级别生成得递归调用数，简而言之就是sql中得sql。3、consistent gets 逻辑读：逻辑读从内存中读取数据块的数量，，阅读详情

AUTOTRACE选项说明

相关推荐