Re: SGA too large?

Sounds like fun. :-}

You don't say if there's only one instance running on the machine, or if you have to support multiple instances on the same box. I'm assuming from the tone of your message that it's probably just the one instance. I'm also assuming that since it's Applications 11 that you probably have a *bunch* of users, but that they're coming in via SQL*net, not direct connects. Please correct me if any of this is wrong. Also, you might just double-check with your management that you know about "everything" that is intended to be on the machine, you wouldn't want to tune your Apps to rely on all 12 procs/12 gigs and then have them say "Now add this database...".

I don't have any idea about the SGA limit that degrades performance, but you may have trouble getting above a 4 Gig limit, especially on Solaris 2.6.

I would try to migrate ASAP to Solaris 7 (or 2.7 if you prefer) and Oracle 8.1.6 if the Applications will allow it (check Metalink for support matrix to be sure). I've found over the years that when you deal with large memory sizes, the latest O/S and Oracle seem to be the best...

Definitely consider MTS and/or Connection Management if you have lots of connections.

If there are some slow running reports on large tables, you can maybe help a little by setting the degree of parallelism on a table-by-table basis, especially if your I/O subsystem will support it. Be sure you're configured properly for parallel, check your init.ora params. If you're not OFA compliant, get there.

I would also suggest that your company consider the Veritas Database Edition for Oracle, if you don't already have it. It includes the volume manager, Veritas file system (no more fsck!), and the Quick I/O and cached Quick I/O modules. The cached Quick I/O in particular can use that extra memory that you can't allocate to the SGA to give you *better* than raw performance on file-system based volumes. IMHO, well worth the money, especially for large systems like this.

HTH, *Dale*

Jay Weinshenker wrote:

> Heya.
>
> Sun Solaris 2.6
> Oracle 8.0.5.2.1
> Oracle Applications 11.0.3
>
> So today my management gave me a nice little surprise - they are TRIPLING
> the number of processors and QUADRULPLING the amount of memory in my
> database server. This will take us from 4 Processors and 3Gigs of RAM to
> 12 Processors, 12G of RAM.
>
> As you might guess, I'm a bit floored.
>
> So once the hardware comes in, I'll be faced with resizing our SGA and such
> to make best use of this hardware.
>
> I seem to recall there being a set point at which increasing the SGA
> actually DEGRADES performance. Anyone know of this or care to elaborate?
>
> Also, if anyone cares to list how they would utilize this extra hardware to
> improve their performance, feel free to make suggestions. I never really
> had to consider how to best utilize 3 to 4 times the resources I had...
>
> J
>
> --
> Author: Jay Weinshenker
> INET: jweinshe_at_concentric.net
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
Received on Mon Sep 18 2000 - 08:27:20 CDT