RE: v$SGA and V$SGASTAT
Date: Fri, 5 Sep 2014 08:46:49 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D901E0C14B_at_exmbx05.thus.corp>
Close to true, and probably what will happen (with some small errors)
Technically it's just a little more complicated than that (in principle) since there's also the sga_max_size - which will (probably) default to the value of the memory_max_target, which will have defaulted to the value of the memory_target, and the sga_target is only a target and can be exceeded temporarily. So technically you could see v$sgastat (what's actually in use) going over the 7280M but only temporarily. v$sga would always be larger because of the effect of memory_(max_)target.
As for using the advisors to change the memory sizing - if the machine has the memory the best thing to do with it is almost always to give it to Oracle, but you should read the thread on buffer advisor recently started by LS Cheng. I am very cautious about extracting any meaning from the memory advisors since they generally report data since instance startup.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
_at_jloracle
From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on behalf of nico torres [frasebook4_at_gmail.com] Sent: 04 September 2014 21:21
To: ORACLE-L
Subject: Re: v$SGA and V$SGASTAT
Thanks a lot for your response Jonathan,
So If I would follow SGA advisor recommendations, and I set my SGA_TARGET=7280 MB (of course sizing up my memory_target first) , then I should be expecting v$sga to report way more than 7280MB (and v$sgastat showing only 7280MB) right?
2014-09-04 16:31 GMT-03:00 Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk<mailto:jonathan_at_jlcomp.demon.co.uk>>:
The values from V$sga include the pga memory (and the discrepancy is reported in the "variable size"). Allowing for stop bytes, memory in-flux, (bugs?) etc. your sum(value) matches your memory_target (6400/1024= 6.25, rather than 6.22)
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
_at_jloracle
From: oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org> [oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org>] on behalf of nico torres [frasebook4_at_gmail.com<mailto:frasebook4_at_gmail.com>] Sent: 04 September 2014 20:15
To: ORACLE-L
Subject: v$SGA and V$SGASTAT
Hi guys, I came here for some directions.
Could anyone be so kind as to tell me what's the real size of my SGA?
select sum(value)/1024/1024/1024 from v$sga;
SUM(VALUE)/1024/1024/1024
6.22208786
select sum(bytes)/1024/1024/1024 from v$sgastat;
SUM(BYTES)/1024/1024/1024
4.15960738
Show SGA gives me more or less the same size as v$sga:
>show sga
Total System Global Area 6680915968 bytes
Fixed Size 2213936 bytes
Variable Size 3758098384 bytes
Database Buffers 2885681152 bytes
Redo Buffers 34922496 bytes
But v$sga_target_advice shows me around the same size as v$sga_stat:
select * from v$sga_target_advice;
SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR ESTD_PHYSICAL_READS
---------- --------------- ------------ ------------------- -------------------
2080 .5 10267321 1.2162 2.3383E+10
2600 .625 9248356 1.0955 1.1862E+10
3120 .75 8657406 1.0255 5179419036
3640 .875 8505448 1.0075 3459260045
4160 1 8442132 1 2745226605
4680 1.125 8414275 .9967 2427603887
5200 1.25 8398235 .9948 2250811293
5720 1.375 8386416 .9934 2117942326
6240 1.5 8356024 .9898 1771220206
6760 1.625 8353492 .9895 1771220206
7280 1.75 8350959 .9892 1771220206
7800 1.875 8350959 .9892 1771220206
8320 2 8350959 .9892 1771220206
Speaking of which, v$sga_target_advice was the reason I got into this question.
I discovered I could get considerable less Physical reads from sizing up sga up to 6240MB, but then I got very confused when v$SGA told me SGA is more than 6GB.
The only memory parameter I've set is memory_target of 6400M.
Thanks!
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Sep 05 2014 - 10:46:49 CEST