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.
Numerical representation of the SQL plan for the cursor. Comparing one PLAN_HASH_VALUE to another easily identifies whether or not two plans are the same (rather than comparing the two plans line by line).
Name of the internal operation performed in this step (for example, TABLE ACCESS)
A variation on the operation described in the OPERATION column (for example, FULL)
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.
Current mode of the optimizer for the first row in the plan (statement line), for example, ALL_ROWS. When the operation is a database access (for example, TABLE ACCESS), this column indicates whether or not the object is analyzed.
Depth (or level) of the operation in the tree. It is not necessary to issue a CONNECT BY statement to get the level information, which is generally used to indent the rows from the PLAN_TABLE table. The root operation (statement) is level 0.
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
Step that computes the pair of values of the PARTITION_START and PARTITION_STOP columns
Other information specific to the execution step that users may find useful. See EXPLAIN PLAN for values.
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.
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.
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:
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
0: This value is used for rows containing data that pertain to the entire CDB. This value is also used for rows in non-CDBs.
我想要获取技术服务或软件
服务范围:MySQL、ORACLE、SQLSERVER、MongoDB、PostgreSQL 、程序问题
服务方式:远程服务、电话支持、现场服务,沟通指定方式服务
技术标签:数据恢复、安装配置、数据迁移、集群容灾、异常处理、其它问题
本站部分文章参考或来源于网络,如有侵权请联系站长。
数据库远程运维 Oracle 视图 DBA_HIST_SQL_PLAN 官方解释,作用,如何使用详细说明
相关文章
- Oracle 视图 DBA_HIST_SQL_SUMMARY 官方解释,作用,如何使用详细说明
- Oracle如何导入SQL文件(oracle导入sql文件)
- Oracle 参数 OBJECT_CACHE_OPTIMAL_SIZE 官方解释,作用,如何配置最优化建议
- 实用技巧:精通Oracle常用SQL(oracle常用sql)
- Oracle实例启动过程一步步指引(oracle启动实例)
- 简单操作,快速掌握Oracle命令(oracle命令使用)
- Oracle事务处理: 理解与使用SQL语句(oracle事务语句)
- Oracle数据库映射及其应用(oracle数据库映射)
- pl/sql与Oracle PL/SQL集成的ASM技术。(asmoracle)
- 深入浅出Oracle嵌套连接:提高SQL查询效率(oracle嵌套连接)
- Oracle EMCA: 了解Oracle数据库的快速管理工具(oracle的emca)
- Oracle内存表的释放方法研究(oracle内存表释放)
- Oracle官方JDK下载可靠安全快捷(jdk官网oracle)
- 升级进入Oracle 12c,让你的数据一跃开启新的篇章(12c oracle)
- Oracle中表连接技术简介(oracle中 表连接)
- Oracle妙用右侧截取字符(oracle从右取字符)
- Oracle也用SQL有没有别的区别(oracle也是sql吗)
- Oracle会计报表平台在财务运营中发挥重要作用(oracle会计报表平台)
- 妙用Oracle中的去尾函数精简SQL查询(oracle中的去尾函数)
- Oracle云平台可负担的低价云计算服务(oracle云平台 价格)
- 使用Oracle操作实现事务的指南(oracle中事务怎么写)
- Oracle POS 全能的零售管理解决方案(oracle中pos)
- 符Oracle无法支持没有运算符的查询(oracle不支持非运算)
- Oracle SQL中最多的数量统计实例(oracle sql数量)
- 者Oracle SQL初学者突破入门,打好基础(oracle sql初学)
- Oracle SQL实现数据库修改的技巧(oracle sql修改)
- Oracle联合推动新数据分析之路(oracle nuion)
- 使用Oracle EMP表练习SQL编程(oracle emp示例)
- 如何利用Oracle CCREC应对业务挑战(oracle ccrec)
- 解密Oracle错误代码00984 找回AI算法在数据库中的异常(oracle 00984)