View the autotrace switch of the execution plan

The autotrace switch is opened in the Sqlplus can also get the target SQL execution plan, but also can get the target SQL at the execution of the resource consumption, that is, by setting the autotrace switch we can observe the target SQL execution of the physical reading, logical reading, The number of redo produced has been ordered.

(1) In the current session of Sqlplus, execute command set autotrace on, the autotrace switch can be opened completely in the current session. In this way, all of the SQL that is subsequently executed in this session will show additional execution plans and resource consumption for these SQL in addition to displaying the results of the SQL execution.

(2) Execute command set Autotrace off in the current session of Sqlplus, you can close the autotrace switch in the current session so that all SQL executed in this session will only display the results of the SQL execution. The autotrace switch default value is off.

(3) Execute set autotrace traceonly in the current session of Sqlplus, which can be opened in the current session in a way that does not display the specific contents of the results of SQL execution, in this way with the set The only difference between autotrace on is that for set Autotrace traceonly, Oracle shows only the number of SQL execution results, not the specifics of the execution results, in which case we tend not to care about the specifics of the results of these SQL executions , but only concerned with their execution plans and resource consumption.

(4) In the current session of Sqlplus, the set autotrace traceonly explain is executed, and the autotrace switch can be opened in the current session in a paradigm that shows only the SQL execution plan. The difference between this and set Autotrace traceonly is that the set Autotrace Traceonly explain does not display the resource consumption and execution results of the target SQL, but only the execution plan of the target SQL.

(5) In the current session of Sqlplus, execute command set autotrace traceonly statistic, you can open the autotrace switch in the current session only to display the SQL resource consumption mode. The only difference between this approach and set Autotrace traceonly is that the set autotrace traceonly statistic does not display the execution plan of the target SQL, but only the number of execution results and the resource consumption of the target SQL.

The commands for setting up the AUTOTRACE switch also follow Oracle's consistent practice of using shorthand, specifically:

Keyword Autotrace can be abbreviated to Autot

Keyword Traceonly can be abbreviated as trace

Keyword explain can be abbreviated as EXP

Keyword statistic can be abbreviated as stat

In accordance with the above shorthand rules, the following wording is equivalent

Set autotrace on shorthand for set Autot on

Set Autotrace off shorthand for set Autot off

Set Autotrace traceonly shorthand for set Autot trace

Set Autotrace traceonly explain shorthand for set Autot trace exp

Set Autotrace traceonly statistic shorthand for set Autot trace stat

Here is also a select empno,ename,dname from emp a,dept b where a.deptno=b.deptno; to illustrate:

First Use Set Autotrace on

Sql> Select Empno,ename,dname from emp a,dept b where a.deptno=b.deptno;

EMPNO ename dname

---------- ---------- --------------

7782 CLARK ACCOUNTING

7839 KING ACCOUNTING

7934 MILLER ACCOUNTING

7566 JONES

7902 FORD

7876 ADAMS

7369 SMITH

7788 SCOTT

7521 WARD SALES

7844 TURNER SALES

7499 ALLEN SALES

EMPNO ename dname

---------- ---------- --------------

7900 JAMES SALES

7698 BLAKE SALES

7654 MARTIN SALES

Rows selected.

Execution Plan

----------------------------------------------------------

Plan Hash value:844388907

----------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |

----------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT |           |   14 |     364 | 6(17) | 00:00:01 |

|  1 | MERGE JOIN |           |   14 |     364 | 6(17) | 00:00:01 |

|   2 | TABLE ACCESS by INDEX rowid|     DEPT |    4 |     52 | 2 (0) | 00:00:01 |

|    3 | INDEX full SCAN |     pk_dept |       4 |     | 1 (0) | 00:00:01 |

|* 4 | SORT JOIN |           |   14 |     182 | 4(25) | 00:00:01 |

|    5 | TABLE ACCESS full |    EMP |   14 |     182 | 3 (0) | 00:00:01 |

----------------------------------------------------------------------------------------

predicate information (identified by Operation ID):

---------------------------------------------------

4-access ("A". ") DEPTNO "=" B "." DEPTNO ")

