Oracle SQL Execution Plan

There are many ways to get the execution plan of Oracle. Here we only introduce a few of them.
(1) To get plan from Memory
For SQL ID:
Select * from table (dbms_xplan.display_cursor ('& SQL _id '));
Select * from table (dbms_xplan.display_cursor ('& SQL _id', NULL, 'all '));
For SQL ID, Child Cursor:
Select * from table (dbms_xplan.display_cursor ('& SQL _id', & child_number, 'all '));

For SQL Text:
Select t .*
From v $ SQL s, table (dbms_xplan.display_cursor (s. SQL _id, s. child_number, 'all') t
Where s. SQL _text like '% & querystring % ';

(2) From AWR:
For SQL ID:
Select * from table (dbms_xplan.display_awr ('& SQL _id '));
Select * from table (dbms_xplan.display_awr ('& SQL _id', NULL, NULL, 'all '));

For SQL ID, Plan Hash Value in the current database:
Select * from table (dbms_xplan.display_awr ('& SQL _id', '& plan_hash_value', NULL, 'all '));

For SQL ID, Plan Hash Value in a different database ID:
Select * from table (dbms_xplan.display_awr ('& SQL _id', '& plan_hash_value', & dbid, 'all '));

For SQL ID that was executed during a certain period:
Select t .*
From (select distinct SQL _id, plan_hash_value, dbid
From dba_hist_sqlstat
Where SQL _id = '& SQL _id'
And snap_id between & begin_snap and & end_snap) s,
Table (dbms_xplan.display_awr (s. SQL _id, s. plan_hash_value, s. dbid, 'all') t;

For SQL Text:
Select t .*
From (select distinct r. SQL _id, r. plan_hash_value, r. dbid
From dba_hist_sqltext q, dba_hist_sqlstat r
Where q. SQL _id = r. SQL _id
And q. SQL _text like '% & querystring %') s,
Table (dbms_xplan.display_awr (s. SQL _id, s. plan_hash_value, s. dbid, 'all') t;

(3) construct an execution plan by querying the V $ SQL _PLAN View
You can use this view to obtain the SQL Execution Plan saved in the cache of the current database instance library. Because the plan is saved in the memory, the information that this view can view is limited, if the information has been swapped out of memory, it cannot be viewed.
Select '| Operation | Object Name | Rows | Bytes | Cost |'
As "Explain Plan in library cache:" from dual
Union all
Select rpad ('| substr (lpad ('', 1 * (depth-1) | operation |
Decode (options, null, '','' | options), 1, 35), 36, '') | '|
Rpad (decode (id, 0 ,'----------------------------',
Substr (decode (substr (object_name, 1, 7), 'sys _ LE _ ', null, object_name)
| '', 1, 30), 31,'') | '| lpad (decode (cardinality, null ,'',
Decode (sign (cardinality-1000),-1, cardinality | '',
Decode (sign (cardinality-1000000),-1, trunc (cardinality/1000) | 'k ',
Decode (sign (cardinality-1000000000),-1, trunc (cardinality/1000000) | 'M ',
Trunc (cardinality/1000000000) | 'G'), 7, '') | '|
Lpad (decode (bytes, null ,'',
Decode (sign (bytes-1024),-1, bytes | '',
Decode (sign (bytes-1048576),-1, trunc (bytes/1024) | 'k ',
Decode (sign (bytes-1073741824),-1, trunc (bytes/1048576) | 'M ',
Trunc (bytes/1073741824) | 'G'), 6, '') | '|
Lpad (decode (cost, null, '', decode (sign (cost-10000000),-1, cost | '',
Decode (sign (cost-1000000000),-1, trunc (cost/1000000) | 'M ',
Trunc (cost/1000000000) | 'G'), 8, '') | 'as" Explain plan"
From v $ SQL _plan sp
Where sp. hash_value = & hash_value;