Create your own Oracle interpretation plan
1. Explain the plan
When an explain plan is used to generate an expected execution plan for a query, the output includes the following:
Each table accessed by SQL;
Method for accessing each table;
The join method used by each data source to be joined;
All operations to be completed listed in order;
Lists the predicates of each step in the plan.
Explain plan
Select t1.name, t2.grade
From table1 t1
Left join table2 t2
On t1.id = t2.id
Where t1.id = 704
;
Explained
Query Result: (all operations related to the predicates are marked with an asterisk)
Select * from table (dbms_xplan.display );
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2814340807
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%
--------------------------------------------------------------------------------
| 0 | select statement | 1 | 141 | 8
| X 1 | hash join outer | 1 | 141 | 8
| 1 | table access by index rowid | TABLE1 | 1 | 115 | 2
| * 3 | index range scan | INDEX_TABLE1_ID | 1 | 1
| * 4 | table access full | TABLE2 | 1 | 26 | 5
--------------------------------------------------------------------------------
Predicate Information (identified by operation id ):
---------------------------------------------------
1-access ("T1". "ID" = "T2". "ID" (+ ))
3-access ("T1". "ID" = 704)
4-filter ("T2". "ID" (+) = 704)
Note
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
-Dynamic sampling used for this statement (level = 2)
22 rows selected
2. Contents of the system interpretation schedule
Desc plan_table
Name Type Nullable Default Comments
------------------------------------------------------
STATEMENT_ID VARCHAR2 (30) Y
PLAN_ID NUMBER Y
TIMESTAMP DATE Y
REMARKS VARCHAR2 (4000) Y
OPERATION VARCHAR2 (30) Y
OPTIONS VARCHAR2 (255) Y
OBJECT_NODE VARCHAR2 (128) Y
OBJECT_OWNER VARCHAR2 (30) Y
OBJECT_NAME VARCHAR2 (30) Y
OBJECT_ALIAS VARCHAR2 (65) Y
OBJECT_INSTANCE INTEGER Y
OBJECT_TYPE VARCHAR2 (30) Y
OPTIMIZER VARCHAR2 (255) Y
SEARCH_COLUMNS NUMBER Y
ID INTEGER Y
PARENT_ID INTEGER Y
DEPTH INTEGER Y
POSITION INTEGER Y
COST INTEGER Y
CARDINALITY INTEGER Y
BYTES INTEGER Y
OTHER_TAG VARCHAR2 (255) Y
PARTITION_START VARCHAR2 (255) Y
PARTITION_STOP VARCHAR2 (255) Y
PARTITION_ID INTEGER Y
OTHER LONG Y
OTHER_XML CLOB Y
DISTRIBUTION VARCHAR2 (30) Y
CPU_COST INTEGER Y
IO_COST INTEGER Y
TEMP_SPACE INTEGER Y
ACCESS_PREDICATES VARCHAR2 (4000) Y
FILTER_PREDICATES VARCHAR2 (4000) Y
PROJECTION VARCHAR2 (4000) Y
TIME INTEGER Y
QBLOCK_NAME VARCHAR2 (30) Y
Common Fields
Create your own explain Plan
select
id,parent_id,
lpad(
' '
,
level
)||operation||
' '
||options||
' '
||object_name
as
operation
from
plan_table
start
with
id=0
connect
by
prior
id = parent_id;
Result:
ID PARENT_ID OPERATION
--------------------------------------- --------------------------------------- -----------------------------------------
0
SELECT
STATEMENT
1 0 HASH
JOIN
OUTER
2 1
TABLE
ACCESS
BY
INDEX
ROWID TABLE1
3 2
INDEX
RANGE SCAN INDEX_TABLE1_ID
4 1
TABLE
ACCESS
FULL
TABLE2
By viewing the explain plan, you can modify your SQL statements to improve efficiency. For example, modifying or adding Indexes
You can use the dbms. display_cursor function to view the execution plan, or query related fields in V $ SQL _PLAN_STATISTICS_ALL.