Re: Bind Variables...

Mark-

You say the statement of mine "reproduced in the end" is incorrect. I am not sure what you mean, but perhaps you are doubting my assertions about parsing, especially that it is application-driven and my differentiation between hard and soft parsing. I stand by my statements and offer the following clarifications.

The reason an applications might "grind to a halt" from non-use of bind variables has nothing to do with the parsing or binding processes themselves. It happens because the sql presented to the server is always unique by virtue of having "hard coded", constant predicates. Once a cursor is opened from its initial parse, it looks for all practical purposes just like any other statement. It has a unique hash value that identifies it and if it remains in the library cache intact, it can be reused. That is what sharing is, no more. Any statement/cursor is inherently sharable.

I mentioned before there are two general types of parse, "hard" and "soft". When a statement is first presented, a unique hash value is generated for the sql text string. The hash value is a key for looking up the statement in the library cache. Being new, it is not found. Then a cursor must be constructed in the library cache. This is known as a hard parse. Lots of work must be done to syntax-check the statement, to verify permissions, to maybe translate synonyms, to make sure the referenced objects exist, including columns, create a plan, etc. Each little piece requires its own chunk of memory, which comes from the available portion of the shared pool. Busy busy for both the library cache and shared pool latches.

If a statement is presented again in the exact same text string, the user session's process generates that same unique hash value and this becomes a "lookup" key for the library cache. When the statement is found, it can be executed again. It has to be fully intact, referenced objects cannot have changed, permission rules still apply, etc. This revalidation process is known as a soft parse. Much cheaper than a hard parse, but it still involves considerable work. Heavy soft parsing is evidenced by fairly high 'latch free' waits, mostly for the library cache latch. Little or no shared pool latching, because the bits and pieces are typically intact.

If the application closes a cursor, it loses its reference to the cursor in the library cache and another "open" of the cursor involves a soft parse. If the application keeps the cursor open, that reference is not lost and the cursor can simply be executed again without that soft parse.

You mentioned you were not talking about "application parsing", but to the database, a parse request is still a parse request. There is no distinct thing as an "application parse".

When bind variables are not used in an application, since each statement tends to be unique (with such notable exceptions as 'select sysdate from dual') then that hard parse effort has to be made for each statement. Over time, the work involved in finding free memory and flushing old unused cursors out of memory, reclaiming and reusing that memory takes increasing amounts of time. The free memory of the shared pool gets fragmented and it takes ever longer to find each suitable piece. Nasty business. The "grinding to a halt" is made worse when the shared pool is enlarged - more fragmentation and more time to scan for specific-sized pieces of memory.

So, a statement with bind variables is not treated any differently with respect to parsing than a non-bind statement. The only real difference, other than the variable binding itself at execution time, is that statements with bind variables have those "placeholders" that make the statement's text string common. The bind values themselves are presented to the server as a separate step. Each new execution with new bind values simply uses an existing statement by substituting the placeholders with the bind values. This is how the "sharable" sql statement is in fact shared, the same way you can "share" a document or spreadsheet template.

A statement without bind variables does not get "compiled" again unless it gets flushed out of the library cache and has to be reloaded - effectively hard parsed again. If a non-bind statement is executed frequently, it will stay in the lib cache and won't have to be hard parsed. If the application keeps that non-bind cursor open, it can be simply executed repeatedly without even a soft parse, the same as a bind variable cursor. The problem is, most non-bind statements don't get repeatedly executed. With that 'select sysdate from dual' exception I noted earlier.

So it's not that non-bind variable sql gets parsed again, rather it *never* gets parsed again and only occupies space. Each statement tends to be new and unique and is hard parsed. Little or no soft parsing and no "execute only".

Regarding debugging, bind variables are easy enough to debug. You need to learn the 10046 trace event and output, or use the 'sql trace in session' procedure with the extended "level" parameter. I guess your 4gl tool sucks, sorry. But debugging should not qualify as a reason to avoid bind variables. Speak to your tool vendor in a firmer voice.

Hope this helps,

• ricky

mark wrote:
>
> Hello Ricky
>
> I do not use Oracle 9i and so i cant comment on it. But this statement
> of yours reproduced in the end <<seems>> to be incorrect. I remember
> reading some manuals and Thomas Kytes first chapter( which is free
> ;-)) where he had mentioned that not using bind variables might cause
> the application to grind to a halt. This is because it
> compiles(parses) it again. I think Howard Rogers book downloadable
> from tripod again says it.
>
> We are NOT talking about application parsing here. I do know that we
> can open cursors in the application in memory, reuse it again and
> again. I do not think Oracle reparses the ones parsed again , if the
> exact SQL can be found in memory.
>
> Again my Oracle brain is not the brightest ;-), but still the general
> rule remains. Avoid binds if you have a Sql that executes once or a
> very few times and the time of execution is not a problem. Use binds
> for repetitive operations.
>
> One of the **biggest** advantage of not using binds is that they can
> be debugged more easily. I am referring to 4GL tools which can be used
> to connect to Oracle. It is a nightmareto debug applications which
> have already been sold to the client if they have binds?. Oracle
> (previous to 9i) has some undocumented features to write the value of
> the binds to the trace file. But i do not know much about this.
>
Received on Thu Nov 08 2001 - 11:56:58 CST