数据库中set autotrace有哪些用法
Usage: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
说明：
前三个参数{OFF | ON | TRACE[ONLY]} 里必需选择一个，而且只能选择一个
后两个参数[EXP[LAIN]] [STAT[ISTICS]]是可选的，也可以都不选择
TRACE[ONLY]的含意是只显示explain和statistic，不显示SQL的结果集，带TRACE[ONLY]的参数的以下的4,5,6最常用的是第4种
只要带上off，后面的[EXP[LAIN]] [STAT[ISTICS]]就无效了
1:set autotrace on;
显示：记录集+explain+statistics
scott@ORCLPDB01 2023-03-18 13:55:15> set autot on
scott@ORCLPDB01 2023-03-18 13:55:23> select * from emp where empno = 7566;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
Elapsed: 00:00:00.02
Execution Plan
----------------------------------------------------------
Plan hash value: 2949544139
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 38 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=7566)
Statistics
----------------------------------------------------------
66 recursive calls
0 db block gets
124 consistent gets
4 physical reads
0 redo size
964 bytes sent via SQL*Net to client
387 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed
2:set autotrace on explain
显示：记录集+explain
scott@ORCLPDB01 2023-03-18 13:55:38> set autot on exp
scott@ORCLPDB01 2023-03-18 13:57:01> select * from emp where empno = 7566;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 2949544139
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 38 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=7566)
3:set autotrace on statistic
显示 ：记录集+statistics
scott@ORCLPDB01 2023-03-18 13:57:04> set autot on stat
scott@ORCLPDB01 2023-03-18 13:57:53> select * from emp where empno = 7566;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
Elapsed: 00:00:00.00
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
964 bytes sent via SQL*Net to client
387 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
4:set autotrace traceonly
显示： expain+statistics(不显示数据)
scott@ORCLPDB01 2023-03-18 13:58:49> set autot trace
scott@ORCLPDB01 2023-03-18 13:59:05> select * from emp where empno = 7566;
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 2949544139
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 38 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=7566)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
964 bytes sent via SQL*Net to client
391 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
5：set autotrace traceonly statistics
显示： statistics
scott@ORCLPDB01 2023-03-18 13:59:47> set autot trace stat
scott@ORCLPDB01 2023-03-18 14:00:06> select * from emp where empno = 7566;
Elapsed: 00:00:00.01
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
964 bytes sent via SQL*Net to client
387 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
6：set autotrace traceonly explain
显示： explain
scott@ORCLPDB01 2023-03-18 13:59:45> select * from emp where empno = 7566;
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 2949544139
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 38 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=7566)
7:set autotrace off
scott@ORCLPDB01 2023-03-18 14:00:07> set autot off
scott@ORCLPDB01 2023-03-18 14:00:33> select * from emp where empno = 7566;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
Elapsed: 00:00:00.00
显示：只显示记录集,这也就是默认的SQL PLUS的窗口效果
