Oracle How to get Execution Plan for SQL executed inside of PLSQL?

All we need is an easy explanation of the problem, so here it is.

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)

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

You can look up the sql_id And child_number in 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 dbms_xplan.display_cursor function

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply