Re: New to Oracle 9i...Increasing SGA parameters.

  • From: Tanel Põder <tanel.poder.003@xxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Thu, 15 Jul 2004 10:11:09 +0300

> > show sga;
>  =20
> Total System Global Area  135339388 bytes

This shows how much address space Oracle has mapped/allocated for itself.
This doesn't mean that this memory is actually grabbed from somewhere - it
just shows how large the total SGA can grow. It can be changed only using

> Fixed Size                   454012 bytes

This is fixed portion of SGA where lots of internal low-level structures and
arrays are stored. Admins cannot control the size of fixed SGA explicitly.

> Database Buffers           25165824 bytes

This area caches data blocks (for tables, indexes,etc) and can be
dynamically adjusted.

> Redo Buffers                 667648 bytes

This area buffers and consolidates transaction log entries (called redo
entries in Oracle).

> Variable Size             109051904 bytes

And variable size contains all the rest of memory structures, which can
allocate memory up to "Total System Global Area" - sum(Fixed Area, Database
Buffers and Redo Buffers). This "variable area" may be partly unused if the
memory structures in it have not allocated all of it's available memory. And
modern operating systems handle this never allocated or unused memory well,
by never really allocating it from physical memory or just paging it out.

So, Total System Global Area which we usually define using SGA_MAX_SIZE
parameter, equals Fixed size + Database Buffers + Redo Buffers and whatever
is left over, will be shown as Variable size.

Occupants of "Variable area" are normally shared pool, large pool, java pool
and streams pool in certain 10g configurations.
Each of these pools can be manuallu controlled using an init.ora parameter.
When you have set SGA_MAX_SIZE larger than sum of these pools, then this
free memory will be also counted in the variable size. You can change total
of these pool sizes and DB Buffer Cache dynamically up to SGA variable size

However, if your SGA_MAX_SIZE is defined smaller than sum of all these
memory regions mentioned above, then Oracle bumps sga_max_size up as much as
needed, to be able to accommodate every required memory area to SGA.

> > ALTER SYSTEM set db_cache_size=3016524  <- Increase, was 2516524

Actually you decreased it, cache size was 25165824 (24MB).

>   system altered
> > show sga
> =20
> Total System Global Area  135339388 bytes <- Stays the same size ???

SGA_MAX_SIZE doesn't change dynamically, so this memory is still addressable
and usable by Oracle (as I mentioned, if you won't ever use this memory
again, it will probably be paged out to disk after a while).

> Fixed Size                   453912 bytes=20
> Variable Size             130051904 bytes <- Increase fine

Since you decreased Database Buffers, there is more free memory left for
Oracle in it's processes address space and free memory is accounted in
"Variable Size".

> Database Buffers            4194304 bytes <- Suddenly decreases :-(

You tried to set it to 3MB, but Oracle rounded it up to next 4MB since this
is the minimum memory allocation (granule) size in variable SGA. Right now
there is 4MB of Database Buffers + 450k of Fixed size + 660k of Redo buffers
+ 130M of Variable Size in SGA which totals about 135M as listed in "Total
System Global Area".

> Redo Buffers                 667648 bytes
> > shutdown immediate
> >startup
> > show sga
>  =20
> Total System Global Area  114367768 bytes <- All of sudden smaller ???

Total SGA went smaller because you don't have SGA_MAX_SIZE set or it is
smaller than total sizes of all required memory pools in it (Total SGA will
be calculated based on total sizes of it's memory pools).

> Fixed Size                   453912 bytes
> Variable Size             109051904 bytes <- Back to original size!

Due restart you got rid of "freed" memory which was accounted into "Variable
Size" in previous example.

> Database Buffers            4194304 bytes <- stays as it was before

This is due Oracle's SPFILE which can keep the changes issued by alter

> Redo Buffers                 667648 bytes=20

> System: Win2k, Oracle 9i, version 9.0.2
> Any help, pointers to resources, and even "Jokes at my expense :-)" will
> be appreciated=20

The whole picture would be different (and Total SGA size value would be
static) if you'd defined SGA_MAX_SIZE to 1GB for example and keep your total
buffers and pools  smaller. Don't worry if your experimenting server doesn't
have 1GB of memory, Oracle won't start grabbing this memory in most cases,
if it doesn't really access it.

The next step would be to check various *_pool_size parameters and then
v$sgastat, which gives more detailed overview, how much memory is used for


Please see the official ORACLE-L FAQ:
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
Archives are at
FAQ is at

Other related posts: