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