Explain Oracle execution plan

Source: Internet
Author: User

Explain the following example of an Oracle execution plan: www.2cto.com [html] SQL> explain plan for 2 select. name, B. name 3 from t1 a, t2 B 4 where. id = B. id 5 and. id = '1'; Explained. SQL> set lines 200 SQL> set pagesize 0 SQL> select * from table (dbms_xplan.display (); Plan hash value: 2680223496 www.2cto.com success | Id | Operation | Name | Rows | Byte S | Cost (% CPU) | Time | percent | 0 | select statement | 1 | 20 | 5 (0) | 00:00:01 | 1 | nested loops | 1 | 20 | 5 (0) | 00:00:01 | 2 | table access by index rowid | T1 | 1 | 10 | 1 (0) | 00:00:01 | * 3 | index unique scan | T1_PK | 1 | 0 (0) | 00:00:01 | * 4 | table access full | T2 | 1 | 10 | 4 (0) | 00:00:01 | Required Predicate Information (identified by operation id): --------------------------------------------------- 3-access ("". "ID" = 1) 4-filter ("B ". "ID" = 1) 17 rows selected. for Oracle execution plans, parallel rows are executed from top to bottom, non-parallel rows are executed first on the right. For example, in this example, the operation with id 3 is the rightmost, so it is the first to execute, followed by the operations with id 2 and 4 are more right-aligned, then, according to the parallel execution principle from top to bottom, the operation with id 2 is executed first than the operation with id 4. Then, perform the operation with id 1. In this example, the execution plan is sorted by id as 3> 2> 4> 1 ← the execution plan displayed by DBMS_XPLAY used in this example, where: id: is the unique serial number of each operation, operation: name and method of each operation. name: indicates the operation object Rows: Oracle estimates the number of Rows affected by the current operation. bytes: Oracle estimates the amount of data affected by the current operation (in bytes). Cost: the cost of performing this operation calculated by Oracle: the Time taken by Oracle to perform this operation ------------------------------------------------------------------------------------------ ----------------------- If the id is 0, select statement indicates that the type of this STATEMENT is a select STATEMENT, rather than a real operation. An operation with id 1 indicates that it needs to associate the result set obtained from operations with id 2 and id 4 in a nested loop. The operation with id 2 is table access by index rowid, And the name is T1, indicating that it accesses TABLE T1 through the index rowid. The ROWID on the index needs to be obtained through its suboperation 3. The operation with id 3 is index unique scan, and the operation with name T1_PK indicates that the Rowid required by the parent operation 2 is obtained through the UNIQUE key value of the primary key. The operation with id 4 is table access full, and the name is T2, indicating that it is a full table scan for T2. Note: * exists before IDs 3 and 4, indicating that this operation has a predicate condition: access or filter) the preceding [html] Predicate Information (identified by operation id): --------------------------------------------------- 3-access ("". "ID" = 1) 4-filter ("B ". "ID" = 1) the difference between access condition access and filter condition access condition is to help the operation locate the Qualified Data on the object, and then read the data. The filtering condition is that the operation has obtained data from the object, and then filtered out the data that does not meet the requirements according to the condition.
 

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.