| Lesson 2 | Instance components |
| Objective | Describe the main components of the Oracle SGA. |
Describe Main Components of Oracle SGA
The System Global Area, or SGA, is a shared memory structure that is the focal point of activity for an Oracle instance.
In fact, the term SGA is often used to describe a running Oracle instance.
The SGA contains both data and control information for an instance, and is referenced in one way or another by virtually all of the background processes.
The SGA contains both data and control information for an instance, and is referenced in one way or another by virtually all of the background processes.
Configuring the SGA
When Oracle is started, a region of memory is configured according to initial parameters that are defined in two files:
two files .
These parameter files define the memory region and start-up characteristics for the Oracle instance. These files tell the database software how to configure SGA.
Because the SGA resides within an operating system, it is dependent upon the operating environment. In UNIX, Oracle must share memory space with many other memory regions, competing for the limited memory and processing resources. The Oracle instance occupies an area of memory (the SGA), while external Oracle applications, also in a memory space, communicate with Oracle to service their data needs.
- init.ora and
- config.ora
These parameter files define the memory region and start-up characteristics for the Oracle instance. These files tell the database software how to configure SGA.
Because the SGA resides within an operating system, it is dependent upon the operating environment. In UNIX, Oracle must share memory space with many other memory regions, competing for the limited memory and processing resources. The Oracle instance occupies an area of memory (the SGA), while external Oracle applications, also in a memory space, communicate with Oracle to service their data needs.
The following diagram reviews the SGA components:
- The database buffer cache
- The shared pool (comprising session memory, the data dictionary cache, and the library cache)
- The redo log buffer
- This is the memory allocated by db_block_buffer to hold the incoming Oracle data blocks.
- This is the area of memory reserved for the redo logs before they move into the online redo log files.
- This area of memory resides within the shared pool and holds session information such as cursors and host variables.
- This area of memory resides within the shared pool and holds shared SQL, PL/SQL procedures, locks and latches.
- This memory area resides within the shared pool and holds data dictionary objects.
SGA Instance Components
Putting SGA tuning in Context
While tuning the Oracle SGA is quite important, there are many other factors that have an ever bigger impact on Oracle performance such as proper database design, SQL tuning, and disk I/O tuning, and tuning the operating environment.
| Database design issues | No amount of instance tuning can fix a poor table design or alleviate poor SQL response time. |
| Disk I/O tuning | If Oracle cannot quickly retrieve blocks from the disk, instance tuning cannot help. |
| OS issues | If your processor is short on memory or CPU, no amount of instance tuning will be effective. |
SGA performance information
When you suspect an instance tuning problem, the best source of information is always the V$ views.
This is because virtually all instance performance information is held in these views.
In the next lesson, you will start by learning some basic tuning considerations of the shared pool.
This is because virtually all instance performance information is held in these views.
In the next lesson, you will start by learning some basic tuning considerations of the shared pool.
