DBMS_XPLAN.DISPLAY_CURSOR与解释计划(如果未使用collect_plan_statistics提示) [英] DBMS_XPLAN.DISPLAY_CURSOR vs Explain Plan if not using gather_plan_statistics hint

问题描述

仅要求澄清两者之间的区别.据我了解，EXPLAIN PLAN为您提供理论执行计划，而DBMS_XPLAN.DISPLAY_CURSOR为您提供 actual 执行计划与该语句的执行统计信息.

Just requesting some clarification on the difference between the 2. From what I understand, EXPLAIN PLAN gives you the theoretical execution plan while DBMS_XPLAN.DISPLAY_CURSOR gives you the actual execution plan with execution statistics for the statement.

EXPLAIN PLAN将此数据存储在PLAN_TABLE中，而DBMS_XPLAN使用V $ SQL_PLAN，V $ SQL_PLAN_STATISTICS和V $ SQL_PLAN_STATISTICS_ALL视图获取其信息.

EXPLAIN PLAN stores this data in a PLAN_TABLE while DBMS_XPLAN uses the V$SQL_PLAN, V$SQL_PLAN_STATISTICS and V$SQL_PLAN_STATISTICS_ALL views for its information.

但是，为了让DISPLAY_CURSOR收集该语句的实际运行时统计信息，需要设置/*+ gather_plan_statistics */提示.否则，仅填写V $ SQL_PLAN，这只会为您提供执行计划，而不会提供实际的执行统计信息.仅在/*+ gather_plan_statistics */中填充V $ SQL_PLAN_STATISTICS.

However, for DISPLAY_CURSOR to collect the actual runtime statistics for that statment, one needs to set the /*+ gather_plan_statistics */ hint. Otherwise, only V$SQL_PLAN is filled which will only give you the execution plan but not the actual execution statistics. It is only with the /*+ gather_plan_statistics */ where V$SQL_PLAN_STATISTICS is filled.

所以我的问题是，如果我不使用collect_plan_statistics提示，那么EXPLAIN PLAN和DISPLAY_CURSOR会始终为我提供相同的执行计划(针对同一条语句)吗?

So my question is, if I do not use the gather_plan_statistics hint, will EXPLAIN PLAN and DISPLAY_CURSOR always give me the same execution plan (for the same statement)?

推荐答案

差异不是很细微，差异很大.

The differences are not very subtle, they are huge.

正如您正确提到的，解释计划将其数据存储在plan_table中，然后从该表中查询计划.这意味着不执行sql，仅要求优化器提供计划.在该设置中，计划在很大程度上取决于您在其中运行解释计划的会话的优化器环境.

As you correctly mentioned, explain plan stores it's data in the plan_table and the plan is queried from that table. This means that the sql is NOT executed, only the optimizer is asked to deliver a plan. In that setup the plan depends heavily on the optimizer environment of your session in which you run the explain plan.

使用DBMS_XPLAN.DISPLAY_CURSOR，您可以获取计划，因为它已经执行过.不会通过发出DBMS_XPLAN.DISPLAY_CURSOR来存储该计划；因为它已执行，所以存储在v $结构中.

With DBMS_XPLAN.DISPLAY_CURSOR you get the plan as it has been executed before. The plan is not stored by issuing the DBMS_XPLAN.DISPLAY_CURSOR; it stored in the v$ structures because it has been executed.

您可以在会话中运行

select * from dual;
select * from table(dbms_xplan.display_cursor);

查询是在从双重选择"中执行的，这也导致创建一个计划，并且我将其存储在v $结构中. display_cursor只是找到最后执行的游标并显示其遵循的计划.使用此设置，/* + collect_plan_statistics */没有附加值，因为该计划及其统计信息已经存在于shared_pool中.

The query is executed in the 'select from dual', this also results in the creation of a plan and that i stored in the v$ structures. the display_cursor just finds the last executed cursor and displays the plan it followed. With this setup /*+ gather_plan_statistics */ has no added value because the plan and it's statistics are already present in the shared_pool.

另一个问题，计划是否始终相同取决于许多因素.变量是否相同?您是否正在使用自适应游标共享"?您是否正在使用SQL计划稳定性...

Your other question, whether or not the plan is always the same depends on many factors. Are the variables the same?, are you using Adaptive Cursor Sharing, are you using SQL Plan Stability ...

您的问题:给解释计划和display_cursor提供相同的计划吗?我不会依靠它，因为有了解释计划，该计划取决于您的会话优化器环境. display_cursor是更好的方法，最好使用由应用程序创建的命名游标.如果不使用SQL计划稳定性，则当优化程序统计信息更改时，计划也可能更改.如果您使用自适应光标共享"，则当变量更改时，计划也会更改.

Your question: give explain plan and display_cursor the same plan? I would not rely on that because with explain plan, the plan depends from your sessions optimizer environment. display_cursor is the better way, and preferably using a named cursor that is created by the application. If you don't use SQL Plan Stability, the plan can change when the optimizer statistics change. If you use Adaptive Cursor Sharing, the plan can change when the variables change.

可以在乔纳森·刘易斯(Jonathan Lewis)中找到有关采样开销的一些不错的阅读资料博客.同样来自乔纳森(Jonathan): gather_plan_statistics 我通常更聪明地使用statistics_level设置全部"用于调试，而不是使用/* + collect_plan_statistics */提示.该提示会更改代码并导致新的sql_id.

A bit of nice reading about the overhead of the sampling can be found at Jonathan Lewis blog. Also from Jonathan: gather_plan_statistics I is often smarter to use statistics_level setting 'all' for debugging as opposed to using the /*+ gather_plan_statistics */ hint. The hint changes code and causes a new sql_id.

我希望这会有所帮助.

这篇关于DBMS_XPLAN.DISPLAY_CURSOR与解释计划(如果未使用collect_plan_statistics提示)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！