Re: MTS and Memory

ctcgag_at_hotmail.com wrote in message news:<20040910133003.520$q6_at_newsreader.com>...
> hjr_at_dizwell.com (Howard J. Rogers) wrote:
> >
> > But in any case, that doesn't get you off the hook. You asked a
> > generic question about how far you can 'bump up' your SGA. You were
> > told, I think, that the official recommendation from Oracle is that
> > the SGA should never consume more than 50% of available physical RAM.
>
> I've heard this many times here, but I've not seen it in Oracle
> documentation. So I spent more time than I'd care to admit on tahiti
> searching for their SGA sizing recommendations (9.2), and I couldn't find
> such advice. Usually they just say that it should be set "appropriately"
> (Gee thanks, guys.) or they tell you to go look in some other document
> (which of course just refers you to yet another document). The most
> specific advice I could find is that it should not be so large as to cause
> swapping.

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96533/memory.htm#47750

>
> > You were also told (by me) that unless you are suffering lots of
> > ORA-4031 errors (indicating that you are running out of memory in the
> > shared and/or large pools) you don't (probably) need to increase the
> > size of your SGA (specifically the large or shared pools) in the first
> > place.
>
> If there are excessive disk sort or hash operations, would that also
> indicate that more memory is needed? IOW, if I can't grab my
> sort_area_size or hash_area_size worth of memory from the large pool, would
> I throw an error or would I make do with less memory and go to disk?

They tell you to increase sort_area_size because of disk accesses.

column value format 999,999,999

select 'INIT.ORA sort_area_size: '||value from v$parameter
where name like 'sort_area_size'
/  

select a.name, value
from v$statname a, v$sysstat
where a.statistic# = v$sysstat.statistic# and a.name in ('sorts (disk)', 'sorts (memory)', 'sorts (rows)')
/

jg

--
@home.com is bogus.
http://www.bl.uk/treasures/shakespeare/homepage.html