Discussions
Categories
- 17.9K All Categories
- 3.4K Industry Applications
- 3.3K Intelligent Advisor
- 62 Insurance
- 536K On-Premises Infrastructure
- 138.2K Analytics Software
- 38.6K Application Development Software
- 5.7K Cloud Platform
- 109.4K Database Software
- 17.5K Enterprise Manager
- 8.8K Hardware
- 71.1K Infrastructure Software
- 105.2K Integration
- 41.5K Security Software
Different between SGA_TARGET and TOTAL SGA

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.
Answers
-
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
-
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
-
Hi,
as you are using memory_target, I think is better to check in V$MEMORY_DYNAMIC_COMPONENTS.