Several Methods for Oracle to obtain an execution plan

To obtain the execution plan in the cache library, you can directly query the dynamic performance view v $ SQL _plan and v $ SQL _plan_statistics_all, but the more convenient method is SQL _id and sub-

To obtain the execution plan in the cache library, you can directly query the dynamic performance view v $ SQL _plan and v $ SQL _plan_statistics_all, but the more convenient method is SQL _id and sub-

1. Estimate the execution Plan-Explain Plan

The Explain plan uses an SQL statement as the input to obtain the execution plan of the SQL statement and store the execution plan output to the schedule.

First, add explain plan for before the SQL statement you want to execute. Then, store the generated execution plan to the schedule. The statement is as follows:

Explain plan for SQL statement

Then, query the generated execution plan in the schedule. The statement is as follows:

Select * from table (dbms_xplan.display );

Note: The Explain plan only generates the execution plan and does not actually execute the SQL statement. Therefore, the execution plan may be inaccurate because:

1) The current environment may be different from the environment when the execution plan is generated;
2) the data type of the bound variable is not considered;
3) No variable pegging.

2. query the execution plan cached in the memory (dbms_xplan.display_cursor)

If you want to obtain the real execution plan of the executed or just-executed SQL statement (that is, to obtain the execution plan in the library cache), you can query it in the dynamic performance view. The method is as follows:

1) obtain the SQL statement cursor

A cursor is divided into a parent cursor and a child cursor. The parent cursor is represented by the SQL _id (or combined address and hash_value) field, and the Child cursor is represented by the child_number field.

If the SQL statement is running, you can obtain its cursor information from v $ session, for example:
Select status, SQL _id, SQL _child_number from v $ session where status = 'active' and ....

If you know that an SQL statement contains certain keywords, you can obtain its cursor information from the v $ SQL view, such:
Select SQL _id, child_number, SQL _text from v $ SQL where SQL _text like '% keyword %'

2) obtain the execution plan in the database cache

To obtain the execution plan in the cache library, you can directly query the dynamic performance view v $ SQL _plan and v $ SQL _plan_statistics_all. However, the more convenient method is to use SQL _id and sub-cursor as parameters and execute the following statement:
Select * from table (dbms_xplan.display_cursor ('SQL _ id', child_number ));

3) obtain the previous execution plan:
Set serveroutput off
Select * from table (dbms_xplan.display_cursor (null, null, 'allstats last '));

3. query the historical Execution Plan (dbms_xplan.display_awr)

AWR regularly saves the execution plan in the dynamic performance View to the dba_hist_ SQL _plan view. If you want to view the historical execution plan, you can query it as follows:
Select * from table (dbms_xplan.display_awr ('SQL _ id ');

4. Using sqlplus for SQL development is (Autotrace)

Set autotrace is a function of the sqlplus tool and can only be used in sessions connected through sqlplus. It is very suitable for testing the performance of SQL statements during development. It has the following parameters to choose from:

Set autotrace off ---------------- the execution plan and statistics are not displayed. This is the default mode.
Set autotrace on explain ------ only show the optimizer execution plan
Set autotrace on statistics -- only display STATISTICS
Set autotrace on ------------------- both the execution plan and statistics are displayed.
Set autotrace traceonly ------ not actually executed. Only the expected execution plan is displayed, which is the same as the explain plan.

5. Generate a Trace file to query detailed execution plans (SQL _Trace, 10046)

SQL _TRACE can be used as an initialization parameter at the instance level or only at the session level. Enabling SQL _TRACE at the instance level will track activities of all processes, including background processes and all user processes, this usually causes serious performance problems. Therefore, we use SQL _trace to track the current process in the following way:

SQL> alter session set SQL _trace = true;
... The SQL statement to be tracked...
SQL> alter session set SQL _trace = false;
To track other processes, you can use the system package DBMS_SYSTEM. SET_ SQL _TRACE_IN_SESSION provided by Oracle. For example:
SQL> exec dbms_system.set_ SQL _trace_in_session (sid, serial #, true) -- start tracing
SQL> exec dbms_system.set_ SQL _trace_in_session (sid, serial #, false) -- end the trail

After the trace file is generated, use tkprof to convert the trace file generated by SQL trace into a readable format. The syntax is as follows:
Tkprof inputfile outputfile

The 10046 event is an upgraded version of SQL _TRACE. It is also a Trace session that generates a Trace file, but its content is more detailed,