How Oracle execution plans are acquired

1. Use explain plan for to get the execution plan

Plan  for <sql>; Select *  from Table (Dbms_xplan.display ());

The results are as follows:

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

Advantages

1. No need for real SQL execution, quick and easy

Disadvantages

1. The execution plan here is not a real execution plan.
2. Unable to get statistics about the runtime (how many logical reads, how many times recursive calls, how many times the physical reads)
3. Cannot tell how many times the table has been accessed
4. Unable to determine how many rows of data were processed

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

2. Use Autotrace to get the execution plan

 set  AUTOTRACE off  --  set  AUTOTRACE on  EXPLAIN  Only show execution plan and execution result  set  AUTOTRACE on  Span style= "color: #0000ff;" >statistics  --  displays only the statistics and execution results of the execution  Span style= "color: #0000ff;" >set  AUTOTRACE --  contains execution plans, statistics, and execution results  set  AUTOTRACE traceonly --  

When using SET AUTOTRACE on EXPLAIN, the execution results are displayed, and the execution plan,

When using SET AUTOTRACE on STATISTICS, the execution results, and statistics, are displayed.

When using SET AUTOTRACE on, display execution results, execution plan and statistics,

Show execution plan and statistics when using SET AUTOTRACE traceonly

--------------------------------------------------------------------------------------------------------------- ------
Advantages

1. You can output the relevant statistics of the runtime (how many logical reads, how many times recursive calls, how many times the physical reading of the case);

Disadvantages

1. Get not a real execution plan
2. You cannot see how many times the table has been accessed.

--------------------------------------------------------------------------------------------------------------- ------
3.

How Oracle execution plans are acquired