Lesson 1

This module will provide an overview of Oracle instance tuning and will introduce you to the overall instance components that we will be investigating in detail throughout the course. It will also provide you with some background about the types of performance problems that you will learn to diagnose and resolve as you make your way through the rest of Oracle Instance Tuning. By the time you complete this module, you should be able to:

1. Describe the main components of the Oracle SGA
2. Describe the operation of the shared pool
3. Describe the functions of the library cache
4. Query the data dictionary cache
5. List the UGA and session memory considerations
6. Describe how the data buffer cache affects performance
7. Describe the performance issues related to the redo log buffer

When considering instance tuning, take care in the initial design of the database to avoid bottlenecks that could lead to performance problems. In addition, you must consider:

1. Allocating memory to database structures
2. Determining I/O requirements of different parts of the database
3. Tuning the operating system for optimal performance of the database

After the database instance has been installed and configured, you must monitor the database as it is running to check for performance-related problems.

Performance tuning requires a different, although related, method to the initial configuration of a system. Configuring a system involves allocating resources in an ordered manner so that the initial system configuration is functional. Tuning is driven by identifying the most significant bottleneck and making the appropriate changes to reduce or eliminate the effect of that bottleneck. Usually, tuning is performed reactively, either while the system is in preproduction or after it is live.
The most effective way to tune is to have an established performance baseline that you can use for comparison if a performance issue arises. Most database administrators (DBAs) know their system well and can easily identify peak usage periods.
Take for example the following scenario: The peak periods could be between 10.00am and 12.00pm and also between 1.30pm and 3.00pm. This could include a batch window of 12.00am midnight to 6am. It is important to identify these peak periods at the site and install a monitoring tool that gathers performance data for those high-load times. Optimally, data gathering should be configured from when the application is in its initial trial phase during the Quality Assurance cycle. Otherwise, this should be configured when the system is first in production. Ideally, baseline data gathered should include the following:

1. Application statistics (transaction volumes, response time)
2. Database statistics
3. Operating system statistics
4. Disk I/O statistics
5. Network statistics

In the Automatic Workload Repository, baselines are identified by a range of snapshots that are preserved for future comparisons.
In the next lesson, we will begin by reviewing the main components of an Oracle instance.