v$SGA and V$SGASTAT

From: nico torres <frasebook4_at_gmail.com>
Date: Thu, 4 Sep 2014 16:15:55 -0300
Message-ID: <CAJWEwFx3WsJe7G3XA3FBhz19gt81cGfvx9Zd+bSsMib5_uuwng_at_mail.gmail.com>

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-l

Received on Thu Sep 04 2014 - 21:15:55 CEST