Databases
cancel
Showing results for 
Search instead for 
Did you mean: 

Oracle 9i requirement for nfile is LARGE

James Ellis_1
Super Advisor

Oracle 9i requirement for nfile is LARGE

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.
"In the middle of difficulty lies opportunity" -Einstein
11 REPLIES
Sridhar Bhaskarla
Honored Contributor

Re: Oracle 9i requirement for nfile is LARGE

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


You may be disappointed if you fail, but you are doomed if you don't try
Gary L. Paveza, Jr.
Trusted Contributor

Re: Oracle 9i requirement for nfile is LARGE

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
Super Advisor

Re: Oracle 9i requirement for nfile is LARGE

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.
"In the middle of difficulty lies opportunity" -Einstein
James Ellis_1
Super Advisor

Re: Oracle 9i requirement for nfile is LARGE

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.
"In the middle of difficulty lies opportunity" -Einstein
Sridhar Bhaskarla
Honored Contributor

Re: Oracle 9i requirement for nfile is LARGE

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

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

Re: Oracle 9i requirement for nfile is LARGE

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
--

"Reality is just a point of view." (P. K. D.)
Jeff Schussele
Honored Contributor

Re: Oracle 9i requirement for nfile is LARGE

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
PERSEVERANCE -- Remember, whatever does not kill you only makes you stronger!
RAC_1
Honored Contributor

Re: Oracle 9i requirement for nfile is LARGE

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
There is no substitute to HARDWORK
Geoff Wild
Honored Contributor

Re: Oracle 9i requirement for nfile is LARGE

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

That was recommended by SAP.

Rgds...Geoff
Proverbs 3:5,6 Trust in the Lord with all your heart and lean not on your own understanding; in all your ways acknowledge him, and he will make all your paths straight.
Hein van den Heuvel
Honored Contributor

Re: Oracle 9i requirement for nfile is LARGE


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
Frequent Advisor

Re: Oracle 9i requirement for nfile is LARGE

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.