Oracle 10g Statspack

Last month, I had the opportunity to hear TUSC's rich Niemiec about Oracle's performance analysis issues in the Dallas Oracle user group, one of the tips he gave us, which I felt was very beneficial, that Statspack still needed in Oracle 10g.

The most talked about Oracle 10g performance is the new automated Workload repository (AWR) feature and proactive performance monitoring through automated database diagnostics monitoring (ADDM). They are widely reported as a substitute for statpack. It is true, according to Mr. Niemiec, that they can do more to represent the future of Oracle performance analysis.

However, he also said not to ignore Statspack, not only because it still exists in Oracle 10g as an inheritance, but it has also made a number of updates, including a number of new tools that can be used in awr. In order to fully use AWR, you need to add additional registrations to the diagnostics and analysis packages, but Statspack is already included in the Oracle price.

Here are some of the new features that Mr. Niemiec mentioned in Oracle 10g Statspack:

• Time Model Statistics: Oracle's time model contains db_time, the time required to process database requests, and the various wait times when a session waits for a resource. Time model statistics decompose db_time into its constituent parts, so you can see the processing time that Oracle spends by manipulating the type.
• Wait for event histogram: Instead of recording all the counters for how many waits when each type occurs, the histogram continues to decompose the various counts by the time. You can see how many wait times are less than 1ms, how much less than 4ms, and how much less than 8MD waits. If you want to test wait times, this may not be a priority analysis tool, but they are simple.
• Reading a file histogram: like waiting for an event histogram, it breaks down the count of disk blocks by the time it takes to read, and you can more easily see which devices are not performing well.
• Operating system Statistics: It shows the system's CPU idle time and user time ratio, active time and idle time ratio and the number of bytes in and out.
• Undo statistics: This is a detailed method of measuring the efficiency of the undo section.

Although some new options have been added, such as setting the maximum capacity to create tables when running scripts, the process of running statspack has not changed much.