it depends. totally insufficient data to answer.
"Maybe" is the only resonable answer one can give.
I can give you examples whereby they would be identical performance wise and another whereby the static sql would be better and another whereby the dynamic sql would be.
so, you have to provide more background information.
a) case a - they are same
in this case, you are executing an insert into table AS SELECT. You do this once in the session. The AS SELECT takes some measurable amount of time.
In this case, both will parse the sql statement once (a sql statement has to be parsed AT LEAST once and AT MOST once per session - if you execute it once, it'll be parsed once). Since they both parse once and both execute it once, they will spend the same amount of resources exactly - they are the same in this case.
b) case b - static sql is best.
You have a routine like:
CREATE TABLE t
(
dt date,
x int,
y varchar2(30)
)
PARTITION BY RANGE (dt)
(
PARTITION part1 VALUES LESS THAN (to_date('01-jan-2008','dd-mon-yyyy')) ,
PARTITION part2 VALUES LESS THAN (to_date('01-jan-2009','dd-mon-yyyy'))
)
/
create or replace procedure static
as
begin
insert into t partition(part1) values ( sysdate-1000, 1, 1 );
update t set x = x+1 where rownum = 1;
end;
/
create or replace procedure dynamic
as
l_query long;
begin
for i in 1 .. 2
loop
if ( i = 1 )
then
l_query := 'insert into t partition(part1) values ( sysdate-1000, 1, 1 )';
else
l_query := 'update t set x = x+1 where rownum = 1';
end if;
execute immediate l_query;
end loop;
end;
/
@trace
exec static
exec dynamic
connect /
@trace
exec static
exec static
exec dynamic
exec dynamic
@tk "sys=no"
the tkprof would show that the static insert is parsed once and executed many, whereas the dynamic is parsed once per execution (note the wacky code in the dynamic routine, if I used different logic, this would not be the case - execute immediate caches the last executed sql statement on a given line of code)
c) case c - dynamic sql is better. Here you have some weird sql statement that is generic beyond belief (like a huge multi-table insert or something with every possible case considered). You spend so many extra resources being generic in the static sql - that it is just inefficient.