Re: Help:Memory set for Solaris Box

"Joe Sath" <dbadba62_at_hotmail.com> wrote in message news:cU5T8.2337$vr5.1792_at_nwrddc01.gnilink.net...
> I am posting my question here because I know many good DBAs are also good
> SAs.
>
> We are currently using Oracle 817 on Solaris 2.7
>
> We have 2G memory.
> I set db buffer 800MB (which 300MB for keep, 100MB for recycle)
> SGA we set 200MB
> sort_area_size we set at 1M
>
> From top command, I can see there is always 1000MB swap memory in use.

Well, that's not good, is it? It means that operating system is doing physical I/O when Oracle thinks it has found a hit in the cache, and can do logical I/O.

Your problem is that the ENTIRE size of the SGA should never represent more than somewhere between 1/3rd and one half of the available physical RAM on your box. You have 2Gb RAM; I have no idea what your SGA is, because "db buffer" isn't an Oracle parameter, and neither is "SGA". I shall presume that "db buffer" means "database buffer cache". I haven't a clue what "SGA" means until you get to 9i, which you aren't using, but I'll guess it means 'shared_pool_size'. So I guess that just those two components of your SGA are shewing up half your available physical RAM. Add on Java Pool, PGA, Large Pool and so on, and it's clear that you are well over the physical constraints of your box.

Since I don't understand what parameters you are talking about, put it this way: the log_buffer+shared_pool_size+(db_block_buffers x db_block_size)+java_pool_size+large_pool_size shoudn't equal more than about 1Gb, at worst, given your 2Gb of RAM. Consult your init.ora to see whether this is true or not for you.

> From vmstat, I can see there is high page-in, page-out, (about 100, 300)
and
> sr (around 10) is also very high, does this mean I have put too much
memory
> for Oracle? What is the good size I should assign for Oracle?
>
> I do not see any performance issue yet, all the hit ratio, both db buffer
> hit and libray are close to 100%.

Please, please, please, please. Do NOT tune by ratio. It's a disgusting way to tune, and it means precisely nothing. Can I point you to www.oracledba.co.uk, the TUNING link, where you will find a small bit of PL/SQL that lets you "dial a ratio". You want 85%, it can be yours. Want 99%... yup, just sit back and wait. Good old Connor: that's the best demonstration I know that hit ratios are an utter waste of time.

Let me also put it another way: Oracle will register a hit what your O/S has paged out to the paging file. Therefore, though you might have a 100% hit ratio (Which would normally mean lots of access to data via memory), that's only being achieved because your O/S has swapped bits of the buffer cache (say) out to disk -so what appears to be a HIT still actually needs physical I/O to do the deed.

You need to eliminate O/S paging as far as possible. Your init.ora parameters need to be pulled back to whatever point allows that to happen.

Regards
HJR
>But just curious.
>
>
>
Received on Fri Jun 28 2002 - 22:16:10 CDT