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

6.1 Performance Tuning Basics

Database performance is one of the trickiest components of the
operation of your database. One of the curious aspects of performance
is that "good performance" is
defined by its absence rather than by its presence. You can recognize
bad performance easily, but good performance is usually defined as
simply the absence of bad performance. Performance is simultaneously
a very simple topicany novice user can implicitly understand
itand an extremely complex topic that can strain the ingenuity
of the most proficient database administrator.

Before getting into a specific discussion of Oracle performance, it
makes sense to define a basic methodology for investigating
performance problems.

There are three
basic steps to understanding how to address performance issues with
your Oracle database:

Define performance problems

Check the performance of the Oracle server software

Check the overall performance of the server machine

6.1.1 Defining Performance Problems

The first step in
performance tuning
is to determine if there actually is a
performance problem. In the previous section, we mentioned the
concept of poor performance, which even end users can recognize. But
what exactly is poor performance?

Poor performance is inevitably the result of disappointmenta
user feels that the system is not performing as expected.
Consequently, you must first evaluate how real the unmet expectations
are in the first place.

If performance expectations are realisticfor example, if
performance has degraded from an earlier level, you then need to
identify which of the system's components are
causing the problems. You must refine a general statement like
"the system is too slow" to
identify which types of operations are too slow, what constitutes
"too slow," and when these
operations are slowing down. For example, the problem may occur only
on specific transactions and at specific times, or all transactions
and reports may be performing below the users'
expectations.

Once you've defined the performance expected from
your system, you can begin to try to determine where your performance
problem lies. Performance problems occur when there is a greater
demand for a particular resource than the resources available to
service that demand, and the system slows down while applications
wait to share the resource.

6.1.2 Oracle Server Performance

The
first place to begin looking for resource bottlenecks is the Oracle
database software. You can use the Oracle Enterprise Manager
(described in more detail in Chapter 5) to identify less than optimal
use of Oracle's internal resources. Bottlenecks
within your database result in sessions waiting unnecessarily, and
performance tuning is aimed at removing these bottlenecks.

Oracle's dynamic
performance views are one of the most
powerful tools for detecting bottlenecks within your Oracle database.
All of these performance views have names that begin with
"V$", and, from
Oracle9i on, there are also global views (for
all nodes in a Real Application Clusters database) that begin with
"GV$". Two views, in
particular, identify the sources of these waits; these are invaluable
for guiding your analysis:

V$SYSTEM_EVENT

Provides aggregated, system-wide information about the resources for
which sessions are waiting

V$SESSION_WAIT

Provides detailed, session-specific information about the resources
for which individual sessions are waiting

You can use these two views to pinpoint the resources that are
causing the most waits. Focusing on the resources presenting the
largest source of waiting will provide the largest performance
improvements.

Oracle Database 10g includes an enhanced wait model that makes it easier to determine exactly who is waiting for what resource at what time.

You may find that your problem has a
simple source, such as a lower-than-expected database buffer cache
hit ratio, which indicates that the cache is not working at its
optimal level. For a resource like the cache, which is controlled by
the Oracle server software, you can simply increase the
initialization parameter DB_BLOCK_BUFFERS to
increase the size of the cache, which may improve the hit ratio.

Other situations may not be quite so clear-cut. For instance, you may
find that it takes a relatively long time to fetch database rows from
the disk. This situation may be caused by contention on the database
server's disks, which could be caused by less than
optimal placement of Oracle files on disk or by other applications on
the server.

Later in this chapter, we'll show you how Oracle
uses its own internal resources, such as disk and memory, and how
understanding this will help you to manage the resources allocated to
the Oracle database software.

6.1.3 Machine Resource Usage

You can also run into performance issues
with resources for the database server itself or on the machine on
which the database server is running. If your Oracle database is not
properly configured, adding machine resources may help reduce
performance bottlenecks, but this is a fairly expensive way to solve
the problem. Further, the problem will likely resurface as these
additional resources are consumed. But if your Oracle database is
properly configured and you find that the host computer is
experiencing resource shortages, adding machine resources is in
order.

The performance of your Oracle
database is based on how it uses machine resources and what resources
are available. These machine resources include processing
power or CPU, memory, disk I/O, and network bandwidth. You can trace
the bulk of database performance problems back to a bottleneck on one
or more of these resources.

Network bandwidth can be a bottleneck, but because the network
enables communication between the server and the client, the use of
this bandwidth is more a function of the client applications
communicating with the database server than a problem with the
database server software. For this reason, this chapter focuses on
how Oracle uses the three key machine resources: CPU, memory, and
disk I/O. The slowest device in a computer is the disk drive and, as
a result, the most common source of database performance issues is
disk I/O. The majority of this chapter therefore focuses on
performance as it relates to physical disk I/O.

Network bandwidth does come into play when using your Oracle database to supply data over the Internet, because there is typically less network bandwidth available over the Web. Although you can't typically surmount this type of problem simply by improving the performance of your Oracle database; you can monitor network bottlenecks with Enterprise Manager, as discussed later, with Oracle Database 10g.

The database server machine may
bottleneck on multiple resources, including CPU and I/O. In fact,
computer environments are designed so that one resource can try to
compensate for the lack of another resource, which can lead to a
deficit in the compensating resource as well. If you run out of
physical memory, the operating system will swap areas of memory out
to the disk, which can cause I/O bottlenecks. You can identify your
machine resource usage using tools provided by the machine vendor or
operating system utilities.
For example, on Unix systems, you
can use sar, iostat, and
vmstat; on Windows, you can use the Performance
Monitor.

With the latest version of Enterprise Manager in Oracle Database 10g, you can get information on many types of system resource utilization.

6.1.4 When All Else Fails

You may still have performance
problems with your application system even if you have not identified
a problem in any of the areas previously described. In this case,
your performance problem may not lie in the database server, but in
the design of the application or the database itself. At this point,
you will face the difficult problem of having to analyze the
interaction of individual modules and SQL statements in your
application system and the database server. You may find that you get
lucky and find a handful of SQL statements that are causing your
performance problem. However, it's more likely that
you will have to reconsider the design of your application
systemin particular, if you are running an older release of
the Oracle database.

Enterprise Manager 10g
and the Automatic Database
Diagnostic Monitor (ADDM) for Oracle Database
10g can automatically identify SQL statements
that are using the most resources or are less-than-optimal. The SQL
Tuning Advisor component can even suggest solutions for the
identified performance problems. (These tools are described later in
this chapter.)

Needless to say, more complex application redesign is far beyond the
scope of this book, so the rest of this chapter will concentrate on
helping you to understand Oracle machine resources.^[1]

^[1] For more details about the vast topic of Oracle performance,
refer to the tuning books mentioned in Appendix B.

6.1.5 A Final Note on Performance

Performance is based in the real world. Whenever you attempt to
address performance problems, you must make sure to carefully monitor
the areas that you are attempting to improve, both before and after
your changes. You must use a systematic approach not only for
discovering the source of a performance problem, but also for
implementing the appropriate solution.