0570-0572

Previous Table of Contents Next

Page 570

Whenever an operation is undertaken that requires sorting, Oracle attempts to do it in the memory of the user process that requests the sort . Sorts are constrained by the following INIT.ORA parameters:

SORT_AREA_SIZE	The maximum amount of space (in bytes) that a user process has available to perform a sort
SORT_AREA_SIZE_RETAINED	The minimum amount of space (in bytes) that a user process will ever have available

Exceeding SORT_AREA_SIZE causes a sort to disk to occur. When no space in the memory is available for the sort area, Oracle must resort to using the designated temporary tablespace for sorting operations. Using disk for sort operations is much less efficient than utilizing available memory. Consequently, you might want to consider increasing SORT_AREA_SIZE.

To determine whether a sort is performing efficiently , you must first determine the level ”memory or disk ”at which the sort occurs. For example:

 select name, value from v$sysstat where name like `sort%' / 

produces output similar to

 NAME                                                 VALUE --------------------------------------------------   ----- sorts (memory)                                         370 sorts (disk)                                             7 sorts (rows)                                          1997 

Interpreting the output from the sort statistics is not as simple as calculating a hit ratio. Obviously, the lower the value of the sorts to disk, the better the sort is performing. However, having many sorts to disk does not necessarily mean that the database is not sorting optimally. You should consider whether you can safely raise the value of SORT_AREA_SIZE without causing an adverse impact on the database. Likewise, these might be batch jobs, which process an inordinate amount of data. Because of the volume of data processed , it is impossible to increase the SORT_AREA_SIZE large enough to eliminate these sorts to disk.

When you deal with sorts, it is as important to know why certain results occur as it is to know that the results do occur. Watched diligently and with a knowledge about current operations, the sorts on a database are low-maintenance items.

Ramifications of SGA Changes

It is relatively easy to change the size of the buffers in the SGA, but you must consider the ramifications of making changes.

Page 571

The most obvious benefit of increasing the size of the SGA is that the larger the SGA, the more information can be processed in memory. By enabling the database to have most of its data cached, physical disk I/O is minimized, which results in a system that is constrained more by the speed of the processor than by the speed of the I/O devices. The law of diminishing marginal utility applies, however. Depending on the size of the database and the amount of activity being performed, increasing the size of the SGA buffers ceases to have any positive effect after a certain point. After this occurs, the database begins to hoard memory that could be better used by the operating system or other applications.

Another concern in tuning a database SGA is failing to consider that some parameters incur memory for every connection instead of only one. Consider, for example, the scenario in which the DBA wants to increase the size of the sort area. After some investigation, he concludes that having a 10MB sort area would greatly improve performance because many sorts are taking place to disk. This system also experiences a high level of user activity ”500 users. Instead of creating a single 10MB sort area, the DBA has actually created 500 10MB sort areas. The total memory cost is approximately 5GB ”more RAM than most systems have.

Don't forget to factor in user processes and other non-Oracle applications that might reside on the system. DBAs often think that they are the only people on a hardware platform. The Oracle Installation and Configuration Guide has charts that enable you to calculate memory requirements based on the products being used. It is far better to make adjustments before you create an instance. Otherwise, you must expend the time and frustration of tracking down SGA settings that artificially induce paging and swapping onto the system.

Consider the following guidelines when you adjust the SGA and its associated buffers:

• Always make certain that the SGA fits comfortably in available memory, not overall system memory.
• Never make buffers larger than they need to be. Allow, of course, for growth. However, if you are actively monitoring the system, you can increase the values as needed without wasting space in memory.
• Watch out for database parameters that incur use for every user, such as SORT_AREA_SIZE. Don't set them so high that they cause the system to begin paging and swapping.
• Adequately plan changes. Remember the proverb, "An ounce of prevention is worth a pound of cure."
• Be wary of changes that affect other parameters. Changing the database block size, for example, can affect the values of several other INIT.ORA parameters and cause a database instance to consume more memory than expected.

Database instances require more disk space as they grow larger. The same is true with memory. A growing database will eventually outstrip the memory available on the system. Don't make the mistake of ignoring possible memory problems when you do a performance analysis.

Page 572

Contention Issues

DBAs often ignore the physical aspects of a system. With all the logical structures that a DBA must deal with on a day-to-day basis, it is easy to forget about the physical elements that support them, such as SCSCI cards, bandwidth, or an I/O bus. Whenever you fail to consider the physical elements, contention can occur within the database.

Like spoiled children, database elements fight over resources. This is the most basic definition of contention. When contention happens, the database must wait for an event to occur. This event ”such as writing a block of data to a physical device or locking a row inside a database table ”causes an appreciable slowdown in database performance. It is the responsibility of the DBA and others, such as the system administrator, to work with the database to minimize contention. When you minimize contention, the database performs at consistent, efficient speeds.

I/O Contention and Load Balancing

Contention among physical storage devices is the most common type of contention. Each disk drive has heads that travel back and forth across the magnetic medium (the disk) to read and write information. A database is made up of several physical data files, many of which reside on the same physical disk, so it is easy to see how contention can occur. If the database requests access to several data files on the same disk, the result is contention as the drive head moves across the disk to the first location and accesses the file, moves to the second location and accesses the file, and so on. Fortunately, you can minimize I/O contention.

It is important to understand types of database files and the types of operations performed on them. Figure 23.2 compares the types of files and operations.

Figure 23.2.
File and access.

In a perfect world, you could place each database file on a separate disk. On smaller database instances, this might even be possible, but they are the exception. In practice, most databases have multiple files and a limited number of disks on which to place them ” generally just the amount of space that is needed. So it becomes important to work with the system administrator to determine the optimal layout of the physical database files.

As Figure 23.2 shows, each database file has specific operations. Redo logs, for example, handle straightforward, sequential output. Database files handle intensive read and write operations. You should put the following files on a physical disk separate from the other database files:

Previous Table of Contents Next