JL Computer Consultancy

The following item was recently posted on the Oracle-L list. Since it captured a couple of questions that I hear quite often, I thought I’d answer it here.

I am running OLTP system on Oracle 10.2 and J2EE weblogic.

I am getting some ratios as:

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.

The answer I gave was as follows:

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 “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 respectively on the latches related to the library cache and shared pool.

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 default behaviour changed in 9.2.0.5). Use the two statistics session cursor cache hits and 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 may 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 and compare that with the statistic 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, in which case the reported rollback per transaction % can probably be ignored as a minimal threat.

Back to Index of Topics