Create your own oracle interpretation plan and oracle interpretation plan

Source: Internet
Author: User

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.

  

  

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.