Re: hard vs soft parse

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> writes:

> I have to plead ignorance of PERL, but
>
> a) I think the feature you are describing can be achieved
> in PRO*C by fiddling with the HOLD_CURSOR,
> RELEASE_CURSOR and MAX_OPEN_CURSOR
> directives

Ah, I was always assuming something like Perl's DBI or Java's JDBC where everything is handled using handles to objects representing the database connections and cursors. In that type of environment you can throw the handles around freely storing them in a cache, either for connection pooling or caching closed cursors.

> b) Have you you ever run a simple test with the sample

It works. But that's because the Perl driver handles this cache entirely at the application level. What fails is that when the database structure changes the cursor becomes invalid and the application has to do some footwork to recognize that and call prepare again.

> You are quite right, of course, a suitably powerful
> and flexible environment could allow you to parse
> a statement once, then re-bind on demand. But a
> suitably powerful enforcement of methods and standards
> might also be needed to avoid runaway consumption
> of resources.

Ah yes. Mainly that *every* query has to use placeholders religiously. If any constants are embedded in the query and change on every invocation then the cache will be badly broken. Either it will flush out the other cursors or it will grow till it hits the max_open_cursors limit.

It also means you can never have the same query active twice in the same thread. That works fine in a single threaded OLTP environment where that's probably an error. But in a multi-threaded environment it would require a cleverer cache that could keep multiple instances of the same cursor around and then you have to worry about some call not cleaning the cursor up when it's finished.

-- 
greg