使用autotrace查看执行计划
发表于：2024-12-03 作者：千家信息网编辑
千家信息网最后更新 2024年12月03日，set autotrace off不产生autotrace报告，默认值set autotrace on explainautotrace报告只展示最优的执行方式（optimizer execution
千家信息网最后更新 2024年12月03日使用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安全错误
数据库的锁怎样保障安全
雅马哈新增数据库元件
5G 运营商 网络安全
虚拟机如何更改服务器
数据库每张表都是关键字吗
网络安全法3大意义
预防网络安全的四字词语
小软件开发测试
网络安全学校标语
java数组的读取数据库
关于网络安全净化意识
数据库文件修改时间怎么修改
微信群数据库
网络安全金融标准
上市网络安全审查一般多少天
武汉网络安全基地专场
征途服务器搭建
高薪网络技术支持 维护
三级网络技术需要二级证书吗
广电网络技术视频教程
海岛奇兵经验等级数据库
服务器没开可以加载资源吗
湛江数据链软件开发价目表
明家联合 佳云科技 互联网
一个硕士毕业改行做软件开发
和龙软件开发哪家强
计算机网络技术五年大专
给数据库每一条数据添加一个数据
GPS时间服务器说明书
中国自己的软件开发工具
查网站服务器ip
