This content has been marked as final. Show 3 replies
use bind variable in the code
Preserve report formatting by using the code tag.
Buffer Cache Hit Ratio is debunked as a method of performance tuning.
See http://jonathanlewis.wordpress.com/statspack-examples/ for AWR strategies.
Does this AWR report correspond to a period of performance problems?
Do you have an AWR report from when performance was ok?
The numbers in a report depend on the timeframe.
This isn't obviously a particular "busy" database.
But assuming this report is relevant to the problem, relative to soft parses you're doing quite a lot of hard parses.
Relative to logical io you're doing a high amount of physical IO.
12 gig is not much memory but we know nothing about your database.
See link above and examine other sections of your report.
AWR is a database level report. SQL Trace might be a better approach for specific application functionality.
as Dom has already said, possibly the best approach is to ask your users what exactly is slow, and trace it. Having said that, a couple of things don't look right in your AWR:
1) 41 hard parse per second -- that's a lot for a system with only 2 CPUs (considering that you only have 282 executes per seconds). You should look at DB and OS CPU metrics, and pay close attention to library cache contention events
2) you are doing 29.9 rollbacks per second (!). Actually, the majority of your transactions are rollbacks (!!). Do you have any reasonable explanation for this? Rollbacks are very expensive performancewise and should be used sparingly.
For a more complete analysis, please post top timed events and time model statistics sections. Be sure to preserve formatting using
tags. Best regards, Nikolay