We began this chapter by pointing out that, on one level, poor performance is easy to defineit occurs when the expectations of your user community, in terms of response time, are not met.
Of course, this is a situation you want to actively avoid. You can accomplish this by proactively monitoring potential sources of performance degradation. Oracle has a number of tools you can use to monitor resource usage and performance.
The dynamic performance views mentioned earlier in this chapter are a key source of information about the operation of the Oracle database. Although Oracle supplies an increasing number of automatic and graphical methods for tracking and examining the same information, most of these tools use the V$ tables to obtain the data they need.
Enterprise Manager (EM) was described in detail in Chapter 5, although certain features of EM are worth highlighting here in terms of their use in performance monitoring and tuning.
Enterprise Manager provides both high-level and detailed views of resource utilization for Oracle servers, and these can give a quick indication of the cause of performance problems. EM also has a complete system of alerts and thresholds, which can be set up to proactively inform you that a particular resource is nearing a critical usage level. Enterprise Manager includes a set of advisors, which can be run to give you suggestions on how to optimize performance in different areas, such as tuning SQL statements or suggesting new materialized views (both new in Oracle Database 10g.)
Enterprise Manager 10g has the ability to immediately spot the SQL statements that have used the most database resources and SQL statements that may not be fully optimized, as well as presenting the execution plan for these statements. These tools are handy for calling out specific statements that may be impacting the operation of the Oracle database environment.
Enterprise Manager 10g also includes a new type of performance analyzer called Application Performance Management (APM). APM gives you the ability to set up beacons, which are client processes that periodically execute transactions and report the response time. APM goes beyond the Oracle environment to help you understand performance from an end user's point of view, which can, in turn, help you to spot other sources of performance problems, such as network transmission slowdowns.
The Automatic Workload Repository (AWR) is a new feature in Oracle Database 10g. The AWR captures and stores information about resource utilization by Oracle workloads. By default, statistics are captured every 30 minutes and are stored for 7 days.
AWR helps Oracle Database 10g to identify potential performance issues by comparing workloads over time, and acts as the foundation for many of the manageability features in Oracle Database 10g, such as the Automatic Database Diagnostic Monitor (ADDM). The ADDM automatically identifies and reports on resource bottlenecks, such as CPU contention, locking issues, and poor performance from specific SQL statements.