nfile , Oracle and SAP

[Kevin Hill_2]

Hi all,

We have RP 8400 which runs the Oracle 9.2.0.4 database for our SAP R3 enterprise system.

Last month we had a problem when the current open files hit the limit and Oracle crashed.

The current setting of nfile is 125000. I initially felt this value was too high and I should not increase it. the Db currently has 1274 data files. this value is fast increasing - around 20 per month. We intend to stabilise this value. In the meantime sar reports the current open file descriptors as dangerously close to the limit. the value hovers around the 119000 mark and sometimes crosses 124000.

How high can I set the nfile value to ?
What is thee relation to memory ?

regards,
Kevin

[Clifford Chi]

Hi Kevin,

This will help:

http://docs.hp.com/en/939/KCParms/KCparam.Nfile.html

[Hein van den Heuvel]

Hmmm, that seems like an awful lot of files to manage, but from an OS and a DBA/Admin perspective.
How big/small are they?
Are you limiting them to 2GB per chance?
What is stopping you from going towards say 20GB files reducing the number of files by afactor of 10.

fwiw... When I ran large SAP benchmarks with a Terabyte database I would still only 50 files or so.

On the connection side... how many application servers and how many sap dialogue/update servers on each?
Some setups, in a misplaced effort to speed up the application have over allocated.server processes leaving many idle, but with active connections.

My toool there is a simple ps -ef | grep sap | sort -k 4 -R
to see the sap servers by decending cpu time. Those servers getting less than 10% of the cpu time of the top server should possibly be removed.

And even if they are not idle, but all almost equally busy, then too many application server processes only serve to pushe down the contention to the DB box where potentially it would be more efficiently resolved at the app server level through the dispatcher there.

fwiw,
Hein.

[Bill Hassell · Accepted solution]

nfile sizes the kernel's open file table. However, an open file is not just simple files, it also includes network sockets, pty's used for network connections, so for a vary large server with hundreds of users and thousands of processes, 125,000 may be necessary. There is no way you can control the growth until you stop new programs from running and prevent users from logging on. In other words, these file entries are being requested by the programs that are running.

Like any production server, downtime is a problem so do NOT increase nfile to 126,000. Change it to 200,000 and then begin analyzing all the processes that are running. You can set nfile to 500,000 or even a million. I have seen systems that run just fine with nfile = 1,500,000 (that's 1.5 million). There's nothing wrong with setting nfile extremely high if the system requires it. You can't afford to have Oracle crash so make sure there's more than enough file descriptors available at all times.

Do not try to "stabilize" the value--the apps and database programs currently require the high value to run. Instead, use Glance and Measureware to track all the processes and see where the growth is rfeally occurring. Then get the developers to redesign the code. It ios certainly possible that if the quantity of Oracle files increases by 20, then middleware code may open all of these new files. If the middleware code runs once for each login, then 200 users will need nfile increased by 4000 (20 new files x 200 user processes).

You may also have dead programs or dead socket connections that are consuming extra file descriptors.

[Kevin Hill_2]

I finally increased the value to 200000