pok3rm4n
asked on
SGA allocation vs consumption
Hello Experts,
I have some servers where almost 80% of RAM has been allocated for SGA resulting in swapping. As far as I am concerned, we can either get more memory or reduce SGA. First choice is easy. However, I want to see what the actual SGA consumption history. For example, I have a server which got 12 GB RAM and 10GB SGA. How can I check how much of the 10GB SGA is actually being used or the usage history over a month.
I tried looking on net and found a query someone posted but I am not sure it is actually telling me the consumption. Can anyone please tell me how we can get actual SGA consumption history?
<<Truncated output for readability>>
Thanks
I have some servers where almost 80% of RAM has been allocated for SGA resulting in swapping. As far as I am concerned, we can either get more memory or reduce SGA. First choice is easy. However, I want to see what the actual SGA consumption history. For example, I have a server which got 12 GB RAM and 10GB SGA. How can I check how much of the 10GB SGA is actually being used or the usage history over a month.
I tried looking on net and found a query someone posted but I am not sure it is actually telling me the consumption. Can anyone please tell me how we can get actual SGA consumption history?
<<Truncated output for readability>>
select sn.INSTANCE_NUMBER, sga.allo sga, pga.allo pga,(sga.allo+pga.allo) tot,
2 trunc(SN.END_INTERVAL_TIME,'mi') time
3 from
4 (select snap_id,INSTANCE_NUMBER,round(sum(bytes)/1024/1024/1024,3) allo
5 from DBA_HIST_SGASTAT
6 group by snap_id,INSTANCE_NUMBER) sga
7 ,(select snap_id,INSTANCE_NUMBER,round(sum(value)/1024/1024/1024,3) allo
8 from DBA_HIST_PGASTAT where name = 'total PGA allocated'
9 group by snap_id,INSTANCE_NUMBER) pga
, dba_hist_snapshot sn
where sn.snap_id=sga.snap_id
10 11 12 and sn.INSTANCE_NUMBER=sga.INSTANCE_NUMBER
and sn.snap_id=pga.snap_id
13 14 and sn.INSTANCE_NUMBER=pga.INSTANCE_NUMBER
15 order by sn.snap_id desc, sn.INSTANCE_NUMBER
16 ;
INSTANCE_NUMBER SGA PGA TOT TIME
--------------- ---------- ---------- ---------- ---------
1 9.668 .329 9.997 15-JUN-17
1 9.668 .329 9.997 15-JUN-17
1 9.668 .328 9.996 15-JUN-17
1 9.668 .32 9.988 15-JUN-17
1 9.668 .319 9.987 15-JUN-17
1 9.668 .321 9.989 15-JUN-17
1 9.668 .32 9.988 15-JUN-17
1 9.668 .321 9.989 15-JUN-17
1 9.668 .32 9.988 15-JUN-17
1 9.667 .323 9.99 15-JUN-17
1 9.668 .321 9.989 15-JUN-17
1 9.668 .321 9.989 15-JUN-17
1 9.668 .321 9.989 15-JUN-17
1 9.667 .325 9.992 15-JUN-17
1 9.667 .323 9.99 15-JUN-17
1 9.667 .325 9.992 15-JUN-17
1 9.667 .323 9.99 15-JUN-17
1 9.667 .325 9.992 15-JUN-17
1 9.667 .324 9.991 15-JUN-17
1 9.667 .324 9.991 15-JUN-17
1 9.667 .324 9.991 15-JUN-17
1 9.667 .323 9.99 15-JUN-17
1 9.667 .323 9.99 15-JUN-17
1 9.667 .323 9.99 15-JUN-17
1 9.667 .323 9.99 15-JUN-17
1 9.667 .326 9.993 15-JUN-17
1 9.667 .324 9.991 15-JUN-17
1 9.667 .324 9.991 15-JUN-17
1 9.667 .325 9.992 15-JUN-17
1 9.667 .325 9.992 15-JUN-17
1 9.667 .323 9.99 15-JUN-17
1 9.667 .325 9.992 15-JUN-17
1 9.667 .324 9.991 15-JUN-17
1 9.667 .326 9.993 15-JUN-17
1 9.667 .323 9.99 15-JUN-17
1 9.667 .324 9.991 15-JUN-17
1 9.667 .324 9.991 15-JUN-17
1 9.667 .327 9.994 15-JUN-17
1 9.668 .323 9.991 15-JUN-17
1 9.668 .325 9.993 14-JUN-17
1 9.668 .324 9.992 14-JUN-17
1 9.668 .324 9.992 14-JUN-17
1 9.668 .324 9.992 14-JUN-17
1 9.668 .323 9.991 14-JUN-17
1 9.668 .325 9.993 14-JUN-17
1 9.668 .325 9.993 14-JUN-17
1 9.668 .324 9.992 14-JUN-17
1 9.668 .322 9.99 14-JUN-17
1 9.668 .323 9.991 14-JUN-17
1 9.667 .324 9.991 14-JUN-17
1 9.667 .323 9.99 14-JUN-17
1 9.667 .329 9.996 14-JUN-17
1 9.667 .346 10.013 14-JUN-17
1 9.667 .353 10.02 14-JUN-17
1 9.654 .327 9.981 14-JUN-17
1 9.687 .32 10.007 14-JUN-17
1 9.687 .32 10.007 14-JUN-17
1 9.687 .32 10.007 14-JUN-17
1 9.687 .32 10.007 14-JUN-17
1 9.687 .32 10.007 14-JUN-17
1 9.687 .32 10.007 14-JUN-17
1 9.687 .318 10.005 14-JUN-17
1 9.687 .318 10.005 14-JUN-17
1 9.687 .319 10.006 14-JUN-17
1 9.687 .316 10.003 14-JUN-17
1 9.687 .315 10.002 14-JUN-17
1 9.687 .316 10.003 14-JUN-17
1 9.687 .315 10.002 14-JUN-17
1 9.687 .315 10.002 14-JUN-17
1 9.687 .317 10.004 14-JUN-17
1 9.687 .315 10.002 14-JUN-17
1 9.687 .316 10.003 14-JUN-17
1 9.687 .317 10.004 14-JUN-17
1 9.687 .318 10.005 14-JUN-17
1 9.687 .319 10.006 14-JUN-17
1 9.687 .315 10.002 14-JUN-17
1 9.687 .316 10.003 14-JUN-17
1 9.687 .316 10.003 14-JUN-17
1 9.687 .317 10.004 14-JUN-17
1 9.687 .316 10.003 14-JUN-17
1 9.687 .315 10.002 14-JUN-17
1 9.687 .315 10.002 14-JUN-17
1 9.687 .317 10.004 14-JUN-17
1 9.687 .315 10.002 14-JUN-17
1 9.687 .313 10 14-JUN-17
1 9.687 .315 10.002 14-JUN-17
1 9.687 .316 10.003 14-JUN-17
1 9.687 .315 10.002 14-JUN-17
1 9.687 .314 10.001 14-JUN-17
1 9.687 .314 10.001 14-JUN-17
1 9.687 .316 10.003 14-JUN-17
1 9.673 .315 9.988 14-JUN-17
1 9.673 .316 9.989 14-JUN-17
1 9.673 .315 9.988 14-JUN-17
1 9.673 .383 10.056 14-JUN-17
1 9.673 .314 9.987 14-JUN-17
1 9.673 .315 9.988 14-JUN-17
1 9.673 .315 9.988 14-JUN-17
1 9.673 .315 9.988 14-JUN-17
1 9.673 .314 9.987 14-JUN-17
1 9.673 .316 9.989 14-JUN-17
1 9.673 .315 9.988 14-JUN-17
1 9.673 .315 9.988 14-JUN-17
1 9.673 .311 9.984 14-JUN-17
1 9.673 .313 9.986 14-JUN-17
1 9.673 .314 9.987 14-JUN-17
1 9.673 .311 9.984 14-JUN-17
1 9.673 .312 9.985 14-JUN-17
1 9.673 .314 9.987 14-JUN-17
1 9.673 .311 9.984 14-JUN-17
1 9.673 .311 9.984 14-JUN-17Thanks
This question needs an answer!
Become an EE member today
7 DAY FREE TRIALMembers can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
AWR reports:
spool c:\AWR_P646.txt
cd /u01/app/oracle/product/11
@ ?/rdbms/admin/awrrpt.sql