Some pitfalls are discussed in the following sections:
- Hit ratios
- Wait events with timed statistics
- Comparing Oracle statistics with other factors
- Wait events without timed statistics
- Idle wait events
- Computed statistics
Interpreting Statistics
When initially examining performance data, you can formulate potential theories by examining your statistics. One way to ensure that your interpretation of the statistics is correct is to perform cross-checks with other data. This establishes whether a statistic or event is really of interest.
Some pitfalls are discussed in the following sections:
- Hit ratios
When tuning, it is common to compute a ratio that helps determine whether there is a problem. Such ratios include the buffer cache hit ratio, the soft-parse ratio, and the latch hit ratio. These ratios should not be used as 'hard and fast' identifiers of whether there is or is not a performance bottleneck. Rather, they should be used as indicators. In order to identify whether there is a bottleneck, other related evidence should be examined.
- Wait events with timed statistics
Setting
TIMED_STATISTICS
to true at the instance level directs the Oracle server to gather wait time for events, in addition to wait counts already available. This data is useful for comparing the total wait time for an event to the total elapsed time between the performance data collections. For example, if the wait event accounts for only 30 seconds out of a two hour period, then there is probably little to be gained by investigating this event, even though it may be the highest ranked wait event when ordered by time waited. However, if the event accounts for 30 minutes of a 45 minute period, then the event is worth investigating.
Note:Timed statistics are automatically collected for the database if the initialization parameter
STATISTICS_LEVEL
is set toTYPICAL
orALL
. IfSTATISTICS_LEVEL
is set toBASIC
, then you must setTIMED_STATISTICS
toTRUE
to enable collection of timed statistics.If you explicitly set
DB_CACHE_ADVICE
,TIMED_STATISTICS
, orTIMED_OS_STATISTICS
, either in the initialization parameter file or by usingALTER_SYSTEM
orALTER
SESSION
, the explicitly set value overrides the value derived fromSTATISTICS_LEVEL
. - Comparing Oracle statistics with other factors
When looking at statistics, it is important to consider other factors that influence whether the statistic is of value. Such factors include the user load and the hardware capability. Even an event that had a wait of 30 minutes in a 45 minute snapshot might not be indicative of a problem if you discover that there were 2000 users on the system, and the host hardware was a 64 node machine.
- Wait events without timed statistics
If
TIMED_STATISTICS
is false, then the amount of time waited for an event is not available. Therefore, it is only possible to order wait events by the number of times each event was waited for. Although the events with the largest number of waits might indicate the potential bottleneck, they might not be the main bottleneck. This can happen when an event is waited for a large number of times, but the total time waited for that event is small. The converse is also true: an event with fewer waits might be a problem if the wait time is a significant proportion of the total wait time. Without having the wait times to use for comparison, it is difficult to determine whether a wait event is really of interest. - Idle wait events
Oracle uses some wait events to indicate if the Oracle server process is idle. Typically, these events are of no value when investigating performance problems, and they should be ignored when examining the wait events.
- Computed statistics
When interpreting computed statistics (such as rates, statistics normalized over transactions, or ratios), it is important to cross-verify the computed statistic with the actual statistic counts. This confirms whether the derived rates are really of interest: small statistic counts usually can discount an unusual ratio. For example, on initial examination, a soft-parse ratio of 50% generally indicates a potential tuning area. If, however, there was only one hard parse and one soft parse during the data collection interval, then the soft-parse ratio would be 50%, even though the statistic counts show this is not an area of concern. In this case, the ratio is not of interest due to the low raw statistic counts.
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/19602/viewspace-1003467/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/19602/viewspace-1003467/