Some Mistakes of using Statspack: Fenng
Statspack is an instance-level TUNING tool provided by Oracle. Many DBAs like to use this tool to optimize the database. However, there are some problems with many friends' use in this tool. The following is a simple analysis of several aspects of the problem.
Sampling time interval on snapshots
We know that the result of Statspack's Report is actually compared to two snapshots (Snapshot, which is the current state of the database).
Under normal circumstances, the expert recommends that the snapshot time interval of the StatsPack report is 15-30 minutes.
Imagine that a person went to the hospital to see a doctor, the doctor measures the body temperature, generally 5-10 minutes or so, why is this so long? Since this time is basically approximation in 5-10 minutes to get your body temperature. If the fruit is too short, it may not reach the established purpose, the measured body temperature will be low, the time is too long, even for a few hours (assuming this), the patient may be coma a few times;).
This is also true for the snapshot time interval for generating a STATSPACK report. If the two SNAP TIME times are too short, some of the main periodic transactions of the database may have not yet been running, and the information collection is not complete. If the interval is too long, the data will be deviated.
Suppose the following situation: The system has been normal, but there is a user in recent days, and the application execution is slow in the A-time application. The time period is normal, and the A time period has a major transaction X run (also the transaction used by the user). The B time period has another transaction Y is running. The spans of the A and B time sections are relatively large. Originally your snapshot If you have been able to collect more accurate data over a period of time, but not enough, your time span used by your Report is too long, thus opening the B timection. Statistics also collected. STATSPACK has been compared, "think" The transaction Y is the main impact on the system (this will also be reflected on the Report), and you, after analysis, it is the case of the culprit, next, you spare no effort to TUNING ......
A problem occurred! After adjusting B, the user continues to report, and the system does not become fast, but it becomes slower, but can't bear it. This situation is dangerous, which may cause damage to the system. In a stricter environment, this has constituted a more serious accident. Perhaps you also have to admit that STATSPACK's snapshot sample time interval really needs to pay attention ...
This is a StatsPack report under the Oracle 8.1.7.0.1:
Snap ID Snap Time sessions ------------------------ -------- Begin Snap: 637 04-Aug-03 11:59 : 33 25 END SNAP: 646 04-AUG-03 16:29:06 25 Elapsed: 269.55 (mins)
It can be seen from which snapshot 637 and snapshot 646 are 269.55 (mins). Such a long time span, even if there is a problem within a certain time interval, there will be deviation here.
The following That STATSPACK report is a bit unreliable:
Snap lengthStart ID end id start time end time (minutes) ---------------------------------- ----------------------------- 314 1053 11-DEC-03 18:07:13 19-DEC-03 10:53: 02 11, 085.8211, 085.82 minutes? Data acquisition and analysis in such a long time is afraid that most of the content cannot be believed.
It is also important to note that the time interval we said is the interval between the BeGin Snap and End Snap, not the interval between the two SNAPs. For spackers collected by SNAP, it is recommended to make pressure on performance and storage for performance and storage in order not to affect performance. For so-called 15-30 minutes, it cannot be inquiry. Adjustments should be adjusted in a specific environment.
Partly
STATSPACK is essentially a sample of the performance statistics of the system, then analyzing, sampling, there will be deviation. How to eliminate deviation? Statistically pointed out that "the difference in differences in the number of samples is reduced". Therefore, only a Report document is determined to determine the performance problem of the database in somewhere, which is a more procedure (except for individual situations). Also dba creates more reports, compared to analysis, it will play a good effect. It is best to submit a few Report when seeking technical support, making it easy to help solve the problem quickly.
About TIMED_STATISTICS parameters
Although this is a low-level mistake, it is regrettable, often seeing some friends ignore the parameters. If the value is set to false at the value of TIMED_STATISTICS, it can be said that the collection of things is not very large (I think you will only want to see some instance names, initialize the information such as the parameters). It can even be said that if the parameter is not set to true, performance analysis is not coming. Related Information "Expert one-on-one Oracle" by Thomas Kyte "Advanced Tuning with Statspack" From OTN "Statspack Guide" by eygle "Performance Tuning with Statspack" PartII From OTN "Performance Tuning with Statspack" PartI From OTN
Original source: http://www.dbanotes.net/oracle/aboutstatspack.htm
