Reading and Understanding Execution Plans

      本文翻译来自于：Oracle Database Performance Tuning Guide, 10g Release 2 (10.2).pdf

       官方下载地址：10g：http://www.oracle.com/pls/db102/homepage

                           11g：http://www.oracle.com/pls/db111/homepage

      To execute a SQL statement, Oracle might need to perform many steps. Each of these steps either retrieves rows of data physically from the database or prepares them in some way for the user issuing the statement. The combination of the steps Oracle uses to execute a statement is called an execution plan（Oracle用于执行SQL语句组合起来的步骤叫做执行计划）. An execution plan includes an access path for each table that the statement accesses and an ordering of the tables (the join order) with the appropriate join method.（执行计划包括每个表的连接路径和每个表最优的JOIN路径）

     Access paths are ways in which data is retrieved from the database

     Overview of EXPLAIN PLAN

     You can examine the execution plan chosen by the optimizer for a SQL statement by using the EXPLAIN PLAN statement. When the statement is issued, the optimizer chooses an execution plan and then inserts data describing the plan into a database table. Simply issue the EXPLAIN PLAN statement and then query the output table.

    These are the basics of using the EXPLAIN PLAN statement:

    ■ Use the SQL script UTLXPLAN.SQL to create a sample output table called PLAN_TABLE in your schema. See "The PLAN_TABLE Output Table" on page 19-4.（使用PLAN_TABLE）

    ■ Include the EXPLAIN PLAN FOR clause prior to the SQL statement. See "Running EXPLAIN PLAN" on page 19-5.(使用EXPLAIN PLAN FOR语句，可以参考该页面-有EXPLAIN PLAN FOR语句的使用方法：

http://www.oracle-developer.net/display.php?id=301)

    ■ After issuing the EXPLAIN PLAN statement, use one of the scripts or package provided by Oracle to display the most recent plan table output. See "Displaying PLAN_TABLE Output" on page 19-5.（使用SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, '语句名字'))进行执行计划的结果的展示）

    ■ The execution order in EXPLAIN PLAN output begins with the line that is the furthest indented to the right. The next step is the parent of that line. If two lines  are indented equally, then the top line is normally executed first.（执行计划的执行顺序是：离右边有最大缩进距离的行数（即离左边最远缩进的行数）），如果两行的缩进相同，则以在前的缩进行数开始执行:如下图所示：

执行的SQL语句

EXPLAIN PLAN FOR
SELECT e.employee_id,
       j.job_title,
       e.salary,
       d.department_name
  FROM employees   e,
       jobs        j,
       departments d
 WHERE e.employee_id < 103
   AND e.job_id = j.job_id
   AND e.department_id = d.department_id;

--执行计划的结果：

Steps in the Execution Plan

     Each row in the output table corresponds to a single step in the execution plan. Note that the step Ids with asterisks（星号，用星号标出的） are listed in the Predicate Information section.（对于输出表的每一行数据在执行计划中都有单独的步骤对应。

    说明：对于用星号标注的步骤号，他被列出在Predicate Information 区域：表示在该步骤时候的过滤条件。

     Each step of the execution plan returns a set of rows that either is used by the next step or, in the last step, is returned to the user or application issuing the SQL statement. A set of rows returned by a step is called a row set.（每个步骤返回的行结果叫做行结果集） 

     The numbering of the step Ids reflects the order in which they are displayed in response to the EXPLAIN PLAN statement. Each step of the execution plan either retrieves rows from the database or accepts rows from one or more row sources as input.（步骤的数值反映了它在展示响应的执行计划的顺序，执行计划的每一步，要要么从数据库中提取数据，要么接受一行或多行的数据输入）

■ The following steps in Example 13–2 physically retrieve data from an object in the database:

      （以下是物理上从数据库中的对象读取数据）
      ■ Step 3 reads all rows of the employees table.
      ■ Step 5 looks up each job_id in JOB_ID_PK index and finds the rowids of the associated rows in the jobs table.
      ■ Step 4 retrieves the rows with rowids that were returned by Step 5 from thejobs table.
      ■ Step 7 looks up each department_id in DEPT_ID_PK index and finds the rowids of the associated rows in the departments table.
      ■ Step 6 retrieves the rows with rowids that were returned by Step 7 from the departments table.

■ The following steps in Example 13–2 operate on rows returned by the previous row source:

      （以下的步骤是，操作先前返回的行结果集）

      ■ Step 2 performs the nested loop operation on job_id in the jobs and employees tables, accepting row sources from Steps 3 and 4, joining each row from Step 3 source to its corresponding row in Step 4, and returning the resulting rows to Step 2.
      ■ Step 1 performs the nested loop operation, accepting row sources from Step 2
and Step 6, joining each row from Step 2 source to its corresponding row in
Step 6, and returning the resulting rows to Step 1.