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.
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
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