Oracle EXECUTE IMMEDIATE changes explain plan of query.
- by Gunny
I have a stored procedure that I am calling using EXECUTE IMMEDIATE. The issue that I am facing is that the explain plan is different when I call the procedure directly vs when I use EXECUTE IMMEDIATE to call the procedure. This is causing the execution time to increase 5x. The main difference between the plans is that when I use execute immediate the optimizer isn't unnesting the subquery (I'm using a NOT EXISTS condition). We are using Rule Based Optimizer here at work.
Example:
Fast:
begin
package.procedure;
end;
/
Slow:
begin
execute immediate 'begin package.' || proc_name || '; end;';
end;
/
