Re: hard vs soft parse

I have to plead ignorance of PERL, but

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

and

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

        'select from dual' that you give.  Write a loop that
        'reuses the cursor' 1000 times say, and see how
        many soft parses this causes in your session.
        Given the meaning of 'prepare' in a purely Oracle
        context, I wouldn't be very surprised if you get one
        parse per trip around the loop.

        (This is behaviour that may have changed between
        versions 7 and 8, by the way - you may find that
        in a v7 environment you get just one parse).

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.

--
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
<87itpghtpg.fsf_at_HSE-MTL-ppp66894.qc.sympatico.ca>...


>

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

>

>> I think that there is a mis-print here,  surely is should read:

>>

>> >"A high soft parse rate is important because unnecessary HARD parses

>>

>> Most applications will have to go through the parse/execute/fetch

>> cycle to get data, OLTP systems rarely have a natural design

>> that makes it straightforward to code for re-binds, which is how

>> you explicitly avoid re-parsing.

>

>Uhm, sure they do if you use a flexible enough language. You either use a a

>persistent variable to store the cursor from one invocation to the next, or

>you store cursors in a cache keyed by the query text and look them up on

 the


>client side.

>