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".
Rollback per transaction %: 44.17
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.
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.