Re: Caching sql query not happening

From: Paul Drake <bdbafh_at_gmail.com>
Date: Fri, 3 Jun 2005 17:15:55 -0400
Message-ID: <910046b40506031415171a1125@mail.gmail.com>

On 6/3/05, Vikas Gautam <g_vikas_at_hotmail.com> wrote:

>=20
>=20
>=20

> > Does anyone know why a statement such as:
> >
> > SELECT ID
> > FROM ELSR dups
> > WHERE (dups.directoryID =3D :"SYS_B_0"
> > AND dups.DeviceIdentifier =3D :"SYS_B_1"
> > AND dups.serviceStartTime BETWEEN TIMESTAMP:"SYS_B_2" AND
> > TIMESTAMP:"SYS_B_3")
> >
> > will never cache in the Shared Pool?
> >
> > In other words, even though all of the parameters are bind variables, t=
his
> > will always be parsed seperately, and never cache.
> >
> > Thanks
> > Vikas

Those are not really bind variables.
They are sys-generated binds placed due to the initialization parameter cursor_sharing being set to something other than EXACT. This means that you leave yourself open to all sorts of wonderful permutations of occurrances that cause you to still be working after 5 pm on friday evenings.

replace the literals with real bind variables.

Pd

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Jun 03 2005 - 17:20:51 CDT