RE: Statspack ratios help

Could it be, that lots of "soft parses" are due to the application that
constantly connects/ disconnects (5.54 Logons per second), but still
executes the same sql in different sessions? 

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Jonathan Lewis
Sent: Wednesday, June 07, 2006 3:25 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: Statspack ratios help


Execute to Parse %:    9.62         

    This is defined by
        100 * (executes  - parses) / executes
    In your case (using the per-second figures)
        (1085.95 - 981.47) / 1085.95

I don't tend to look at the ratios as they lose scale, whereas the
absolute figures give you some idea of the possible size of the problem.

In your case, the numbers are telling you that a lot of the time you
seem to do a parse call for every execution.
Combine this with the fact that most parse calls are reported as soft
parses  (981.46 out of 981.47 per second), and you can see that you have
some scope for improving CPU and latch activity by changing your code to
use "held cursors".

BUT:
(a) The improvement may be very small - and depends on 
    whether you are losing a significant amount of CPU time 
    and wait time in latch spins and sleeps on the library cache
    and shared pool latches.

(b) You may get most of the potential benefit anyway by
    taking advantage of the session cursor cache - have you
    got the session_cached_cursors parameter set to some
    reasonable value (the defaults changed in 9.2.0.5).  
    Use the two statistics:
        session cursor cache hits
        session cursor cache count
    to see what use you are making of each session's cache.


Rollback per transaction %:   44.17

This can be very deceptive - and can be seriously distorted by the
front-end tools the end-users have.  To see if these "rollbacks" really
are doing work rolling back, you need to check the statistics:
    rollback changes - undo records applied compare that with 
    db block changes
to see how many of your db block changes are forward changes, and how
many of them are due to rolling back.

It may be that your software does something like:
    query database
    rollback;    -- redundantly
a lot of the time.



I've posted this note - with names removed - on my website as it's
useful to have real examples of statspack data to use when explaining
principles.


Regards

Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html


> Date: Tue, 6 Jun 2006 14:05:07 -0400
> From: "Sandeep Dubey" <dubey.sandeep@xxxxxxxxx>
> Subject: Statspack ratios help
> 
> 
> Load Profile                            Per Second       Per
Transaction
> ~~~~~~~~~~~~                       ---------------
---------------
>                  Redo size:            156,162.18
3,020.55
>              Logical reads:             26,407.64
510.79
>              Block changes:                904.27
17.49
>             Physical reads:                  0.39
0.01
>            Physical writes:                 34.01
0.66
>                 User calls:              5,863.32
113.41
>                     Parses:                981.47
18.98
>                Hard parses:                  0.01
0.00
>                      Sorts:                 16.97
0.33
>                     Logons:                  5.54
0.11
>                   Executes:              1,085.95
21.00
>               Transactions:                 51.70
> 
> 
> Rollback per transaction %:   44.17
> Buffer Nowait %:  100.00       Redo NoWait %:   99.97
>            Buffer  Hit   %:  100.00    In-memory Sort %:  100.00
>            Library Hit   %:  100.00        Soft Parse %:  100.00
>         Execute to Parse %:    9.62         Latch Hit %:   99.88
> Parse CPU to Parse Elapsd %:   69.06     % Non-Parse CPU:   91.60
> 
> With 100% soft parse, execute to parse ratio is so low. Is it bad, how

> I can I improve it?
> 
> I see rollback per transaction as 44.17. We are using Hibernate that 
> generates database mapping and produces most of the SQLs. How can I 
> invetigate further? But I doubt if application is doing some big time 
> rollbacks.
> 

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



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

Other related posts: