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

[Hein van den Heuvel]

Not that it matters much, but I woudl expect a much lower number for SAP solutions. Reason being that SAP acts like a transacation monitor where many frontend connections (hundred+) are mapped onto a few (ten+) work processes (DIAlogue and VerBUchers) with a few Oracle slaves. This gives a 'fan-in' of 10x to 50x.
By contrast, a Baan solution has an Oracle slave for each end user. leading to a high nproc and nfile need.

fwiw,
Hein.

[David Ritchie]

The best way to approach this is set it high,
and then monitor it with sar -v and check for overflows. For example...

# sar -v | more

HP-UX boromir B.11.11 U 9000/785 06/11/04

00:00:00 text-sz ov proc-sz ov inod-sz ov file-sz ov
00:15:01 N/A N/A 118/2048 0 616/2728 0 474/4107 0
00:30:00 N/A N/A 118/2048 0 616/2728 0 474/4107 0
00:45:00 N/A N/A 118/2048 0 616/2728 0 474/4107 0

The problem is that DBA's, in combination with support desk people at Oracle, PeopleSoft, Baan, SAP, have SA's increase the size of nfile way, way over what is actually used or needed. Unused slots in the file table are needed, but there is a performance penalty to pay when the size of nfile is extremely high.

This results is slow performance in the cases where the kernel does a full scan
of the file table, and as I recall, this can be a particularly bad issue when NFS is involved (close() calls in particular).

I would suggest setting the number high initially, and monitor it with sar -v over time and tune it down to increase performance. Also, if you are running hfs file systems, make certain that ninode is set low... huge ninode values have a large negative impact on performance.