CIS508 Database Network Administration and Tuning

<< back to courseware demo page - This online lecture is for demonstration purposes

Lecture I

Tuning the Database Server

Learning Objectives
 

• To present the job roles involved in tuning an Oracle database.
• To illustrate the goals and steps involved in Oracle tuning.

Lecture Menu

Learning Objectives

Database Server Tuning

Tuning Goals

Tuning Steps

Summary

Practice Test and Answers

 

 
 

Database Server Tuning: Introduction

• Tuning an Oracle8i server environment is necessary to effectively manage a growing database and maintain the performance of applications. Database tuning is the optimization of database performance. This topic covers the job responsibilities that come with the roles involved in tuning the database.
• To tune the Oracle8i server, it is essential that the people involved in tuning understand their respective responsibilities. The roles include system architect, application developer, database administrator, and hardware/software administrator. One person may be responsible for more than one role.
• The system architect communicates information about the Oracle8i database design to the database administrator (DBA) and the application developer. The database design includes the logical structure of the database along with database size information.
• The database must be designed correctly to ensure that database performance does not deteriorate. It is difficult to tune an Oracle8i database that is based on a poor design. Often, an application designer performs some of the system architect's duties, focusing only on the logical structures and sizing involved with the applications as they are designed.
• By communicating the database design information, the system architect ensures that everyone using the database understands the flow of data and the processes involved in the execution of applications.
• An application developer communicates the implementation strategies to the system architect and to the DBA. The implementation strategies include information about the effect of applications on databases and the relationship between diverse application processes.
• Application tuning begins with the design and development of the application using the implementation strategies, modules, and SQL statements that are part of the application programs. Much of performance tuning is achieved through effective application design and development.
• A DBA's tuning responsibility is to configure the database and monitor and document system activity, so that system performance problems can be identified and corrected as quickly as possible.
• A hardware/software administrator documents and communicates the configuration of the system environment in which the database and application reside. The configuration is communicated to the system architect, the DBA, and the application developer. This helps in designing and administering the system effectively.

Tuning Goals

• Improvement in database and application performance helps to support the database application. You can improve the overall performance of the Oracle8i server by tuning its components. This topic covers the basic goals of database tuning.
• Begin tuning with clear, quantifiable, real-world performance objectives; for example, the objective to process 10,000 orders per day. The tuning that you implement for response time, reading and writing, or other operations should be geared toward your real-world objectives.
• You tune the Oracle8i server to achieve certain goals, such as reducing response time, minimizing contention, and optimizing the utilization of CPU and memory resources. There are six primary goals in tuning the Oracle8i server.
• One of the goals of tuning an Oracle8i server is to ensure that SQL statements access the smallest possible number of Oracle blocks needed to complete their work. This speeds up the response time for the statement and reduces the impact on other queries and data manipulation language (DML) statements being executed in the database.
• Another goal of tuning is to ensure that frequently used data blocks are cached in memory. Data blocks cached in memory can be readily accessed. The availability of needed data in memory reduces the number of overall physical reads.
• An additional goal of tuning is to ensure that users share code, such as frequently used SQL statements, within memory areas. This helps to avoid reparsing and duplicate storage of such SQL statements.
• A further goal of tuning the Oracle8i server is to ensure that reading and writing are performed as rapidly as possible. For example, separating different segment types across tablespaces can minimize write contention. Maximizing the number of blocks that are obtained during a read operation minimizes the number of read operations required.
• Minimizing or preventing contention for resources is another goal of Oracle8i server tuning. Good application tuning and database design help ensure that users have access to data when required.
• Finally, the database should be tuned to optimize the speed in which backup procedures and housekeeping tasks are performed. This protects data and minimizes the impact of those tasks on database performance.

Tuning Steps

• As the DBA, it is your responsibility to ensure that Oracle8i server performance does not negatively affect the availability and usability of the database. To ensure the effective performance of an Oracle8i server, you follow certain steps to tune the server. In this topic, you learn the steps you should follow to properly tune a database.
• Six steps are involved in tuning the Oracle8i database. In the first step, you tune the architecture and design of the data model.
• The second step involves tuning the database applications. The most common applications in Oracle8i are Decision Support Systems and Online Transaction Processing.

• Decision Support Systems (DSS) applications distill large amounts of information into understandable reports. Decision makers in an organization use this data to determine what strategies the organization should follow.
• Online Transaction Processing (OLTP) applications are high-throughput, insert/update intensive systems. They involve large volumes of data that grow continuously.
• To tune the applications, it is important to design efficient SQL statements. Efficient SQL statements take less time to process. You must also consider the data access and manipulation operations in detail.
• The next step in tuning the Oracle8i server is to tune memory structures. Memory tuning involves tuning the System Global Area (SGA) and user process memory.
• Input/output (I/O) tuning is the next step in tuning the Oracle8i server. I/O tuning involves configuring the distribution of data files, segment types, and tablespaces. I/O tuning also involves monitoring the interaction between data files and memory during reads and writes.
• The interaction between files can be tuned by separating the most frequently accessed data into different tablespaces and separating tables from their related indexes. You can also separate rollback segments from tables and data dictionary objects from all other objects.
• Another step in tuning the Oracle8i server involves reducing contention between actions in the database. Since an Oracle database is normally used by multiple users, tuning is needed to minimize the time that processes have to wait until a resource is available. Resources that can involve contention include blocks, shared pool, locks, and latches.
• The final step in tuning is to tune the operating system to handle the current demands of the database server.

Summary

• Three goals of performance tuning are improving the performance of particular SQL queries, improving the performance of applications, and improving the performance of the entire database.
• The steps for performance tuning are as follows:

1. Tune application design (if possible)

2. Tune application configuration

3. Tune operating system structures

4. Tune memory structures S. Tune I/O 6. Detect and resolve contention

• The preceding performance-tuning steps should be executed in the order given to avoid making sweeping database changes that cause things to break in unanticipated ways.

Review Questions

1 Why must a database be tuned?

2. What is the cause of most performance problems on Oracle databases?

3. What are some of the goals for performance tuning on a database?