Result Evaluation |
Since database design and usage can differ a lot between different systems,
there is no general recipe to success on the evaluation of the reports (not
even on the configuration of OSPRep). Nevertheless, there are several places
of the report that may deserve closer attention even on the first evaluation
and/or by the novice admin.
|
Charts |
The charts generated by charts.sh will let you know whether there
are continous or sporadic problems. There are four different chart types
available:
|
Link | Chart |
Comment |
Cumul | Cumulative Timeouts |
Since the values are collected cumulative, the graph will either be
a horizontal line, or the values will increase. |
CumAvg | Timeouts per Snapshot |
Based on the same cumulative collection, values are divided by the
amount of snapshots since instance startup. |
Delta | Timeouts (Differential) |
Based on the same cumulative collection, differences between the
snapshots are evaluated and displayed in this diagram. |
Ratio | Ratio Stats |
Provide diagrams on some important ratios (that's why they are named
such), plus some other statistics like logon counts. |
|
Problem zones are most easy to identify using the Cumul and Ratio charts. The
longer the interval between the first and the last snapshot evaluated gets,
the more useless the Delta charts become: with really large intervals, they
look like someone just brushed the screen :-) But they may be really useful
for short intervals.
|
Reports: Go4Colors |
For a quick survey of your databases values, you can use the "Go4Colors" mode:
just open the report and scroll through it looking for the colors "red" and
"yellow" (refering to the default stylesheet shipped with OSPRep). If there's
no cell with one of those background colors, there should be no problems. If
there is, you may wanna stop to investigate at this point. See the section on
Go4Colors below for more details.
|
Reports: Detailled |
A good point to start is to see the "Instance Efficiency Percentage". In this
section you can get a general idea on your database instances performance.
Most items in this table provide you a link to the part of the report to
continue with if problems are indicated by the efficiency percentage of the
entry concerned.
|
Next, you may wish to evaluate the "Top 5 wait events". Again, here are links
provided to guide you to other segments of the report giving you closer/further
information on relevant topics.
|
These are just some ideas of where to start and what to keep in
mind. Of course, there are many more things to consider - but either you already
know about these, you will learn about these, or you will find somebody else
to tell you: this documentation is not intended to be a seminary on Oracle
Performance Tuning, there are other sources on this. If OSPRep (with its
documentation, reports and hints) got you started on this topic, or gave you
new/more ideas about it, I did a good job :-)
|
Go4Colors |
The "Go4Colors Mode" is intended to highlight possible problems in order to
not overlook them. As a side effect, the DBA that supervises many instances
has a quick way to determine whether there are possible problems that need
his attention without reading the whole report. However, one should not rely
on these colors, since they are mostly based on arbitrary values: again we have
no perfect recipe on what "regular values" should look like - and a value
suitable in an OLTP environment already could indicate issues for tuning in
a batch environment, and vice versa.
|
Normally, a table cell with yellow background indicates a "warning", red
background means "alert". But again, you shouldn't take these indicators
literally: everything very much depends on your environment. So even a cell
with red background doesn't mean you have to panic!
|
The values that lead to a "warning" or "alert" are at this time hard coded.
Maybe with a future release I will separate them into configurable files, so
we could even have one file for OLTP and one for Batch, plus everybody could
define his/her own one... But right now, this is not the case. So if you want
to know what values are defined, please refer to the following table:
|
Parameter | Warning |
Alert |
Instance Efficiency |
Buffer NoWait | < 80 | < 60 |
Redo NoWait | < 90 | < 75 |
Buffer Hit | < 50 | < 30 |
In-Memory Sort | < 85 | < 70 |
Library Hit | < 80 | < 60 |
Soft Parse | < 80 | < 60 |
Parse CPU to ELA | < 80 | < 60 |
Latch Hit | < 85 | < 70 |
TOP_N_SQL |
FTS in execution plan | | Cost > 1.000 |
Datafiles Statistic |
Size Change in %/7d | > 50 | > 100 |
TableSpace & File IO Summary |
Avg Blks/Rd measured
by
DB_FILE_MULTIBLOCK_READ_COUNT |
> 80% | > 100% |
AvgRd (ms) | > 50 | > 80 |
Library Cache |
PctMiss | > 15 | > 40 |
Reloads/GetRequests | > 10 | > 30 |
Invalidations/GetRequests | > 10 | > 20 |
|