Oracle
Internal Architecture
Program Global Area - PGA
The Program Global Area, or PGA, is a variable sized buffer of
non-shared
memory which is limited in growth only by the physical hardware.
Basically,
a separate PGA is allocated by the Oracle server when a user connects
to the database and a session is created. Separate PGA's are also
allocated
for each Oracle background process. The PGA is exclusive to that server
process and is read and written only by Oracle code acting on behalf
of that process.
The PGA consists of three components: a Stack Area, a Data Area and a
Sort Area. The Stack Area is always present in each PGA and holds
information such as a session's variables and arrays. The Data Area,
as its name suggests, is used to store data at a session level. The
Sort
Area is memory allocated to store data being sorted.
The initial size of the PGA is fixed depending on the operating system
and the database initialization parameter settings. However the size
of the PGA is variable once users start connecting. If sufficient PGA
memory is not available when a user attempts to connect to an Oracle
database their connection will be rejected with an Oracle error
message.
However, if a user connection is successful, they can never run out of
PGA space. The overall size of the PGA can be affected by using Multi
Threaded Server, as well as by Oracle initialization parameters such
as open_links, db_files and sort_area_size. As the use of Multi
Threaded
Server (MTS) has the biggest impact on the sizing of the PGA, please
refer to that section for more detailed information regarding PGA
sizing
including information on the initialization parameters as well as MTS
is covered in the relevant sections of this paper.
MULTI THREADED SERVER
An Oracle database can be configured in either a dedicated server
mode
or in a Multi Threaded Server (or MTS) mode. Using Multi Threaded
Server gives an Oracle database the ability to support tens of
thousands
of concurrent users. Oracle is able to accomplish this by moving user
processes and memory components from the individual user processes into
the SGA. This added overhead does require an increase in total SGA
sizing
but this is minimal compared to the overall memory reductions from the
would-be dedicated user processes. Also, growth of the database memory
is in a linear fashion and not exponential as would be the case in a
dedicated server mode. In an MTS configuration the SGA is split into
an additional segment of memory called the User Global Area (or UGA).
Instead of having a dedicated process for each client request there is
only one process per shared server. Also, the number of shared servers
can be controlled and the number of processes is much less than with
a dedicated server configuration. The process space previously used by
each dedicated server process is no longer required as there are now
fewer processes. As a result, there is substantial resource saving.
Parameters to setup:
System Global Area - SGA
Also referred to as the Shared Global Area, the System Global Area, or
SGA is a dynamically allocated segment of memory which is allocated
when a database starts up. Conversely, it is de-allocated when a
database
instance is shut down. Unlike the PGA there is only one SGA per
database
instance and it is static in size.
The SGA consists of several memory structures each of which can
independently
have a major impact on overall system performance. It is because of
this
that a majority of tuning information concentrates
on the components of the SGA. Some basic rules of thumb to consider
when
configuring the SGA for performance are:
To approximate size of the SGA (Shared Global Area), use following
formula:
DB_CACHE_SIZE + DB_KEEP_CACHE_SIZE + DB_RECYCLE_CACHE_SIZE + DB_nk_CACHE_SIZE + SHARED_POOL_SIZE + LARGE_POOL_SIZE + JAVA_POOL_SIZE + LOG_BUFFERS + 1MB |
REDO LOG BUFFER
The Redo Log Buffer is an area of allocated memory within the SGA for
buffering redo information prior to being written to the redo log
files. The redo buffers are a crucial component of the Oracle
recoverability
process and are accessed for almost every database process and
transaction,
including both DML and DDL. Even uncommitted transactions access the
Redo Log Buffer.
The Redo Log Buffer helps to absorb processing spikes caused by the
memory to memory transfer of data (SGA to Redo Buffer) verses the
memory
to disk transfer of data (Redo Buffer to Redo Log). As the buffer fills
up, the output process (LGWR) is awakened to empty the buffer to the
redo log files. The LGWR process requires some lead time, since it is
possible that a large transaction could generate redo faster than the
LGWR process can write to disk. To help alleviate this possible
bottleneck,
once the redo buffer becomes one third full, the LGWR process will be
awakened into action. Otherwise the LGWR process will awaken every 3
seconds or during a checkpoint process.
The size of this buffer is specified in bytes using the log_buffer
parameter. In general, a larger
redo buffer size reduces redo log file I/O, particularly if
transactions
are long or numerous. In a busy system, the value 65536 or higher is
not unreasonable, however values above 1Mb are unlikely to yield any
significant benefit. The default size of the redo log buffer is
dependant
on the hardware and operating system platform. The size of the
redo log buffer can be viewed with the following query:
select * from v$sgastat where name = 'log_buffer';
POOL NAME
BYTES
----------- ------------------- ---------
log_buffer
31457280
When an Oracle database instance is started up, the size of 'Redo
Buffers'
shown can differ from the value of the log_buffer size specified in the
parameter file. This is due to memory set aside for what is known as
"guard" pages which help to protect the redo buffer.
NOTE: If you have small transactions each transaction COMMIT causes
redo to be flushed to disk before the COMMIT returns control to the
user.
Tips for Tuning Redo
- Locate Redo Log files on a separate disk to data if at all possible
- Use larger Redo Log Files if necessary
- Increase the number of Redo Log Groups (& files)
- Use NOLOGGING where possible (Remeber: SQL statements such as UPDATE,
DELETE, conventional path INSERT, and various DDL statements not listed
above) are unaffected by the NOLOGGING attribute)
- It is more effective to size Redo Logs larger and set
LOG_CHECKPOINT_INTERVAL to a higher number (ie. 9999999 - 7x9).
- Init Parameters:
- LOG_BUFFER - (Increase - Evaluate)
- CHECKPOINT_INTERVAL - (Increase)
- DB_BLOCK_MAX_DIRTY_TARGET - (Increase)
JAVA POOL
The Java Pool is a fixed piece of memory allocated to the Java Virtual
Machine (or JVM). It is used to store the shared part of each Java
class
actually used per session. These are basically the read-only parts
(vectors,
methods, etc) and are typically about 4KB to 8KB per class. The size
of the Java Pool is specified in bytes using the java_pool_size
parameter. The default size of the Java Pool
is 20MB.
On a dedicated server, only using Java stored procedures, it is
possible
to size the Java Pool as low as 10m as none of the per session Java
states
are stored in the Java Pool, for dedicated servers, it is stored in the
User Global Area within the PGA. However in an Multi-Threaded Server
(or MTS) environment, which is required for CORBA and EJB's, the Java
Pool could be very large. CORBA and EJB's require more memory, and a
large Java-intensive application could quite possibly require up to 1
gigabyte of Java Pool memory.
On MTS servers, some of the User Global Area (or UGA) used for per
session Java states are stored in the Java Pool. Also, because the size
of the Java Pool is fixed, the total requirement for your application
must be estimated and then multiplied by the number of concurrent
sessions
created. All UGA's must be able to fit in the Java pool. As a general
guideline, the Java Pool should be sized to 50MB or higher for large
applications. While the default of 20MB is adequate for most typical
Java stored procedure usage. The following query can determine how much
Java pool memory is being used:
SELECT *
FROM V$SGASTAT
WHERE pool = 'java pool';
DATABASE BUFFER CACHE (or buffer cache)
Before explaining the purpose and operation of the database buffer
cache it is important to clarify the concept of database blocks. All
data stored in an Oracle database is stored on disk as blocks. A block
is a fixed number of bytes in the range of 2KB to 32KB which is defined
during database creation. Determining a suitable block size can have a
major impact on
database performance and overall disk and memory usage. Typically a
data
warehousing database should have a relatively large block size (around
16K) where as an OLTP intensive database will perform better with a
smaller
block size (around 8K).
The database buffer cache is a statically sized
memory cache allocated during database instance startup. It is used as
a temporary memory store for blocks of data as they are read from and
written to disk. Each buffer contains a single Oracle block, which
in turn could contain several rows of data. The buffer cache contains
three main list structures: The Least Recently Used (or LRU) List, the
Dirty Buffer (or LRUW) and the Hashed Chain List. The Dirty Buffer
holds
blocks that have been modified or updated and need to be written to
disk.
The LRU list holds free and pinned buffers which are either empty and
therefore free for reuse, or blocks of data that are being accessed but
not modified. The Hashed Chain List holds the same buffers as the other
two lists but its buffers are arranged depending on their data block
addresses. When a buffer and therefore a block are held in the Hashed
Chain List it can only be in one of the two other lists at any given
time.Both the Dirty Buffer and the LRU are list memory structures,
meaning that blocks are always inserted on one end of the list (the
most
recently used end - MRU) and gradually moved to the LRU end as new
blocks
are inserted behind them. If a data block is never required again it
will eventually be aged out of the list.
When a user reads information from a table, the Oracle database first
queries the Hashed Chain List to see if the required rows are already
loaded into memory, if so the Hashed Chain List will determine which
buffer list the block has been loaded into. This enables Oracle to
return these rows without ever requiring a disk I/O operation.
Otherwise
if the rows are not already present in the Hashed Chain List, the
blocks
and their associated rows will be retrieved from the relevant database
object from disk and inserted into both the Hash Chained list as well
as the LRU list within the buffer cache prior to any user seeing them.
Once a block is loaded into the buffer cache it is available for use
by all database users.
The size of the database buffer cache is controlled by the instance
startup parameter: db_cache_size
(previously db_block_buffers).
To determine the total size of the
database
buffer cache simply multiply this number by the database block size -as
each represents a single database block. For example:
db_block_size * db_block_buffers (or db_cache_size) = data buffer
cache
size
In many cases simply increasing this parameter can improve system
performance as use of the buffer cache can eliminate a large volume of
disk i/o as many data operations can be satisfied by pure memory
operations
thus improving performance in orders of magnitude. However sizing the
database buffer cache too large can have two drawbacks; firstly, the
larger the buffer cache the larger the LRU is to be scanned to
determine
if a block is in memory or not and secondly, if the buffer cache is
sized
too large it could possibly induce unnecessary memory swapping. This
must be considered when sizing the
buffer cache and must not be disregarded. On many production databases
it is not uncommon for the database buffer cache to occupy over
two-thirds
of the total SGA space.
NOTE: The Oracle initialization parameter - USE_INDIRECT_DATA_BUFFERS
enables an extended buffer cache mechanism for 32-bit platforms that
can support more than 4 GB of physical memory. (Not supported in
Windows NT)
SHARED POOL
The Shared Pool is the area in memory allocated to Oracle for holding
shared memory constructs including the data dictionary cache, library
cache and the execution plans for the corresponding SQL statements.
Shared SQL areas are required to process every unique SQL statement
submitted to a database, however a single shared SQL area can be
"shared"
by multiple sessions if the same identical statement is issued. The
total
size of the shared pool is specified using the shared_pool_size
parameter. It is specified in bytes and
should
not be set too low. If this parameter is set too low, you will not be
able to take advantage of the memory allocated to the Database Buffer
Cache, even if the database buffer cache has been set to a reasonably
large size. Conversely, the size of the shared pool should not be set
too high either, as too high a value can result in a waste of memory
with possible adverse performance effects. Querying v$sgastat will show
available free memory. It will also show
how much memory is being wasted. For example:
select substr(name,1,25), value/1024/1024 "Size MB" from v$parameter
where name = 'shared_pool_size';
SUBSTR(NAME,1,25)
Size MB
------------------------- ----------
shared_pool_size
90
select pool, name, round(bytes/1024/1024,2) "Size (MB)"
from v$sgastat
POOL
NAME
Size (MB)
----------- -------------------------- ----------
shared pool free
memory
61
large pool free
memory
12.25
java pool free
memory
26.5585938
In this example the first statement displays how large the shared pool
is: 90 MB. The second statement then displays how much of the memory
in the shared pool is free: 61MB. This indicates that the shared pool
is being under utilized. Almost two-thirds is not being utilized. This
memory could be allocated elsewhere.
NOTE: The size of the shared pool (or shared_pool_size) can be
monitored
through the data dictionary cache and the library cache. Both should
be continuously monitored for an appropriate hit ratio.
Inside the Sared Pool Size he also have:
1. DATA DICTIONARY CACHE
The Data Dictionary Cache is where information from the data
dictionary for shared access is stored. These includes information
about the database, it's structures and it's users (columns
definitions, tables names, users, password, privileges, etc). When a
user
processes
a SQL statement, the data dictionary is referenced by Oracle several
times. Reducing physical disk I/O is very important, hence the more
information
that is stored in memory, the less needs to be read from disk. The data
dictionary cache is very important in this respect because this is
where
the data dictionary components are buffered. The only means of tuning
the data dictionary is by increasing the shared pool (or
shared_pool_size parameter). The data dictionary component can be
monitored via V$ROWCACHE
using the following select statement:
select sum(gets) "Gets",
sum(getmisses) "Get Misses",
(1-(sum(getmisses)/sum(gets))) * 100 "Hit Ratio"
from v$rowcache;
Gets Get Misses Hit Ratio
--------- ---------- ---------
509234 272
96.946586
This value should generally be over 90%, however whenever the database
instance is first started, this value will be somewhere around 85%.
2. LIBRARY CACHE
The library cache consists of shared SQL and PL/SQL areas. When SQL is
executed, the statement has to be parsed. The library cache reduces
the overhead of this by maintaining parsed SQL and PL/SQL in the
library
cache. Whenever a statement is re-executed, there is no need to
re-parse
the statement if the statement exists in the Library Cache. In essence,
this can reduce the work of the database and improve performance,
especially
in an OLTP environment where the same SQL statements are typically
reissued.
Also, the use of bind variables in the SQL statements can also help
this.
More information on Bind Variables is covered in a section on its own.
Statistics reflecting library cache activity is stored in the dynamic
performance view V$LIBRARYCACHE. These statistics reflect all library
cache activity since the most recent instance startup. Like the Data
Dictionary, the only means of tuning the library cache is by increasing
the shared pool (or shared_pool_size parameter).
LARGE POOL
The large pool is an optional area of
the SGA similar to the shared pool, but with restrictions on its usage.
Only certain types and sizes of memory can be allocated in this
pool. The memory for the large pool does not come from the shared pool
but instead directly out of the SGA thus adding to the amount of shared
memory Oracle needs at startup.
The two main uses of the large pool are:
1. For the User Global Area (UGA) of sessions connected using
multi-threaded
server (MTS)
2. Buffering for sequential file IO (e.g. used by the recovery process
when multiple IO slaves are configured)
The large pool is protected by the 'shared pool' latch for memory
allocation and management. However, unlike the shared pool, there is
no LRU mechanism in the Large Pool. So chunks of memory are never aged
out of the large pool - memory must be explicitly allocated and freed
by each session. If there is no free memory left when a request is made
for large pool then an ORA-4031 will be signaled.
The size of the large pool is specified by the large_pool_size
parameter
and the minimum size chunk of memory which can be allocated is
determined
by the large_pool_min_alloc parameter. By default the large pool is not
allocated, and needs to be explicitly specified to exist. When
specifying
a value for the large pool it can be specified in either megabytes or
kilobytes in order to accomplish this the large_pool_size parameter can
be assigned a numerical value or a number followed by the suffix "K"
(for Kilobytes) or "M" (for Megabytes).
If large_pool_size is left unset and the pool is required by either
parallel query or backup I/O slaves, then Oracle will compute a value
automatically. This occurs when parallel_automatic_tuning is TRUE. The
computation will add 250k per session for the MTS server if
mts_dispatchers
is configured.
NOTE: the default computation can yield a size that is either too large
to allocate or causes performance problems. In such a case, the
large_pool_size should be explicitly set to a value sufficiently small
enough for the database to start.
Large Pool using MTS
If there is a large pool configured, MTS will ONLY try to use this pool
for a sessions UGA. When a new session is started a small amount of
memory (known as the fixed UGA) is allocated in the shared pool and the
rest of the session memory (UGA) is taken from the large pool.
If there is insufficient space left in the large pool and ORA-4031
error
will be returned. Memory is allocated from the large pool in chunks of
at least large_pool_min_alloc bytes in size to help avoid memory
fragmentation.
This can impact the amount of memory used by each MTS session when
compared
to memory usage with no large pool configured. If no large pool is
configured
MTS will use the shared pool for the entire UGA as was the case in
Oracle
version 7x.
MULTIPLE DATABASE BUFFER POOLS (more information HERE)
Multiple Database Buffer Pools are, as their name suggests, separately
configured pools within the database buffer cache. With multiple buffer
pools we are able to segregate memory operations based on the types of
objects being accessed. For example one table could contain information
that is rarely accessed so storing it's blocks in memory over an
extended
period of time could be seen as a waste of resources. On the other hand
another table could be frequently accessed and the I/O requirements to
constantly reload its blocks into memory could be a time consuming
process. Hence with Multiple buffer pools we are able to partition our
buffer pool memory into separate areas that can be used to store
different types of data objects to help overcome this issue. Oracle
provides us with three separate types of buffer pools that can be
configured. They
are Keep, Recycle and Default.
Keep
- Intended for frequently accessed objects
- Lookup Tables and dimensions are good candidates for the Keep pool
- When sizing the Keep pool it should be sized so it can contain the
sum of all objects intended to be stored, as objects can still be aged
out of this pool if there is insufficient space
Recycle
- Intended for rarely accessed or large objects which could be
considered a waste of space if cached
- Randomly accessed large tables are good candidates for the Recycle
pool
- The Size of Recycle pool can be relatively small as the intention of
this pool is for the buffers to be frequently overwritten
Default
- Contains the data blocks that are not explicitly assigned to any
buffer pool
- Contains objects that are explicitly assigned to the DEFAULT pool
- There is no need to define the size and number of LRU latches for the
Default buffer pool as it assumes what is not explicitly assigned to
the Keep or Recycle pools
The are two new initialization parameters added for configuring
Multiple
Buffer Pools they are buffer_pool_keep and buffer_pool_recycle. When
setting these parameters the number of buffers/blocks are specified
along
with the number of LRU latches. In addition the db_block_lru_latches
parameter must be specified.
NOTE: Multiple Buffer Pools each contain their own Dirty Buffer Cache
and Hash Chain Lists in conjunction with the addition LRUs specified
during their creation.
When configuring these parameters it is also important to realize that
the number of latches are subtracted from the total number allocated
to the instance, just like the buffers. There must be a minimum of 50
buffers per LRU latch allocated to each buffer pool.
An example of how a parameter file may look follows:
DB_BLOCK_SIZE = 8192
DB_BLOCK_BUFFERS (or db_cache_size) = 20000
BUFFER_POOL_KEEP = (buffers:8000, lru_latches:4)
BUFFER_POOL_RECYCLE = (buffers:1600, lru_latches:2)
DB_BLOCK_LRU_LATCHES = 12
In this scenario our block size is 8KB and our total memory allocation
is 20,000 buffers each at 8KB giving us a total buffer cache of
approximately
163MB. This buffer cache is then broken down into three separate pools:
Keep Pool at approximately 65MB, Recycle Pool at approximately 12MB and
the remainder of 86MB and 12 latches going to the default pool. Of
course
in order for a database object to take advantage of this it is
necessary
for the object to be either created or modified with the buffer
parameters
specified within the storage clause. The following example demonstrates
this:
CREATE TABLE Foo_Keep
(nfoo number (1),
vfoo varchar2(5) )
STORAGE (buffer_pool KEEP)
/
Table created.
CREATE TABLE Foo_Default
(nfoo number (1),
vfoo varchar2(5) )
STORAGE (buffer_pool DEFAULT)
/
Table created.
ALTER TABLE Foo_Recycle STORAGE (buffer_pool RECYCLE);
Table altered.
SELECT table_name, buffer_pool
FROM user_tables
WHERE table_name LIKE 'FOO%'
/
TABLE_NAME BUFFER_POOL
------------------ -----------
FOO_DEFAULT DEFAULT
FOO_KEEP KEEP
FOO_RECYCLE RECYCLE
NOTE: When using Multiple Buffer Pools, it is important to remember
that the placement of objects within a specific buffer pool is
explicit.
Remember that objects are not explicitly assigned a buffer pool will
end up in the Default Pool. This means that the adoption of a multiple
buffer pool strategy is an all or nothing approach. The benefits of
using
multiple pools are enormous but can be easily outweighed if configured
incorrectly. Finally, the Oracle8i Tuning Guide has an excellent
section
that cover the steps taken to determine whether or not multiple buffer
pools will be of benefit to your application.
CACHING ORACLE OBJECTS
In an effort to help a table or cluster remain in memory longer, Oracle
provides the CACHE command as part of a table or cluster's storage
attributes. If a table or cluster has the Cache attribute set, the
blocks
of that object will be inserted into the MRU end of the LRU list in the
Database Buffer Cache whenever they are accessed. This does not
guarantee
the blocks to remain permanently in memory, but instead that they will
remain as long as feasible. Updating an extremely large table (perhaps
larger then the buffer cache) can age out any previously cached blocks.
An example of using the cache clause follows:
create table foo_x
( nfoo_x number(1),
vfoo_x varchar2(3) );
Table created.
create table foo_y
( nfoo_y number(1),
vfoo_y varchar2(3) )
CACHE ;
Table created.
select table_name, cache
from user_tables
where table_name like 'FOO%'
TABLE_NAME CACHE
------------- -----
FOO_X N
FOO_Y Y
The default Cache attribute for a Table or Cluster is NOCACHE.
Explicitly
setting a table to be NOCACHE does have benefits as well - much as the
recycle
buffer pool is used to store rarely accessed tables, these too can be
specified using NOCACHE.
NOTE: The CACHE and NOCACHE attribute is also a part of the LOB storage
clause. Using NOCACHE for a LOB column is extremely beneficial. A LOB
that is several gigabytes in size could easily cause the entire
contents
of the database buffer pool to be aged out completely.
Oracle also provides the following two initialization parameters to
assist with caching of other objects
Cursors_Space_For_Time
- Default value is FALSE
- If set to TRUE, a shared SQL area cannot be aged out of the shared
pool until all application cursors associated with its statement are
closed. This can improve performance as each referenced
cursor remains in memory. When set to TRUE this can possibly lead to
more memory consumption
Session_Cached_Cursors
- Default value is 0 - can be set dynamically
- specifies the number of session cursors to cache. Repeated parse
calls of the same SQL statement cause the session cursor for that
statement to be moved into the session cursor cache. Subsequent parse
calls will find the cursor in the cache and need not reopen the cursor.
- An LRU list is used to maintain entries in the session cursor cache.
At startup time, Oracle creates a number of system background
processes for each instance to perform system functions:
LATCH
CONTENTION
A latch is a type of a lock that can be very quickly acquired and
freed. Latches are typically used to prevent more than one process from
executing the same piece of code at a given time. A process acquires a
latch when working with a structure in the SGA. It continues to hold
the latch for the period of time it works with the structure. The latch
is dropped when the process is finished with the structure. Each latch
protects a different set of data, identified by the name of the latch.
If a required latch is busy, the process requesting it spins, tries
again and if still not available, spins again. The loop is repeated up
to a maximum number of times determined by the initialization parameter
_SPIN_COUNT. If after this entire loop, the latch is still not
available, the process must yield the CPU and go to sleep. Initially is
sleeps for one centisecond. This time is doubled in every subsequent
sleep. This causes a slowdown to occur and results in additional CPU
usage, until a latch is available. The CPU usage is a consequence of
the "spinning" of the process. "Spinning" means that the process
continues to look for the availability of the latch after certain
intervals of time, during which it sleeps.
To increase the number of latches available to your database, increase
the DB_BLOCK_LRU_LATCHES to a value of two times the number of CPUs on
the server machine. The default value of this parameter is half the
number of CPUs on your machine. Increasing this value make more latches
available and reduce latch contention
The main cause for Latch Contention, and therefore poor performance, is
because of poorly written SQL statements, frequent context switches
etc. However Oracle provides several initialization parameters which
can help reduce Latch contention to a large degree, although the best
solution is still to resolve poorly written SQL code where possible.
Four areas which can help in addressing Latch contention and thus
improve
overall system performance are:
- Parsed SQL statements may be aged out of the SGA if insufficient
memory is allocated to shared pool. In this case the shared_pool_size
parameter should be
increased.
- Use of identical SQL statements, this helps can help in sharing of
SQL statements which can both overcome shared pool usage as well as
parsing efficiency.
- Using BIND variables wherever possible will permit sharing of SQL
statements which can reduce shared pool usage while reducing parsing.
- The use of the Cursor_Sharing parameter.
More information HERE
INITIALIZATION
PARAMETERS AFFECTING MEMORY
USAGE:
DB_BLOCK_BUFFERS (or
db_cache_size)
Refer to the section on the Database Buffer Cache in this document for
more information on this parameter.
Specified as an integer value representing the number of buffers
available in the database buffer cache. To determine the true size of
the database buffer cache, multiply this parameter by the
db_block_size.
The total size of the database buffer cache should typically be sized
to represent two-thirds of the SGA.
NOTE: The db_block_buffer parameter is the single most significant
parameter affecting performance on an Oracle database.
SHARED_POOL_SIZE
Refer to the section on the Shared Pool in this document for more
information on this parameter.
The Shared_Pool_Size is specified in bytes. The default size for this
setting parameter is never adequate and can usually be multiplied by a
factor of 10 on most platforms. However before haphazardly increasing
the value for this parameter the steps outlined in the Shared Pool
section
of paper should be read und understood. This does not mean for us and
the to all versions of Oracle and is measured in bytes. This is the
memory
that is allocated to Oracle for areas like the data dictionary, stored
procedures, and statements. Comprising a large part of the SGA, the
Shared_Pool_Size
is comprised of the dictionary cache and library cache, and just like
DB_Block_Buffers (or db_cache_size) above, should
not be set too low. If this parameter
is set too low, you will not be able to take advantage of the memory
that has been allocated by the DB_Block_Buffers (or db_cache_size), even if the
DB_Block_Buffers (or db_cache_size)
has been set a reasonable value.
SORT_AREA_SIZE
Specified in bytes. Oracle allocates a Sort Area sized to the value of
Sort_Area_Size to every user process for performing data sorts. The
Sort Area is memory that is allocated outside of the SGA. In a
dedicated
server environment it is easy to monitor the effects of modifying this
parameter by monitoring the size of the user processes via tools such
as ps -ef. If the Sort_Area_Aize is set too small, a process will be
forced to sort on disk in the user's temporary tablespace.
SORT_AREA_RETAINED_SIZE
Specified in bytes. The Sort_Area_Retained_Size parameter is used in
conjunction with the Sort_Area_Size parameter. It controls the size
that the Sort_Area_Size will be reduced to once a user sort is
complete.
This parameter should be set less than or equal to the Sort_Area_Size
parameter to be truly effective.
SHARED_POOL_RESERVE_SIZE
Specified in bytes. The Shared_Pool_Reserve_Size parameter controls the
allocation of a portion of the shared pool for storing large objects.
The default setting of this parameter is 5% of the shared_pool_size
setting.
Setting this to a relatively low value ensures that the majority of the
area within the shared pool is not being used for large objects, but
rather in a "separate area".
SHARED_MEMORY_ADDRESS
It is recommended you refer to the Oracle Installation Guide which is
shipped with your Oracle software if you are considering using this
parameter as it is very platform specific
HI_SHARED_MEMORY_ADDRESS
It is recommended you refer to the Oracle Installation Guide which is
shipped with your Oracle software if you are considering using this
parameter as it is very platform specific
LOG_BUFFER
Refer to the section on the Redo Log Buffer in this document for more
information on this parameter.
JAVA_POOL_SIZE
Refer to the section on the java Pool in this document for more
information
on this parameter.
LARGE_POOL_SIZE
Refer to the section on the Large Pool in this document for more
information on this parameter.
BUFFER_POOL_KEEP
Refer to the section on Multiple Buffer Pools in this document for more
information on this parameter.
BUFFER_POOL_RECYCLE
Refer to the section on Multiple Buffer Pools in this document for more
information on this parameter.
DB_BLOCK_LRU_LATCHES
Specified as an integer, DB_Block_LRU_Latches indicates the maximum
number of LRU latch sets. The buffers of a buffer pool are equally
divided among the working LRU latch sets of the buffer pool so that
each
buffer is protected by one LRU latch. Normally, the more latches you
specify, the less contention exists for those latches. However, too
many
latches may result in small LRU lists, potentially reducing the cache
life of a database block. Typically you should set this parameter to
the number of CPUs or a multiple of that number. The default setting
for this parameter is equal to CPU_COUNT/2 , which is usually adequate.
Refer to the section on Multiple Buffer Pools in this document for more
information on this parameter.
Sample from the command SHOW SGA:
Total System Global Area 23460696 bytes
Fixed
Size
72536 bytes
Variable
Size
22900736 bytes
Database
Buffers
409600 bytes
Redo
Buffers
77824 bytes
* Total System Global Area
- Total in bytes of all the sub-divisions that makes up the SGA.
* Fixed Size
- Contains general information about the state of the database
and the instance, which the background processes need to access.
- No user data is stored here.
- This area is usually less than 100k in size.
* Variable Size
- This section is influenced by the following init.ora parameters
shared_pool_size
large_pool_size
java_pool_size
- See 'Approximating Size of the SGA' below for
version specific information.
* Database Buffers
- Holds copies of data blocks read from datafiles.
size = db_block_buffers * block size
* Redo Buffers
- A circular buffer in the SGA that holds information about
changes made to the database.
- Enforced mininum is set to 4 times the maximum database block
size for the host operating system.
Approximating size of the SGA
In Oracle9i, the SGA can be configured as in prior releases to be
static, or can now be dynamically configured.
The size of the dynamic SGA is determined by the values of the
following database initialization parameters: DB_BLOCK_SIZE,
DB_CACHE_SIZE, SHARED_POOL_SIZE, and LOG_BUFFER.
Beginning with Oracle9i, the SGA infrastructure is dynamic. This
means that the following primary parameters used to size the SGA can be
changed while the instance is running:
- Buffer cache ( DB_CACHE_SIZE) -- the size in bytes of the cache
of standard blocks
- Shared pool ( SHARED _POOL_SIZE) -- the size in bytes of the area
devoted to shared SQL and PL/SQL statements
- Large pool (LARGE_POOL_SIZE) (default is 0 bytes) -- the size in
bytes of the large pool used in shared server systems for session
memory, parallel execution for message buffers, and by backup and
restore processes for disk I/O buffers.
The LOG_BUFFER parameter is used when buffering redo entries to a redo log. It is a static parameter and represents a very small portion of the SGA and can be changed only by stopping and restarting the database to read the changed value for this parameter from the initialization parameter file (init.ora).
Note that even though you cannot change the MAX_SGA_SIZE parameter value dynamically, you do have the option of changing any of its three dependent primary parameters: DB_CACHE_SIZE, SHARED_POOL_SIZE, and LARGE_POOL_SIZE to make memory tuning adjustments on the fly.
To help you specify an optimal cache value, you can use the dynamic DB_CACHE_ADVICE parameter with statistics gathering enabled to predict behavior with different cache sizes through the V$DB_CACHE_ADVICE performance view. Use the ALTER SYSTEM...SET clause... statement to enable this parameter.
Beginning with Oracle9i, there is a concept of creating tablespaces with multiple block sizes and specifying cache sizes corresponding with each block size. The SYSTEM tablespace uses a standard block size and additional tablespaces can use up to four non-standard block sizes.
The standard block size is specified by the DB_BLOCK_SIZE parameter. Its cache size is specified by the DB_CACHE_SIZE parameter. Non-standard block sizes are specified by the BLOCKSIZE clause of the CREATE TABLESPACE statement. The cache size for each corresponding non-standard block size is specified using the notation: DB_nK_CACHE_SIZE parameter, where the value n is 2, 4, 8, 16, or 32 Kbytes.
The standard block size, known as the default block size, is usually set to the same size in bytes as the operating system block size, or a multiple of this size. The DB_CACHE_SIZE parameter, known as the DEFAULT cache size, specifies the size of the cache of standard block size (default is 48M bytes). The system tablespace uses the standard block size and the DEFAULT cache size.
Either the standard block size or any of the non-standard block sizes and their associated cache sizes can be used for any of the other tablespaces. Ifyou intend to use multiple block sizes in your database storage design, you must specify at least the DB_CACHE_SIZE and one DB_nK_CACHE_SIZE parameter value. You must specify all sub-caches for all the other non-standard block sizes that you intend to use. This block size/cache sizing scheme lets you use up to four different non-standard block sizes for your tablespaces and lets you specify respective cache sizes for each corresponding block size.
Because the DB_BLOCK_SIZE parameter value can be changed only by re-creating the database, the value for this parameter must be chosen carefully and remain unchanged for the life of the database.
To approximate size of the SGA (Shared Global Area), use following
formula:
DB_CACHE_SIZE + DB_KEEP_CACHE_SIZE + DB_RECYCLE_CACHE_SIZE + DB_nk_CACHE_SIZE + SHARED_POOL_SIZE + LARGE_POOL_SIZE + JAVA_POOL_SIZE + LOG_BUFFERS + 1MB |
NOTE: ADD IN EACH DB_nk_CACHE_SIZE. THERE CAN BE UP TO 4 DB_nk_CACHE_SIZE (2, 4, 8, 16, 32k) DEFINED. ONE OF THE BLOCK SIZES IS THE DEFAULT BLOCK SIZE AND ITS CACHE SIZE IS DEFINED BY DB_CACHE_SIZE.
For more information see Note:61623.1 Calculating the Size of the Shared Pool (Note
61340.1)
Shared pool is the amount of fixed, preallocated space in the SGA for
use by multi-threaded server session PGA, shared SQL area, and other
small, dynamically allocated SGA data structures.
While analyzing shared pool sizing, it is helpful to first increase the
shared pool to a very large value, so that the dynamically allocated
SGA structures may be allowed to expand to a desirable size. Once
this sizing exercise has been completed, the shared pool may be
downsized to the appropriate value.
Formula
Max Session Memory * No. of
concurrent users
+ Total Shared SQL Area Usage
+ PLSQL Sharable Memory
+ Minimum 30% Free Space
----------------------------------------------
= Minimum Allowable Shared
Pool
Example
Find the SID for an example user session:
select sid from v$process p, v$session s where p.addr=s.paddr and
s.username='OPS$JSMITH';
SID
----------
29
Get the maximum session memory for this session:
select value from v$sesstat s, v$statname n where s.statistic# =
n.statistic# and n.name = 'session uga memory max' and sid=29;
VALUE
----------
273877
Get the total shared SQL area:
select sum(sharable_mem) from v$sqlarea;
SUM(SHARAB
----------
8936625
Get the PLSQL sharable memory area:
select sum(sharable_mem) from v$db_object_cache;
SUM(SHARAB
----------
4823537
Example shared pool calculation:
274K shared memory * 400
users
+ 9M
Shared SQL Area
+ 5M PLSQL
Sharable Memory
+ 60M Free Space
(30%)
---------------------------------
= 184M Shared Pool