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.
Value of the optional STATEMENT_ID parameter specified in the EXPLAIN PLAN statement
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 PLAN_TABLE, then use the UPDATE statement to modify the rows of the PLAN_TABLE.
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:
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.
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, NON-UNIQUE for indexes
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 CPU_COST and IO_COST columns.
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
SERIAL Serial execution. Currently, SQL is not loaded in the OTHER column for this case.
PARALLEL_FROM_SERIAL Serial execution. Output of step is partitioned or broadcast to parallel execution servers.
PARALLEL_TO_SERIAL Parallel execution. Output of step is returned to serial query coordinator (QC) process.
PARALLEL_TO_PARALLEL Parallel execution. Output of step is repartitioned to second set of parallel execution servers.
PARALLEL_COMBINED_WITH_PARENT Parallel execution; Output of step goes to next step in same parallel process. No interprocess communication to parent.
PARALLEL_COMBINED_WITH_CHILD Parallel execution. Input of step comes from prior step in same parallel process. No interprocess communication from child.
number Start partition has been identified by the SQL compiler, and its partition number is given by number
KEY Start partition will be identified at run time from partitioning key values
ROW LOCATION Start partition (same as the stop partition) will be computed at run time from the location of each record being retrieved. The record location is obtained from a user-specified ROWID or from a global index.
number Stop partition has been identified by the SQL compiler, and its partition number is given by number
ROW LOCATION Stop partition (same as the start partition) will be computed at run time from the location of each record being retrieved. The record location is obtained from a user-specified ROWID or from a global index.
Step that has computed the pair of values of the PARTITION_START and PARTITION_STOP columns
Other information that is specific to the execution step that a user might find useful (see the OTHER_TAG column)
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:
Name (if any) of the outline or the SQL profile used to build the execution plan
The outline data, a set of optimizer hints that can be used to regenerate the same plan
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.
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 QB_NAME hint)
我想要获取技术服务或软件
服务范围:MySQL、ORACLE、SQLSERVER、MongoDB、PostgreSQL 、程序问题
服务方式:远程服务、电话支持、现场服务,沟通指定方式服务
技术标签:数据恢复、安装配置、数据迁移、集群容灾、异常处理、其它问题
本站部分文章参考或来源于网络,如有侵权请联系站长。
数据库远程运维 Oracle 视图 PLAN_TABLE 官方解释,作用,如何使用详细说明
相关文章
- Oracle 视图 DBA_TYPE_ATTRS 官方解释,作用,如何使用详细说明
- Oracle 视图 V$LOCK_TYPE 官方解释,作用,如何使用详细说明
- Oracle授权协议:正式授权使用的指南(oracle授权书)
- 数据库使用SqlDBx连接Oracle数据库的指南(sqldbx连接oracle)
- 使用Oracle结果集函数优化查询效率(oracle结果集函数)
- Oracle物理读:效能提升之道(oracle物理读)
- Oracle公司地址:让你轻松找到Oracle的位置(oracle公司地址)
- 如何使用Oracle删除分区?(oracle删分区)
- Oracle 中的 WITH 语句使用技巧(oracle with用法)
- Oracle数据库中使用时间差函数(oracle 时间差函数)
- Oracle:运用排它锁保障数据安全(oracle 排它锁)
- Oracle 删除数据报错解决方案(oracle-01403)
- Oracle公司荣耀之星黄玮(oracle公司黄玮)
- 如何使用Oracle进行冷备份(oracle冷备份怎么做)
- 使用DBCP连接Oracle数据库的快捷方法(dbcp链接oracle)
- 数据库ES和Oracle数据库实现最佳性能的方案(es oracle)
- Oracle中视图编写把复杂数据简洁有效地呈现(oracle中视图的编写)
- 增长Oracle云版的收入表现乐观增长(oracle云版的收入)
- Oracle中序列提供的自增ID管理功能(oracle中的序列作用)
- Oracle 数据库中使用AND拼接的威力(oracle中and拼接)
- Oracle XE入门指南学会使用这个免费数据库(oracle xe 入门)
- Oracle RAW类型数据比较深入洞察(oracle raw比较)
- Oracle OSB技术极大提升数据处理效率(oracle osb使用)
- 利用Oracle EMC监控提高系统运行效率(oracle emc监控)
- Oracle AS让企业信息管理更轻松(oracle as 长度)
- 最新Oracle 9i你的最佳选择(oracle 9i 吧)