Statspack - negative execute to parse ratio

From: Leigh <leighlhg_at_shaw.ca>
Date: Fri, 03 May 2002 03:08:24 GMT
Message-ID: <IanA8.10798$GG6.841922@news3.calgary.shaw.ca>

While running statspack to diagnose a performance problem that my users complained about, I found that the snapshots taken while this problem occured (very slow switching between application screens while other users were running reports or other processes) indicated a negative 'execute to parse' ratio:

Instance Efficiency Percentages (Target 100%)

            Buffer Nowait %:   99.98       Redo NoWait %:  100.00
            Buffer  Hit   %:   98.81    In-memory Sort %:   99.97
            Library Hit   %:   99.16        Soft Parse %:   98.67
         Execute to Parse %:  -33.17         Latch Hit %:  100.00
Parse CPU to Parse Elapsd %:   70.50     % Non-Parse CPU:   99.98

 Shared Pool Statistics        Begin   End
                                            ------  ------
                Memory Usage %:   36.50   37.12

% SQL with executions>1: 73.20 63.62 % Memory for SQL w/exec>1: 67.10 62.24

Thomas Kyte in one of his Q&A (asktom.oracle.com) wrote that this is due to :
1) that means someone is parsing statements and NEVER executing them. They are just chewing up your CPU, latching the shared pool, killing your performance.
....
Followup:
There are tools (bad ones) that parse a statement but never execute it. Or they
parse it to discover what inputs/outputs it takes and then later on parse it again to execute it (so the parse/execute is 2:1).

It is commonly referred to as "inefficient coding techniques". I see it with
some 3rd "generic" libraries. They parse like there is no tomorrow -- works great in a single user environment -- starts to stink with two users (or more).

My question is: how to narrow down the culprit that cause this problem? Where do I look in the rest of the statspack report?

The application is written in Powerbuilder, and the users access the application through Citrix. Oracle version 8.1.6 on Window NT.

Thanks for your help,

Leigh Received on Thu May 02 2002 - 22:08:24 CDT

Statspack - negative execute to parse ratio - help!!!