Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Strange problem.

Re: Strange problem.

From: peter <p_msantos_at_yahoo.com>
Date: 6 Dec 2005 11:56:37 -0800
Message-ID: <1133898996.965626.74070@g49g2000cwa.googlegroups.com>


I suspect that you checked the execution plan via autotrace or explain plan through sqlplus.
I'm not sure what version of oracle you are on, but it sounds like you've just fallen into the trap of bind_peeking. When you check the execution plan via sqlplus, oracle does not peek into the bind variables to determine how it will execute the query. But when the query actually runs, it will peek into the bind variables to determine how to execute the query.. If that statements ages out of the shared pool, next time it's executed oracle will peek again.

You can check the trace files to be 100% sure or you can test using literals instead of bind variables..just to be sure.

Another option is to also turn off bind peeking for testing via the alter session command.
alter session set "_optim_peek_user_binds"=false;

--peter Received on Tue Dec 06 2005 - 13:56:37 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US