View the execution plan in Oracle

The execution plan in Oracle must be declared. The following is based on Oracle10g and will not be discussed for 8i and earlier versions.
I. Execution form
Generally, we can execute SQL * Plus. In form, if there are two main differences in the output result method, there are two different execution methods.
As for how to use the dbms_xplan package, I do not need to detail it here.
1) execution method 1 -- set autotrace traceonly ..
SQL> set serveroutput on
SQL> set autotrace traceonly
The complete format is:
Set autot [race] {on | off | trace [only]} [exp [lain] [stat [istics]
Then run the command to view the result directly, as shown in the example (some blank space has been manually deleted ):
SQL> select * From tab;
Row 442 has been selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 457676135
--------------------------------------------------------------------------------
| ID | operation | Name | rows | bytes | cost (% CPU) | time |
--------------------------------------------------------------------------------
| 0 | SELECT statement | 1066 | 90610 | 204 (4) | 00:00:03 |
| 1 | nested loops outer | 1066 | 90610 | 204 (4) | 00:00:03 |
| * 2 | table access full | OBJ $ | 1066 | 83148 | 152 (5) | 00:00:02 |
| 3 | Table Access Cluster | tab $ | 1 | 7 | 1 (0) | 00:00:01 |
| * 4 | index unique scan | I _obj # | 1 | 0 (0) | 00:00:01 |
--------------------------------------------------------------------------------
Predicate information (identified by Operation ID ):
---------------------------------------------------

2-filter ("o". "Type #" <= 5 and "o". "Owner #" = userenv ('schemaid') and
"O". "Type #"> = 2 and "o". "linkname" is null)
4-access ("o". "OBJ #" = "T". "OBJ #" (+ ))
Statistics
----------------------------------------------------------
8 recursive cballs
0 dB block gets
1684 consistent gets
0 physical reads
0 redo size
11810 bytes sent via SQL * Net to client
704 bytes encoded ed via SQL * Net From Client
31 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (Disk)
442 rows processed
(Note: The Dark green part is not available in the explain Plan output)
For more information about this command, see <SQL * Plus User's Guide and reference release 10.2> B14357-01.
If you cannot remember, you can use SQL> help set to view available set commands.
2) Explain plain
Execute explain Plan for select * From tab directly;
SQL> set autotrace off;
SQL> explain Plan for select * From tab;
Explained.
SQL> select * from table (dbms_xplan.display );
The specific result is omitted.
This command must refer to the <Oracle Database SQL reference 10g Release 2 (10.2)> B14200-02
For other views, see V $ SQL _workarea, V $ SQL _plan, V $ SQL _plan_statistics, V $ SQL _plan_statistics_all.
Other books that can be referenced are: Oracle database performance tuning Guide (for explain output) and Oracle Database reference (the dynamic performance view mentioned above, which starts with V $ ).
3) Comparison of the two methods
A) The preceding method is simpler, with only one setting and valid for the next time (in the SQL * Plus Environment). The output results are also more detailed, with disadvantages
Is the output result can be saved only with spool.
B) the latter method is a little more troublesome and needs to be executed for each independent SQL statement. However, the advantage is that the output results can be stored in tables because
Dbms_xplan.display is a pipeline table function. each row of the output is of the varchar2 type.
In general, I prefer the set method.
They all use the plan_table table. The script for executing plan_table is ORACLE_HOME \ RDBMS \ admin \ utlxplan. SQL.

Ii. available reference books
1) SQL * Plus User's Guide and reference release 10.2
2) Oracle Database SQL reference 10g Release 2 (10.2)
3) Oracle database performance tuning Guide
4) Oracle Database reference