OraFAQ Forum: Server Administration

Home » RDBMS Server » Server Administration » How to change SGA

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to change SGA [message #64059]

Thu, 09 December 2004 22:17

Vamsi Mohan
Messages: 16
Registered: December 2003

Junior Member

Can some one suggest me how to change the SGA for oracle 8i database running on Win NT 4.0

What is the optimal size of the SGA ( in comparision to RAM ) for an OLTP database.

Thanks in Advance
N.Vamsi Mohan

Report message to a moderator

Re: How to change SGA [message #64060 is a reply to message #64059]

Fri, 10 December 2004 05:11

croK
Messages: 170
Registered: April 2002

Senior Member

I would suggest to set SGA half the size of physical RAM.
You have to set several parameters in your init.ora file:

shared_pool_size=xxx where XXX equals to half the
size of the physical RAM
db_block_buffers and log_buffers must set according the activities of your database. You can start by setting
log_buffers=32768
and db_block_buffers=550
then you must do some tuning to see if those values are good enough or not.

Hope this help.

Report message to a moderator

Re: How to change SGA [message #64061 is a reply to message #64059]

Fri, 10 December 2004 05:11

Alan
Messages: 68
Registered: October 1999

Member

hello
if you type in show sga at a sql prompt it tells you your sga size

eg

SQL> show sga

Total System Global Area 2055425940 bytes
Fixed Size 73620 bytes
Variable Size 446435328 bytes
Database Buffers 1556480000 bytes
Redo Buffers 52436992 bytes

these are init.ora parameters which can only be changed by bouncing the database.

cheers

Report message to a moderator

Re: How to change SGA [message #64069 is a reply to message #64060]

Sun, 12 December 2004 19:12

Daljit Singh
Messages: 290
Registered: October 2003
Location: Texas

Senior Member

>>log_buffers=32768

Hi,

Do you really think that log_buffers=32768 is adequate size for log buffers. As i know the default size is 512K or 128* no of CPUs whichever is greater, so what is the funda behind the number - 32768, Please explore.

Daljit Singh.

Report message to a moderator

Re: How to change SGA [message #64078 is a reply to message #64069]

Mon, 13 December 2004 04:55

croK
Messages: 170
Registered: April 2002

Senior Member

This is taken from my init.ora file (sample file created after oracle8i installation):

log_buffer = 8192 # INITIAL
# log_buffer=32768 # SMALL
# log_buffer = 32768 # MEDIUM
# log_buffer = 163840 # LARGE

Report message to a moderator

Previous Topic:	DBWR In Action
Next Topic:	stored procedure problem

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Jan 24 23:23:06 CST 2025