Oracle 视图 V$SQL_PLAN 官方解释，作用，如何使用详细说明
本站中文解释
Oracle视图v$sql_plan可以查看Oracle优化器采用的特定SQL语句的执行计划，也可以查看其相关执行成本和执行节点等。可以查看该计划是否高效地执行了给定的查询语句，并做出相应的改进方案。
使用方式：
进入SQL*Plus，用下面的语句查看单条Sql语句的执行计划：
SELECT * FROM V$SQL_PLAN WHERE SQL_ID=’&SQLID’;
可以通过执行以下语句来查看某条SQL语句的完整执行计划：
SELECT * FROM V$SQL_PLAN WHERE SQL_ID=’&SQLID’ ORDER BY ID;
官方英文解释
V$SQL_PLAN contains the execution plan information for each child cursor loaded in the library cache.
| Column | Datatype | Description |
|---|---|---|
|
|
|
Address of the handle to the parent for this cursor |
|
|
|
Hash value of the parent statement in the library cache. The two columns |
|
|
|
SQL identifier of the parent cursor in the library cache |
|
|
|
Numerical representation of the current SQL plan for this cursor. Comparing one |
|
|
|
Numeric representation of the complete SQL plan for this cursor. Comparing one |
|
|
|
Address of the child cursor |
|
|
|
Number of the child cursor that uses this execution plan. The columns |
|
|
|
Date and time when the execution plan was generated |
|
|
|
Name of the internal operation performed in this step (for example, |
|
|
|
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 that use parallel execution, this column describes the order in which output from operations is consumed. |
|
|
|
Object number of the table or the index |
|
|
|
Name of the user who owns the schema containing the table or index |
|
|
|
Name of the table or index |
|
|
|
Alias for the object |
|
|
|
Type of the object |
|
|
|
Current mode of the optimizer for the first row in the plan (statement line), for example, |
|
|
|
A number assigned to each step in the execution plan |
|
|
|
ID of the next execution step that operates on the output of the current step |
|
|
|
Depth (or level) of the operation in the tree. It is not necessary to issue a |
|
|
|
Order of processing for all operations that have the same |
|
|
|
Number of index columns with start and stop keys (that is, the number of columns with matching predicates) |
|
|
|
Cost of the operation as estimated by the optimizer’s cost-based approach. For statements that use the rule-based approach, this column is null. |
|
|
|
Estimate, by the cost-based optimizer, of the number of rows produced by the operation |
|
|
|
Estimate, by the cost-based optimizer, of the number of bytes produced 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 computes the pair of values of the |
|
|
|
Other information specific to the execution step that users may find useful. See |
|
|
|
Stores the method used to distribute rows from producer query servers to consumer query servers |
|
|
|
CPU cost of the operation as estimated by the optimizer’s cost-based approach. For statements that use the rule-based approach, this column is null. |
|
|
|
I/O cost of the operation as estimated by the optimizer’s cost-based approach. For statements that use the rule-based approach, this column is null. |
|
|
|
Temporary space usage of the operation (sort or hash-join) as estimated by the optimizer’s cost-based approach. For statements that use the rule-based approach, 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 the optimizer’s cost-based approach. For statements that use the rule-based approach, this column is null. |
|
|
|
Name of the query block |
|
|
|
Remarks |
|
|
|
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:
|
|
|
|
The ID of the container to which the data pertains. Possible values include:
|
