Oracle 视图 V$SQL_PLAN_STATISTICS_ALL 官方解释，作用，如何使用详细说明
本站中文解释
Oracle视图V$SQL_PLAN_STATISTICS_ALL是oracle内部板块，基于dyamically generated views (DGVs)而建立，提供SQL执行计划中显示操作所用到的资源和使用时间的统计信息，使系统管理员可以迅速准确地检查SQL的性能的优劣，从而及时作出合理的调整、优化。
使用方法：
1. 首先执行查看需要性能分析的SQL，如有必要，可以在执行语句后面使用EXPLAIN PLAN来分析SQL的执行计划
2. 执行查询V$SQL_PLAN_STATISTICS_ALL视图，并根据SQL_ID或相关字段进行检索
3. 根据查询返回的数据，了解SQL执行计划中哪些操作占用了多少资源，从而查看SQL在那里受到阻碍。
4. 使用图形性能监控器根据V$SQL_PLAN_STATISTICS_ALL中收集的信息，可以快速地查看SQL执行计划中资源使用情况，进而对SQL进行优化
官方英文解释
V$SQL_PLAN_STATISTICS_ALL contains memory usage statistics for row sources that use SQL memory (sort or hash-join). This view concatenates information in V$SQL_PLAN with execution statistics from V$SQL_PLAN_STATISTICS and V$SQL_WORKAREA.
| 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 statement 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:
|
|
|
|
Number of times this cursor has been executed |
|
|
|
Number of times this operation has been started, during the last execution |
|
|
|
Number of times this operation has been started, accumulated over the past executions |
|
|
|
Number of rows produced by the row source, during the last execution |
|
|
|
Number of rows produced by the row source, accumulated over the past executions |
|
|
|
Number of buffers retrieved in consistent mode, during the last execution. Buffers are usually retrieved in consistent mode for queries. |
|
|
|
Number of buffers retrieved in consistent mode, accumulated over the past executions. Buffers are usually retrieved in consistent mode for queries. |
|
|
|
Number of buffers retrieved in current mode, during the last execution. Buffers are retrieved in current mode for statements such as |
|
|
|
Number of buffers retrieved in current mode, accumulated over the past executions. Buffers are retrieved in current mode for statements such as |
|
|
|
Number of physical disk reads performed by the operation, during the last execution |
|
|
|
Number of physical disk reads performed by the operation, accumulated over the past executions |
|
|
|
Number of physical disk writes performed by the operation, during the last execution |
|
|
|
Number of physical disk writes performed by the operation, accumulated over the past executions |
|
|
|
Elapsed time (in microseconds) corresponding to this operation, during the last execution |
|
|
|
Elapsed time (in microseconds) corresponding to this operation, accumulated over the past executions |
|
|
|
Sizing policy for this work area:
|
|
|
|
Estimated size (in KB) required by this work area to execute the operation completely in memory (optimal execution). This is either derived from optimizer statistics or from previous executions. |
|
|
|
Estimated size (in KB) required by this work area to execute the operation in a single pass. This is either derived from optimizer statistics or from previous executions. |
|
|
|
Memory size (in KB) used by this work area during the last execution of the cursor |
|
|
|
Indicates whether this work area ran using |
|
|
|
Degree of parallelism used, during the last execution of the cursor |
|
|
|
Number of times this work area was active |
|
|
|
Number of times this work area ran in optimal mode |
|
|
|
Number of times this work area ran in one pass mode |
|
|
|
Number of times this work area ran below the one pass memory requirement |
|
|
|
Average time this work area is active (in hundredths of a second) |
|
|
|
Maximum temporary segment size (in bytes) created by an instantiation of this work area. This column is null if this work area has never spilled to disk. |
|
|
|
Temporary segment size (in bytes) created in the last instantiation of this work area. This column is null if the last instantiation of this work area did not spill to disk. |
|
|
|
The ID of the container to which the data pertains. Possible values include:
|
|
|
|
The database ID of the PDB |
See Also:
-
“V$SQL_PLAN_STATISTICS”
-
“V$SQL_WORKAREA”
