| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Bind Peeking with java application
On Mar 14, 7:09 pm, "joel garry" <joel-ga..._at_home.com> wrote:
> On Mar 14, 1:30 am, "Cristian Cudizio" <cristian.cudi..._at_yahoo.it>
> wrote:
>
>
>
> > On Mar 13, 7:54 pm, "joel garry" <joel-ga..._at_home.com> wrote:
>
> > > On Mar 13, 3:57 am, "Cristian Cudizio" <cristian.cudi..._at_yahoo.it>
> > > wrote:
>
> > > > On Mar 12, 7:55 pm, sybra..._at_gmail.com wrote:
>
> > > > > On 12 Mar 2007 10:02:48 -0700, "Cristian Cudizio"
>
> > > > > <cristian.cudi..._at_yahoo.it> wrote:
> > > > > >So i suspect that on eavy load Oracle during hard parse does not make
> > > > > >bind peeking
>
> > > > > Any reference in the docs?
> > > > > Because obviously this is nonsense.
>
> > > > > --
> > > > > Sybrand Bakker
> > > > > Senior Oracle DBA
>
> > > > sorry, i've some problems with posting. I thing you are rigth "this is
> > > > nonsense"
> > > > there are no reference in the docs but it appens
>
> > > Seehttp://jonathanlewis.wordpress.com/2006/12/11/bind-peeking/-you
> > > need to run a trace on the problem customers system to find out if it
> > > is indeed doing the peek. Are you sure the problem isn't because it
> > > _is_ doing the peek? That is, after all, the essence of the bind
> > > peeking problem - the first access to the db "correctly" does an FTS,
> > > when most of the accesses should not. So you flush, and the
> > > subsequent access is ok. It is difficult to reproduce that without
> > > knowing what the first access to the customer's database was. You say
> > > it is during heavy load - that would perhaps set up the conditions for
> > > the sql to be aged out of memory and need to be hard parsed again.
>
> > > Metalink not-a-bug 5763227 notes that the 9.2 JDBC driver does not
> > > support bind peeking, so going from 9.2 to 10g makes symptoms similar
> > > to yours, FWIW.
>
> > > There probably is other stuff going on too.
>
> > >http://www.freelists.org/archives/oracle-l/05-2004/msg01444.htmlif
> > > you are brave.
>
> > > jg
> > > --
> > > @home.com is bogus.http://www.sfdt.com/
>
> > The reference to note 5763227 is VERY INTERESTING, i've never
> > suspected that bind peeking may
> > depend by drivers.
> > I've already read Jonathan Lewis post,
> > unfortunately not being able to reproduce it is difficult for me
> > activate tracing on event 10053 and
> > verify what appens (and our application has a connection pool that
> > makes impossible to individuate
> > what is the connection tha run the query.
> > As i've already written we use JDBC OCI 10.2. But now i try to write
> > a small java
> > program to test with event 10053 and verify what appens. What you say
> > is that if there is a bug in
> > our software for wich the incorret value is passed as bind value for
> > the query that makes correct
> > to choose a FTS. To prove that i will use Fine Graned Audit to find if
> > is that
>
> Does it really have to be a bug in your app? Could it be your app
> sometimes just has to pass a value that the optimizer decides is best
> served by FTS? Also, how is your customer generating histograms? Can
> you use the 9 driver?
>
> Also google on trcsess.
>
> jg
> --
> @home.com is bogus.
> "You still have the freedom to do things that are stupid in America.
> Unfortunately, many people are exercising that freedom with abandon."
> - Christopher Cruise
I think that there is not such a bug in our application, if such, the
query with
bind value -1 would give a resultset of 20 milion records, our
application woud try to
load such result in RAM and that woul give an OutOfMemory that we have
never found
in application logs. Histograms are generated with default
configuration wich is AUTO,
Oracle use a sample of about 6500 records for histogram on column
"col1".
I've made a little test, with drivers 10.2.0.1 (those used in our
production systems)
and 9.2.0.3 activating event 10053 as suggested by J.L. and in both
situations
i found :
-10.2.0.1 :
oacdty=02 mxl=22(21) mxlc=00 mal=00 scl=00 pre=00 oacflg=03 fl2=1000000 frm=00 csi=00 siz=24 off=0 kxsbbbfp=2a979b5270 bln=22 avl=05 flg=05value=6567877
-9.2.0.3:
oacdty=02 mxl=22(21) mxlc=00 mal=00 scl=00 pre=00 oacflg=03 fl2=1000010 frm=00 csi=00 siz=24 off=0 kxsbbbfp=2a979b50f8 bln=22 avl=05 flg=05value=6567899
That means tha bind peeking appens also with 9.2.0.3 jdbc drivers
I would stress on the evidence that on an installation with 10.1.0.3
(then upgraded to 10.1.0.5)
with similar number of records and distribution the problem has NEVER
occurred,
on other 4 installations with 10.2.0.2 has occurred more than once.
I can try to enable 10053 event on production system an all sessions
opened
by the application but i think that would be difficult to found the
needed information, our
application launches hundreds of queries.
Received on Thu Mar 15 2007 - 04:28:32 CDT
 |
 |