Re: Low Hit Ratio

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

run glance or top to see if your cpu's are pegged and how big the processes are. You may need more cpus. Be sure you are not swapping (look at swapinfo), and that you haven't set unlockable memory too low (dmesg|grep lock). I'll wildly guess you will see that Oracle is wailing 100% on your cpu's, especially when you have 4 or more reports running concurrently. You may see an increase in total throughput by serializing the reports into 2 or 3 batch streams. Depends on what exactly the reports are doing as to whether increasing SGA will do anything, but the 1/3 to 1/2 of physical (unlocked) mem rule may be reasonable. Go to metalink.oracle.com and find the paper about limitations of SGA on hp/ux (It's something like 1.75 G, depending on OS bitsize, whether you are using multiple instances and so on), as well as the paper about common unix tuning errors and the dba tuning scripts. I think you should easily be able to justify more physical memory, but probably based on online usage rather than reportage. And if you have Autoraid, look into something more modern.

You might very well want another machine with a replicated db for reporting, if your users can deal with issues such as reporting from a point in time.

A minor downside of bigger SGA is longer checkpointing, but if you do the tuning properly that shouldn't be an issue.

Of course, don't forget to do traditional tuning on the slow running reports. (The "proper" methodology according to Oracle is to look at SQL first, but I think that methodology ignores the reality of production systems - a running system should be checked for _gross_ out-of-proportion tuning and hardware issues first IMHO - go through the Oracle Tuning books from Oracle Press if you haven't already).

jg

--
_at_home is bogus.