| Calculating SGA MAX Size [message #110205] |
Fri, 04 March 2005 02:16  |
sridcpp
Messages: 23
Registered: February 2005
Location: India
|
Junior Member |
|
|
How should i decide SGA size
No of Users : Apprx 100
RAM : 1GB
What should be the SGA_MAX SIZE
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Calculating SGA MAX Size [message #110889 is a reply to message #110205] |
Thu, 10 March 2005 17:36  |
jbatista
Messages: 8
Registered: March 2005
Location: Horsham, PA
|
Junior Member |
|
|
Approximating size of the SGA
8.0.X
To approximate size of the SGA (Shared Global Area), use the following formula:
((db_block_buffers * block size) +
(shared_pool_size + large_pool_size + log_buffers) + 1MB
8.1.X
((db_block_buffers * block size) +
(shared_pool_size + large_pool_size + java_pool_size + log_buffers) + 1MB
9.X
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.
Also, take a look at the following Oracle Document in MetaLink:Doc ID 1008866.6
|
|
|
|
| Re: Calculating SGA MAX Size [message #111677 is a reply to message #110205] |
Fri, 18 March 2005 10:09 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
You want as much RAM as possible for oracle to use on the server (vs other apps software on it). Also, if you have lots of stored packages and procedures and more of an OLTP active environment you will need a larger SGA. If you have fewer concurrent users executing ad hoc type stuff you need a larger PGA. If you aren't using stored java procedures and/or bitmap indexes, set those memory params to 0.
If you are on 10g, use sga_target.
Whatever you set it initially will just be a guess, then you monitor and tweak it as you go.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
