Regarding system parameters for Oracle DB with 2 instance

[Satish Kumar_5]

Dear All,

We are running 2 instance of oracle database on rp3440 with 8 gb RAM and 4 processors.Now i want to know whether we need to double the parameters specified by oracle eg nproc=4096 for one instance.What will be the optimized setting for kernel. We are running HP-UX 11i.if anyone have a same kind of setup pls suggest. we are using Oracle EBS 11.5.9, oracle apps with Databse.

Regs
Satish

[RAC_1]

In any case 2048 is too low. Try setting it to 20000. Observe the real time usage with
sar -v 5 5
glance -t

If it is unneccesarily high set it slightly low.

Anil

[Fred Ruffet]

Oracle prerequisites are a basis you have to work on.
To follow your example of nproc, it specifies the maximum number of processes allowed to be launched at a time. If each database is configured for a max processes number of 100, 4096 is enough.
So it depends on your Oracle configuration for each DB. Even with one DB, you may have to increase parameter given by Oracle.

Regards,

Fred

[Satish Kumar_5]

Hi,

We have around 800 process on both DBs.So i think nproc is fine. but still when we execute some command it took a second or 2 before executing. i have checked load and its not more than 5 total.. sar output for ur ref:

HP-UX ERP3 B.11.11 U 9000/800 12/01/04

19:09:52 %usr %sys %wio %idle
19:09:57 0 3 0 96
19:10:02 2 4 1 94
19:10:07 1 3 2 95
19:10:12 1 3 0 95
19:10:17 0 3 0 96

Average 1 3 1 95

whats wrong?? any help will be very appreciated.
regs
Satish

[Eric Antunes]

Hi,

Check:

#sar -b 5 15

And:

#sar -v 5 15

And finally:

#swapinfo -tam

Best Regards,

Eric

[Fred Ruffet]

could you post output for these :
sar -ubw 10 6 # this one will run 1 minute
swapinfo -tam

Regards,

Fred

[Fred Ruffet]

Oups, I forgot this command :
top -n $(ps -edf | wc -l) -f /tmp/frutopfile.$$ && cat /tmp/frutopfile.$$ && rm /tmp/frutopfile.$$

Regards,

Fred

[Satish Kumar_5]

Hi,

Thanks so much to u all guys. I m attaching the output of commands u were asking. pls have a look and suggest. Thanks Again

[Eric Antunes]

Hi Satish,

From the output of top, it seems you are using more than 1200Mb for the Oracle SGA (System Global Area) and Buffer Cache. In my opinion, this is too much... You need to tune this 2 paramaters: See Metalink Note 100709.1

Kindest Regards,

Eric

[Fred Ruffet]

Eric,

Why is this too much ? With 8GB of RAM, 1.2GB allocated for oracle is not much.

Satish,

You're talking about 2 instances, and I can only see one. top output may not be ful as long as We don't have header, and I don't see vhand (wich was my goal). I would really like to see the line from top for vhand process (and optinally uptime).

Regarding to stats you're system is swapping, and (yes, once again) I'm asking what is your settings for OS dbc.
Could you please give output for "mount" command (without parameter) and for those two :
kmtune -q dbc_max_pct
kmtune -q dbc_min_pct
Also, do you have Online JFS ?

It makes many command I ask for, but I have an idea behind all of that :)

Regards,

Fred

[Eric Antunes]

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

[Eric Antunes]

I meant to say:

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

[Satish Kumar_5]

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]

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

[Fred Ruffet]

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

[Satish Kumar_5]

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