DBMS_XPLAN.DISPLAY_CURSOR vs Explain Plan if not using gather_plan_statistics hint

Question

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 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.

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.

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)?

Answer 1

The differences are not very subtle, they are huge.

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.

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.

In a session you can run

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

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.

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 ...

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.

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.

I hope this helps.