Re: statement caching in oracle

Actually, I think there are some caveats here (which is why I'm searching this topic). It appears that Oracle _will not_ cache a PreparedStatement on the server if you close that statement on the client (Thin Driver, 8.1.6). In my test program, the only way to get it to cache the statement on the server was to implement a client-side cache, which would leave the PreparedStatement object open.

If anyone knows a way to allow me to call PreparedStatement.close() and still have the server cache the statement -- or even better, reuse a statement from another session, please let me know.

-kdg

In article <3A646B19.66A861F8_at_brokat.de>,   Frank Gerlach <Frank.Gerlach_at_brokat.de> wrote:
> I suggest to use PreparedStatement only, which will allow Oracle to
 reuse
> already parsed and optimized statements. Oracle does this by creating
 a
> hash over the statement text (the "SELECT.."), which means it will not
 work
> without a PreparedStatement. I do not think it is a good idea to call
> internal methods of the Oracle jdbc implementation. Use the Oracle
 Server
> admin tools to change the statement cache size, as the statement cache
 is a
> server-side entity !
>
> miso_pach_at_my-deja.com wrote:
>
> > Hi
> >
> > In my java program i repetedly call certain queries. I would like to
> > take advantage of a statement cache, so that these queries would not
> > have to be parsed by the database every time and thus improve the
> > performance.
> >
> > I am using the latest JDBC thin driver (8.1.7), which provides
> > API for setting the size of the clients statemet cache. This works
 fine
> > with a physical instance of a PooledConnection. However if am trying
> > to use it in combination with OracleConnectionCacheImpl class I will
> > only get access to logical connections which don't let you set
> > their statement cache. I tried calling
> > setStmtCacheSize()
> > on the OracleConnectionCacheImpl which didn't work. The prepare
> > statement on the 2nd connection just hanged. The first connection
 was
> > bizzarly fine..
> >
> > How can I set the statement cache for all connection created by the
> > OracleConnectionCacheImpl class?
> >
> > Insted of setting this from the java program I could surly set the
> > connection cache somehow for the whole database. How do I do that?
> > This could be a workaround if the oracle's java API doesn't let me
 do
> > it.
> >
> > any responses would be appreciated.
> >
> > thanks,
> >
> > Miso
> >
> > Sent via Deja.com
> > http://www.deja.com/
>
>

Sent via Deja.com
http://www.deja.com/ Received on Wed Jan 31 2001 - 10:01:32 CST