SGA issue --help rqd

[portal]

Folks
we have Oracle 8.1.5 on Solaris 2.7 on e450 box.
we are running two instances running on this box
and total database size is about 1gb..
we are having around 350 active connections for each databasememory on our server is 2GB.And now as you guys can seeour total SGA on each database is aroung 130MB only
to increase this i did modified shmmax parameters but still
my SGA as a whole is still 130MB..could u guys pls give me someidea on how to tune this properly for more SGA...and alsowe dont have any Java applications running and is there any way we can eliminate Java shared pool of 20Mb--or is it mandatory..
could u guys pls thro some ideas on this pls and help me in tuning
this db..

set shmsys:shminfo_shmmax=8053063600
set shmsys:shminfo_shmmin=1
set shmsys:shminfo_shmmni=100
set shmsys:shminfo_shmseg=20
set semsys:seminfo_semmns=1220
set semsys:seminfo_semmni=100
set semsys:seminfo_semmsl=820
set semsys:seminfo_semopm=100
set semsys:seminfo_semvmx=32767

NAME VALUE
-------------------- ----------
Fixed Size 64912
Variable Size 91381760
Database Buffers 41943040
Redo Buffers 180224

db_file_multiblock_read_count = 32
db_block_buffers = 10240
shared_pool_size = 83886080
log_buffer = 163840
db_block_size = 4096

thanks
sat

[marist89]

You're half way there now. You also need to tell oracle how to go out and grab more memory. You will want to look at your init.ora parameters for each instance. The two likely candidates for increase would be "db_block_buffers" (# blocks of db_block_size) and "shared_pool_size" (straight calculation).

[tamilselvan]

Increase the DB_BLOCK_BUFFER and bounce the database.

[portal]

thanks folks
i did increased the database buffers in initora file
still doent seen any change in total sga as only dbbuffers were increased
thx
sat

[sambavan]

The factors that contribute to the size of SGA are

SGA = log_buffers + shared_pool_size + large_pool_size + java_pool_size + (db_block_buffers * db_block_size)

Once you make any changes to these parameters, you have to restart the instance to get them to take charge of the new parameters.

also check the v$sgastat for the distribution of resources.

Sam

[st2000]

so sambavan, can you tell how is SGA done in my db..


SVRMGR> show sga
Total System Global Area 559910896 bytes
Fixed Size 69616 bytes
Variable Size 498221056 bytes
Database Buffers 61440000 bytes
Redo Buffers 180224 bytes
SVRMGR>


I know Database buffers only..(db block buffers * blocksize)

How about defining the rest..

This question I have asked several times and no one responds. I wondered why..
Thx.

[tamilselvan]

Total System Global Area 559910896 bytes
Fixed Size 69616 bytes - OS Dependant , You can't change
Variable Size 498221056 bytes - Shared Pool Size
Database Buffers 61440000 bytes - Buffer Cache
Redo Buffers 180224 bytes - Log Buffer.

I hope you are clear now.

[sambavan]

I hope it would be clear now, after tamilselvan had explicitly defined things. Answering to one of your other questions of java_pool_size, the 20M seems to be the default one and you do not want to further reduce that size, b'cos it might cause the problem at the time of starting the instance. If you would like, you can go ahead and increase it, but not reduce it if you have any of the oracle intermedia and other stuff that were installed by default.

to see whether the java components have been installed in the database, try doing

describe dbms_java


Good luck,
Sam

[portal]

Folks,tamil,sam,maris
Thanks ..to resolve this issue of sga in my db..and most of
my doubts were cleared now..
could u guys pls thro some light on FREELISTS/Freegroups
as we have a table with inserts of 400,000 at a time
and i observed contentin for dbblock buffers..and came to know we can do it by freelists..as we dont have any on this table..if i have to do this how much shall we give for that table on 8.1.5..and no.of connections on db is 150..
thanks
sat