SGA size determination

From: Olen <klineo_at_wlrfoods.com>
Date: 1997/06/12
Message-ID: <33A01E82.522D@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 a HP 9000 os is HPUX 10.01 Oracle server version is 7.1.6.2.

  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, guidance 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 Thu Jun 12 1997 - 00:00:00 CDT