cache allocation stratagy for multi Oracle Dbases

[doug mielke]

I have some develpment servers with multiple (3 each) Oracle instances.
4 way N class with 8 gig ram. 11.00 hp/ux Oracle 8i and older.

My DBA wants to have control over disk caching through the SGA, and given HP's supposed weakness in handleling large disk cache, I'm fine with that stratagy.

On a few servers, however, we have multiple development instances created for specific development groups, such as our offices in North America, Europe and Asia.

It seems to me that in this environment having any cache buffers set up in the Oracle SGA is a waste of memory, given that these instances are used at different times of day.

I want to limit the buffereing of the SGA, and raise the Unix cache to make disk caching more 'global'

My questions are:
First, am I missing something, or does this seem like a good idea?
Second, Are there pitfalls to this stratagy? Is there a minimum amount of data that Oracle will demand to be put in 'memory'?
Also, Are the data structures that Oracle's SGA uses to manage caching more or less efficient than HP/UX's? I think I'm well versed in how Unix handles this, but clueless as to Oracles methods.

[Steven E. Protter]

This doc was written by one of HP's best oracle guys.

http://www2.itrc.hp.com/service/cki/search.do?category=c0&docType=Security&docType=Patch&docType=EngineerNotes&docType=BugReports&docType=Hardware&docType=ReferenceMaterials&docType=ThirdParty&searchString=UPERFKBAN00000726&search.y=8&search.x=28&mode=id&admit=-1335382922+1063202328782+28353475&searchCrit=allwords

DOC ID:UPERFKBAN00000726

It might help, but it sounds like you know this stuff already.

The only impression I'm getting is that you might be over engineering this scenario. If you have adequate shared memory resources and aren't hitting swap too hard, and have the kernel set to oracle specifications, you're going to run oracle fine, especially with your hardware which is quite fast.

My impression could be wrong though, but I've been running the same number of Oracle instances on D boxes for a few years now.

SEP

[A. Clay Stephenson]

In principle, the Data Block Buffers in Oracle's SGA and the UNIX buffer cache work much the same. Statistics are maintained about how often blocks are accessed so that intelligent decisions can be made about which blocks to keep and which to write to disk. The main difference is that the UNIX buffer cache rapidly reaches a point of diminishing returns (and even negative returns) when the buffer cache exceeds about 800MB on a well-patched 11.0 box. Oracle SGA's, on the other hand, work happily at multi-GB sizes and they need to be at least 1GB or so on even modest sized instances. Your problem is that 8GB can very quickly be eaten up running 3 large instances.

You should also be aware that the SGA does more than simply buffer data blocks. For example, shared SQL code, the database dictionary, and redo log buffers are also cached in the SGA.

In your case, you would probably be best advised to limit your buffer cache to about 400MB or so and then allow the DBA's to reasonably set the SGA sizes. I am assumming that you are running 64-bit Oracle. I would limit shmmax to about 2GB or so, so that no one instance can gobble up all the resources in sight.

I would also tell the DBA's to intentionally cripple the development instances with smaller than normal SGA's because the one thing that they do not want are unpleasant surprises when they move to production. By intentionally limiting the development SGA's they are forced to devise more efficient code.

[Indira Aramandla]

Hi,

DB_BLOCK_BUFFERS parameter is measured in Oracle blocks. The value of this parameter is very important for storing data into memory as users are requesting information from the system. DB_Block_Buffers are the number of buffers in the SGA cache that will be available for user data to be stored in memory. The size of the buffer cache, which is used to cache db blocks in the SGA, is specified by this parameter. Because the data is cached, this reduces the amount of physical I/O.

In turn, the setting of this parameter has a large effect on the buffer cache hit ratio, which you generally want to be above 90%. The hit ratio can be dynamically determined based on the following query:

When an Oracle instance is started, the OS is being asked to give some Key resources according to the parameters specified in init.ora. Oracle uses shared memory for efficiency. The OS does not have to load up the same address page(s) into the memory each time a process needs to reference it. Instead the process can just reference the one memory location, read/write to it, and then leave. The number of semaphores allocated is directly dependent on the PROCESSES parameter in the init.ora file. The total amount of semaphores required is derived from the 'processes' parameter inside that Oracle instance init.ora file

[Jeff Schussele]

Hi Doug,

I essentially agree w/Clay.
Let The DBAs set a realistic SGA that is definitively transferrable to Production in Scope. But you *must* therefore insure that LVM operations are purely sync to gain max throughput - i.e. convosync, mincache=direct.
But also remember only data & indices - not redo or logs.

Rgds,
Jeff

[Con O'Kelly]

Hi Doug

My understanding is that you can't really use the UNIX buffer cache in place of the SGA. Every DB I/O operation will have to go through the Oracle SGA buffer.

The size of the UNIX buffer cache will have little impact on the performance of Oracle (unless of course it is really small).
As Clay has already mentioned a UNIX buffer cache of 400MB-800MB is more than adequate for almost any sized Oracle DB.

But you will run into all sorts of performance issues with Oracle if you don't size the SGA correctly.
However I do like Clay's point idea of not oversizing SGA's in Dev environments.
Often there is a belief that increasing the SGA size will fix any Oracle performance issues & this is most defintely not the case.

One last point is that if you have OnlineJFS you can bypass the UNIX buffer cache entirely for DB I/O operations.
Have a look at the mincache=direct & convosync=direct fileystem mount options. In some cases this MIGHT give some small performance gains for Oracle. Note that you should only use these options for the filesystems that contain the Oracle datafiles. You'll see plenty of discussions about this on the forums.

Cheers
Con

[Michael Schulte zur Sur]

Hi Doug,

you say, that the different instances are used at different times of the day. Could you shutdown those not needed or at least run them with a lower memory profile, so you could channel most of the memory into the active instance?

just a thought.

Michael