Oracle SGA size

hussainkhan22
hussainkhan22 used Ask the Experts™
on
When I am creating a new database how much must be the SGA size for good performance
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
There is no specific guideline. It's based on many factors.

First and foremost: how is the database going to be used?
Size of the database?

If there is only one database on the machine and the machine is strictly for that database: Allocate all the memory you can and you cannot go wrong.
Devinder Singh VirdiLead Oracle DBA Team

Commented:
SGA size deponds on DB size, Hardware with CPU, transaction used, growth, Current Performance Issues etc.

Database of size 4TB with 2G SGA may giving good performance,  on other hand side, DB size of 4TB with 6GB SGA size may hit performance issues. Deponding on what is going in DB and how.

Author

Commented:
First question suppose there are 5 databases around 100GB each on my server. So how much SGA I must specify for each database in general.

Second question if I have only one database on my server as you mention allocate all the memory you can. So how can I find how must memory is there on my server. After finding shall I allocate all the memory to that particular database. Does OS or other application doesn't need any memory just clearing my doutbs

I really appreciate your help
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
>>So how much SGA I must specify for each database in general.

We cannot answer that. Only you know your databases and how they are used. A smaller database might need more memory than a larger database for optimum performance.

There is no magic formula. You need to continually monitor your database and adjust parameters accordingly.

The wizards in Grid Control are getting pretty good with these estimates.

>>So how can I find how must memory is there on my server.

Windows, right click on MyComputer and sleect properties.

UNIX: The easiest way is run: top

>>After finding shall I allocate all the memory to that particular database.

That depends on the OS. A good staring point is start with 70-75% available memory and tweak from there.
Devinder Singh VirdiLead Oracle DBA Team

Commented:
Other possibilities:-

>> there are 5 databases around 100GB
Start with 1GB for each database provided you have atleast 6GB or RAM in order to avaoid usage of swap space which is very bad. Run load test for at least 2 to 3 hrs, then generate AWR/ADDM reports and look for its advice. It may advice you to increase SGA by 10% only. It will show you how much benefit will you get by increasing SGA etc.

You dont need to allocate maximum memory to Oracle without any need. Just increase as per the requirement.
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
>>You dont need to allocate maximum memory to Oracle without any need.

Agreed but for a single server running a single database and nothing else, if the OS isn't using the RAM you might as well give it ALL to Oracle.

Author

Commented:
What is the parameter to set SGA. Can we increase SGA after creation of database, if yes than how can we increase SGA.
Devinder Singh VirdiLead Oracle DBA Team

Commented:
Use sga_max_size and sga_target
Devinder Singh VirdiLead Oracle DBA Team

Commented:
Also read the following link.
http://www.dba-oracle.com/t_sga_sizing.htm
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
>>Can we increase SGA after creation of database

Yes.  It is controlled by init parmaters.  The two for SGA virdi already provided.

I also assume you are talking total memory for Oracle not JUST the SGA.  There is also the PGA and it is controlled my pga_aggregate_target.

In 11g those parameters are obsolete and you should let Oracle manage both the SGA and PGA and use: MAX_MEMORY and MAX_MEMORY_TARGET

Commented:
It depends on the implementation of unix... generally, the available memory is the amount of virtual memory for userspace, which is physical + swap I think unix have a min&max setting for the filesystem cache, so there is a minimum amount of cache which will be in memory.

Generally, the allocated memory will be put on swap if more is allocated than physical, based upon a touch count of the memory page this means that if you have a 8G server, and 8G of swap, it means the servers 'sees' 16G of memory thus you can allocate an Oracle instance of 10G. of which 2G must be on swap of course, because of the physical memory limitation.

Please mind there are all sorts of mechanisms to forbid oracle memory to go to swap, SGA like large pages, in almost any implementation they are not pagable PGA is another case. essentially that is memory allocated by the foreground process, and private. which makes it an excellent candidate for paging/swapping

Author

Commented:
Can you please explain again how to increase size of SGA after creating the database. I really appreciate your help.
Commented:
alter system set sga_target=253M scope=both;
alter system set sga_max_size=250mM scope=spfile;
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
Just to add:  Some parameters cannot be adjusted 'live' so the "scope=spfile" requires you to restart the instance for them to take effect.

Author

Commented:
So we can change SGA maximum value after creating the database. I thought once the database is created we cann't change SGA maximum size. Anyway what is the difference between scope=both and scope=spfile
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
>>I thought once the database is created we cann't change SGA maximum size.

Incorrect.  We've been saying that all along.

>>Anyway what is the difference between scope=both and scope=spfile

Check the docs:
http://download.oracle.com/docs/cd/E11882_01/server.112/e17120/create006.htm#ADMIN11120
Devinder Singh VirdiLead Oracle DBA Team

Commented:
>> Anyway what is the difference between scope=both and scope=spfile
In simple word, where you want to save the changes you are doing at system level.
There are three possibilities. Save in File, memory or both.
Traditional file was Init.ora, but anyone can read/change this file, so oracle introduce new file called spfile, where oracle can easily read/write in its own format.
So when you specify both, changes not only apply in spfile, but also in memory (SGA). This eliminate the afford to open traditional file and update the parameters manually.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial