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:
LinkChart Comment
CumulCumulative Timeouts Since the values are collected cumulative, the graph will either be a horizontal line, or the values will increase.
CumAvgTimeouts 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)
DeltaTimeouts (Differential) Based on the same cumulative collection, differences between the snapshots are evaluated and displayed in this diagram.
RatioRatio 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:
ParameterKey WarningAlert
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 planAR_EP_FTSCost > 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.
w3cOSPRep © 2003-2007 by Itzchak Rehberg & IzzySoftIzzySoft