topic Re: nfile question in Operating System - HP-UX

nfile question

uvc — Thu, 23 Dec 2004 08:57:22 GMT

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?

Re: nfile question

Eric Antunes — Thu, 23 Dec 2004 10:41:53 GMT

Hi,

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

Regards,

Eric Antunes

Re: nfile question

uvc — Thu, 23 Dec 2004 10:59:17 GMT

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

Re: nfile question

Fred Ruffet — Thu, 23 Dec 2004 10:59:20 GMT

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

Re: nfile question

uvc — Thu, 23 Dec 2004 11:02:23 GMT

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

Re: nfile question

Eric Antunes — Thu, 23 Dec 2004 11:11:17 GMT

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

Re: nfile question

uvc — Thu, 23 Dec 2004 11:13:21 GMT

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

Re: nfile question

Fred Ruffet — Thu, 23 Dec 2004 11:25:49 GMT

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

Re: nfile question

Michael Schulte zur Sur — Thu, 23 Dec 2004 11:47:55 GMT

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

Re: nfile question

uvc — Thu, 23 Dec 2004 12:01:51 GMT

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.

Re: nfile question

Bill Hassell — Thu, 23 Dec 2004 22:13:30 GMT

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.