On Mon, 15 May 2000, Autarch wrote:
> On Mon, 15 May 2000, Jay Jacobs wrote:
>
> > mod_perl" and there's one thing I don't quite grok about it. Let's say I
> > have a site that goes through select statements like water. If I were to
> > cache the statement handler (as described in the guide), how does the
> > database (database dependant) handle a bunch of dangling statement
> > handlers? Will the database process size grow a ton? I'm not sure if I'm
> > hitting what I wanted to ask, but hopefully I'm close enough.
>
> Are you talking about the part that says you should use prepare_cached
> vs. prepare?
>
> If so, this doesn't do anything under most DBD modules, AFAIK. Under
> Oracle, it causes Oracle to hold the statement handle (an Oracle
> cursor)in cache after it generates the execution plan. If you open too
> many of these eventually Oracle complains (I think you can close them by
> wiping out $dbh->{CachedKids} which deletes the statement handles which
> causes them to go through their DESTROY). The oracle error you get is
> something like 'Too many open cursors' I think.
Actually, the two things you mention are different concepts. Yes, Oracle
does have a cache where it keeps most-recently-used execution plans. If
you make proper use of bound parameters in your SQL, you can exploit this
execution plan cache.
A statement handle is a handle that is associated with one of those
execution plans. Many handles may be associated with the same SQL
statement. You can bind parameters to the handle, execute it, and fetch
rows from it. There are resources associated with the handle, such as the
bound parameters and some state information. These are the resources that
are consumed if you agressively accumulate open statement handles on the
client side.
-jwb
