| SGA "usage" [message #457868] |
Wed, 26 May 2010 04:21  |
firefly
Messages: 53
Registered: March 2009
Location: Europe
|
Member |
|
|
|
If I have my SGA MAX & TARGET to e.g 10G set how can I tell the amount of SGA actually being used? My understanding is that the 10G will be reserved by the O/S, but how do I know how much of it is being used?
|
|
|
|
|
|
| Re: SGA "usage" [message #457885 is a reply to message #457881] |
Wed, 26 May 2010 04:52  |
firefly
Messages: 53
Registered: March 2009
Location: Europe
|
Member |
|
|
Thanks for that..I can scehdule a job to run this every few mins during or our nightly load but is there any way to view this info for last night? I have Grid Control with the Perf mgt pack..?
|
|
|
|
|
|
| Re: SGA "usage" [message #457901 is a reply to message #457887] |
Wed, 26 May 2010 05:46 |
firefly
Messages: 53
Registered: March 2009
Location: Europe
|
Member |
|
|
Thanks for that but am a little confused:
My SGA MAX & TARGET is actually set to 24G
When I run SELECT sum(value/1024/1024/1024) FROM DBA_HIST_SGA where snap_id=17583 (for my snapshot) I also get 24G. Does this mean that all of my SGA was being utilised?
When I run select sum(bytes/1024/1024/1024) from DBA_HIST_SGASTAT where snap_id=17583 (for the same snapshot) I only get 16GB. Can you hepl explain this?
Finally, when I run select sum(bytes/1024/1024/1024) from V$SGASTAT right now I get 31G even though as mentioned my sga max & target is only 24G.
Can you pls shed some light?
Thanks
|
|
|
|
|
|
| Re: SGA "usage" [message #457913 is a reply to message #457912] |
Wed, 26 May 2010 06:52 |
firefly
Messages: 53
Registered: March 2009
Location: Europe
|
Member |
|
|
Hi Michel,
SELECT name, value/1024/1024/1024 FROM DBA_HIST_SGA where snap_id=17583
which ones are "free memory" stats?
NAME VALUE/1024/1024/1024
---------------------------------------------------------------- --------------------
Database Buffers 14.765625
Fixed Size .001958385
Redo Buffers .013664246
Variable Size 9.21875237
|
|
|
|
|
|
| Re: SGA "usage" [message #460791 is a reply to message #457915] |
Mon, 14 June 2010 17:17 |
mikerault
Messages: 21
Registered: June 2010
Location: Alpharetta, Ga
|
Junior Member |
|
|
The shared pool, large pool and streams pool will all have free memory components. As an aside, you should not have sga_target and sga_max_size set equal to each other. Oracle will allocate all memory configured in sga_target and then Oracle ends up robbing other components when memory is needed. It is considered a good practice to set floor values for your major components (see the list above) and then allow the sga_target to calculate and set the sga_max_size to some percentage above that, I usually use 25%. This also applies in Oracle11g with the memory_target and memory_max_size settings with the exception that you must also include the pga_aggregate_target setting. If you look at the v$sga_resize_ops view and see all actions are DEFERRED then this is probably an indication you are thrashing memory.
Mike
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
