RE: v$SGA and V$SGASTAT
- From: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
- To: "frasebook4@xxxxxxxxx" <frasebook4@xxxxxxxxx>, ORACLE-L <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 4 Sep 2014 19:31:28 +0000
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
@jloracle
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on behalf
of nico torres [frasebook4@xxxxxxxxx]
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!
Other related posts:
