RE: Turn off Bind Peeking
- From: "Larry G. Elkins" <elkinsl@xxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 20 May 2004 21:55:13 -0500
Jonathan,
PL/SQL Bind Peeking. Table t1 contains 25K rows. One row has 'LARRY' for
owner, the other 24,999 have 'SCOTT'. BMI on the column, stats gathered,
including histograms. And 9.2.0.1 Win2k. LMT, uniform, 8k blocksize, And oh
yeah, no assm ;-)
Test of pure SQL with literals shows the BMI picked up when specifying
LARRY, FTS when specifying SCOTT.
Ran the test below 4 times. Run once, and then toggle so SCOTT goes first on
second run. BMI access for both on first run, due to peeking at LARRY. On
the second run, FTS, based on peeking at SCOTT first. Repeat the test with
the alter session command in place to turn off bind peeking, then it didn't
matter of I did LARRY or SCOTT first, it always picked up the FTS. Plan
looked at via STAT lines in raw trace file. Still having some problems
reconciling things with what I see in V$SQL_PLAN and CHILD NUMBER version
plans, but wasn't it Tanel P. that recently mentioned issues with V$SQL_PLAN
not always telling the truth?
Anyway, it seems to me that the parameter caused it to not peek in the case
of PL/SQL bind. Fire away, I threw this together pretty quickly while quite
exhausted ;-) I could have put the index in a dedicated TS, and the table in
a dedicated TS, and offline/online the TS's between each pass and check the
file stats (or isn't there an event we can set in 9i to flush the buffer
cache) to really know for sure? Maybe we don't trust any plan we see
anywhere ;-)
I'll try to look at the cursor sharing aspect at some other time, but for
right now it isn't applicable as force is used, and I'm pressed on time.
Here's the quick and dirty I used:
alter system flush shared_pool;
alter session set events '10046 trace name context forever, level 4'; -- I
didn't care about waits, just binds!!!!
rem alter session set "_optim_peek_user_binds"=false;
declare
cursor c1 (p_owner varchar2) is select * from t1 where owner = p_owner;
begin
for I in 1..2 loop
if I=1 then
open c1 ('LARRY'); -- favors BMI access
else
open c1 ('SCOTT'); -- favors FTS access
end if;
close c1;
End loop;
End;
/
exec dbms_session.reset_package;
exit;
> -----Original Message-----
> From: oracle-l-bounce@xxxxxxxxxxxxx
> [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Jonathan Lewis
> Sent: Wednesday, May 19, 2004 8:16 AM
> To: oracle-l@xxxxxxxxxxxxx
> Subject: Re: Turn off Bind Peeking
>
>
>
> When you experiment with it - let us know if
> it turns off pl/sql bind peeking and cursor_sharing
> bind peeking as well. 'user_binds' seems a little
> ambiguous.
>
> Regards
>
> Jonathan Lewis
>
> http://www.jlcomp.demon.co.uk
>
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
> The Co-operative Oracle Users' FAQ
>
> http://www.jlcomp.demon.co.uk/seminar.html
> Optimising Oracle Seminar - schedule updated May 1st
>
>
> ----- Original Message -----
> From: "Larry G. Elkins" <elkinsl@xxxxxxxxx>
> To: <oracle-l@xxxxxxxxxxxxx>
> Sent: Wednesday, May 19, 2004 1:40 PM
> Subject: Turn off Bind Peeking
>
>
> Listers,
>
> Has anyone used the undocumented parameter
> "";" to turn
> off bind peeking? Just curious if people have run into any bugs
> or oddities
> when using this parameter.
>
> Larry G. Elkins
> elkinsl@xxxxxxxxx
>
>
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
> put 'unsubscribe' in the subject line.
> --
> Archives are at //www.freelists.org/archives/oracle-l/
> FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
>
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
