6.4 Oracle and Memory Resources
Accessing information in memory is
much faster than accessing information on a disk. An
Oracle
instance uses the database server's memory resources
to cache the information accessed to improve performance. Oracle
utilizes an area of shared memory called the System Global Area (SGA)
and a private memory area for each server process called the
Program Global Area (PGA). Operating
systems use virtual memory, which means that an Oracle instance can
use more memory than is physically available on the machine.
Prior to Oracle9i, you could specify the size
for the SGA or any of its components�database buffer cache,
shared pool, or large pool�only in the initialization file, and
the size of these memory allocations could not be changed without
shutting down and restarting the instance.
Oracle9i enabled
dynamic resizing of these pools based on a minimum memory allocation
called a
granule.
Oracle Database 10g
automatically manages shared memory.
Exhausting a database
server's supply of physical memory will cause poor
performance. If you are running an older release of Oracle, you
should gauge the size of the various memory areas Oracle uses or add
more memory to the machine to prevent a memory deficit from
occurring. What constitutes the right size for the various areas is a
function of your application behavior, the data it uses, and your
performance requirements.
6.4.1 How Oracle Uses the System Global Area
Oracle uses the SGA for the
following operations:
Caching of database blocks containing table and index data in the
database buffer cache Caching of parsed and optimized SQL statements, stored procedures,
and data dictionary information in the shared pool Buffering of redo log entries in the redo log buffer before
they're written to disk
In versions
prior to
Oracle 9i, the amount of memory allocated to
each of these areas within the SGA is determined at instance startup
and cannot be altered without restarting the instance. The
initialization parameters that allocate memory are:
- DB_BLOCK_BUFFERS
Specifies the number of database blocks the database buffer cache can
contain
- SHARED_POOL_SIZE
Specifies the size of the shared pool, in bytes
- LOG_BUFFER
Specifies the size of the redo log buffer, in bytes
Of these three memory areas, the majority of tuning efforts focus on
the database buffer cache and the shared pool.
6.4.1.1 The database buffer cache
Tuning the database
buffer cache is relatively simple: You assess what percentage of the
database blocks requested by users are read from the cache versus
from the disk. This percentage is termed the hit ratio. If
response times are too high and this ratio is lower than 90% (as a
rule of thumb), increasing the value of the initialization parameter
DB_BLOCK_BUFFERS may increase performance.
|
You can use the Oracle Enterprise Manager to get
information about the cache hit ratio.
|
|
It's tempting to assume that continually increasing
the size of the
database buffer cache will translate into
better performance. However, this is true only if the database blocks
in the cache are actually being reused. Most OLTP systems have a
relatively small set of core tables that are heavily used (for
example, lookup tables for things such as valid codes). The rest of
the I/O tends to be random, accessing a row or two in various
database blocks in the course of the transaction. Because of this,
having a larger buffer cache may not contribute to performance
because there isn't much reuse of data blocks
occurring.
In addition, not all operations read from the database buffer cache.
For example, large full-table scans are limited to a small number of
buffers to avoid adversely impacting other users by dominating the
cache. If your application performs a lot of table scans, increasing
the buffer cache may not help performance because the cache will not
contain the needed data blocks. Parallel table scans completely
bypass the buffer cache and pass rows directly to the requesting user
process. As with most performance issues, your understanding of how
your application is actually using your data is the key that will
help guide your database buffer-cache tuning.
6.4.1.2 The shared pool
The shared pool is used at several
points during the execution of every operation that occurs in an
Oracle database. For example, the shared pool is accessed to cache
the SQL sent to the database and for the data dictionary information
required to execute the SQL. Because of its central role in database
operations, a shared pool that is too small may have a greater impact
on performance than a database buffer cache that is too small. If the
requested database block isn't in the database
buffer cache, Oracle will perform an I/O to retrieve it, resulting in
a one-time performance hit. A shared pool that is too small will
cause poor performance for a variety of reasons affecting all users.
These reasons include the following:
Not enough data dictionary information can be cached, resulting in
frequent disk access to query and update the data dictionary. Not enough SQL can be cached, leading to memory
"churn," or the flushing of useful
statements to make room for incoming statements. A well-designed
application issues the same statements repeatedly. If there
isn't enough room to cache all the SQL the
application uses, the same statements get parsed, cached, and flushed
over and over, wasting valuable CPU resources and adding overhead to
every transaction. Not enough stored procedures can be cached, leading to similar memory
churn and performance issues for the program logic stored and
executed in the database.
Once you've diagnosed which of these problems is
occurring, the solution is fairly simple: increase the size of the
shared pool. Shared pool sizes in the 150-250 MB range are not
uncommon for large, active databases. For more information about
examining shared pool activity to identify problems, see the
appropriate Oracle Tuning Guide, as well as the
third-party books listed in Appendix B.
6.4.1.3 The redo log buffer
While the redo log buffer consumes a very
small amount of memory in the SGA relative to the database buffer
cache and the shared pool, it's critical for
performance. Transactions performing changes to the data in the
database write their redo information to the redo log buffer in
memory. The redo log buffer is flushed to the redo logs on disk when
a transaction is committed or when the redo log buffer is one-third
full. Oracle "fences" off the
portion of the redo log buffer that's being flushed
to disk to make sure that its contents aren't
changed until the information is safely on disk. Transactions can
continue to write redo information to the rest of the redo log buffer
(the portion that isn't being written to disk and
therefore isn't fenced off by Oracle). In a busy
database, transactions may generate enough redo to fill the remaining
unfenced portion of the redo log buffer before the I/O to the disks
for the fenced area of the redo log buffer is complete. If this
happens, the transactions will have to wait for the I/O to complete
because there is no more space in the redo log buffer. This situation
can impact performance. The ratio that detects this waiting is
calculated using system statistics from the dynamic performance view
V$SYSSTAT
as:
redo log space requests / redo entries
As a general rule, if this ratio is greater than 1:5,000 you should
increase the size of the redo log buffer. You can keep increasing the
size of the redo log buffer until the ratio does not get any lower.
Alternatively, the statistic "redo buffer allocation
retries" can be used. It is also available through
V$SYSSTAT and is an indication of how often a session waited for
space in the redo log buffer. An example of the query you may use to
obtain the statistic is:
SELECT name, value FROM V$SYSSTAT
WHERE name = 'redo buffer allocation retries';
You should monitor these statistics over a period of time to gain
insight into the trend. The values at one point in time reflect the
cumulative totals since the instance was started, and
aren't necessarily meaningful as a single data
point. Note that this is true for all statistics used for performance
tuning. Ideally, the value of "redo buffer
allocation retries" should be close to 0. If you
observe the value rising during the monitoring period, you should
increase the size of the redo log buffer.
6.4.1.4 Automatic sizing for the SGA
Oracle Database 10g
helps to eliminate tuning SGA pools with automatic sizing for the
SGA. The database automatically allocates memory for each of the SGA
pools, and you only have to specify the total amount of memory
required by setting the SGA_TARGET parameter. Oracle Database
10g proactively monitors the memory requirements
for each pool and dynamically reallocates memory when appropriate.
You can still specify the minimum amount of memory for any of the SGA
pools while using automatic SGA sizing. A few SGA pools, including
the Keep cache, Recycle cache, Streams cache and caches for
nonstandard database block sizes, are still manually sized.
6.4.2 How Oracle Uses the Program Global Area
Each server has a Program Global Area (PGA), which is a
private memory area that contains information about the work the
server process is performing. There is one PGA for each
server process. The total amount of memory
used for all the PGAs is a function of the number of server processes
active as part of the Oracle instance. The larger the number of
users, the higher the number of server processes and the larger the
amount of memory used for their associated PGAs. Using the
Multi-Threaded Server (known as shared servers from
Oracle9i on) reduces total memory consumption
for PGAs because it reduces the number of server processes.
The PGA consists of a working memory area for things such as
temporary variables used by the server process, memory for
information about the SQL the server process is executing, and memory
for sorting rows as part of SQL execution. The initial size of the
PGA's working memory area for variables, known as
stack
space, cannot be directly controlled because it is
predetermined based on the operating system you are using for your
database server. The other areas within the PGA can be controlled as
described in the following sections.
6.4.2.1 Memory for SQL statements
When a server process executes a SQL
statement for a user, the server process tracks the session-specific
details about the SQL statement and the progress by executing it in a
piece of memory in the PGA called a private SQL area,
also known as a
cursor.
This area should not be confused with the shared SQL area within the
shared pool. The shared SQL area contains shareable details for the
SQL statement, such as the optimization plan. Optimizers and
optimization plans are discussed in Chapter 4.
The private SQL area contains the session-specific information about
the execution of the SQL statement within the session, such as the
number of rows retrieved so far. Once a SQL statement has been
processed, its private SQL area can be reused by another SQL
statement. If the application reissues the SQL statement whose
private SQL area was reused, the private SQL area will have to be
reinitialized.
Each time a new SQL statement is received, its shared SQL area must
be located (or, if not located, loaded) in the shared pool.
Similarly, the SQL statement's private SQL area must
be located in the PGA or, if it isn't located,
reinitialized by the server process. This reinitialization is
relatively expensive in terms of CPU resources.
A server process with a PGA that can contain a higher number of
distinct private SQL areas will spend less time reinitializing
private SQL areas for incoming SQL statements. If the server process
doesn't have to reuse an existing private SQL area
to accommodate a new statement, the private SQL area for the original
statement can be kept intact. Although similar to a larger shared
pool, a larger PGA avoids memory churn within the private SQL areas.
Reduced private SQL area reuse, in turn, reduces the associated CPU
consumption, increasing performance. There is, of course, a trade-off
between allocating memory in the PGA for SQL and overall performance.
OLTP systems typically have a "working
set" of SQL statements that each user submits. For
example, a user who enters car rental reservations uses the same
forms in the application repeatedly. Performance will be improved if
the user's server process has enough memory in the
PGA to cache the SQL those forms issue. Application developers should
also take care to write their SQL statements so that they can be
easily reused, by specifying bind variables instead of different
hard-coded values in their SQL statements. This technique is
discussed in more detail in Chapter 8.
The number of private SQL areas, and therefore
the amount of memory in the PGA they consume, is determined by the
intialization parameter
OPEN_CURSORS.
You should set this parameter based on the SQL your application
submits, the number of active users, and the available memory on the
server.
6.4.2.2 Memory for sorting within the PGA
Each server process uses memory in its
PGA for sorting rows before returning
them to the user. If the memory allocated for sorting is insufficient
to hold all the rows that need to be sorted, the server process sorts
the rows in multiple passes called
runs. The
intermediate runs are written to the temporary tablespace of the
user, which reduces sort performance because it involves disk I/O.
Sizing the sort area of the
PGA is a critical tuning point. A sort area that's
too small for the typical amount of data requiring sorting will
result in temporary tablespace disk I/O and reduced performance. A
sort area that's significantly larger than necessary
will waste memory. The correct value for the sort area depends on the
amount of sort activity performed on behalf of your application, the
size of your user population, and the available memory on the
database server. The sort area is determined by the following
initialization parameters, which can be modified dynamically at the
system and session level without restarting the instance:
- SORT_AREA_SIZE
Specifies the maximum amount of memory a server process can use for
sorting before using the temporary tablespace stored on disk.
- SORT_AREA_RETAINED SIZE
Specifies the lower bound on sort memory that the server process will
retain after completing sorts. Setting this parameter equal to
SORT_AREA_SIZE means that each server process will eventually
allocate and hold memory in the PGA equal to SORT_AREA_SIZE. Setting
SORT_AREA_RETAINED_SIZE to a value lower than SORT_AREA_SIZE will
reduce total memory consumption, and server processes will release
memory after completing sorts, freeing memory up for use by other
processes. If most sorts use the maximum amount of memory as defined
by SORT_AREA_SIZE, you can set SORT_AREA_RETAINED_SIZE to the same
value. This avoids shrinking and growing the sort memory
repeatedly.
|
Each parallel execution process allocates its
own sort area. So, if SORT_AREA_SIZE is 4 MB and you execute a query
using a degree of parallelism of 8, the PE processes can allocate up
to 32 MB of memory for sorting. Keep this in mind when setting the
SORT_AREA_SIZE and degrees of parallelism to avoid unexpectedly high
memory consumption.
|
|
6.4.2.3 Automatic sizing for the PGA
As with the SGA, Oracle Database 10g also
provides automatic sizing for the PGA. You can set a parameter
(WORKAREA_SIZE_POLICY) to tell the Oracle database to automatically
allocate memory for the different PGA memory pools, based on the
total amount of PGA memory allocated by the PGA_AGGREGATE_TARGET
parameter. The allocation is based on the needs of the individual SQL
statements. Using automatic sizing for the PGA eliminates the need to
size individual portions of the PGA, such as SORT_SIZE_AREA.
| 
No comments:
Post a Comment