Create your own Oracle interpretation plan

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.