What is my SGA?

[nabeel]

I check this :

SQL> SELECT * FROM V$SGASTAT ;

POOL NAME BYTES
----------- -------------------------- ----------
^ fixed_sga 735096
^ buffer_cache 1006632960
^ log_buffer 100927488
shared pool errors 40040
shared pool enqueue 488464
shared pool KGK heap 7000
shared pool KQR L PO 1599648
shared pool KQR M PO 3049208
shared pool KQR S SO 4608
shared pool sessions 761600
shared pool sql area 31678504
shared pool 1M buffer 2098176
shared pool KGLS heap 8710480
shared pool processes 322000
shared pool db_handles 290000
shared pool log_buffer 1576992
shared pool parameters 7376
shared pool free memory 796739680
shared pool PL/SQL DIANA 974232
shared pool FileOpenBlock 1935392
shared pool PL/SQL MPCODE 611704
shared pool library cache 19180608
shared pool miscellaneous 18546640
shared pool MTTR advisory 465880
shared pool PLS non-lib hp 3144
shared pool joxs heap init 4240
shared pool kgl simulator 1919440
shared pool sim memory hea 1927304
shared pool table definiti 15264
shared pool trigger defini 106512
shared pool trigger inform 1768
shared pool trigger source 600
shared pool Checkpoint queue 3079680
shared pool VIRTUAL CIRCUITS 574960
shared pool dictionary cache 3221760
shared pool KSXR receive buffers 1034000
shared pool FileIdentificatonBlock 349824
shared pool message pool freequeue 940944
shared pool KSXR pending messages que 853952
shared pool event statistics per sess 2847040
shared pool fixed allocation callback 1000
large pool free memory 67108864
java pool free memory 117440512

43 rows selected.

Then i checked :

SQL> show sga

Total System Global Area 2198837112 bytes
Fixed Size 735096 bytes
Variable Size 1090519040 bytes
Database Buffers 1006632960 bytes
Redo Buffers 100950016 bytes

and at the last i checked :

SQL> show parameter shared_pool

NAME TYPE VALUE
------------------------------------ ----------- --------------------

shared_pool_reserved_size big integer 42781900

shared_pool_size big integer 855638016




Now when my sga size is 855 MB why am i getting 735096
in the first 2 queries?? What is my actuall sga size? Please provide me a link or something which could help me in clearin my doubt regarding this...

Your quick help is appricated...

Thanks in advance..

[Sweetie]

hi

I think you need to read more documentation because seems like you dont know what is s SGA!

Shered pool is part of SGA not the complete SGA

Fixed size is the bootstrap memory needed by Oracle to start an instance, it's not SGA or shared pool

[guru_heaven]

plz sir, and you call yourself a DBA?

database baby sitter rather

[nabeel]

It depends on the OS.

Oracle on Windows will pre-allocate it. Since windows will not actually give us the physical ram until we use the physical ram, it is just in your paging file.
It seems like Oracle will report this as the Total SGA, and will show the difference in the VARIABLE SIZE !! And when memory is de-allocated from one of the components, it will go back into the Total SGA bucket, and not back to the OS.

The bad thing about this is that the Total SGA is mis-leading (cause it is not what is actually allocated). For example, our machine has total 4 Gig of Physical memory...and each one of these instance reports approx. 3 Gig of Total SGA.

Now can you find it out sir!

[Sweetie]

Hi

I dont understand what you are saying. Do you know the components which forms SGA?? Also Oracle shows those values from the parameters not from OS

One point, it seems that the person who set the parameters didnt have much clue about the meaning of parameters. You need to have a talk with this person

[nabeel]

This is for Sweetie and guru, hope this helps u

Dynamic SGA implements an infrastructure that allows the SGA configuration to change without shutting down the instance. This then allows the sizes of the database buffer cache, shared pool, and large pool to be changed without shutting down the instance. Conceivably, the database buffer cache, shared pool, and large pool could be initially under configured and would grow and shrink depending upon their respective work loads, up to a maximum of SGA_MAX_SIZE.

The size of the SGA is determined by several initialization parameters. The parameters that most affect SGA size are:

DB_CACHE_SIZE: The size of the cache of standard blocks.

LOG_BUFFER: The number of bytes allocated for the redo log buffer cache.

SHARED_POOL_SIZE: The size in bytes of the area devoted to shared SQL and PL/SQL.

LARGE_POOL_SIZE: The size of the large pool; the default is zero.


My question is what is that makes fixed and variable size!!! and it still stands there Sweetie and guru. Stop findin a baby sitter and look for the answer!

[Sweetie]

Hi

fixed size is a SGA component, not the GSA itself. It varies from platform to platform. It's sort of bootstrap part of SGA

variable size, if you sum all pool sizes you can see they form variable size

the problem is, you asked what is your SGA however variable and fixed do not form all your SGA and that's why I have been repeatdly asking you if you know what forms SGA. Also you have not said anywhere you are running Oracle 9i, release where dynamic SGA is implemented. And I still dont understand your question

[Kishore]

Put the question more specific so that some one can answer

[SANJAY_G]

http://asktom.oracle.com/pls/ask/f?p...D:365088445659

HTH

[nabeel]

Thanks alot Sanjay that was a great aritcle!