Re: hard vs soft parse

Interesting co--incidence following this mail.

I've just been to a site using Oracle Application Server 4.0.7, and the server ends up bombing users out with error 1001 (invalid cursor) every now and again.

After rigorous checks, it seems fairly clear that the problem is being caused by OAS caching cursors and then re-using the cached cursors after they have become invalid. (NB not due to limited cursor space, maxopencursors, etc.)

I think it's only a development problem - it is possible that it happens only after packages have been re-compiled.

--
Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Practical Oracle 8i:  Building Efficient Databases

Publishers:  Addison-Wesley
Book bound date: 8th Dec 2000
See a first review at:
http://www.ixora.com.au/resources/index.htm#practical_8i
More reviews at: http://www.jlcomp.demon.co.uk/book_rev.html



Greg Stark wrote in message
<873dgigfpx.fsf_at_HSE-MTL-ppp66894.qc.sympatico.ca>...


>

>

>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.

>