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

Oracle instance tuning is a critical aspect of managing Oracle databases, aiming to optimize the performance of the database system. The Oracle Instance consists of various components, each of which plays a significant role in overall database performance. Understanding the relationship between these components and how they can be tuned is key to effective performance management. Here's a breakdown of the main Oracle Instance components and their relevance to instance tuning:

1. SGA (System Global Area): The SGA is a shared memory area that contains data and control information for one Oracle database instance. It includes several key structures:
   • Buffer Cache: Holds copies of data blocks read from the database. Tuning the buffer cache involves adjusting the size to prevent frequent disk I/O.
   • Shared Pool: Contains cacheable elements such as SQL statements and PL/SQL procedures. Tuning the shared pool size can help reduce parsing overhead and improve the execution time of SQL queries.
   • Large Pool: Optionally used to relieve the shared pool load for certain operations, such as large I/O operations or backup and restore activities.
   • Java Pool: Used for all session-specific Java code and data within the JVM. Tuning involves ensuring adequate space for Java-based applications.
   • Streams Pool: Used for Oracle Streams processes to manage data replication and messaging environments.
2. PGA (Program Global Area): The PGA is a memory region that contains data and control information exclusive to a server process. Memory allocated in the PGA is used for session-specific information such as sort space, cursor state, and other session-specific operations. Tuning the PGA involves managing its size to optimize sort operations and other session-specific memory needs.
3. Background Processes: These are processes that work behind the scenes to manage data consistency, integrity, and recovery. Key background processes include:
   • DBWR (Database Writer): Responsible for writing modified blocks from the database buffer cache to the disks. Tuning involves adjusting the write batch size and frequency.
   • LGWR (Log Writer): Responsible for writing redo log entries to the redo log files. Tuning might focus on the commit behavior and log buffer sizes.
   • SMON (System Monitor)** and **PMON (Process Monitor): Involved in recovery and cleanup processes. While less directly tuned, ensuring their efficiency is critical for instance recovery and performance.
4. Redo Log Buffer: This buffer temporarily stores all changes made to the database. Tuning the redo log buffer can help reduce the log file sync waits and increase transaction throughput.
5. Locks and Latches: These are mechanisms to manage concurrent access to data structures in memory. Efficient management of locks and latches is essential to reduce contention and improve the concurrency of the database.
6. Parameters: Oracle allows hundreds of parameters to be set that influence the behavior of the instance. These parameters can be adjusted to fine-tune the Oracle environment, such as memory allocation, process limits, and timeout settings.

Effective instance tuning typically involves a holistic approach where changes to one component may affect the performance of others. Tools like Oracle's Automatic Workload Repository (AWR) and Automatic Database Diagnostic Monitor (ADDM) can be used to monitor the database and provide recommendations for tuning. Each tuning decision should be based on careful analysis and understanding of how these components interact within the specific environment.

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.