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. Expects snapshots to
be taken on a regular base, e.g. once every hour (as is the default
when following the instructions delivered with Oracle StatsPack) |
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 alone, since they are mostly based on arbitrary values (if you
didn't set them up specifically): 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 (except you set up the parameters specifically): 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 to be
configured in the config file. The
table below lists the available parameters together with there corresponding
keys (used in the config file) and the initial values shipped
with the OSPRep archive:
|
Parameter | Key |
Warning | Alert |
Instance Efficiency |
Buffer NoWait | *_IE_BUFFNW | < 80 | < 60 |
Redo NoWait | *_IE_REDONW | < 90 | < 75 |
Buffer Hit | *_IE_BUFFHIT | < 50 | < 30 |
In-Memory Sort | *_IE_IMSORT | < 85 | < 70 |
Library Hit | *_IE_LIBHIT | < 80 | < 60 |
Soft Parse | *_IE_SOFTPRS | < 80 | < 60 |
Parse CPU to ELA | *_IE_PRSC2E | < 80 | < 60 |
Latch Hit | *_IE_LAHIT | < 85 | < 70 |
Resource Limits | *_RLIM | < 80 | < 90 |
Redo Wastage Percentage | *_RWP | < 20 | < 30 |
TOP_N_SQL |
FTS in execution plan | AR_EP_FTS | | Cost > 1.000 |
Seconds per Execution | *_ET | > 3600 | > 7200 |
Datafiles Statistic |
Size Change in %/7d | *_DF_CHNG | > 50 | > 100 |
TableSpace & File IO Summary |
Avg Blks/Rd measured
by
DB_FILE_MULTIBLOCK_READ_COUNT |
*_TS_BLKRD | > 80% | > 100% |
AvgRd (ms) | *_TS_RD | > 50 | > 80 |
Library Cache |
PctMiss | *_LC_MISS | > 15 | > 40 |
Reloads/GetRequests | *_LC_RLPRQ | > 10 | > 30 |
Invalidations/GetRequests | *_LC_INVPRQ | > 10 | > 20 |
|
If you have specific values for a "common specific" environment (such as an
"usual OLTP environment", or an "conventional DataWareHouse setup") concerning
the "Go4Colors" parameters, please send them to me, and I will be happy to
include specific configurations with the next versions of OSPRep.
|