Operating System - HP-UX
1748210 Members
3331 Online
108759 Solutions
New Discussion юеВ

Re: Regarding system parameters for Oracle DB with 2 instance

 
Eric Antunes
Honored Contributor

Re: Regarding system parameters for Oracle DB with 2 instance

Fred,

That's Oracle advices... From Metalink Note I've refered above:

"...We have discussed having the Shared_Pool_Size set too low, but adverse effects may comes from setting the Shared_Pool_Size too high as well. Having the shared pool set too large increase the probability of latch contention when the database needs to find a free peace of memory to load a new statement. You can query v$sgastat to show the available free memory. This will tell you memory is being wasted. As an example, let's look at the following problem:

select name, bytes/1024/1024 "Size in MB"
from v$sgastat
where name='free memory';
...
"

I see the following:
NAME Size in MB
Free memory 18,2546768188477

This tells me that there is 18M of free memory in the shared pool, which is acceptable in a 192M shared pool like the one I've defined. But if I'd a 64M shared pool this 18M value wouldn't be so acceptable.

As you can see, this is all relative to each particular situation.

I suggest to Satish to execute the following query to see if his shared pool is well sized...

Kindest Regards,

Eric
Each and every day is a good day to learn.
Eric Antunes
Honored Contributor

Re: Regarding system parameters for Oracle DB with 2 instance

I meant to say:

I suggest to Satish to execute the ABOVE query to see if his shared pool is well sized...
Each and every day is a good day to learn.
Satish Kumar_5
Regular Advisor

Re: Regarding system parameters for Oracle DB with 2 instance

Hi Thanks so much Fred and Eric for ur time.I m not using Online JSF.I m ataching the output of commands kmtune and mount. have a look.

Thanks again so much for ur time.
Sridhar Bhaskarla
Honored Contributor

Re: Regarding system parameters for Oracle DB with 2 instance

Hi Satish,

There is no general 'optimum' setting for nproc parameter. It is really dependent on how many processes you bring up on the system. Your 'sar -v 2 2' will show you the current proc-sz. It is not a good idea to have any parameter way too beyond it's usage. For ex., if the number of processes (even a 'ps -ef|wc -l' will do) is 1000 and if your nproc is set to 25000, that's simply waste of kernel memory. At the same time, you will need to allow some headroom so in case of surprizes, it can save the production. Also, you may have to set maxuprc (max user processes) as nproc - 2. Associated with more processes, there will be more files. So, check your 'nfile' parameter also. Run 'sar -v 2 2' when the system is heavily loaded to get the idea on your settings.

Your dbc_max_pct is at 50% which is default. 11i is not bad in managing buffer cache but there is no point having 50% of memory as buffer cache in any case. So, reduce it to around 8%. I guess the physical memory on the box is around 8-10GB. Keep min_pct at 2%.

-Sri
You may be disappointed if you fail, but you are doomed if you don't try
Fred Ruffet
Honored Contributor

Re: Regarding system parameters for Oracle DB with 2 instance

Satish,

Sorry for the late post. I was on vacation :)

As Sridhar said, you Dynamic Buffer Cache settings are too big.
Refer to this thread to see what relations there are between DBC and Oracle :
http://forums1.itrc.hp.com/service/forums/questionanswer.do?threadId=750342

If you don't use OnlineJFS, you won't be able to bypass the cache for Oracle DB, but with 8GB of RAM, a setting of dbc_max_pct=4 will set your dbc to a max value of 300MB. This is probably far enough, and will save 3.5GB of memory for you (and Oracle). It will prevent your system from swapping and particularly from swapping Oracle SGA.

Regards,

Fred
--

"Reality is just a point of view." (P. K. D.)
Satish Kumar_5
Regular Advisor

Re: Regarding system parameters for Oracle DB with 2 instance

Thanks so much Sri and Fred. I will try this. so all for ur time.