RE: PL/SQL and Bind Variables / Literals
- From: "Milen Kulev" <makulev@xxxxxxx>
- To: <troach@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 18 Jun 2009 07:44:13 +0200
Hello Tom,
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Thomas Roach
Sent: Donnerstag, 18. Juni 2009 03:19
To: oracle-l@xxxxxxxxxxxxx
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
Other related posts:
