Re: Oracle 8i and ESMA

You will most likely run into the law of diminishing returns. Just because you have 8GB of RAM available doesn't mean that Oracle may use it the best way possible. I can take my 4GB database and ensure that the block buffer space will have space to hold it all. But due to the way Oracle manages the block buffer space, you may not be using it all in a highly efficient manner.

Oracle (or almost all other commercially available RDBMS systems) was not designed to be an "in-memory" database. It was designed with the assumption that one will NOT be able to fit all of their data into memory. So when you try to fit all of your data into memory, it is going against the basic design of Oracle. If you want an "in-memory" database, check out one of the commercially available DBMS products that specialize in this area.

You should try as Sybrand suggested. Examine your buffer cache hit ratio. Then bump up your db_block_buffers. Examine your buffer cache hit ratio again. After a while, you will not see any significant improvement in the hit ratio and further increases in the size of the buffer cache will not give you any improvements.

HTH,
Brian  

pvinnakota_at_my-deja.com wrote:
>
> Hi,
> Thanks for your insights. First things first, the block size is 8K.
> We have a large database (several million records over 3/4 GB). The
> notion is if all the records are in DB Buffers, the query performance
> improves. On top of it, if 8GB RAM is available, and there is a way to
> use all of it, why not try it. Can a two-bit contractor DBA(me) gainsay
> it, hit ratios or no hit ratios?
> TIA,
> In article <972737847.11771.1.pluto.d4ee154e_at_news.demon.nl>,
> "Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote:
> > Increasing db_block_buffers should be based on measuring the hitrate
 by
> > running ultbstat and utlestat.
> > Bumping up db_block_buffers indiscriminatly is not going help, it will
 only
> > increase paging activities, making your loose any gain.
> > As a general guideline the total SGA should not be bigger than about
 one
> > third of physical memory, so in your case 2.3 G.
> > More than 256k db_block_buffers (regardless of block_size and you
 don't
> > specify that one) is clearly outrageous, and it is not going to help
 you a
> > damn, as for the last .1 percent hitrate increase you need
 extraordinary
> > amounts of memories.
> > I recommend measuring the hitrate first before you proceed. If it is
 above
> > 95 percent, don't do anything to increase it, between 90 and 95
 percent it
> > is questionable to anything.
> >
> > Hth,
> >
> > Sybrand Bakker, Oracle DBA
> >
> > <pvinnakota_at_my-deja.com> wrote in message
> > news:8tcld2$5o6$1_at_nnrp1.deja.com...
> > > Hi,
> > > We installed Oracle 8i (8.1.6) on an NT server with 8GB RAM. Now we
 are
> > > trying to increase the SGA size beyond 4GB using the guidelines in
 the
> > > whitepaper on this subject. I followed the guidelines and made the
> > > requisite modifications to the init.ora etc.
> > >
> > > In the process, we could ramp the SGA size up to 2.7GB by gradually
> > > increasing the DB_BLOCK_BUFFERS. There was a gradual increase in
 the
> > > SGA size from 623MB to 2.7GB. Once the the DB_BLOCK_BUFFERS number
> > > crosses certain limit(256k), the size of the SGA is reverting to a
 very
> > > small size (157MB).
> > >
> > > Has anyone tried this and succeeded? Please help with your
 insights.
> > >
> > > Thanks,
> > >
> > > Prasad V.,
> > >
> > >
> > > Sent via Deja.com http://www.deja.com/
> > > Before you buy.
> >
> >
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
 

-- 
========================================
Brian Peasland
Raytheons Systems at
  USGS EROS Data Center
These opinions are my own and do not
necessarily reflect the opinions of my 
company!
========================================