Oracle Autotrace

AUTOTRACE


SQL> conn scott/tiger

SQL> set autotrace on
SP2-0618: Cannot find the Session Identifier.  Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report

SQL> @$ORACLE_HOME/sqlplus/admin/plustrce.sql

SQL> grant plustrace to scott;

SQL> conn scott/tiger

顯示結果集以及執行計劃
SQL> set autotrace on 


不顯示結果集，顯示執行計劃
SQL> set autotrace traceonly




SQL> select * from dept
  2  minus
  3  select * from dept1;


    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
Execution Plan
----------------------------------------------------------
Plan hash value: 1627988746


-----------------------------------------------------------------------------
| Id  | Operation           | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |       |     4 |   150 |     8  (63)| 00:00:01 |
|   1 |  MINUS              |       |       |       |            |          |
|   2 |   SORT UNIQUE       |       |     4 |   120 |     4  (25)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| DEPT  |     4 |   120 |     3   (0)| 00:00:01 |
|   4 |   SORT UNIQUE       |       |     1 |    30 |     4  (25)| 00:00:01 |
|   5 |    TABLE ACCESS FULL| DEPT1 |     1 |    30 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------


................




SQL> select * from dept where deptno not in(select deptno from dept1);


    DEPTNO DNAME          LOC
---------- -------------- -------------
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
        10 ACCOUNTING     NEW YORK




Execution Plan
----------------------------------------------------------
Plan hash value: 1948948592


----------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |     4 |   172 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN ANTI NA |       |     4 |   172 |     7  (15)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| DEPT  |     4 |   120 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| DEPT1 |     1 |    13 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------


............




SQL> select * from dept a,dept1 b where a.deptno=b.deptno(+) and b.deptno is null;


    DEPTNO DNAME          LOC               DEPTNO DNAME          LOC
---------- -------------- ------------- ---------- -------------- -------------
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
        10 ACCOUNTING     NEW YORK




Execution Plan
----------------------------------------------------------
Plan hash value: 2254963940


-----------------------------------------------------------------------------
| Id  | Operation           | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |       |     1 |    60 |     7  (15)| 00:00:01 |
|*  1 |  FILTER             |       |       |       |            |          |
|*  2 |   HASH JOIN OUTER   |       |     1 |    60 |     7  (15)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| DEPT  |     4 |   120 |     3   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| DEPT1 |     1 |    30 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------


.........

歡迎關注我的微信公眾號"IT小Chen"，共同學習，共同成長！！！