Oracle 视图 DBA_HIST_SQL_PLAN 官方解释，作用，如何使用详细说明
本站中文解释
视图
Oracle的DBA_HIST_SQL_PLAN视图是Oracle数据库提供的一个系统视图，可以用来查看实例中保存在AWR(数据库自动性能统计)历史计划数据中SQL 语句执行计划的历史记录，其中包括SQL_ID,PLAN_HASH_VALUE,OPERATION,OPTIONS,OBJECT_NODE, OPTIMIZER_COST。这些都可用来查询是否有变化，根据这些参数比较优化的可能性。
要使用DBA_HIST_SQL_PLAN视图，可以使用标准SQL查询，首先在PL/SQL环境中连上实例，然后执行此视图的查询语句，可以查看指定的SQL语句的历史计划，以检查历史执行计划的变化，并且根据这些参数考虑合适的优化方案。
例如，下面的SQL用来查询SQL_ID为“3rk666qh3q2g2”执行历史计划。
SELECT
sql_id,
plan_hash_value,
operation,
options,
object_node,
optimizer_cost
FROM dba_hist_sql_plan
WHERE sql_id = ‘3rk666qh3q2g2’;
官方英文解释
DBA_HIST_SQL_PLAN displays the execution plan information for each child cursor in the workload repository.
This view captures information from V$SQL_PLAN and is used with the DBA_HIST_SQLSTAT view.
| Column | Datatype | NULL | Description |
|---|---|---|---|
|
|
|
|
Database ID |
|
|
|
|
SQL identifier of the parent cursor in the library cache |
|
|
|
|
Numerical representation of the SQL plan for the cursor. Comparing one |
|
|
|
|
A number assigned to each step in the execution plan |
|
|
|
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, |
|
|
|
|
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 |
|
|
|
|
Timestamp for when the plan was produced |
|
|
|
|
Provides extra information specific to an execution step of the execution plan. The content of this column is structured using XML because it allows multiple pieces of information to be stored, including the following:
|
|
|
|
|
The database ID of the PDB for the sampled session |
|
|
|
|
The ID of the container that
|
See Also:
-
“V$SQL_PLAN”
-
“DBA_HIST_SQLSTAT”
