[Mullins02] Chapter 10. System Performance

来源：百度文库编辑：神马文学网时间：2024/04/29 22:24:30

Chapter 10. System Performance

Apoorly performing system can degrade the performance of all databasesand applications deployed on that system. No amount of database,application, or SQL tuning can improve performance when a poorlyimplemented system is causing performance problems. Applications accessdatabases and both are implemented on an overall system environment, asshown in Figure 10-1.Therefore, a system problem can cause all databases and applications toperform poorly, just like a database problem can cause all applicationsthat access that database to perform poorly.

Figure 10-1. The tuning boxes

A system problem can cause all databases and applications to perform poorly.

Thesystem comprises the hardware and software required for the DBMS tooperate and for applications to access databases using the DBMS. It isimperative that the DBA understands the system and operatingenvironment where the database applications will be run. The DBA mustbe able to facilitate changes to any component of the system to tunethe database environment. Of course, the DBA cannot be expected to bean expert in every aspect of the system, and therefore the DBA willneed to work with other teams within the organization to initiatesystem changes.

Thefollowing sections provide introductory coverage of system-relatedperformance and tuning tactics. They do not provide in-depth treatmentof the subjects because that is not the primary focus of the book.

The Larger Environment

ADBMS operates within the context of a much larger environment thatconsists of other software and hardware components. Each of thesecomponents must be installed, configured, and managed effectively forthe DBMS to function as required. The DBA needs to understand how theDBMS interacts with the server hardware, the operating system, and anyother required software. Tuning and configuring these components andconnections properly can have a dramatic impact on system performance.

A DBMS operates within the context of a larger environment.

Interaction with the Operating System

Whenthe operating system experiences a performance problem, all of thesoftware that runs on that operating system may experience performanceproblems. To help ensure an optimal operating system for your databaseapplications, the DBA should ask the following questions.

Has a sufficient amount of memory been allocated for operating system tasks?
Most operating systems have the capability of allocating a specific amount of disk space as a swap area. The swap area is used when the OS runs out of memory. Has a sufficient amount of disk space been allocated to the swap area?
How were the database files allocated when the database was implemented? Interaction with the file system can cause some operating systems to create additional overhead. By changing the database files to use raw disk, OS and file system overhead can be eliminated. (Additional information on raw disk usage can be found in Chapter 17.)
Some operating systems allow the administrator to set the priority of tasks that run under the auspices of the OS. Has each database-related task been assigned a priority? Is the priority appropriate for that specific task?
Is the operating system at the version and release level recommended by the DBMS vendor? Have any bug fixes been shipped for the OS that are applicable for the particular brand of database server you are running?
Have the operating system configuration parameters been modified when installing the DBMS? If so, has sufficient testing been done to ensure that the parameters were modified correctly and do not impact any other processes that run on the database server?

Allied Agents

Asdiscussed in previous chapters, the DBMS has to ally itself with manyother software components to deliver service to the end user. Examplesof allied agent software include

Transaction processors like CICS and Microsoft Transaction Server
Networking software such as TCP/IP and SNA
Message queueing software such as MQSeries and MSMQ
Web connectivity and development software such as ColdFusion
Programming languages such as Java, COBOL, and C

The DBMS has to ally itself with many other software components to deliver service to the end user.

Eachof these allied agents needs to be configured properly to interact withthe DBMS, and it is the DBA's responsibility to understand the setuprequirements. In larger shops the DBA might not perform the actualconfiguration—leaving it, instead, to more skilled professionals whospecialize in administering and managing the software. However, insmaller shops the DBA may have to configure all of the software himself.

Hardware Configuration

TheDBMS runs on computer hardware. That hardware may be a large-scalemainframe, an intermediate Unix system, or a PC running Windows.Regardless of its scale, the hardware must be installed and set upproperly for the DBMS to operate efficiently.

The hardware must be installed and set up properly for the DBMS to operate efficiently.

Again, here are some questions the DBA should ask to assure an optimal hardware environment for the database applications.

Is the computer hardware and capacity appropriate for the DBMS environment? In other words, does the DBMS vendor recommend this particular hardware implementation?
Is the computer firmware (e.g., ROM BIOS) up-to-date?
Has a sufficient amount of memory been installed for all of the system software to be installed (OS, DBMS, and other allied agents)?
Has an appropriate amount of disk storage space been allocated and configured for use by the DBMS?
What type of disk storage is being used and is it appropriate for large data volumes and high-speed database queries?
Are all the network cables connected and functioning properly?
Are all physical connections (e.g., cables, plugs, and board sockets) fully connected and operational?
Is the hardware connected to an uninterruptible power supply?
Is the hardware connected to a surge protection device?

Disk Storage and I/O

Oneof the biggest bottlenecks for database performance is the physicalcost of performing I/O operations. Data resides on a disk, and a diskis a mechanical device. It requires machine parts that move in order toread encoded data from a spinning platter. This physical movement takestime, and anything that can be done to reduce I/O time can enhanceperformance.

Aconsideration for optimizing disk access is to utilize solid statedevices. A solid state device is actually computer memory that isconfigured to work like a disk drive. When data is read from a solidstate device, there is no physical component to the I/O operation—thedata resides in memory and is transferred from memory to the DBMS andthen to the requester.

A consideration for optimizing disk access is to utilize solid state devices.

Considerplacing database objects with high performance requirements on solidstate devices instead of physical disk drives, RAID devices, or storagearea networks.

