Simple Explain Plan
SQL> set autotrace trace;
SQL> set autotrace off;
12.10 PLAN_TABLE Columns
The PLAN_TABLE used by the EXPLAIN PLAN statement contains the columns listed in Table 12-1.
Table 12-1 PLAN_TABLE Columns
| Column | Type | Description |
|---|---|---|
|
| Value of the optional |
|
| Unique identifier of a plan in the database. |
|
| Date and time when the |
|
| Any comment (of up to 80 bytes) you want to associate with each step of the explained plan. This column is used to indicate whether an outline or SQL Profile was used for the query. If you need to add or change a remark on any row of the |
|
| Name of the internal operation performed in this step. In the first row generated for a statement, the column contains one of the following values:
See Table 12-3 for more information on values for this column. |
|
| A variation on the operation described in the See Table 12-3 for more information on values for this column. |
|
| Name of the database link used to reference the object (a table name or view name). For local queries using parallel execution, this column describes the order in which output from operations is consumed. |
|
| Name of the user who owns the schema containing the table or index. |
|
| Name of the table or index. |
|
| Unique alias of a table or view in a SQL statement. For indexes, it is the object alias of the underlying table. |
|
| Number corresponding to the ordinal position of the object as it appears in the original statement. The numbering proceeds from left to right, outer to inner with respect to the original statement text. View expansion results in unpredictable numbers. |
|
| Modifier that provides descriptive information about the object; for example, |
|
| Current mode of the optimizer. |
|
| Not currently used. |
|
| A number assigned to each step in the execution plan. |
|
| The ID of the next execution step that operates on the output of the |
|
| Depth of the operation in the row source tree that the plan represents. The value can be used for indenting the rows in a plan table report. |
|
| For the first row of output, this indicates the optimizer's estimated cost of executing the statement. For the other rows, it indicates the position relative to the other children of the same parent. |
|
| Cost of the operation as estimated by the optimizer's query approach. Cost is not determined for table access operations. The value of this column does not have any particular unit of measurement; it is merely a weighted value used to compare costs of execution plans. The value of this column is a function of the |
|
| Estimate by the query optimization approach of the number of rows accessed by the operation. |
|
| Estimate by the query optimization approach of the number of bytes accessed by the operation. |
|
| Describes the contents of the
|
|
| Start partition of a range of accessed partitions. It can take one of the following values: n indicates that the start partition has been identified by the SQL compiler, and its partition number is given by n.
|
|
| Stop partition of a range of accessed partitions. It can take one of the following values: n indicates that the stop partition has been identified by the SQL compiler, and its partition number is given by n.
|
|
| Step that has computed the pair of values of the |
|
| Other information that is specific to the execution step that a user might find useful. See the |
|
| Method used to distribute rows from producer query servers to consumer query servers. See Table 12-2 for more information on the possible values for this column. For more information about consumer and producer query servers, see Oracle Database Data Warehousing Guide. |
|
| CPU cost of the operation as estimated by the query optimizer's approach. The value of this column is proportional to the number of machine cycles required for the operation. For statements that use the rule-based approach, this column is null. |
|
| I/O cost of the operation as estimated by the query optimizer's approach. The value of this column is proportional to the number of data blocks read by the operation. For statements that use the rule-based approach, this column is null. |
|
| Temporary space, in bytes, used by the operation as estimated by the query optimizer's approach. For statements that use the rule-based approach, or for operations that do not use any temporary space, this column is null. |
|
| Predicates used to locate rows in an access structure. For example, start or stop predicates for an index range scan. |
|
| Predicates used to filter rows before producing them. |
|
| Expressions produced by the operation. |
|
| Elapsed time in seconds of the operation as estimated by query optimization. For statements that use the rule-based approach, this column is null. |
|
| Name of the query block, either system-generated or defined by the user with the |
12.2 The PLAN_TABLE Output Table
The PLAN_TABLE is automatically created as a global temporary table to hold the output of an EXPLAIN PLAN statement for all users. PLAN_TABLE is the default sample output table into which the EXPLAIN PLAN statement inserts rows describing execution plans. See "PLAN_TABLE Columns" for a description of the columns in the table.
While a PLAN_TABLE table is automatically set up for each user, you can use the SQL script utlxplan.sql to manually create a local PLAN_TABLE in your schema. The exact name and location of this script depends on your operating system. On UNIX, it is located in the $ORACLE_HOME/rdbms/admin directory.
For example, run the commands in Example 12-2 from a SQL*Plus session to create the PLAN_TABLE in the HR schema.
Example 12-2 Creating a PLAN_TABLE
CONNECT HR/your_password @$ORACLE_HOME/rdbms/admin/utlxplan.sql Table created.
Oracle recommends that you drop and rebuild your local PLAN_TABLE table after upgrading the version of the database because the columns might change. This can cause scripts to fail or cause TKPROF to fail, if you are specifying the table.
If you want an output table with a different name, first create PLAN_TABLE manually with the utlxplan.sql script and then rename the table with the RENAME SQL statement. For example:
RENAME PLAN_TABLE TO my_plan_table;
12.3 Running EXPLAIN PLAN
To explain a SQL statement, use the EXPLAIN PLAN FOR clause immediately before the statement. For example:
EXPLAIN PLAN FOR
SELECT last_name FROM employees;
12.4 Displaying PLAN_TABLE Output
Some examples of the use of DBMS_XPLAN to display PLAN_TABLE output are:
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());
SELECT PLAN_TABLE_OUTPUT
FROM TABLE(DBMS_XPLAN.DISPLAY('MY_PLAN_TABLE', 'st1','TYPICAL'));
12.5 Reading EXPLAIN PLAN Output
This section uses EXPLAIN PLAN examples to illustrate execution plans. The statement in Example 12-5 is used to display the execution plans.
Example 12-5 Statement to display the EXPLAIN PLAN
SELECT PLAN_TABLE_OUTPUT
FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, 'statement_id','BASIC'));
Examples of the output from this statement are shown in Example 12-6 and Example 12-7.
Example 12-6 EXPLAIN PLAN for Statement ID ex_plan1
EXPLAIN PLAN
SET statement_id = 'ex_plan1' FOR
SELECT phone_number FROM employees
WHERE phone_number LIKE '650%';
---------------------------------------
| Id | Operation | Name |
---------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS FULL| EMPLOYEES |
---------------------------------------
This plan shows execution of a SELECT statement. The table employees is accessed using a full table scan.
Every row in the table employees is accessed, and the WHERE clause criteria is evaluated for every row.
The SELECT statement returns the rows meeting the WHERE clause criteria.
Example 12-7 EXPLAIN PLAN for Statement ID ex_plan2
EXPLAIN PLAN
SET statement_id = 'ex_plan2' FOR
SELECT last_name FROM employees
WHERE last_name LIKE 'Pe%';
SELECT PLAN_TABLE_OUTPUT
FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, 'ex_plan2','BASIC'));
----------------------------------------
| Id | Operation | Name |
----------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | INDEX RANGE SCAN| EMP_NAME_IX |
----------------------------------------
This plan shows execution of a SELECT statement.
Index EMP_NAME_IX is used in a range scan operation to evaluate the WHERE clause criteria.
The SELECT statement returns rows satisfying the WHERE clause conditions.
댓글 없음:
댓글 쓰기