Oracle 视图 PLAN_TABLE 官方解释，作用，如何使用详细说明
本站中文解释
Oracle视图 PLAN_TABLE 是一个系统特定的虚拟表，一般用于托存表空间，显示IPlan执行预查询结果。任何获取用户帐号权限的用户可以查看该表的数据。
使用方法：
1. 首先，需要执行以下命令以创建Plan_table表：
SQL> execute DBMS_XPLAN.DISPLAY_CURSOR;
2. 执行想要分析的SQL语句
3. 再次运行 DBMS_XPLAN.DISPLAY_CURSOR; 来查看执行计划
4. 查询Plan_Table表中存储的信息，可以重新构建SQL语句，优化索引以提高查询性能。
官方英文解释
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.
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.
| Column | Datatype | NULL | Description |
|---|---|---|---|
|
|
|
Value of the optional |
|
|
|
|
Unique identifier of a plan in the database |
|
|
|
|
Date and time when the |
|
|
|
|
Any comment (of up to 4000 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:
|
|
|
|
|
A variation on the operation described in the |
|
|
|
|
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. |
|
|
|
|
Owner of 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 |
|
|
|
|
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:
|
|
|
|
|
Stop partition of a range of accessed partitions:
|
|
|
|
|
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 |
|
|
|
|
Provides extra information specific to an execution step of the execution plan. The content of this column is structured using XML since multiple pieces of information can be stored there. This includes:
|
|
|
|
|
Method used to distribute rows from producer query servers to consumer query servers |
|
|
|
|
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 |
