Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> SGA size computation question

SGA size computation question

From: Olen <klineo_at_wlrfoods.com>
Date: 1997/07/07
Message-ID: <33C1AC57.6EFC@wlrfoods.com>#1/1

I have a bit of a problem, I've been trying to find out what contributes to the Variable Size of the SGA.

  Let me go into some detail. My sysadmin gave me the word that I could have up to 468000000 bytes (~446 Meg) for my database. We're running on an HP 9000 os is HPUX 10.01 Oracle server version is 7.1.6.2. One the one instance on this server.

  I then set out to use all I could - typical DBA attitude - in the best manner possible.

  At that time a 'select * from v$sga' returned these values:

Fixed Size . . . . . . . . . . . . 46384
Variable Size. . . . . . . . . .88392624
Database Buffers . . . . . . . 139747328
Redo Buffers . . . . . . . . . . 1048576

For a total SGA size of. . . . 229234912

the init.ora contained the following values: (I am including the ones Oracle identifies as controlling the size of the SGA)   (Db_block_buffers * db_block_size) + sort_area_size + shared_pool_size
+ 1MB *refer pg 3-2 Oracle 7 Server for HP 9000 Installation and
Configuration Guide Release 7.1.6 *

db_block_size. . . . . . . . . . . .4096
db_block_buffers . . . . . . . . . 34118
sort_area_size . . . . . . . . . 2097152
shared_pool_size . . . . . . . .76521472
log_buffer . . . . . . . . . . . 1048576

+ 1 MEG. . . . . . . . . . . . . 1048576
Should equal a SGA of. . . . . 220463104

That's a difference of . . . . . 8771808

Now friends 8566 K [8771808/(2^10)] might not mean much to you and me but the hardware guys get upset with that kind of swag.

Well I didn't know there was that kind of difference between the number when I started my memory grab ... er ... much needed increase in the size of the SGA.

  I set up a little spreadsheet to help me with the math.   I ended up with planned settings as follows:

db_block_buffers . . . . . . . . . 94118
sort_area_size . . . . . . . . . 2097152
shared_pool_size . . . . . . . .76521472
log_buffer . . . . . . . . . . . 2097152

+ 1 MEG. . . . . . . . . . . . . 1048576
Should equal a SGA of. . . . . 467271680

Which is under my quota of 468000000 by 728320 bytes (711 K) right? - sharp pencil figuring, to sharp.

Wrong - the database wouldn't even come up, got that "ORA-07310 SMSCRE: unable to create SGA Additional Information 2". So I started reducing the DB_block_buffers. I finally settled on 88200 and ended up with the following settings:

db_block_buffers . . . . . . . . . 88200
sort_area_size . . . . . . . . . 2097152
shared_pool_size . . . . . . . .76521472
log_buffer . . . . . . . . . . . 2097152

+ 1 MEG. . . . . . . . . . . . . 1048576
Should equal a SGA of. . . . . 443031552

However a query of the v$sga returns the following:

Fixed Size . . . . . . . . . . . . 46384
Variable Size. . . . . . . . . 103941688
Database Buffers . . . . . . . 361267200
Redo Buffers . . . . . . . . . . 2097152

For a total SGA size of. . . . 467352424

  For a difference of . . . . .24320872  that's 23750 K folks

 I did a little looking at the V$SGASTAT table and came up with some interesting information.

For starters I have 2 log_buffers, I don't know how I got 2, when I increased the size of the init.ora parameter log_buffer from 1048576 to 2097152 the size of the first went according to plan and doubled, so did the second one from 8192 to 16384.

Among other things that increased were the db_block_hash_buckets, db_block_multiple_hashcha. I have found out that each increase by 1 of the enqueue_resources costs 72 bytes.

I understand that some of the items (most?) In V$SGASTAT are dynamic based on utilization, what I would like to know is which ones are dependent on fixed values - for example the cost in bytes per additional enqueue_resource and the db_block_hash_buckets, and which ones are based on a ratio of a known parameter.

  Thank you for any assistance, guidence you can give. If you need more information let me know, I would like to come out of this with an understanding of what the values in V$SGASTAT are based on and what happened to the memory I thought I had. Received on Mon Jul 07 1997 - 00:00:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US