Create your own oracle interpretation plan and 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 for 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|* 1 | HASH JOIN OUTER | | 1 | 141 | 8| 2 | 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_tableName 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.