DBMS_XPLAN.DISPLAY_CURSOR after EXPLAIN PLAN without execute the query

Community Forum › Category: Database › DBMS_XPLAN.DISPLAY_CURSOR after EXPLAIN PLAN without execute the query
0 Vote Up Vote Down
matheusdba Staff asked 3 years ago

Can you use DBMS_XPLAN.DISPLAY_CURSOR after executing only an EXPLAIN PLAN for a SQL statement, but *without* actually executing the SQL statement itself ?

 — to ellaborate when we execute “explain plan for SQL_IDa” that statmement would have a seperate sql_id .. lets say sql_idBb.. so when using dbms_xplan.dispaly_cursor.. the sql_id you are mentioning is sql_idb.. hope that helps..

Question Tags: ,
1 Answers
0 Vote Up Vote Down
matheusdba Staff answered 3 years ago

Lets go by parts:

  • The EXPLAIN PLAN does only the explanation for the plan on your session.
  • Before executing any SQL there is a PARSE step (Parse, Execute, Fetch). During the parse is when the plan is generated.
  • The DBMS_XPLAN.DISPLAY_CURSOR takes the plans for the referred SQLID from the Shared Pool.
  • So, as long as you have the parse for the SQLID you have it on the shared pool and the DBMS_XPLAN.DISPLAY_CURSOR can be used.
  • However, the EXPLAIN PLAN does not parse the SQL to the Shared Pool.
  • So yes, you need to execute the query or at least start executing it.