Execution plan in Oracle-view

The content is mainly from the reading study notes, the following records the common query execution plan method.

2.2 How to view the execution plan
1.explain Plan
2.dbms_xplan Bag
3.autotrace
4.10046 Events
5.10053 Events
6.awr/statspack report (@?/rdbms/admin/awrsqrpt)
7. Script (Display_cursor_9i.sql)

2.2.1 Explain plan
Explain plan for SQL
SELECT * FROM table (dbms_xplan.display);
Sql> explain plan for SELECT * from EMP;
has been explained.

Sql> select * FROM table (dbms_xplan.display);

Plan_table_output
---------------------------------------------------------------------------

Plan Hash value:3956160932

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
--------------------------------------------------------------------------
| 0 |      SELECT STATEMENT |    |   12 |     468 | 3 (0) | 00:00:01 |
|  1 | TABLE ACCESS full|    EMP |   12 |     468 | 3 (0) | 00:00:01 |
--------------------------------------------------------------------------

8 rows have been selected.

The principle is that Oracle writes the execution plan of explain plan's SQL to plan_table$, executes the query table (dbms_xplan.display), and formats the output.
Plan_table$ is an on commit preserve rows of global temporary table, each session can only see their own, non-interference.

2.2.2 Dbms_xplan Bag
Depending on the scenario, there are four ways to do this:
1.select * FROM table (dbms_xplan.display);
2.select * FROM table (Dbms_xplan.display_cursor (Null,null, ' advanced '));
3.select * FROM table (dbms_xplan.display_cursor (' Sql_id/hash_value ', Child_cursor_number, ' advanced ');
4.select * FROM table (Dbms_xplan.display_awr (' sql_id '));

The first method is explain plan.
The second approach is to look at the execution plan for the SQL you just executed.
The third method is to view the execution plan for the specified SQL, which requires sql_id or Hash_value, and Child_number (V$sql).
Select Sql_text,sql_id,hash_value,child_number from V$sql where sql_text like '%xxxx% ';
The fourth method is to view all historical execution plans for the specified SQL, and the second, third method requires its SQL execution plan in the shared pool, if it is out of the shared pool (V$sqlarea), as long as it is captured to the AWR Repository, you can view it in the fourth way. (The Version_count representative in V$sqlarea has several execution plans)
Select Sql_text,sql_id,version_count,executions from V$sqlarea where sql_text like ' xxx '; -Can query to
exec dbms_workload_repository.create_snapshot; --Acquisition of AWR
alter system flush Shared_pool; -Clean the shared pool
Select Sql_text,sql_id,version_count,executions from V$sqlarea where sql_text like ' xxx '; -Cannot be queried
It is not possible to view the second and third methods at this time. However, the fourth method cannot display the predicate condition, which is imperfect.

2.2.3 Autotrace
Set Autotrace {off|on|traceonly}
[Explain]
[Statistics]
Not only the execution plan but also the resource consumption can be obtained.
Set autotrace on = Set Auto on-result set + execution plan + consumption resource amount
Set Autotrace off = set Autot off--off
Set Autotrace traceonly = Set auto trace-execution Plan + consumption resource amount
Set Autotrace traceonly explain = set Autot trace exp--Execution plan
Set Autotrace traceonly statistics = set Autot trace stat-consumption resource amount

2.2.4 10046 events and Tkprof
1. Activating 10046 Events
Alter session SET Events ' 10046 Trace name context forever,level 12 ';
Oradebug Setmypid;
Oradebug Event 10046 Trace name context Forever,level 12;
2. Execute SQL
3. Closing 10046 Events
Alter session SET Events ' 10046 Trace name context off '
Oradebug Event 10046 Trace name context off
4.tkprof tracefile outputfile
Note that oradebug can only be used by SYS users, not very convenient.

using Display_cursor_9i.sql
@/xxx/display_cursor_9i.sql Hash_value Child_number
Using Printsql
Creating a process using the SYS user @/xxx/printsql.prc
Set serveroutput on size 1000000
exec printsql (xxxxx, ' SID ')
exec printsql (xxxxx, ' SPID ')

2.3 Real Execution Plan
Explain plan, set Autotrace, select * from table (Dbms_xplan.display) may not be allowed, mainly because the binding variable, in the case of the default open binding variable to spy on bind peeking, The execution plan for the above method is only semi-finished.

2.4 Execution Plan execution order
First from the beginning of the continuous look to the right, until you see the rightmost side of the place, for not tied, on the right to perform first, if you see the juxtaposition, from the top down to see, for the parallel part, on the first execution.

Execution plan in Oracle-view