Oracle Essentials Oracle Database 11g (32 page)

The AWR helps the Oracle database identify potential performance issues by comparing workloads over time. It also acts as the foundation for many of the manageability features introduced since Oracle Database 10
g
, such as the Automatic Database Diagnostic Monitor (ADDM).

Oracle’s ADDM automatically identifies and reports on resource bottlenecks, such as CPU contention, locking issues, or poor performance from specific SQL statements.

In Oracle Database 11
g
, ADDM can perform analysis on clusters. Alerts sent by ADDM to the Enterprise Manager dashboard can point to causes of contention as they occur. 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. Thresholds can be set such that the dashboard informs you when 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 tune your applications or optimize performance in the Oracle database.

For tuning your applications, you’ll likely look to the SQL Advisor. Introduced in Oracle Database 11
g
, it combines the functionality of the SQL Tuning Advisor, the SQL Access Advisor, and the new Partition Advisor. The SQL Advisor leverages information on CPU and I/O consumption captured in the AWR and identifies high-impact SQL statements indicated by the ADDM to make recommendations. The advisor checks to make sure statistics are not stale, identifies optimal paths through SQL profiling, determines if the addition of indexes, materialized views, or other database structures would be beneficial, and indicates whether changes to the high-impact SQL statements would improve efficiency.

Key database tuning advisors include:

Memory Advisor

For optimal setting of MEMORY_TARGET for automatic memory management in Oracle Database 11
g
(described later in this chapter) and optimal setting of SGA_TARGET for shared memory management

Performance Tuning Basics

|

157

Segment Advisor

For storage management and space allocation

Undo Advisor

For managing transactions

Other advisors, such as the Mean Time to Recovery (MTTR) Advisor, optimize the setup of Oracle, including log files. (See the
“Database Advisors”
section in

Chapter 5 for more information on the various Oracle advisor tools.)

Machine Resource Usage

You can also run into performance issues if inadequate resources are available to the database server. If your Oracle database is not properly deployed, adding machine resources might help initially reduce performance bottlenecks, but can be an expensive way to solve the problem. Further, the problem will likely resurface as additional resources are consumed. But if your Oracle database is properly designed and configured and you find such resource shortages, adding machine resources can be in order.

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

Network bandwidth between the server and a client is less of a problem today because of the better bandwidth available in most locations. In a RAC deployment, youshould also pay attention to the interconnect bandwidth as excessively heavy traffic can slow performance. But such interconnects continue to increase in speed as well, and network bandwidth in general is much less of an issue today if proper design choices are made.

Because of this trend, we will next focus on how Oracle uses the three key machine resources: CPU, memory, and disk I/O. The slowest access is to disk and, as a result, the most common database performance issues are I/O related. The majority of this chapter therefore focuses on performance as it relates to physical disk I/O.

Network bandwidth can become a concern when using your Oracle database to retrieve very large data sets over the network. Although you can’t typically surmount this type of problem simply by improving the performance of your Oracle database; you can monitor network and application server bottlenecks with Enterprise Manager, as of Oracle Database 10
g
.

The database server machine may encounter bottlenecks caused by contention for multiple resources. In fact, computer environments are designed so that one resource
158

|

Chapter 7: Oracle Performance

can try to compensate for the lack of another resource, sometimes leading to a defi-cit 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 and can cause I/O

bottlenecks.

You can identify your machine resource usage using Oracle Enterprise Manager and tools provided by the machine vendor or operating system utilities. Since the introduction of Enterprise Manager 10
g
, a performance analyzer called Automatic Performance Monitoring (APM) has been included. 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 youunderstand performance from an end user’s point of view, and this, in turn, can help you to spot other sources of performance problems, such as network transmission slowdowns.

When All Else Fails

Your performance problems could be caused by your applications in situations where performance tuning falls short in delivering desired results. For example, to solve slow I/O, you might try restriping or adding throughput to the disk subsystem.

However, this situation could be caused by poorly tuned SQL and would thus be better fixed by rewriting the SQL.

At this point, you should analyze the interaction of individual modules and SQL

statements in your application system and the database server. You could find that a handful of SQL statements are causing your performance problem. However, it’s more likely that you will have to reconsider the design of your application system.

Enterprise Manager and the Automatic Database Diagnostic Monitor (ADDM) 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 youto understand Oracle machine resources. For more details about the vast topic of Oracle performance, refer to the tuning books mentioned in Appendix B.

A Final Note on Performance Basics

Performance has real-world business implications. 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. Important baseline data gathered by the AWR includes application, database, operating system, disk I/O, and network statistics.

Performance Tuning Basics

|

159

