Oracle How to get Execution Plan for SQL executed inside of PLSQL?
Is it possible to examine the execution plan for a SQL statement executed inside a PLSQL block?
DECLARE l_count PLS_INTEGER; BEGIN SELECT COUNT(1) INTO l_count FROM foo; END; /
For regular SQL I would normally run the following to check the execution plan:
select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));
However, this just reports:
NOTE: cannot fetch plan for SQL_ID: 3q0sujncq54wy, CHILD_NUMBER: 0 Please verify value of SQL_ID and CHILD_NUMBER; It could also be that the plan is no longer in cursor cache (check v$sql_plan)
You can look up the
v$sql. Something like:
Select s.sql_id, s.child_number From V$sql s Where upper(sql_text) like upper('SELECT COUNT(%FROM foo') And Sql_text not like '%v$sql%';
Note that pl/sql normalises static SQL for you to boost cursor sharing - it will be in upper case with much less white space.
Once you’ve got these, you can just input into the