|
|
 |
Donald K. Burleson
Oracle Tips |
Oracle SGA Parameters
Self-tuning
Oracle's memory regions involves altering the values of a number of Oracle
parameters. While there are over 250 Oracle9i parameters that govern the
configuration of every aspect of the database, there are only a handful of
Oracle9i parameters that are important for Oracle SGA tuning:
db_cache_size - This parameter determines the number of database block
buffers in the Oracle SGA and is the single most important parameter in
Oracle memory.
db_keep_cache_size - db_keep_cache_size is used to store small tables
that perform full table scans. This data buffer pool was a sub-pool of
db_block_buffers in Oracle8i.
db_recycle_cache_size - This is reserved for table blocks from very large
tables that perform full table scans. This was buffer_pool_keep in
Oracle8i.
large_pool_size - This is a special area of the shared pool that is
reserved for SGA usage when using the multi-threaded server. The large pool
is used for parallel query and RMAN processing, as well as setting the size
of the Java pool.
log_buffer - This 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. 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 additional memory.
hash_area_size - This parameter determines the memory region reserved for
hash joins. Starting with Oracle9i, Oracle Corporation 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
work areas by setting pga_aggregate_target. hash_area_size is retained only
for backward compatibility purposes.
pga_aggregate_target This parameter defines the RAM area reserved for
system-wide sorting and hash joins.
sga_max_size This parameter defines the maximum size of the Oracle SGA,
and cannot be modified while the instance is running.
The above
text is an excerpt from "Creating
a Self Tuning Oracle Database", by Rampant TechPress. It is only
$9.95 and all scripts in this tips can be immediately downloaded.
http://rampant-books.com/book_2003_2_audit.htm
|
Download your Oracle scripts now:
www.oracle-script.com
The
definitive Oracle Script collection for every Oracle professional DBA
|
|
|