Filter ("A". ") DEPTNO "=" B "." DEPTNO ")

Statistics

----------------------------------------------------------

0 Recursive calls

0 db Block gets

Ten consistent gets

0 physical Reads

0 Redo Size

941 Bytes sent via sql*net to client

524 Bytes received via sql*net from client

2 sql*net roundtrips To/from Client

1 Sorts (memory)

0 Sorts (disk)

Rows processed

As we can see, executing the Set autotrace on and then executing the target SQL shows the execution plan and resource consumption of the SQL in addition to the specific execution results.

Then use set Autotrace traceonly

Sql> Select Empno,ename,dname from emp a,dept b where a.deptno=b.deptno;

Rows selected.

Execution Plan

----------------------------------------------------------

Plan Hash value:844388907

----------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |

----------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT |           |   14 |     364 | 6(17) | 00:00:01 |

|  1 | MERGE JOIN |           |   14 |     364 | 6(17) | 00:00:01 |

|   2 | TABLE ACCESS by INDEX rowid|     DEPT |    4 |     52 | 2 (0) | 00:00:01 |

|    3 | INDEX full SCAN |     pk_dept |       4 |     | 1 (0) | 00:00:01 |

|* 4 | SORT JOIN |           |   14 |     182 | 4(25) | 00:00:01 |

|    5 | TABLE ACCESS full |    EMP |   14 |     182 | 3 (0) | 00:00:01 |

----------------------------------------------------------------------------------------

predicate information (identified by Operation ID):

---------------------------------------------------

4-access ("A". ") DEPTNO "=" B "." DEPTNO ")

Filter ("A". ") DEPTNO "=" B "." DEPTNO ")

Statistics

----------------------------------------------------------

0 Recursive calls

0 db Block gets

Ten consistent gets

0 physical Reads

0 Redo Size

941 Bytes sent via sql*net to client

524 Bytes received via sql*net from client

2 sql*net roundtrips To/from Client

1 Sorts (memory)

0 Sorts (disk)

Rows processed

As we can see, executing the Set autotrace traceonly and then executing the target SQL, except that the specific contents of the SQL execution result are not shown, the others are identical to the display results using set autotrace on.

Let's use set Autotrace traceonly explain

Sql> Set Autotrace traceonly explain

Sql> Select Empno,ename,dname from emp a,dept b where a.deptno=b.deptno;

Execution Plan

----------------------------------------------------------

Plan Hash value:844388907

----------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |

----------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT |           |   14 |     364 | 6(17) | 00:00:01 |

|  1 | MERGE JOIN |           |   14 |     364 | 6(17) | 00:00:01 |

|   2 | TABLE ACCESS by INDEX rowid|     DEPT |    4 |     52 | 2 (0) | 00:00:01 |

|    3 | INDEX full SCAN |     pk_dept |       4 |     | 1 (0) | 00:00:01 |

|* 4 | SORT JOIN |           |   14 |     182 | 4(25) | 00:00:01 |

|    5 | TABLE ACCESS full |    EMP |   14 |     182 | 3 (0) | 00:00:01 |

----------------------------------------------------------------------------------------

predicate information (identified by Operation ID):

---------------------------------------------------

4-access ("A". ") DEPTNO "=" B "." DEPTNO ")

Filter ("A". ") DEPTNO "=" B "." DEPTNO ")

We can see that executing the set Autotrace traceonly explain and then executing the target SQL shows only the execution plan for that SQL.

Finally we'll use set autotrace traceonly statistic

Sql> Select Empno,ename,dname from emp a,dept b where a.deptno=b.deptno;

Rows selected.

Statistics

----------------------------------------------------------

0 Recursive calls

0 db Block gets

Ten consistent gets

0 physical Reads

0 Redo Size

941 Bytes sent via sql*net to client

524 Bytes received via sql*net from client

2 sql*net roundtrips To/from Client

1 Sorts (memory)

0 Sorts (disk)

Rows processed

As we can see, execute the Set autotrace traceonly statistic before executing the target SQL, showing only the number of execution results of the SQL and the consumption of the resources consumed.

View the autotrace switch for execution plan