Re: SGA sizing OenVMS

From: Bill Beaton <bb61_at_calgary.qc-data.com>
Date: 1997/02/19
Message-ID: <5efil4$a5q$1@f02s02.tac.net>#1/1

In article <330A06B9.3952_at_hawking.westcon.prc.com>,

        Frank Barbarino <barbarin_at_hawking.westcon.prc.com> writes:
> I'm real curious...
> why would you want to have an SGA greater them 40MB???
> Unless you have a boatload of packages and procedures
> that are real hogs? The sga I thought was mainly to hold
> packages and the data dictionary tables? Isnt it just
> wasted memory/disk space? I would love to hear back.
>
> Michael S. Abbey wrote:

>> 
>> Anyone got any comments about sizing the SGA at greater than 40
>> megabytes per instance on an Alpha 8400???
>> 

A couple of major performance benefits are available ... 1. Use 'BOB' (Big Oracle Blocks) ... I've found that 32K db_block_size is

   a performance boost on sequential queries. 2. Put as many tables in memory as possible ... I prioritize by starting with

   tables frequently used in complex joins, and then moving down to just    frequently accessed tables. Don't forget the 'CACHE' parameter on the    create or alter table statement, and don't forget to do a 'select *'    against the cached table to get it in the shared memory. Also, there is    an init.ora parameter that eludes me at the moment for specifying how    many blocks to remain resident in the MRU area. Be sure that it is as    large as the sum(blocks) from the dba_segments for the tables being    cached.
3. Allocate lots of db_block_buffers in the non-MRU area to allow reasonable

   storage of the indices for non-cached tables. 4. Pin all packages into the SGA ... this includes ORACLE's DIUTILS, etc.

You can, given the appropriate sql statement mix, get performance several orders of magnitude better than base ORACLE, by taking advantage of 64-bit VLM ORACLE on 8400 series servers.

Bill Received on Wed Feb 19 1997 - 00:00:00 CST