However,implementing solid state devices has some potential problems. The firstis cost. Only recently has the initial cost of solid state devicesbegun to decrease. The second potential problem is persistence. Somesolid state devices require a constant supply of power to prevent thedata from being erased. In such cases, be sure that solid backup andrecovery plans are implemented for database objects.

Components of the DBMS

ADBMS is a very complex system requiring hundreds of thousands of linesof computer code. A DBMS is so complex that multiple programs arerequired to deliver the requisite data management functionality; eachprogram interoperates with other programs to provide a databasemanagement system.

EachDBMS vendor breaks down DBMS functionality into different components.The DBA must study the makeup of the DBMS and gain an understanding ofeach component piece and how it integrates with the other components ofthe DBMS. For a high-level overview of the architecture of the OracleDBMS, refer to the sidebar “The Architecture of Oracle.”

TheDBA must become an expert on the inner workings of the DBMS in order toensure an optimized environment for database applications. A failure orproblem in any single component of the DBMS can cause severeperformance degradation for every application accessing the database.

The Architecture of Oracle

To effectively administer an Oracle environment, the DBA must understand the basic “architectural” blueprint of Oracle. Oracle is composed of five basic components that operate in an integrated manner to provide support for the client tasks: file structures, memory structures, processes, rollback segments, and redo logs.

An Oracle instance is the combination of all of the memory structures and background processes that are allocated when an Oracle database is started. Oracle users frequently confuse an Oracle instance with an Oracle database. An Oracle database has both physical structures (data files) and logical structures (table, index). The physical structure of an Oracle database is determined by the files created at the operating system level by the Oracle instance during database creation (e.g., controlfile, logfile) or by the DBA during normal operation (e.g., CREATE tablespace, CREATE controlfile). An Oracle database comprises three physical file structures:

Control files— A small administrative file that is used by the Oracle database.
Redo log files— A record of changes made to data. The redo log files are used in recovery circumstances to ensure that no data is lost should a failure prevent changed data from being written to disk.
Database files— A file that contains the database information, including both system and application data.

An Oracle parameter file contains all configuration parameters for an Oracle instance. These parameters can be set to different values to tailor the Oracle instance to the system's operating configuration as well as to the needs of the individual application(s) contained in the Oracle database.

Oracle database files contain the data associated with a particular database. All of the files discussed are not absolutely required for normal database operations, but this configuration is highly recommended for a well-designed, efficient environment. Oracle database files can be grouped by the following categories.

Control files record the physical structure of the Oracle database. The control file is read during every database startup.
Data files associated with Oracle tablespaces include system data files, application data files, default data files, temporary files, and rollback files.
Redo log files (the database transaction log) record changes made to data.
The config.ora file, associated with the Oracle client, specifies certain defaults, file, and directory names for the Oracle client.

Additionally, Oracle utilizes specific memory structures to perform DBMS-related tasks. These memory structures are contained in the main memory (RAM) of the computer running the Oracle instance. The basic memory structures for an Oracle instance are the system global area (SGA), the program global area (PGA), and the sort area.

The SGA is a group of shared memory structures allocated by the Oracle instance to contain data and control information. The SGA contains the data cache, redo log buffer, and shared pool for SQL statement parsing and processing.

The PGA is a work area for both user and background processes. Each process has its own PGA. The contents of the PGA will vary, depending on the type of process and Oracle configuration.

The Oracle sort area is a memory structure used to sort, order, and group data whenever a user process requests a sort to be performed.

Finally, we come to the Oracle processes, where most of the data management work is accomplished. Each process is composed of a series of tasks. Oracle has two general types of processes: user processes and Oracle processes. A user process is created to execute the program code of an application program. An Oracle process is called by another process to perform specific functions on behalf of the invoking process. Oracle processes can be further broken down into server processes and background processes. Server processes communicate with user processes acting as a “relay” between the user process and SGA information. Background processes perform designated data management and processing functions for the Oracle instance.

Let's examine the functionality of each of the Oracle background processes.

The process monitor (PMON) background process performs “cleanup duties” when a user process fails with an error condition. PMON cleans up the cache, releases locks, and performs other miscellaneous tasks.

The system monitor (SMON) background process provides instance recovery during startup. SMON also cleans up temporary segments that are no longer in use, compresses free space into contiguous extents, and in a parallel server environment, provides instance recovery for a failed CPU.

The database writer (DBWR) process writes data from the data cache contained in memory out to the physical disk files.

The log writer (LGWR) manages the redo log buffer. If an Oracle instance takes more checkpoints than the LGWR process can handle, checkpoint duties may be turned over to the checkpoint (CKPT) background process. The archiver (ARCH) process performs log archival by copying online redo log files to auxiliary storage devices as logs fill up.

The recover (RECO) background process automatically resolves failures involving distributed transactions.

Finally, Oracle deploys user and server processes. The user process links the client application to the Oracle instance. The server process parses and executes SQL statements and performs all tasks required to send results back to the requesting application.

This overview of Oracle's architecture is necessarily brief. It is intended to communicate just how specialized the components of a DBMS must be. An Oracle DBA will need to understand all of the above components, how they interact, and how they can be tuned to optimize the performance of the Oracle system and applications.

Create Bookmark (Key: b)Create Bookmark
Create Note or Tag (Key: t)Create Note or Tag
Download (Key: d)Download
Email This Page (Key: e)Email This Page
PrintPrint

Html View (Key: h)Html View
Zoom Out (Key: -)Zoom Out
Zoom In (Key: +)Zoom In
Toggle to Full Screen (Key: f)
Previous (Key: p)Previous
Next (Key: n)Next