Different between SGA_TARGET and TOTAL SGA

Yockee

Hi,

What is the difference between SGA_TARGET (in V$PARAMETER) and Total SGA (the sum of all values in V$SGA) ?

For the SGA_TARGET, I run : SELECT name,value,display_value,isdefault,isbasic FROM V$PARAMETER WHERE name IN ('memory_target', 'sga_target', 'pga_aggregate_target'); I get around 14 GB.

For the total SGA, I run : SELECT sum(value)/1024/1024 "TOTAL SGA (MB)" FROM v$sga; I get around 26 GB. The query on SGA size (SELECT name,value/1024/1024 “SGA (MB)” “FROM v$sga;) gives out result as follows :

From https://docs.oracle.com/cd/B19306_01/server.102/b14237/initparams193.htm#REFRN10256, it says "SGA_TARGET specifies the total size of all SGA components".

If SGA_TARGET is the total of all SGA, then why do they differ ?

Thanks for clearing up my confusion.

I am using Oracle 11.2g.

Luís Gustavo Lira

Hi,

actually, you are using Automatic Memory Management, when you set MEMORY_TARGET the instance automatically distributes memory between SGA and PGA dynamically.

Commonly when we set MEMORY_TARGET, we then set the SGA_TARGET and the PGA_AGGREGATE_TARGET to zero and let Oracle dynamically distribute the values ​​for SGA and PGA.

In this case, when you assign a value of SGA and PGA actually Oracle understands it as a minimum value where it could not dynamically change to a value smaller than this.

You can check more details in the documentation:

https://docs.oracle.com/cd/E18283_01/server.112/e17120/memory003.htm#BGBJAHEJ   

Yockee

Agreed. Thanks :)

But what is the difference between SGA from v$sga and V$PARAMETER as I mentioned above ? Which one is the current (being used) SGA_TARGET ?

thank you

Luís Gustavo Lira

Hi,

as you are using memory_target, I think is better to check in V$MEMORY_DYNAMIC_COMPONENTS.