1.set atuot 用法

数据库中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的窗口效果