| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Turn off Bind Peeking
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;
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Jonathan Lewis
> Sent: Wednesday, May 19, 2004 8:16 AM
> To: oracle-l_at_freelists.org
> 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_at_flash.net>
> To: <oracle-l_at_freelists.org>
> 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_at_flash.net
>
>
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request_at_freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
>
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Thu May 20 2004 - 21:52:08 CDT
 |
 |