Oracle 9i requirement for nfile is LARGE

[James Ellis_1]

I am investigating a problem, DBA upgrading to Oracle9i and they sent me their requirements. One of their requirement is to change the nfile formula to (15*NPROC+2048). I am leery of changing the nfile parameter default formula (this is a prod box), the current default formula has the nfile set at 9269, but Oracle DBA needs to have nfile at 74348.

nfile current formula is this:
(16*(NPROC+16+MAXUSERS)/10+32+2*(NPTY+NSTRPTY+NSTRTEL))

As you can see, the values of NPROC and MAXUSERS probably determine the final value of nfile because its a multiple of 16. What is the largest value NPROC and maxusers can be?

Has anyone attempted to change the nfile defauly formula to (15*NPORC+2048)? I believe HP recommends not to change the defauly formula, so I am trying to find other ways to get nfile up to 74348.

Any tips guys and gals?

Thanks.

[Sridhar Bhaskarla]

Hi James,

My 'nfile' parameters are simple numbers. Not formulae. I do not believe HP recommends to follow the formulae anymore.

Your nproc is set to 4820. Unless you think that your system will run those many processes, you can reduce it. Look at your current numbers using the formula 'sar -v 2 20'. Depending on them you can set the values of nproc and nfile initially and then change them after seeing the trend over a week or so.

-Sri

[Gary L. Paveza, Jr.]

We changed our nfile to a flat 196608 (no formulas). We use a standard 1000 for MAXUSERS and set NPTY, NSTRPTY and NSTRTEL to 512 for all servers.

[James Ellis_1]

Sridhar,

Yes, the production server now has NPROC set to 4820. However, DBA requests that I set nfile formulae to (15*NPROC+2048)....this will turn out a VERY LARGE value for nfile.

Is there any harm to putting this formula in nfile? Does anyone know what the max value I can assign NPROC?

Basically, I need to make nfile as large as 74348 and in otfer to do this, this formula is needed.

By the way, I tried to just assign 74348 to nfile, and I got error messages saying because this was larger than the formula dictates. In other words, the formulae determines how high a value can be assigned.

Has anyone had a case where Oracle requires nfile=74348?

Thanks.

[James Ellis_1]

Gary,

Thanks. But I could not just assign nfile the value of 74348, because the formula was the limiting factor. did you just remove the formula or what?

Thanks.

[Sridhar Bhaskarla]

Hi James,

I just wipe out the formula.

Well it's not really for oracle. The formula is an approximation that there will be 15 openfiles per process which is not unexpected for oracle. I have one system with 61450 and it's running nearly at 80%.

Make the change, watch the system for sometime and then lower if it is too high.

-Sri

[Fred Ruffet]

Sounds strange to me that the DBAs tell the Sys admin how to configure his kernel parameters. I believe that they saw this formula somewhere without really knowing what it mean. What about asking them what the sum of db_files oracle parameter for all oracle instances will be ? This would show how many files they will really need...

regards,

Fred

[Jeff Schussele]

Hi James,

And as just an FYI...the only cost will be in a slightly larger kernel memory footprint as it will have to keep the nfile table in memory.
As Sri points out, make the change & then just watch usage with sar. cron it to run every few minutes to keep an accurate usage history as there may be short spikes that need to be accomodated & a constant cronned sar will catch them.

Rgds,
Jeff

[RAC_1]

The max. that nproc can be set to is 4194304
(I checked /usr/conf/master.d/core-hpux)

I also like to set a direct value and a formulae. A better way would be set it high enough, and moniot the usage with sar -v 2 10 and glance -t and then tuning it.

Anil

[Geoff Wild]

My nfile on my large SAP/Oracle servers is set to hard coded 189100.

That was recommended by SAP.

Rgds...Geoff