Re: Advice on running Oracle with SGA > 20 GB
Date: Mon, 1 Feb 2010 16:44:43 -0800 (PST)
Message-ID: <7f345c79-bc05-428e-a415-ac3d4bb91e01_at_19g2000yql.googlegroups.com>
On Feb 1, 2:15 pm, vsevolod afanassiev <vsevolod.afanass..._at_gmail.com> wrote:
> The amount of memory provisioned in new servers keeps growing, we
> have
> several servers where single image of OS (Solaris, AIX, HP-UX) has
> access to
> more than 32 GB RAM. A question arises how to use this memory most
> efficiently to run Oracle databases, in particular what problems one
> may experience when running Oracle 9iR2 or 10gR2 with SGA > 20 GB?
> I am particularly interested in systems with mixed workload, i.e.
> where we see simultaneously highly efficient index-based queries, very
> inefficient
> queries joining multiple tables (consuming in excess of 800 million
> buffer gets
> per execution), large updates, dynamic SQL, ad-hoc queries, etc.
> What would you do on a server with 100 - 200 GB RAM that runs one
> Oracle instance?
> - would you configure direct I/O (bypass UNIX filesystem buffer
> cache)?
This is very system dependent.
> - would you configure SGA around 50 - 70% of physical RAM?
IMO, that is just a wild-ass guess starting point from the days of smaller systems. Really, you need to determine what-all is going on on a fully loaded running system. Then Iterate. With a mixed system, you may want to allow some big PGA at times, to avoid spilling sorts to disk, but that can have funny optimizer effects. 9i is different than 10g, too, there's just no usable rule of thumb.
> - would you use 8K block size of bigger blocks?
There have been some very interesting discussions of this (like this classic: http://www.oraclealchemist.com/oracle/hey-guys-does-size-matter/ ) , which has convinced me to stay with 8K. But my systems are more limited than yours.
> - would you allow automatic resizing of SGA components in 10g (ASMM)
> or use fixed sizes?
My gut feel is to use it, then turn it off if it starts being too stupid, where too stupid means things like getting tuning exactly backwards because it is working with what-was. Trust your testing more than my gut. There are stories floating about... my general view is that all new features need some amount of time to work out the bugs, the greater the spread of your feature usage in production, the more likely you'll hit something obscure, which can mean "difficult to replicate and fix."
> - would you use some OS features like "large pages" on AIX in
> combination with LOCK_SGA?
Haven't been on AIX recently to comment. But it has always seemed idiosyncratic among unix, to me.
Are you RAC?
jg
-- _at_home.com is bogus. SAP and IBM say... what? http://www.informationweek.com/news/software/database_apps/showArticle.jhtml?articleID=222600700&subSection=NewsReceived on Mon Feb 01 2010 - 18:44:43 CST