Number of Open Files in DB Server

[Former Member]

Hi everyone,

I need some advise from Unix and Oracle experts on a query that my customer raised.

There are 3 additional application servers added recently. Before the app servers are brought up, I increased the Oracle processes and sessions parameters.

Then, my customer's UNIX administrator asked this question which made me ponder if it's true :

"Changing some of the Oracle parameters can also effect the Unix kernel parameters. For example if there are 256 data files and 4 application servers each with 99 work processes that means that SAP can have at least 101,376 open files on the DB server without the additional Oracle open files and those need for the OS etc. "

I just wonder, does SAP processes really perform read-write on the Oracle datafiles ? Isn't it that the SAP processes logon to Oracle server processes, and it is the Oracle instance that perform the IO on the datafiles ?

The DB server has 343 processes which corresponds to the actual number of total processes in all the app servers. And there are 202 datafiles.

Does that means only 343 processes will open datafiles ? Or will it be 202 files open only ? Or is it like what the Unix administrator said in the example ?

Can someone please enlighten me ?

Thank you,

Kent Peh

[stefan_koehler]

Hello Kent,

your unix admin is right.

>> I just wonder, does SAP processes really perform read-write on the Oracle datafiles ? Isn't it that the SAP processes logon to Oracle server processes, and it is the Oracle instance that perform the IO on the datafiles ?

The sap processes does not perform any read/write operation on oracle datafiles... but sap work processes are forcing an oracle shadow process by connecting to the database (if it is a dedicated oracle environment). This shadow process handles all requests of the sap work process to the database... so if you are increasing the number of work processes ... your count of shadow processes will increase too and so more files can be opened.

The shadow processes are performing reads on the datafiles and read/write operations on temp files. The write operations to the datafiles are done by the database writer (DBWR). There are several cases when an other processes writes to the oracle datafiles too... but this goes too far for your question.

Maybe not only the open files will be a problem, if you increase the count of work processes.. maybe you will also reach the max processes limit on the database server.

I have never analyzed when a oracle shadow process closes a file handle... so i can not really say it to you.. but if you are interested in that you can test it with "lsof" and "fuser".

So the calculation for a max open files by oracle can be:

<NUMBER of oracle processes> * <ORACLE FILES> (= Traces, Controlfiles, Datafiles, Tempfiles, External Files, and so on...)

For more information about the process architecture .. take a look here:

http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/process.htm#sthref1440

Regards

Stefan

[Former Member]

I have never analyzed when a oracle shadow process closes a file handle... so i can not really say it to you.. but if you are interested in that you can test it with "lsof" and "fuser".

From my experience a shadow process opens a file handle to a datafile on the first access and never closes it as long as he lives. So if you have datafiles which are never accessed, there will be no file handle open on them. But as longer as your database and your workprocesses are running, the closer you will get to the <NUMBER of oracle processes> * <ORACLE FILES>

Please do also not forget, that other processes (SAP work processes) open files too, so do not only size the max open files kernel parameter on the database.

Regards, Michael

[Former Member]

Thanks Stefan and mho for your advice.

So it seems like the unix admin is right about the open files.

I have another doubt :

If I have 202 datafiles and 343 Oracle shadow processes, is it considered 202 open files OR is it 202x343 = 69,286 open files ?

From Stefan's description, it seems to be the latter.

Thanks guys !!

Kent

Singapore

[stefan_koehler]

Hello Kent,

you "only" see the datafiles.. but that are not the only ones which are opened by oracle.

The maximum files that can be opened is:

<NUMBER of oracle processes> * <ORACLE FILES>

<NUMBER of oracle processes> inlcudes shadow processes and background processes of oracle

<ORACLE FILES> includes datafiles, tempfiles, controlfiles, tracefiles and so on...

So if you only want to calculate the max open files for datafiles, then yes 202x343 is correct.. but overall that is not the whole oracle world

Regards

Stefan

[Former Member]

Hi again

I give you some example with commands on a real system.

SQL> select count(*) from dba_data_files;
  COUNT(*)
----------
       262

SQL> select count(*) from dba_temp_files;
  COUNT(*)
----------
         8

SQL> show parameter processes
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
processes                            integer     620

So we have a system with 270 data/tempfiles and a maxium of 620 processes, so the theoretical maximum of open files can be over 270x620. Lets check how many processes we really have at the moment:

orasid> ps -ef | grep ora | wc -l
330

This includes, all shadow and background processes one shell (mine) and even my ps command )

But we do have slightly above 300 oracle processes here. So we might have roughly 270x300 = 81000 files open at the moment. Lets see, i use lsof to list the open files of the oracle command and redirect the output to a file, just because there is really a lot of files open on this system:

orasid> lsof -c oracle > /tmp/lsof.out

This includes directory entries, looks like there is some shared memory stuff, even tcp connections as well (we forgot about these until now). I think all these need file handles on unix as well, so be it. So in our example we have 46557 files open:

orasid> wc -l lsof.out
46557 lsof.out

You can dig further in the lsof.out, for example how many times is one single data file open:

orasid> grep /oracle/SID/sapdata2/stabd_4/stabd.data4 lsof.out | wc -l 
209

I hope i did not do too many mistakes here, have fun!

Best regards, Michael

BTW: there is a maximum of 131072 (= 0x20000) open files on this system (HP-UX IA64)

orasid> /usr/sbin/kctune -v nfile
Tunable             nfile
Description         Maximum number of file descriptors (system-wide)
Module              fs
Current Value       0x20000

Edited by: mho on Feb 7, 2008 9:26 AM