What percentage of RAM should Oracle SGA be set?

[ericfjchen]

If the box's RAM is 10G, what size should Oracle SGA be set for better performance?

[Bill Hassell]

The SGA must be sized by your DBA based on the access methods, row sizes, read/write ratios, temp area sizes, etc. Your DBA then tells you how much RAM is needed. If the SGA size is large than 3/4 of your RAM, you must buy more memory or all the advantages of the SGA aere lost to swapping.

If more RAM is not an option, then the DBA needs to reduce the SGA size until everything fits into RAM without measureable swapping. Note that small RAM sizes (4Gb or less) will always have performance issues when the system is busy.

[ericfjchen]

Thanks in advance. We have no performance issue now. However, someone told me that the better performance for Oracle SGA should be set 20% RAM. But I can't see any Oracle document mentions that. Do you know whether Oracle has any advice about this?

Thanks

Eric

[Indira Aramandla]

Hi Ericfjchen,

Proactive tuning during the database design and development stage is the most effective way to achieve optimal database performance

The appropriate size of SGA is system dependent, and it is limited by the available system resources. For optimal performance, SGA should fit into real memory avoiding the need for swapping.

When sizing the SGA consider tuning all memory structures (the shared pool, the buffer cache and the redolog buffer cache) together so you can assign appropriate resources to each according with the database requirements. The Shared_Pool_Size is the memory that is allocated to Oracle for areas like the data dictionary, stored procedures, and statements. Comprising a large part of the SGA, the shared_Pool_Size is comprised of the dictionary cache and library cache, and just like DB_Block_Buffers above, should not be set too low or too high.

The Shared_Pool_Size can be monitored through the data dictionary cache and the library cache. Both should be continuously monitored for an appropriate hit ratio.

When a user processes a SQL statement, Oracle references the data dictionary several times. Reducing physical disk IO is very important, so the more information that is stored in memory, the less that needs to be read from disk. The data dictionary cache is very important in this respect because this is where the data dictionary components are buffered (think of it as a print buffer). The data dictionary component can be monitored via v$rowcache.

You should see output similar to the following:
Hit Ratio
95.40%

You should aim to have this value above 90%. The exception is when the database is first started, the value will be somewhere around 85%.

In Oracle Database 10g, Automatic Shared Memory Management (ASMM) feature is introduced to automatically determine the size of Database buffer cache (default pool), Shared pool, Large pool and Java pool by setting the parameter SGA_TARGET.

This feature reduces the tasks like dynamically analyzing the database workload and redistribute memory across the SGA pools.

Benefits of Automatic Shared Memory Management are :-Automatic Shared Memory Management simplifies the configuration of the SGA. Before Oracle Database 10G, buffer cache, shared pool, java pool, and large pool need to be manually specified for the database. Under sizing can lead to poor performance and out-of-memory errors (ORA-4031), while over sizing can waste memory.

This feature enables you to specify a total memory amount to be used for all SGA Components (buffer cache, shared pool, java pool, and large pool). Oracle database periodically redistributes memory between these components according to workload requirements. The new SGA size parameter SGA_TARGET now includes all the memory in the SGA, including all the automatically sized components, manually sized components, and any internal allocations during startup.


Indira A

[Hein van den Heuvel]

Eric,

That question is much like 'what percentage of my income should I spend on my mortgage'
The answer to that question is often 'up to 30%'.

Next they often ask.. what is your income, how big a house, what are your other fixed expenses.

For some folks 50% is reasonable, knowing there is little other load and that will buy them a house twhich will comfortably fit the family. For others, paying for more than 1 house, or other known expenses 10% may be all they can affort, and for others still, with generous incomes and modest housing needs 5% is all they need.

Same for you SGA. What else does this system have to support? Known other tasks? Other databases? How much resources can this particular database use? How Much IO can your system sustain?
Fortunately, Oracles STATS_PACK can give a nice indication of the effect of increased / decreased SGA in terms of IOrate change. Check that out.

Contrary to Indira's reply I tend to ignore Cache hit rate, but look at the remaining IO rate. If you are above say 20 IO/sec per disk, you can probably benefit from from a larger SGA. If you are below 5 IO/sec per disk, and below 100 IO/sec on any specific disk, then you will proabbly not notice any improvement when growing the SGA and you can potentially shrink it... if something else can use the memory better.

Good luck!
Hein.

[Hein van den Heuvel]

Oh, one more thought...
10G might no longer be what it used to be, but it still is a significant amount of memory and represents a serious investment. Most folks do not just 'happen' to have that.

Why does the box have 10GB memory?
Didn't someone estimate the needs and configure accordingly?
Be sure to check back with the initial sizing requirements for the box and you are not unlikely to find an initial oracle sizing 'stake in the ground'. Once the system is in production, you can use statspack to see whether the initial estimate needs to be adjusted based on actual usage and data patterns.

Hein.

[ericfjchen]

Now our sever has RAM 10G. Oracle SGA is about 1.5G. There is only one Oracle database on this box. In other words, there is no other application on it. The server doesn't use any swap space currently. The hit ratio keeps about 95%. However, my boss doubt if we enlarge SGA to 2G, the performance should be better. How do I judge the suitable SGA size if we don't use any swap?

[Arturo Galbiati]

Hi,
based on my experience and if you are agin in the test phase (this means that you can gete rrors without impacting production) my suggetsion is to start with 2 GB of SGA, monitor it and in case enlarge it.
A good way to monitor it is using the attached sql script.

HTH,
Art

[Steven E. Protter]

Shalom,

It totally depends on what the database is going to do.

You can not look at system ram and just take a certain percentage.

1) Is it 32 bit or 64 bit Oracle
2) What version or Oracle.
3) What kind of database use: heavy write, heavy read, mixed.
4) If the system is running on another server what are the current sga settings
5) What else does the compuer do?
6) What OS is the computer?
7) Is the current OS 32 bit or 64 bit?
8) What kind of server (ex rx7620,rp7420)

SEP

[Hein van den Heuvel · Accepted solution]

I think Eric might be on Tru64, which is, and has been, 64 bit all the way.

Eric,

A 1.5Gb SGA for a box with 10GB where that instance is the sole purpose of the box is relatively small.

It call for an experiment with doubling the size. Why not?

95% hit rate is relatively poor.
While I discourage using hitrate as teh main measurement of Oracle's effectiveness, this 05% is worrysome.

If you can not simply change it now, then as i suggested before Use Statspack data.
Specifically look for:

"Buffer Pool Advisory"
:
- Size for Estimate (M)
- Size Factr
- Buffers for Estimate
- Est Physical Read Factor
- Estimated Physical Reads

This table indicates the effect Oracle thinks a change in the number of buffers will have.

There is a similar table for:
"Shared Pool Advisory"
Given plenty of memory, you may also want to bumb that, unless that table suggest that it is already plenty big enough.

Be sure to have a good few statstpack snapshort over representative work hours available before and after tweaking Oracle.

Me thinks you may come out of this as a hero.

Good luck,
Hein.