Re: parsing puzzle

Whether you use bind variables has no bearing upon the number of parse calls the application requests of the database server. The connection between parse call counts and bind variable usage is that you can't pull the parse calls out of the loops in your application code if you don't use placeholder variables (that is, "bind" variables), unless (grin) your loop is supposed to process the same values over and over again.

Each parse call your application makes of your database server motivates a network round trip (see footnote). That is true whether you use bind variables or not. If you use bind variables in the parse calls that your application makes, then the database server can save a little bit of code path (including some serialized latch acquisitions), but not much. And you cannot reduce network I/O call counts by only using bind variables. Example:

  // A: This code will prepare() cardinality(S) times and network I/O 2 * cardinality(S) times.
  for every element E in set S {
    c = prepare("this is my SQL that refers to the literal value E"); // pseudocode; not real SQL

    execute(c);
  }

Code segment A is what you appear to have right now. Very unscalable because (a) those prepare() calls have to be serialized in a multi-user environment, (b) the prepare calls consume a ton of CPU capacity, and (c) it generates a huge number of network I/O calls.

(Footnote: There's a technical detail about "each parse call...motivates a network round trip," having to do with OCI command bundling. I'll spare you that explanation for fear that it'll complicate my answer too much.)

  // B: This code will prepare() cardinality(S) times and network I/O 2 * cardinality(S) times.
  for every element E in set S {
    c = prepare("this is my SQL that refers instead to the placeholder ("bind") variable :e"); // pseudocode; not real SQL

    execute(c, E); // Bind the value E into the placeholder :e   }

Code segment B is what you'd have if you just used bind variables. It's slightly more scalable than A because the prepare() calls require no shared pool latch acquisitions, but it's still horribly unscalable because (a) those prepare() calls consume almost as much CPU as in A, (b) those prepare() calls still have to be serialized (library cache latches), and (c) there's every bit as much network I/O as in A.

  // C: This code will prepare() 1 time and network I/O 1 + cardinality(S) times.
  c = prepare("this is my SQL that refers instead to the bind variable :e"); // pseudocode; not real SQL
  for every element E in set S {
    execute(c, E); // Bind the value E into the placeholder :e   }

Code segment C is what you want. It cuts prepare() call execution count to just one, which is as good as it gets unless you add another tier to your architecture and hold the cursor open as a service for multiple user programs to share. The prepare() call count reduction cuts wasted CPU consumption, it cuts competition for latches, and it cuts network I/O by approximately half. If you make your code that looks like A into code that looks like C, you'll make performance better.

On the other hand, if you make code that looks like A into code that looks like B, you're not going to be happy. This is why I'm sensitive to people who recommend just "use bind variables."

The reason that shrinking your shared pool helped you is that it prevented the database server from being able to store SQL statements in that, by virtue of using literal values instead of placeholders, will never be reused. Before you shrank it, your server was spending extra time storing, managing, and then searching information that was incapable of ever being useful.

Nobody can tell for certain from only the diagnostic data that you've provided, but the concurrency problems you're having may be due to contention for library cache or shared pool latches. I don't think you'll be able to tell whether you've got a network problem from the data you've provided, either, but I'd bet that your application is wasting a ton of your end-users' time executing network I/O calls that you could eliminate by changing your application source code so that it doesn't execute parse calls ("prepare" calls) inside of loops.

I hope you'll take a look at "For Developers: Making Friends with the Oracle Database<http://method-r.com/downloads/doc_details/10-for-developers-making-friends-with-the-oracle-database-cary-millsap>." It shows you how to collect diagnostic data for a problem like the one you're having. It uses an performance anti-pattern that's a cousin to the application that's causing your problem.

Good luck. I hope this helps.

Cary Millsap
Method R Corporation
http://method-r.com

On Thu, Apr 8, 2010 at 8:40 PM, Joe A-C <jwc7744_at_yahoo.com> wrote:

> We have an application that is suddenly doing a lot of parsing. The history
> behind this is that about 2 years ago the database was upgraded from 8i to
> 10g at which point performance went down hill. The diagnosis at the time was
> high amounts of parsing due to the fact that bind variables weren't being
> used. After increasing the shared pool didn't help, I eventually brought the
> problem under control by reducing the shared pool. The thinking was that
> since there was less memory to search before parsing (which inevitably
> happened) the time to run would be less. While the application was still
> slow it wasn't as painfully slow as it had been. The app was in this state
> until the vendor sent an upgrade which used more bind variables at which
> point I successfully went back to using auto memory management, thereby
> increasing the shared pool.
>
> Things were relatively calm until a few weeks ago when the parsing issue
> started again. The app owner says that there have been no changes to the app
> and that there is no different processing taking place. The only thing that
> changed is an upgrade to the network. The app owner is saying that the
> network is slower but I don't see how this could be related to high parse
> rates.
>
> I tried the original fix which was to reduce the shared pool to no avail.
> In fact it seemed to make things worse.
>
> Another possibility is that the app was doing a lot of parsing before and
> now the network is the real slow down. I do see an increase in the number of
> network waits but the total time of network waits is a fraction of the time
> for concurrent waits. Here's an example where the number of network waits is
> > 5 million but the total wait time is 360 secs while the total wait time
> for concurrency is 89k secs:
>
> Wait Class Waits %Timeouts Total Wait Time(s) Avg wait(ms) Waits/tx
> Concurrency 855,412 1.32 88,902 104 21.71
> User I/O 1,893,052 0.01 11,349 6 48.05
> Other 23,755 1.67 821 35 0.60
> Commit 40,550 0.11 671 17 1.03
> Configuration 757 89.04 669 883 0.02
> Network 5,602,717 0.00 360 0 142.22
> System I/O 59,770 0.00 295 5 1.52
> Application 549 0.00 6 10 0.01
>
> Can anyone think of how the network could affect the instance in this way?
>
> Any ideas would be appreciated.
>
> Thanks.
> Joe
>
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l