Analysis of several methods for acquiring execution plans in Oracle _oracle

1. Estimated implementation plan-Explain plans
Explain plan takes the SQL statement as input, gets the execution plans for the SQL statement, and stores the execution plan output in the schedule table.
first, in front of the SQL statement you want to execute, add explain plan for, where the resulting execution plans are stored in the schedule, with the following statement:
Explain plan for SQL statement
then, in the schedule, query the execution plan just generated, as follows:
SELECT * FROM table (dbms_xplan.display);
Note: Explain plan only generates execution plans and does not actually execute SQL statements, so the resulting execution plan may not be allowed because:

1 The current environment may be different from the environment when the execution plan is generated;
2) does not consider the data type of the binding variable;
3) do not make variable peep.

2. Query in-memory cache execution Plan (dbms_xplan.display_cursor)
If you want to get the actual execution plan of the SQL statement that is executing or just executed (that is, get the execution plan in the library cache), you can query in dynamic performance view. The method is as follows:

1) Get the cursor of the SQL statement
Cursors are divided into parent and child cursors, and the parent cursor is represented by the sql_id (or joint address and hash_value) fields, and the child cursors are represented by the Child_number field.

If the SQL statement is running, you can obtain its cursor information from the v$session, such as:
Select Status, sql_id, Sql_child_number from v$session where status= ' ACTIVE ' and ....

If you know that the SQL statement contains certain keywords, you can obtain its cursor information from the V$sql view, such as:
Select sql_id, Child_number, sql_text from V$sql where sql_text like '% keyword% '

2 Get the execution plan in the library cache
To get 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 it is more convenient to execute the following statement with SQL_ID and child cursors as arguments:
SELECT * FROM table (dbms_xplan.display_cursor (' sql_id ', child_number));

3 Get the previous execution plan:
Set Serveroutput off
SELECT * FROM table (Dbms_xplan.display_cursor (Null,null, ' allstats last '));

3. Query History Implementation Plan (DBMS_XPLAN.DISPLAY_AWR)
AWR will periodically save the execution plan in the dynamic performance view to the Dba_hist_sql_plan view, and if you want to view the history execution plan, you can use the following method to query:
SELECT * FROM table (Dbms_xplan.display_awr (' sql_id ');

4. In Sqlplus SQL development is (AUTOTRACE)
Set Autotrace is a feature of the Sqlplus tool that can only be used in a session through a Sqlplus connection and is ideal for testing the performance of SQL statements at development time, with the following parameters to choose from:

SET autotrace off----------------does not display execution plans and statistics, which is the default mode
SET autotrace on EXPLAIN------Show only optimizer execution plan
SET autotrace on STATISTICS--Displays only statistical information
SET autotrace on-----------------execution plans and statistics are displayed at the same time
SET autotrace traceonly------Not really executed, show only the expected execution plan, with explain

5. Generate Trace file query detailed execution plan (sql_trace, 10046)
Sql_trace can be enabled at the instance level as an initialization parameter, or only at the session level, enabling Sql_trace at the instance level can cause the activities of all processes to be tracked, including background processes and all user processes, which often result in more serious performance problems, so in general, We use Sql_trace to track the current process by using the following methods:

Sql>alter session Set Sql_trace=true;
... The SQL statement being tracked ...
Sql>alter session Set Sql_trace=false;
If you want to track other processes, you can dbms_system the system packages provided by Oracle. Set_sql_trace_in_session to implement, for example :
sql> exec dbms_system.set_sql_trace_in_session (sid,serial#,true)-Start tracking
sql> exec dbms_system.set_sql_trace_in_session (sid,serial#,false)--End trace

After generating the trace file, use the Tkprof tool to convert the trace file generated by SQL Trace into a readable format, as follows:
tkprof inputfile outputfile
10046 event is an upgraded version of Sql_trace , it is also a trace session that generates a trace file, just the contents of it in more detail,