Overview of the Oracle Database Instance
Oracle Tips by Burleson
Before we explore Oracle instance tuning, we
must first get a clear idea about the nature of an Oracle instance. At
a very general level, an Oracle instance consists of two components:
the System Global Area (SGA) and the Oracle background processes, as
shown in Figure 9-1.
Figure 9-55: An Oracle instance
Within these structures, we find that we can
control both the SGA and the background processes by adjusting Oracle
parameters. Let's begin with a high-level look at the Oracle SGA.
The Oracle SGA
When Oracle is started, the Oracle executable
issues the malloc( ) command to create a region of RAM memory.
The SGA is commonly called the Oracle region, because it is a region
of RAM memory on the database server RAM heap.
The Oracle DBA controls the size of the SGA,
and proper SGA management can have a huge impact on performance.
However, we must remember that the SGA is a static memory region, and
the needs of the Oracle database are constantly changing. Until
Oracle, the SGA was not dynamic and could not be altered. Once
Oracle becomes commonplace, the dynamic adjustment of the SGA will
be possible, and eventually the SGA will reconfigure itself based on
the needs of the database. However, until then, changing the SGA
requires constant monitoring, and it is sometimes a good approach to
develop a general setting for the SGA parameters based on the
historical needs of the application.
With STATSPACK, we can observe the historical
needs of any application that is running in the SGA, and we can
develop a set of global parameters. Using historical STATSPACK
information to tune the SGA region works out well because of Oracle's
high level of sophistication. Tuning Oracle's memory involves a number
of Oracle parameters. While there are hundreds of initialization
parameters, there are a handful of Oracle parameters that are very
important for Oracle tuning:
buffer_pool_keep This data buffer pool is used to store
small tables that perform full table scans.
buffer_pool_recycle This pool is reserved for table blocks
from very large tables that perform full table scans.
db_cache_size This parameter determines the number of
database block buffers in the Oracle SGA and represents the single
most important parameter to Oracle memory.
db_block_size The size of the database blocks can make a
huge improvement in performance. While the default value may be as
small as 2,048 bytes, data warehouses and applications that have
large tables with full table scans will see a tremendous improvement
in performance by increasing db_block_size to a larger value.
As a general rule, the larger the block size, the less physical I/O,
and the faster the overall performance.
db_file_multiblock_read_count This parameter is used for
multiblock reads when performing full table scans or large range
large_pool_size This is a special area of the shared pool
that is reserved for SGA usage when using the multithreaded server.
The large pool is also used for parallel query and RMAN processing.
parameter determines the amount of memory to allocate for Oracle's
redo log buffers. If there is a high amount of update activity, the
log_buffer should be allocated more space.
shared_pool_size This parameter defines the pool that is
shared by all users in the system, including SQL areas and data
dictionary caching. As we will learn later in this chapter, a large
shared_pool_size is not always better than a smaller shared
pool. If your application contains nonreusable SQL, you may get
better performance with a smaller shared pool.
sort_area_size This parameter determines the memory region
that is allocated for in-memory sorting. When the stats$sysstat
value sorts (disk) becomes excessive, you may want to allocate
hash_area_size This parameter determines the memory region
reserved for hash joins. Starting with Oracle, Oracle does not
recommend using hash_area_size unless the instance is
configured with the shared server option. Oracle recommends that you
enable automatic sizing of SQL working areas by setting
pga_aggregate_target, and hash_area_size is retained only for
backward compatibility purposes.
To see the size of your SGA, you can issue the
show sga command from SQL*Plus, as shown below. The output of
the show sga command appears here:
connect system/manager as sysdba;
SQL> show sga
Total System Global Area 405323864 bytes
Fixed Size 49240 bytes
Variable Size 354066432 bytes
Database Buffers 49152000 bytes
Redo Buffers 2056192 bytes
Now let's take a quick look at the important regions
within the SGA. We will return to these areas later in this chapter
and see how to use STATSPACK to tune each memory area.
The Data Buffer Caches
Oracle8, the Oracle SGA has three areas of RAM for caching incoming
data blocks from disk. They are the DEFAULT pool, the KEEP pool and
the RECYCLE pool, as shown in Figure 9-2.
Figure 9-56: The Oracle data buffer pools
The buffer caches are in-memory areas of the
SGA where incoming Oracle data blocks are kept. On standard UNIX
databases, the data is read from disk into the UNIX Journal File
System (JFS) buffer where it is then transferred into the Oracle
buffer. The size of the data buffers can have a huge impact on Oracle
system performance. The larger the buffer cache, the greater the
likelihood that data from a prior transaction will reside in the
buffer, thereby avoiding expensive physical disk I/O.
Remember, the KEEP and RECYCLE pools in
Oracle are only applicable to the DEFAULT pool (the one that is
created with the default db_block_size. In Oracle you can
configure multiple regions from 2k-32k block size, and each has a
distinct buffer cache.
The Log Buffer
As you may
know, Oracle creates redo logs for all update transactions. These redo
logs contain the after image of all row changes and are used to roll
forward in cases where Oracle experiences a disk failure. Oracle keeps
these redo logs in three separate areas, as shown in Figure 9-3.
Figure 9-57: The Oracle redo log areas
As we see in Figure 9-3, the after images are
first written to an area of RAM called the log buffer. The images are
then transferred to Oracle online redo log files. Finally, the online
redo log files are written to the archived redo log file system where
they can be used to recover the database.
The Oracle Shared Pool
After the data
buffer caches, the shared pool is the most important area of the SGA.
The SGA contains a number of subareas, each with a specific purpose,
as shown in Figure 9-4. One of the confounding problems with Oracle is
that all of these subareas are controlled with a single parameter
Figure 9-58: The Oracle shared pool
It is impossible to dedicate separate regions
of memory for the components within the shared pool. The shared pool
is normally the second largest memory component within the SGA,
depending upon the size of the db_block_size parameter. The
shared pool holds RAM memory regions for the following purposes:
Library cache The
library cache stores the execution plan information for SQL that is
currently being executed. This area also holds stored procedures and
cache The dictionary cache stores environmental information,
including referential integrity, table definitions, indexing
information, and other metadata stored within Oracle's internal
information Stores session information for systems that are
using SQL*Net version 2 with Oracle's multithreaded server. Starting
in Oracle, the v$session view also contains information related to
Oracle Net users.
This is an excerpt from "Oracle
High Performance tuning with STATSPACK" by Oracle Press.
If you like Oracle tuning, you may enjoy the new book "Oracle
Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning
tips & scripts.
You can buy it direct from the publisher for 30%-off and get instant access to
the code depot of Oracle tuning scripts.