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

On Fri, 03 May 2002 03:08:24 GMT, "Leigh" <leighlhg_at_shaw.ca> wrote:

>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
>
>

Powerbuilder, IIRC, by default always closes a cursor immediately, including cursors for list-boxes etc. and does not reuse them. Setting the SQLCache parameter to a non-zero value will force PB to cache n cursors.

Hth

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Thu May 02 2002 - 23:48:05 CDT