you need some reparsing - because you want to have potentially different plans each time.
so, you will have to have SOME reparsing.
To minimize it - IF you know the result set will fit nominally into memory - you can use native dynamic sql with bulk collect. This will make it so that if you execute the same statement twice in a row - plsql will have cached it just like static sql.
So, for example:
create or replace procedure p( p_cardhint in varchar2 )
is
l_sql varchar2(4000);
type array is table of dual%rowtype;
l_data array;
begin
if (p_cardhint = 'SMALL')
then
l_sql := 'select /*+ cardinality(t 10) */ * from dual t';
else
l_sql := 'select /*+ cardinality( t 1000 ) */ * from dual t';
end if;
execute immediate l_sql bulk collect into l_data;
end;
/
if we call that like this:
exec p('SMALL');
exec p('SMALL');
exec p('SMALL');
exec p('SMALL');
exec p('LARGE');
exec p('LARGE');
exec p('SMALL');
we'll discover that the small cursor was parsed twice and the large was parsed once:
select /*+ cardinality(t 10) */ * from dual t
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 5 0.00 0.00 0 0 0 0
Fetch 5 0.00 0.00 0 10 0 5
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 12 0.00 0.00 0 10 0 5
********************************************************************************
select /*+ cardinality( t 1000 ) */ * from dual t
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 4 0 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5 0.00 0.00 0 4 0 2
Again, this is valid approach ONLY if you know the query is to return a modest number of rows - hundreds, MAYBE thousands at most.