Re: Low Hit Ratio

Use perfstat package. Take spanshots at the begining and the end. look on metalink or asktom.oracle.com for info on it. Jim

--
Replace part of the email address: kennedy-down_with_spammers_at_attbi.com
with family.  Remove the negative part, keep the minus sign.  You can figure
it out.
"Mahesh Hardikar" <hardikarm_at_yahoo.com> wrote in message
news:4a1c57c2.0301092007.b2b72b5_at_posting.google.com...


> 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