nfile question

[uvc]

Hi,

We're running oracle 9i RAC, with 800 parralel servers and 2000 Database files.

Lately, we had a file table overflow.
Current nfile is 120000.
A simple calculation says that nfile should be about 1600000. However, I'm afraid that it is a very, very, large value that may cause performance problems, etc.

What do you say? Does a value of 500000 sound reasonable?

And another thing - Should I change other parameters accoardingly? (ninode etc?) And how?

[Eric Antunes]

Hi,

One pertinant question: why do you have 2000 database files?? How many tablespaces???

Regards,

Eric Antunes

[uvc]

we currently have about 1500 files and it will reach 2000 almost certainly. How many tablespaces? I don't know. a lot.

[Fred Ruffet]

You may have a pretty big DB... As Eric seems to point out, 2GB limit is no more valid. I found a limit at 32GB and so I do not go further than 30GB. With 2000 files, you should go up to approximativelly 60TB...

Regardless of this kind of things, you will have more performance problems with a too low nfile parameter than with a large one.
When nfile is too low, Oracle will spend time closing file to open others.
Increasing nfile will only grow file descriptors table (and use a little memory).
So, increasing nfile is preferable. But I don't know where is its limit... try 1600000. If you don't hit a limitation, it will be cool.

Regards,

Fred

[uvc]

It's a 15TB database on a two-machine cluster of 64-way SD wih 80GB ram.

[Eric Antunes]

My question was to try to understand why to have so much database files. As a rule, you should only use 2 tablespaces for each application module (Receivables, Payables, etc...): one for tables and the other for indexes. As Fred said with Oracle 9i, you limit is 32Gb...

Eric Antunes

[uvc]

I don't know. Big data warehouse. DBAs decided so...

[Fred Ruffet]

DBA's are very conservatives : They often have good old rules they apply since Oracle 6 :) Now you can engage a debate with them on "why do we use so much little 2GB files ?".

For an instant resolution, increase nfile... or maybe maxfiles. What is your maxfiles setting ? What about max_files_lim ?

Regards,

Fred

[Michael Schulte zur Sur]

uvc,

if you have only 1500 files in oracle what do you need 1600000 files for?
Are you sure we talk about nfile and not about the maximum number of database files in the oracle config file?

just a thought,

Michael

[uvc]

The datafiles are 10GB mostly.
The nfiles are needed because we have 800 parralel oracle servers, each one potentially able to open all 2000 datafiles at the same time.
though this is not probable, we already passed our current 120000 nfile.

[Bill Hassell]

nfile is not constrained as to an upper limit (maybe 2 billion) and I have personally seen nfile = 1.5 million on a very poorly designed application--but it works fine. If you need 160K files, set nfile to 200K or maybe 300K and monitor. You don't need to change ninode at all except to remove the formula (it's obsolete) and set ninode to 2000 to a max of 4000. You'll need to monitor nproc to make sure you don't run out of process table entries.