RE: PL/SQL and Bind Variables / Literals

do you know the values of the bind variables that are causing execution plan change ?

If yes, you can use "statement partitioning" in your PL/SQL code:  

Create or replace procedure proc (p_var number)

As

.
 

If P_var =10 then

Select col, col2 into v_c1, v_c2 from table T where id = p_var;

Elseif P_var = 1000 then

Select col, col2 into v_c1, v_c2 from table T where id = p_var;

End if;  

..
 

End proc;

/  

The column T.id shouldn't have histograms to minimize the chance of bind peeking. Optionally, if you know the "dangerous" ranges

Of the values of T.id (from the sample above) you can craft a histogram which bucket values are the dangerous ones (You should code this functionality yourself using DBMS_STATS.SET_*).  

HTH. Milen    

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Thomas Roach
Sent: Donnerstag, 18. Juni 2009 03:19
To: oracle-l_at_freelists.org
Subject: PL/SQL and Bind Variables / Literals  

Hi Group,

I am looking at options on forcing Oracle to use literals inside of a PL/SQL package. I have a bind variable peeking issue that is causing a cached execution plan that doesn't work well for all scenarios. To get around this (and since the query is executed only a handful of times), I want to force the query to use literals inside of PL/SQL. One option I thought of is to use execute immediate. I was wondering if anyone had any ideas or thoughts if there is a better way to do this.

Thanks,

Tom

--
http://www.freelists.org/webpage/oracle-l