 |
|
Oracle Tips by Burleson |
SPREPORT Load Profile
Earlier, while learning about the 3rd party
Profiler, there was a section with a resource profile. That
resource profile was used to show the various response-time
components. Similarly, the Load Profile section in the STATSPACK
report is a characterization of the amount of work that the database
was doing during the report interval. The components are measured
in two ways; per second and per transaction. These components are
statistics just like logical and physical reads as well as parsing,
sorting and other methods used to measure how busy a database is.
Each of the elements in the Load Profile
contributes to the amount of time it takes to process any given
statement. For example, if users are reporting widespread problems
with performance, and the number of sorts per second is
significantly higher than it was during the baseline measurement, it
may be helpful to look for any recently introduced code with heavy
sort activity. This could be related to code that is performing
unnecessary sorts or to an increase in the normal amount of data
being sorted, and may require an adjustment in the sort_area_size
for some or all users.
If there is a baseline measurement
available, the Load Profile section of this report is a good place
to start the analysis. If the baseline is from a time when there
was user consensus that performance was good, then the Load Profile
from a “bad” period can be compared to the baseline to help find any
discrepancies.
Instance Efficiency Percentages (Target
100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo
NoWait %: 100.00
Buffer Hit %: 92.80 In-memory Sort
%: 99.98
Library Hit %: 98.97 Soft Parse
%: 95.73
Execute to Parse %: 95.30 Latch
Hit %: 100.00
Parse CPU to Parse Elapsed %: 41.47 %
Non-Parse CPU: 73.16
Shared Pool Statistics Begin End
------- ------
Memory Usage %: 95.70 95.68
% SQL with executions>1: 69.23
85.19
% Memory for SQL w/exec>1: 46.21
56.05
The above book excerpt is from:
Oracle Wait Event Tuning
High Performance with Wait
Event Iinterface Analysis
ISBN 0-9745993-7-9
Stephen Andert
http://www.rampant-books.com/book_2004_2_wait_tuning.htm |