You should use a systematic approach to both discovering the source of a performance problem and implementing the appropriate solution. This approach calls for establishing baselines for resource usage and response time before making any changes, and only making a small group of changes before reexamining the performance in the changed environment. It might be tempting to simply try to fix a problem without taking a measured approach, but this tactic will usually lead to additional problems down the road.

In Oracle Database 11
g
, such performance comparisons are made much easier. You can preserve AWR baselines that contain performance data from specific time periods. Baselines can be established for fixed times or moving windows, or they can serve as a template.

Oracle and Disk I/O Resources

From the perspective of machine resources, an input/output operation, or I/O, can be defined as the operating system of the computer reading or writing some bytes from or to the underlying disk subsystem of the database server. I/Os can be small, such as 4

KB of data, or large, such as 64 KB or 128 KB of data. The lower and upper limits on the size of an I/O operation vary according to the operating system. Your Oracle database also has a block size that you can define, called the
database block size
.

An Oracle database issues I/O requests in two basic sizes:

Single database block I/Os

For example, one 8 KB datablock at a time. This type of request reads or writes a specific block. For example, after looking up a row in an index, Oracle uses a single block I/O to retrieve the desired database block.

Multiblock I/Os

For example, 32 database blocks, each consisting of 8 KB, for a total I/O size of 256 KB. Multiblock I/O is used for large-scale operations, such as full table scans. The number of blocks in one multiblock I/O is determined by the initialization parameter DB_FILE_MULTIBLOCK_READ_COUNT.

The Oracle database can read larger amounts of data with multiblock I/Os, so there are times when a full table scan might actually retrieve data faster than an index-based retrieval (e.g., if the selectivity of the index is low). Oracle can perform multiblock operations faster than the corresponding collection of single-block operations.

I/O Planning Principles for an Oracle Database

When you’re planning the disk layout and subsequent placement of the various files that make up your database, you need to consider the different reasons Oracle performs I/O and the potential performance impacts.

160

|

Chapter 7: Oracle Performance

The main destinations of the I/O operations Oracle performs are the following:

• Redo logs

• Data contained in tables

• Indexes on the tables

• The data dictionary, which goes in the SYSTEM tablespace

• Sort activity, which goes in the TEMP tablespace of the user performing the sort

• Rollback information, which is spread across the datafiles of the tablespace containing the database’s rollback segments

• Archived redo logs, which go to the archived log destination (assuming the database is in ARCHIVELOG mode)

The following simple principles for managing these types of I/O can optimize Oracle’s use of the database server’s disk subsystem:

Use disk-striping technologies to spread I/O evenly across multiple spindles
These technologies are covered in detail in the next section,
“Using RAID Disk

Array Technology.”
Oracle has simplified striping in Oracle Database 10
g
and newer releases by enabling striping through Enterprise Manager leveraging ASM.

Use tablespaces to clearly segregate and target different types of I/O

Separate table I/O from index I/O by placing these structures in different tablespaces. Youcan then place the datafiles for these tablespaces on various disks to provide better performance for concurrent access.

Using tablespaces to segregate objects also simplifies tuning later on. Oracle implements I/O activity at the level of the datafile, or the physical object the operating system sees as a file, and each file is a part of only one tablespace, as described in
Chapter 4.
Placing specific objects in specific tablespaces allows you to accurately measure and direct the I/O for those objects by tracking and moving the underlying datafiles as needed.

For example, consider a database with several large, busy tables. Placing multiple large tables in a single tablespace makes it difficult to determine which table is causing the I/O to the underlying datafiles. Segregating the objects allows you to directly monitor the I/O associated with each object. Your Oracle documentation details the other factors to consider in mapping objects to tablespaces.

Place redo logs and redo log mirrors on the two least-busy devices
This placement maximizes throughput for transactional systems. Oracle writes to all copies of the redo log file, and this I/O is not completed until all copies have been successfully written to. If you have two copies of the redo log file, one on a slow device and the other on a fast device, your redo log I/O performance will be constrained by the slower device.

Oracle and Disk I/O Resources

|

161

As described in
Chapter 8,
Oracle Database 10
g
Release 2 gives youthe option of delaying write operations to the redo log for transactions. This capability can improve performance in very high transactional environments, but carries with it the possibility of losing committed data if your database crashes.

Distribute “system overhead” evenly over the available drives
System overhead consists of I/O to the SYSTEM tablespace for the data dictionary, the TEMP tablespace for sorting, and the tablespaces that contain rollback segments for undo information. You should consider the system profile in spreading the system overhead over multiple drives. For example, if the application generates a lot of data changes versus data reads, the I/O to the rollback segments may increase due to higher writes for changes and higher reads for consistent read functionality.