"Brian" <brian_at_databaseknowledge.com> wrote in message
news:1120223253.707519.229760_at_g44g2000cwa.googlegroups.com...
> I did some more research. A cursor is not parsed when the application
> keeps executing the same cursor over and over aging with the exact same
> values in the bind variables. So the Execute to Parse Ratio is really
> measuring how often the application keeps executing the same SQL
> statement (and getting the exact same results).
>
> Not sure why the target is 100%. A low ratio in my case really means
> users are passing different values to bind variables and querying
> different rows the DB.
>
No, you can avoid the soft parse if you change the value of the bind
variable but don't close and reopen the cursor, just reexecute the cursor.
Open the cursor once for the life of the application and just change the
bind variable and reexecute. I worked at a couple of companies and when we
did this we were able to scale very very well. The funny thing is that we
had to modify a popular load testing tool to do this because the company
that supplied the tool didn't know about this optimization. (Tom Kyte also
mentions this optimization in his books.)
Jim
Received on Fri Jul 01 2005 - 09:02:40 CDT
