Obtaining Oracle System Performance Statistics is actually very simple

The following article mainly describes how to obtain the Oracle System Performance Statistics. Many people have asked me questions about Oracle performance. For example, what is slow performance? How to correctly quantify performance statistics? I will not list the issues one by one. Most of the text below comes from Oracle English documents.

If performance problems are effectively diagnosed, performance statistics are required. Oracle can generate statistics based on the overall system performance, sessions, or individual SQL statements. Oracle can also track and obtain the cumulative value of statistical data in segments and services.

When we focus on performance problems in a certain aspect, we only need to take the statistical data of this range and make a judgment. A typical method is to obtain the "first" and "last" of the statistical data during the sampling period, determine the difference in the period, and determine the new energy problem accordingly.

Statistical data, usually from the system startup to the current cumulative statistical data, can be obtained through dynamic views such as V $ sesstat v $ SYSSTAT. Therefore, any shutdown operation on the database system will reset these statistics.

Oracle System Performance Statistics: Oracle 10g/11g contains the following types of statistics:

AWR statistics, accumulative statistics on system operation;

Measurement value, the change value of the differential description of accumulative statistics;

Sample data, statistical data about active sessions.

In version 10, Oracle provides an automatic workload archive function, AWR for short, that is, automatic performance sampling. The data covers most statistics except session-level statistics. This process is repeated over a period of time. Each sampling result is called an AWR snapshot. The difference between each snapshot describes the change in the system performance status. Therefore, this snapshot represents a change in the system during the sampling period.

In addition, Oracle automatically collects another type of statistics, which is called measurement value. The so-called measurement is the degree and ratio of change during the sampling of a cumulative statistical data. "Measurement" covers various performance aspects, including response time, transaction processing throughput, number of database calls, and number of disk reads and writes. The measurement value can be obtained through the dynamic view of the V $ type. The data in the v $ view is an average value within a relatively small interval, which is generally a period of 60 seconds.

Oracle System Performance Statistics: Recent measurement values can be obtained through the v $ view, and long-term measurement values can be obtained through the AWR snapshot.

There is also a statistical data collected by Oracle, called sampling data. The sampling mechanism for sample data is added to Oracle10g, which is called automatic session history (ASH ). The sample data is executed by ASH. ASH samples the current status of all active sessions. The sample data is collected to the memory and accessed through the V $ view. This type of data is also automatically collected during AWR workload sampling.

Oracle10g provides a powerful method for diagnosing system performance problems: comparison. First, we need to establish a statistical baseline. This baseline can use a collection of statistics from the system during efficient and stable operation. Based on this baseline set, compare the performance of the system to find the problem. This method effectively solves the problem that we are unable to cope with thousands of database performance statistics, and avoids us from falling into the ocean of statistics.

The acquisition of the statistical baseline is implemented through AWR. Through AWR, we specify the measurement baseline and the time range for saving the AWR snapshot, or a similar setting, and use the statistical data during this period as the statistical baseline. Obviously, this time range should be selected with caution. The most reasonable measurement baseline is the cumulative statistics obtained when the system has good performance under normal loads. In this way, when the system encounters performance barriers, the root cause of the problem is obtained through comparing the quality of the problem.

All the statistics mentioned here-real-time performance data or AWR historical snapshot data can be viewed through Oracle Enterprise Manager (OEM. In addition, the Enterprise Manager can also be used to capture operating system and network statistics related to AWR statistics.

The above content is an introduction to the Oracle System Performance Statistics. I hope you will get some benefits.