| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: V9.2 & SGA
Also,
you probably thought of this, but have you looked at v$sga (which seems to be the info you get on startup) and v$sgastat - which has details of each of the sections.
On 81714 on NT,
v$sga shows:
Fixed Size Variable Size Database Buffers Redo Buffers And select pool,sum(bytes) from v$sgastat group by pool; gives 4 rows: blank ( which is sum of fixed_sga, db_block_buffers, log_buffer java pool large pool shared pool
The sum of java, large & shared is nearly equal to the variable size in v$sga - see below for some links on this difference:
>From http://www.ixora.com.au/q+a/memory.htm
"Variable SGA size 6 June 1999
Why is the variable size shown when I type SHOW SGA different than the shared_pool_size I set in the init.ora?
Other than the shared pool, the variable area also contains the memory arrays behind V$PROCESS, V$SESSION, V$TRANSACTION, V$LOCK and so on, plus a good number of other things. Some are fixed in size, but many are sensitive to the setting of various init.ora parameters. On some platforms each structure starts on a memory protection boundary, so some extra memory is needed for padding. Nevertheless, this does not affect the size of the shared pool, because Oracle calculates what it needs here on instance startup, and then adds the value of the shared_pool_size parameter before allocating the variable area of the SGA. "
>From askTom
http://asktom.oracle.com/pls/ask/f?p=4950:8:965938::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:71012348056,%7Bvariable%7D%20and%20%7Bsize%7D
"The variable portion of the SGA holds all of the control
structures for the SGA itself. The bigger the SGA, the bigger
the variable portion.
APPROXIMATING SGA SIZE AND SHOWING EXISTING SGA
To approximate the size of the SGA (Shared Global Area), use the
following formula:
( (db_block_buffers * block size) + shared_pool_size +
log_buffers) /.9..."
For a detailed discussion see http://asktom.oracle.com/pls/ask/f?p=4950:8:965938::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:365088445659,%7Bvariable%7D%20and%20%7Bsize%7D eg this says every control_file will consume 256 bytes of variable size memory.
And http://asktom.oracle.com/pls/ask/f?p=4950:8:965938::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:382418730963,%7Bvariable%7D%20and%20%7Bsize%7D goes on to mention "The variable sized component of the SGA, as its name implies, contains data structures that are variably sizes (eg: things like "sessions", "processes", "db_files" will affect this marginally -- things like "shared_pool_size" will large effects on this). The variable size of the SGA is the sum of the sizes of all of these "variably" sized structures (but not block buffers, they are reported separately)."
Hope all this helps.
Regards,
Bruce Reardon
-----Original Message-----
Sent: Tuesday, 25 June 2002 11:43
Try shared_pool_size, large_pool_size, java_pool_size and shared_pool_reserved size.
This is from 8i, there may be additional ones on 9i, or 1 or 2 of those I mentioned may be deprecated.
Jared
On Monday 24 June 2002 15:05, Charlie Mengler wrote:
> Yes, I know I need to RTFM, but if some kine soul has a quick
> answer for me, I'd appreciate it.
>
> > startup
>
> ORACLE instance started.
>
> Total System Global Area 168788768 bytes
> Fixed Size 729888 bytes
> Variable Size 100663296 bytes
> Database Buffers 33554432 bytes
> Redo Buffers 33841152 bytes
> Database mounted.
> Database opened.
>
> > exit
>
> Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit
> Production With the Partitioning, OLAP and Oracle Data Mining options
> JServer Release 9.2.0.1.0 - Production
> oracle_at_actaeon:CAN#
>
>
> I just got done upgrading two V7.3.4.5 instances to V9.2 on a sandbox
> which has only 256MB RAM. Both SGAs are currently sized the same way.
> The OS is paging/swapping like carzy because SGA1+SGA2>256MB. :-(
>
> Which initSGA.ora parameters control the "Variable Size" piece of
> the 9i SGA? I'd like to shrink this total to around 32MB.
>
> TIA & HAND!
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Reardon, Bruce (CALBBAY) INET: Bruce.Reardon_at_comalco.riotinto.com.au Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Mon Jun 24 2002 - 21:53:22 CDT
 |
 |