使用autotrace查看执行计划
发表于：2025-11-07 作者：千家信息网编辑
千家信息网最后更新 2025年11月07日，set autotrace off不产生autotrace报告，默认值set autotrace on explainautotrace报告只展示最优的执行方式（optimizer execution
千家信息网最后更新 2025年11月07日使用autotrace查看执行计划
SQL> set autotrace on explain;
-插入数据测试是否执行了sql
SQL> insert into scott.emp values('5566','explain','','','','','','');
1 row created.
-已经表明执行了sql
-只展示了执行计划
Execution Plan
----------------------------------------------------------
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | 100 | 1 (0)| ss00:00:01 |
| 1 | LOAD TABLE CONVENTIONAL | EMP | | | | |
---------------------------------------------------------------------------------
SQL> select * from scott.emp where empno=5566;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
5566 explain
Execution Plan
----------------------------------------------------------
Plan hash value: 2949544139
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 87 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 87 | 2 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=5566)
SQL> set autotrace on statistics;
SQL> insert into scott.emp values('6677','statistics','','','','','','');
1 row created.
-说明执行了sql
-只展示sql执行的统计信息
Statistics
----------------------------------------------------------
52 recursive calls
5 db block gets
80 consistent gets
0 physical reads
520 redo size
839 bytes sent via SQL*Net to client
826 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
10 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select * from scott.emp where empno=6677;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
6677 statistics
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
869 bytes sent via SQL*Net to client
512 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> set autotrace on;
SQL> insert into scott.emp values('8899','on','','','','','','');
1 row created.
-相当于set autotrace on explain和set autotrace on statistics的和，既输出执行计划，也输出统计信息，同时也执行sql
Execution Plan
----------------------------------------------------------
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | 100 | 1 (0)| 00:00:01 |
| 1 | LOAD TABLE CONVENTIONAL | EMP | | | | |
---------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
45 recursive calls
5 db block gets
77 consistent gets
0 physical reads
512 redo size
839 bytes sent via SQL*Net to client
816 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
10 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select * from scott.emp where empno=8899;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
8899 on
Execution Plan
----------------------------------------------------------
Plan hash value: 2949544139
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 87 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 87 | 2 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=8899)
Statistics
----------------------------------------------------------
9 recursive calls
0 db block gets
18 consistent gets
0 physical reads
0 redo size
861 bytes sent via SQL*Net to client
512 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> set autotrace traceonly;
SQL> insert into scott.emp values('9900','traceonly','','','','','','');
1 row created.
-与set autotrace on 类似，都会输出执行计划和统计信息
Execution Plan
----------------------------------------------------------
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | 100 | 1 (0)| 00:00:01 |
| 1 | LOAD TABLE CONVENTIONAL | EMP | | | | |
---------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
108 recursive calls
5 db block gets
191 consistent gets
1 physical reads
520 redo size
839 bytes sent via SQL*Net to client
825 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
15 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select * from scott.emp where empno=9900;
-与set autotrace on不同的是，没有输出查询结果
Execution Plan
----------------------------------------------------------
Plan hash value: 2949544139
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 87 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 87 | 2 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=9900)
Statistics
----------------------------------------------------------
9 recursive calls
0 db block gets
18 consistent gets
0 physical reads
0 redo size
867 bytes sent via SQL*Net to client
512 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
set autotrace off
不产生autotrace报告，默认值set autotrace on explain
autotrace报告只展示最优的执行方式（optimizer execution path）SQL> set autotrace on explain;
-插入数据测试是否执行了sql
SQL> insert into scott.emp values('5566','explain','','','','','','');
1 row created.
-已经表明执行了sql
-只展示了执行计划
Execution Plan
----------------------------------------------------------
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | 100 | 1 (0)| ss00:00:01 |
| 1 | LOAD TABLE CONVENTIONAL | EMP | | | | |
---------------------------------------------------------------------------------
SQL> select * from scott.emp where empno=5566;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
5566 explain
Execution Plan
----------------------------------------------------------
Plan hash value: 2949544139
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 87 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 87 | 2 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=5566)
set autotrace on statistics
autotrace报告sql执行统计信息（SQL statement execution statistics. ）SQL> set autotrace on statistics;
SQL> insert into scott.emp values('6677','statistics','','','','','','');
1 row created.
-说明执行了sql
-只展示sql执行的统计信息
Statistics
----------------------------------------------------------
52 recursive calls
5 db block gets
80 consistent gets
0 physical reads
520 redo size
839 bytes sent via SQL*Net to client
826 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
10 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select * from scott.emp where empno=6677;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
6677 statistics
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
869 bytes sent via SQL*Net to client
512 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
set autotrace on
autotrace报告包含了最优执行方式和sql执行统计信息SQL> set autotrace on;
SQL> insert into scott.emp values('8899','on','','','','','','');
1 row created.
-相当于set autotrace on explain和set autotrace on statistics的和，既输出执行计划，也输出统计信息，同时也执行sql
Execution Plan
----------------------------------------------------------
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | 100 | 1 (0)| 00:00:01 |
| 1 | LOAD TABLE CONVENTIONAL | EMP | | | | |
---------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
45 recursive calls
5 db block gets
77 consistent gets
0 physical reads
512 redo size
839 bytes sent via SQL*Net to client
816 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
10 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select * from scott.emp where empno=8899;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
8899 on
Execution Plan
----------------------------------------------------------
Plan hash value: 2949544139
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 87 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 87 | 2 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=8899)
Statistics
----------------------------------------------------------
9 recursive calls
0 db block gets
18 consistent gets
0 physical reads
0 redo size
861 bytes sent via SQL*Net to client
512 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
set autotrace traceonly
和set autotrace on类似，但是不输出用户查询的结果SQL> set autotrace traceonly;
SQL> insert into scott.emp values('9900','traceonly','','','','','','');
1 row created.
-与set autotrace on 类似，都会输出执行计划和统计信息
Execution Plan
----------------------------------------------------------
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | 100 | 1 (0)| 00:00:01 |
| 1 | LOAD TABLE CONVENTIONAL | EMP | | | | |
---------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
108 recursive calls
5 db block gets
191 consistent gets
1 physical reads
520 redo size
839 bytes sent via SQL*Net to client
825 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
15 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select * from scott.emp where empno=9900;
-与set autotrace on不同的是，没有输出查询结果
Execution Plan
----------------------------------------------------------
Plan hash value: 2949544139
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 87 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 87 | 2 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=9900)
Statistics
----------------------------------------------------------
9 recursive calls
0 db block gets
18 consistent gets
0 physical reads
0 redo size
867 bytes sent via SQL*Net to client
512 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
信息
统计
输出
报告
方式
结果
查询
不同
同时
数据
用户
测试
数据库的安全要保护哪些东西
数据库安全各自的含义是什么
生产安全数据库录入
数据库的安全性及管理
数据库安全策略包含哪些
海淀数据库安全审计系统
建立农村房屋安全信息数据库
易用的数据库客户端支持安全管理
连接数据库失败ssl安全错误
数据库的锁怎样保障安全
辽宁大连电信dns服务器
中国电科网络安全评论
小学生网络安全绘画简单
软件开发及检测项目公司
配置与管理web服务器笔记
钉钉 服务器 安全性
桐柏软件开发文档
护苗网络安全第一课总结
浪潮服务器开启raid模式
java数据库 抽样算法
什么事数据库应用系统
m公司是一家主营软件开发
网络安全预判
网络安全法知识竞赛和答案
光纤网络技术的论文
从事单片机软件开发需读研吗
论坛 admin 数据库
算命网站源码及数据库
拱墅计算机网络技术咨询服务
软件开发数据的差异和统一
旅游同业软件开发
网络技术公司策划方案
浪潮服务器设置硬盘界面
军事网络安全架构
物竞数据库DMAP
软件开发学习多久
拓扑科思互联网科技的卡能用吗
数据库连接串点
通达oa服务器断电
煎饼网络技术公司
