| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: New to Oracle 9i...Increasing SGA parameters.
> > 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 restart.
> 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 (SGA_MAX_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
reboot?
This is due Oracle's SPFILE which can keep the changes issued by alter system.
> 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 what.
Tanel.
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Thu Jul 15 2004 - 02:07:51 CDT
 |
 |