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 | ------------------------------------------------------------------------------------------
一些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--显示统计信息，不显示执行计划
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
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 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
--------------------------------------------------------------------------
| 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>
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/15720542/viewspace-731672/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/15720542/viewspace-731672/
扫一扫
321
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
