UDPATE: If you're looking for the actual commands on how to do this, view this post (click on the following link):
Otherwise, read on to see what people say is the best way to determine the actual SGA size.
If you've installed Oracle Enterprise Manager, you've probably seen the following error:
Increase the size of the SGA by setting the parameter "sga_target" to 1800 M.
(The size might not be the same).
select * from v$sga;
select sum(value)/1024/1024 "Megabytes" from v$sga;
Here are a few links to useful info regarding this issue.
Taking a look at your current settings:
Oracle Database 10g Release 2 can automatically tune the size of the shared pool, buffer cache, Java pool, large pool, and streams pool. However, you must still manually tune several SGA components, including the log buffer (at startup time only), the keep buffer cache, the recycle buffer cache, and all of the nonstandard block-size buffer caches (for example, the 32K and 16K buffer caches set by the db_32K_cache_size and db_16K_cache_size parameters).
Manually sized components consume SGA from the target value first, and then the remainder of SGA memory is spread across the various autosized pools. In other words, if you set parameter values for any of these manually tuned pools, Oracle Database 10g Release 2 subtracts their sizes from SGA_TARGET first, before allocating memory to the automatically allocated pools.
The SGA_TARGET setting value also includes a small amount of fixed-size SGA. Oracle Database 10g Release 2 sets the fixed size based on the operating system and other criteria. You can see the amount (in bytes) of the fixed-size SGA and totals of other major elements that occupy the SGA by querying the V$SGA view, as follows:SQL> select * from v$sga; NAME VALUE ------------------ ---------- Fixed Size 1247780 Variable Size 124319196 Database Buffers 41943040 Redo Buffers 262144
You can query this same view to determine an initial size for SGA_TARGET when you switch from manual to autotuning, by summing all the components as follows:SQL> select sum(value)/1024/1024 "Megabytes" from v$sga; Megabytes ------------------ 160
Listing 1 shows an example of total real memory allocation for the current SGA from the V$SGA_DYNAMIC_COMPONENTS view (introduced in Oracle9i Database), which contains both manual and autotuned SGA components.
Code Listing 1: Query of V$SGA_DYNAMIC_COMPONENTSSQL> select component, current_size from v$sga_dynamic_components; COMPONENT CURRENT_SIZE ------------------------------- ------------------------- shared pool 92274688 large pool 8388608 java pool 8388608 streams pool 12582912 DEFAULT buffer cache 33554432 KEEP buffer cache 4194304 RECYCLE buffer cache 4194304 DEFAULT 2K buffer cache 0 DEFAULT 4K buffer cache 0 DEFAULT 8K buffer cache 0 DEFAULT 16K buffer cache 0 DEFAULT 32K buffer cache 0 ASM Buffer Cache 0 13 rows selected.
About estimating your SGA and the Rule of Thumb:
Your DBA's rule of thumb (ROT) here is "you want to use 40/50% of RAM for the SGA leaving the other 50% for the dedicated servers (processes — they allocate PGA) and 10% or so for the OS and related processes"
That is, their ROT is how to maximize the use of memory for a typical dedicated server configuration.
That ROT is totally wrong if you are using shared server (there the SGA might be 80-90% of RAM)
That ROT is totally wrong if you are not the only game in town (not the only thing on the server).
That ROT does not mean you are using only 40% of the additional gig of ram you are putting in, it means "40% of it will go towards the SGA, the remaining will be available for dedicated servers to do sorts and hashes and stuff"
but the major considerations here are:
a) how much do you want to assign to your buffer cache for maximum performance
b) how big is your shared/java pool (a function of how much sql/plsql/java you run in your database, no magical number for all to use)
c) do you run in shared server (than the large pool is used and will be large — that is part of the sga) or in dedicated server — then you need to leave OS memory for dynamic allocations
d) what else is going on in the machine.
Do we need to set the various memory params?:
So if we set sga_target we should forget about setting parameters for shared pool, large pool, java
pool and buffer cache correct?
Followup December 20, 2004 – 10am Central time zone:
that would be the prefered way, you can set the others — then they are used as "minimums" — but in for a penny — in for I pound on this one I say
you would either
o use auto SGA sizing
o or not
How to see how much SGA is used:
select * from v$sgastat;
select round(sum(bytes)/1024/1024,2) total_sga,
round(sum(decode(name,'free memory',bytes,0))/1024/1024,2) free,
round((sum(decode(name,'free memory',bytes,0))/1024/1024)/(sum(bytes)/1024/1024)*100,2) free_per
What is the Fixed Size SGA?:
Followup July 24, 2009 – 8am Central time zone:
<quote src=Expert Oracle Database Architecture>
The fixed SGA is a component of the SGA that varies in size from platform to platform and release to release. It is "compiled" into the Oracle binary itself at installation time (hence the name "fixed"). The fixed SGA contains a set of variables that point to the other components of the SGA, and variables that contain the values of various parameters. The size of the fixed SGA is something over which we have no control, and it is generally very small. Think of this area as a `bootstrap¿ section of the SGA, something Oracle uses internally to find the other bits and pieces of the SGA.
the variable part is everything else that isn't the buffer cache, the fixed size, the redo buffers – it includes things like the shared pool, large pool, java pool, etc.ops$tkyte%ORA10GR2> select pool, sum(bytes) from v$sgastat where pool is not null group by pool order by pool; POOL SUM(BYTES) ------------ ---------- java pool 16777216 large pool 16777216 shared pool 268440148
I think that's enough for now.
But let me end with a quote from Tom about whether to manually adjust the settings or let the system automatically manage it:
For a system that has a DBA dedicated to the care and feeding thereof, manual will probably be used for the immediate future. For the system that runs on a box in the corner that you don't have time to really look at closely, auto will rule the day. (opinion). I'm finding on "ok systems", the automatic is doing all right. I haven't heard of any horror stories — but I haven't really seen personally a big system doing it as yet. Some time to get acceptance on it I guess.