| Previous | Table of Contents | Next |
by Dan Hotka
This chapter will concentrate on a variety of Oracle tuning issues, beginning with some general issues surrounding tuning, such as responsibilities and timing. Oracle tuning will be covered in detail under these broad categories: operating system, Oracle RDBMS, database design, and SQL-level tuning. The chapter will conclude with a Tuning Checklist, an organized approach to Oracle tuning.
Several roles have responsibility for various tuning functions. All of these roles can be handled by a single person in smaller shops or by teams of people in larger shops. The system administrator needs to work closely with the database administrator to make sure the system environment is set correctly. The database administrator will handle the init.ora tuning parameters, monitoring, and I/O distribution. The DBA will work closely with the data administrator (or systems analyst) to determine object location needs. The DBA will monitor for resource-intensive SQL statements, but the developer will tune and re-implement offending SQL statements.
There are two basic modes for tuning, proactive and reactive, with corresponding rationales, planning, and responsibility. Proactive tuning involves capacity planning, including planning for the growth of objects, adequate data access response times, and financial benefits. Financial benefits can be achieved if the response times can be made adequate without having to purchase additional hardware. Reactive tuning simply addresses slow response times in response to user complaints.
There are four distinct levels of tuning for any Oracle environment.
The first level of tuning, the operating system level, also includes the hardware platform. This level of tuning concentrates on the physical resources (CPU, memory, and disk), making sure the correct kernel parameters are set and that there is enough physical memory and disk to meet business applications needs and avoid unnecessary resource contention.
The Oracle RDBMS level of tuning consists of making sure that the available memory is being used in an efficient manner, that contention for various Oracle processes is minimal, that the database block size is correct for the efficient access of information, and that the Oracle disk-oriented resources are distributed properly.
Database design-level tuning has probably the biggest impact on how well applications will perform. Many factors have a direct impact on performance, including distributing related data objects in relation to available disk drives, controlling dynamic allocation, and avoiding fragmentation.
The final level of tuning is the SQL level. Finding and tuning a few frequently used, poorly performing or resource-intensive SQL statements can make a radical difference in performance gains to the application.
These levels are listed in the order of importance. It will be difficult to perform the next level of tuning if the prior level has not been considered and tuned. For example, you could tune SQL statements in level 4 and have performance enhancements, but if level 3 is tuned first, level 4 will have more options available and performance gains will be greater. In any tuning situation, it’s best to consider each level of tuning, starting with level 1.
There are many application and Oracle sizing steps that should have been performed to ensure that the appropriate amount of hardware (CPU, memory, and disk) was purchased to support the business needs of the application. It is beyond the scope of this chapter to do this application sizing; for the examples here, we’ll assume that an adequate amount of hardware was purchased. This chapter will focus on how to tune Oracle RDBMS to best take advantage of the available hardware.
The absolute very first step, even prior to installing the Oracle software, is to locate and read the Oracle Installation Guide. This document will discuss the latest changes in the Oracle software, options available during the installation process, and the Oracle Optimal Flexible Architecture. At Oracle installation time, there are many options to consider, such as database block size, the size of the original online redo log files, ARCHIVELOG mode, and so on. Considerable reactive tuning can be avoided by selecting options wisely.
| Previous | Table of Contents | Next |