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.

[Volker Borowski]

Well,
first question would be : Do you have dynamic SGA configured ?
If yes you are more flexible, because you can resize your buffers while the db stays up and online.

set
sga_max_size=8000000000 (8GB)
db_cache_size=3000000000 (3GB)
pga_aggregate_target=1024M (1GB)
shared_pool_size=500M

and distrubute the rest sparefully (java_pool_size if needed, other pools if configured and in use [i.E. if used diffrent db_blocksizes in one db or keep_pool])

This will give you the option to increase all buffers if needed according to the statspack-advice as already sugessted an utilize up to 8GB only for memory purposes.

I'd recommend to keep some spare RAM for OS purposes and binaries being executed.

You can only get more detailed while monitoring the system.

Volker

[TwoProc]

Well, I like my hit ratio closer to 98-99% than 95%. But, how much ram is a bit harder to figure.

The main question that you need answered is "how much can I increase it without going into swap". Then, you can determine how to correctly use what is available in this yet-to-be-determined "safe zone."

Right now, how much free ram do you have used at the peak period. About 80% of that free area is what I'd say you start off thinking about as your available memory to use for trying to increase your performance. Of course, don't just start off by increasing a component of the SGA by that much. Start smaller. This "free area" represents your 10G less OS overhead, possible file system buffereing, the SGA, and all of the little PGA's of every connected user (at peak) and all of the other programs doing whatever they do which takes memory. So, figure out what 80% is, and write that down and, for starters don't add into any pools, buffers, etc, any more than this amount in total.

Secondly, we need to figure what to increase. You said that you've got 95% hit ratio on the cache. Someone said that you could probably double it. I've seen everything from a dramatic increase in performance with a change like this, to nothing noticeable. I think you'll notice a measurable difference in performance (providing that you're measuring with some good tools). A "noticeable" difference may not show, except possibly at peak periods.

Re: your bosses' point: Recognize that the 5% "miss" that you currently have is equal to almost ALL of your Oracle I/O (not including redo-logs and archive logs), and that if you can increase your hit ratio from 95% to 96% you have reduced total I/O by 20% (1/5)!!!!

But, don't just increase the buffer_cache, you probably need to see if you need to increase the size of the shared pool. Have you assessed your code to see if it is getting reloaded back into the shared pool often? If so, you really need to increase space in the shared pool ( as well as the large_pool if using it, ditto the java_pool).
If this isn't tuned right, you'll pick up performance even when your hit ratio on your buffer_cache (data area) is high. So, pay attention to this also.

Also, pay attention to sort_area_size. Are lots of sorts happening on disk? If so, you'll need to tweak this and keep in mind that if you increase this memory, you increase memory for EVERY CONCURRENT (active or not) CONNECTION. So, be VERY CAREFUL with that one.

My thinking is, that if you've got 4 or 5 Gig free in memory that you can use, you'll get some better performance out of your database, if you can use it where it is needed most.

[ericfjchen]

Thanks for all of your comments. They are very useful for me. Our environment is Oracle 9.0.1.4 on Tru64 5.1B. Do you think we should change SGA to dynamic mode? Is it better for me?

[Volker Borowski]

Hmm,

9.0.1 is quite low patchlevel.
There have been some issues in early releases with dynamic sga and automatic pga.
Check if you can patch up-to-date (9.2.0.7) from application-software point of view and switch to dynamic then !

Volker

[ericfjchen]

Due to the application we used, we can't upgrade Oracle to 9.2.0.x. Could you tell me
Which issues are in 9.0.1.4 with dynamic sga and automatic pga? Does any Oracle document mention that?

Thanks so much

Eric

[Frank de Vries]

There are two ways about this, one being very scientific about it, and second using a rough rule of thumb.
From my experience the two in reality show similar results, and the rule of thumb is easier to use like this:

Take 10% for the OS
The remaining part divide by 2.
(So basically 45%) and that I use as a MAX boundary for SGA. (You don't have to use it all at once, it is better to start lower so you can increase later on if needed)
Also if you have 2 or 3 instances, you will need to divide this RAM among the number of instances, as long as MAX is respected.

The other 45% is for PGA and other processes.
Check with ipcs -m after to see your memory
structure is as intended :)

This has always done it for me.

[Hein van den Heuvel]

>>> Due to the application we used, we can't upgrade Oracle to 9.2.0.x.

Just say NO to that. Not acceptable.
Oracle 9.0 .x is pretty much a Beta version
No reasonable product requires it.
I appreciate 'certification requirements' and such, and I would understand requiring 8.1.7, but not 9.0.x.
Someone nneds to make a stand and break through that.

>> Could you tell me
Which issues are in 9.0.1.4 with dynamic sga and automatic pga? Does any Oracle document mention that?

It might not have been in 9.0, but you have the memory.. so for the next week just double the SGA and monitor statspack before and after, notably the "Buffer Pool Advisory", besides the usual hit rate, physical read IO rate and so on.


Hein.

[Alessandro Bocchino]

As regards the buffer cache size, you can enable db_cache_advice instance parameter, then look to v$db_cache_advice
to have an estimate of the changes in logical vs phisical reads changing the block buffers from 50% to 200% of the current value.

Anyway, a lot of Oracle instance parameters are important to have correct performance from the database, the optimal mix can be defined by a detail analisis of the database load and behaviour.

Alessandro Bocchino
http://www.risolve.com

[Frank de Vries]

Hi,
I answered before, but I found a nice
query in the meanitme that may be of help in analysing your SGA:

select pool, name, round(bytes/1024 ,1) "Size kb" from v$sgastat
order by 3 desc

This will show on what sections your SGA is
spend on.

I think this will be of help.