StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>The differences are not very subtle, they are huge.</p> <p>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.</p> <p>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.</p> <p>In a session you can run</p> <pre><code>select * from dual; select * from table(dbms_xplan.display_cursor); </code></pre> <p>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.</p> <p>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 ...</p> <p>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.</p> <p>A bit of nice reading about the overhead of the sampling can be found at <a href="http://jonathanlewis.wordpress.com/2007/04/26/heisenberg/" rel="nofollow">Jonathan Lewis</a> blog. Also from Jonathan: <a href="http://jonathanlewis.wordpress.com/2007/11/25/gather_plan_statistics/" rel="nofollow">gather_plan_statistics </a> 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.</p> <p>I hope this helps.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload