Re: parse calls

sdbillsfan_at_gmail.com wrote:

> I'm curious why I'm getting a reparse for every execution in the
> following scenario (this is a simplified example of a real problem):

Soft parses. It is correct. Each [select * from dual] in SQl*Plus does the following:

1. open a statement handle
2. parse the statement handle
3. bind any parameters required.
4. execute the statement handle
5. closes the statement handle

Oracle will do a hard parse the first time and a soft parse afterwards as the SQL statement will already exist in the shared pool.

SQL*Plus is however a poor example (for good reason) of how to write client code that correctly use repeatable SQL handles.

This is what a client should do:

1. open a statement handle
2. parse the statement handle
3. start loop

    3.1 bind any parameters required.
    3.2. execute the statement handle
4. end loop
5. closes the statement handle

As the handle is re-used, it is parsed once. Hard or soft depending on whether or not that SQL already resides in the shared pool. Then then handle is used repeatedly by the application until done. Only then is it closed.

Worse case - you see a lot of hard parses. This usually means non-shareable SQL and no bind variables.

Better case - you see a few hard parses and lots of soft parses. Shareable SQL is used, but the clients are no re-using their own SQL statement handles.

Best case - just a few hard and software parses. Clients are correctly re-using their statement handles (which implies shareable and bind variable SQL).

--
Billy