Re: Low Hit Ratio

postbus_at_sybrandb.demon.nl (Sybrand Bakker) wrote in message news:<a20d28ee.0301090659.612f033c_at_posting.google.com>...
> hardikarm_at_yahoo.com (Mahesh Hardikar) wrote in message news:<4a1c57c2.0301090023.14bbdfeb_at_posting.google.com>...
> > Hi ,
> >
> > Oracle Version 8.1.7 on HP-UX 11 with 4GB RAM & 3 CPU's (540MHz).
> > Machine Hosts Oracle Apps 11i & Database Server as well.
> >
> > Current settinngs in init.ora are :
> >
> > DB_BLOCK_BUFFERS 80000
> > DB_BLOCK_SIZE 8192
> > Shared_pool_size 382 MB
> > Log_Buffer_size 10MB
> > processes 750
> > open cursors 900
> > session cached cursors 900
> > log_checkpoint_interval 100000
> > sort_area_size 1024000
> > db_writer_processes 3
> > dbwr_io_slaves 3
> > timed_statistics true
> > cursor_space_for_time TRUE
> >
> > Current database size is around 48GB & database is in NOARCHIVELOG
> > mode.
> >
> > We are experiencing low cache hit ratio like 60% in peak hours i.e.
> > when users fire lot many reports.
> >
> > I wish to know if I have some room to increase DB_BLOCK_BUFFERS. 625
> > MB SGA for 48GB DB seems low . But I want to know till how much can i
> > raise it ? Since no of processes are ard 750, how much memory do they
> > take in all ?
> >
> > Any advise on this is kindly appreciated ....
> >
> > Regards ,
> > Mahesh Hardikar
>
>
>
> Guideline is the complete SGA should stay below one third of physical
> memory or you will cause paging and swapping. You'd better off tuning
> your application. Too many people resolve application problems by
> throwing memory at the problem.
>
> Sybrand Bakker
> Senior Oracle DBA

Hi,

Thanks for the guidelines.

The problem is many reports run for hours. (e.g a report ,i don't recollect the name but its in GL) People fire a report by evening & do not get output till next morning. The report we have identified is provided by Apps. We are also checking queries of customized reports developed here.

I agree with Jim saying the requested data might not have been never asked before which is in turn producing cache misses.

I am also doubting about physical placement of data. Although data,index,log , temp & rollback files are on separate filesystem , I am not sure if they are on different HD. I will need to check . We are also thinking to partition tables. Will looking at v$waitstat give me some idea ?

Any advise on some more issues is highly appreciated .....

Thanks again ...

Regards,
Mahesh Hardikar Received on Fri Jan 10 2003 - 05:07:05 CET