Use autotrace to view the execution plan
Set autotrace off does not generate autotrace reports, default
The set autotrace on explainautotrace report only shows the best way to perform (optimizer execution path)
SQL > set autotrace on explain
-insert data to test whether sql has been executed
SQL > insert into scott.emp values ('5566', 'five hundred and sixty six')
1 row created.
-it has been indicated that sql has been implemented
-only shows the execution plan
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 statisticsautotrace reports sql execution statistics (SQL statement execution statistics. )
SQL > set autotrace on statistics
SQL > insert into scott.emp values ('6677 records, statistics, records, books, books
1 row created.
-indicates that sql has been executed
-display only the statistics performed by 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
The set autotrace onautotrace report contains the best execution method and sql execution statistics
SQL > set autotrace on
SQL > insert into scott.emp values ('8899, June, week, month, month, week, week, day, day, week, week, week,
1 row created.
-equivalent to the sum of set autotrace on explain and set autotrace on statistics, outputting both execution plans and statistics, as well as 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 is similar to set autotrace on, but does not output the results of user queries
SQL > set autotrace traceonly
SQL > insert into scott.emp values ('9900', '9900', 'traceonlyly.'
1 row created.
-similar to set autotrace on, execution plans and statistics are output
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
-unlike set autotrace on, no query results are output
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 >
