Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Pierre.

Asked: March 10, 2008 - 2:42 pm UTC

Last updated: March 11, 2008 - 9:14 pm UTC

Version: 10.2.0

Viewed 10K+ times! This question is

You Asked

Hi Tom,

Could you please give us some examples when do you think a very large SGA (I mean 5, 10, 20 Gb) is a good thing and when it is a bad thing ?

Thanks.

and Tom said...

The funny thing is - in most cases - it is just "not necessary"

Many look to "gobs of memory as a panacea", a solution to a performance problem. They get it, install it, configure it, and have to start working with their heads anyway (because it is not a silver bullet for all problems).

Given the size of todays machines however, I would argue that a 5gb SGA is not "very large" (well, on a typical windows machine with all 32bit goodness, it is the stuff of fantasy - but on a real server machine...). 5gb is pretty common. Even 10-20gb are. SGAs in triple digits are interesting - as they would be called 'large'.

When is a large SGA bad - when you made it large in an attempt to hide a performance issue that should be fixed (with much better results) using conventional means (eg: program efficiencies).

When is a large SGA good - when the above paragraph is not true.


The problem is - some people try to increase and increase the size of their SGA to mask an underlying problem. For example "we hard parse like mad, do not use bind variables, the SGA advisors say go bigger" - when in fact, going smaller would likely be the right answer as you are not able to reuse SQL anyway, so might as well make the shared pool more efficient by making it smaller - not larger.

wouldn't it be a shame if someone trying to "tune" the second query here:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:6749454952894#6760861174154

said "look at all of that physical IO, let's make the buffer cache bigger". Then it would be bad.


So, a big SGA in itself is not "bad" or "good"

The reason for having a big SGA might be bad though.

Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

SGA

Don, March 11, 2008 - 5:20 am UTC

According to Arup Nanda in his Oracle Database 11g: The Top New Features for DBAs and Developers articles on OTN ( http://www.oracle.com/technology/pub/articles/oracle-database-11g-top-features/11g-securefiles.html )

He says "One of the advantages of storing unstructured data in OS files instead of database resident objects is the facility of caching. Files can be cached in the operating system's file buffers. A database resident object can also be cached in the database buffer cache. However in some cases the caching may actually be detrimental to performance ... Thus in most cases you may want to disable caching for the LOBs."

Does Oracle have a recommended way to compete with the file system OS caching benefits, except to make a *very large* SGA to hold the LOBs as they come in or out of the cache?

Or with Secure Files, it is just as fast (or faster, as they claim) to write directly to disk and complete bypass the buffer cache? By using the NOCACHE option.


Tom Kyte
March 11, 2008 - 7:21 am UTC

securefiles, reimplementation of lobs, had as a design spec to be at least as fast if not faster for retrieval than a file system - and they are there.

I haven't seen where caching of the lob in 10g could be detrimental - unless they were speaking of the lobs wiping out the relational structured data (in which case, the SGA should have been given all of the OS memory, not just some). Alternatively, the lobs could be segregated to the keep or recycle pools to prevent them from wiping out the other stuff

ldsandon, March 11, 2008 - 7:58 am UTC

That's why, IIRC, Mr. Kyte tells in his "Expert Oracle Database Architecture" to use a separate buffer pool (i.e. the Recycle one) to cache LOBs without impacting the main SGA buffer, but allowing DBWR to write them asynchronously, for example. I just wonder what happens if the buffer pool used is smaller than the largest lob - if the operation fails or whatever, I have to try yet.
Tom Kyte
March 11, 2008 - 9:07 pm UTC

lobs are paged in chunk by chunk, you can have lobs much larger than the cache.

Instance shared among multiple apps

neil, March 11, 2008 - 2:33 pm UTC

What would happen if two well-designed apps(read: use prepared statements) shared an SGA with a not-so-well written one(absolutely zero prepared statements). Would the constant parsing from the inefficient app dominate the shared pool regardless of size? I would imagine that a system that is executing hundreds of thousands of statements without bind variables would take as much SGA as it could, leaving little for two other applications.
Tom Kyte
March 11, 2008 - 9:14 pm UTC

... if two well-designed apps(read: use prepared statements)
...

prepared statements != well designed at all.


it takes exactly:

a) ONE program
b) with ONE sql statement that doesn't use binds
c) executed often enough

to entirely kill a system - just one program, one sql statement - that is ALL it takes.

it will fill sga with junk
it will cause sga to flush - good with bad
causing applications that did not hard parse before to start hard parsing
leading to more problems
and..... ouch

consolidation of apps

reader, March 12, 2008 - 2:49 pm UTC

The idea that one bad app(le) can spoil the bunch is an anti-consolidation argument still used by many DBAs.

in regards to memory advisor recommending more memory SGA for apps that don't share SQL, I thought that was fixed. ADDM is smart enough to recognize that SQL isn't being effectively shared, but previously it wasn't smart enough to tell you that you shouldn't keep upsizing the SGA.






more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.