Oracle Essentials [Electronic resources] : Oracle Database 10g, 3rd Edition نسخه متنی

6.7 Monitoring

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.

6.7.1 Dynamic Performance Views

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.

6.7.2 Enterprise Manager

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.

6.7.3 Automatic Workload Repository and Diagnostics

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.