Re: nfile parameter problem

From: Tanel Poder <tanel.poder.003_at_mail.ee>
Date: Wed, 25 Jun 2003 13:01:49 -0700
Message-ID: <F001.005B9B54.20030625124508@fatcity.com>

Hi!

I think you should go with some sort of connection pooling and/or MTS.

> My main question to you all is : Is there any way to reduce the # of open files opened by Oracle processes ??

The issue is, that with dedicated server every process has to open a datafile if it tries to read a data block which isn't already in buffer cache. If you got let say 1000 connections with dedicated servers (thus 1000 server processes) and 500 datafiles, the worst case is 500 000 used file handlers. Of course, this is really the worst case, when every process has had to read a block from every file.

When going with MTS, you actually have 50 or so processes to serve all 1000 of your connections, thus the need for file handlers is lot smaller. (I think that in Windows going with dedicated servers isn't a procblem, because it's single process architecture - threads can share file handlers between each other, right?)

I wrote this mail in html, because I added my testing about datafiles with comments here.

Cheers,

Tanel.

bash-2.03$ uname -a

SunOS blade.nt 5.8 Generic_108528-09 sun4u sparc SUNW,Sun-Blade-100

bash-2.03$ sqlplus system/[EMAIL PROTECTED]

SQL*Plus: Release 8.1.7.0.0 - Production on Wed Jun 25 21:35:04 2003

Connected to:

Oracle8i Enterprise Edition Release 8.1.7.1.0 - Production

With the Partitioning option

JServer Release 8.1.7.1.0 - Production

First, lets check whether I'm using a dedicated server (MTS's can have files open as a result of other sessions request). Also finding the OS PID to compare with fuser result later on.

SQL> select server from v$session where sid = (select sid from v$mystat where rownum = 1);

SERVER

DEDICATED SQL> select p.spid

from v$process p, v$session s

where s.sid = (select sid from v$mystat where rownum = 1)

and p.addr = s.paddr;

2 3 4

SPID

29064 -- note my OS PID for this session

Now create a tablespace and a table for testing

SQL> create tablespace test2 datafile '/u01/oradata/TEST817/test2_01.dbf' size 1m autoextend off;

Tablespace created.

SQL> create table t (a number) tablespace test2;

Table created.

SQL> insert into t values (1);

1 row created.

SQL> commit;

Commit complete.

Now check with fuser, which processes are holding the datafile open

SQL> !/usr/sbin/fuser /u01/oradata/TEST817/test2_01.dbf

/u01/oradata/TEST817/test2_01.dbf: 29064o 390o

My process is there, because I just created the tablespace

Now I take the tablespace offline/online, to make sure it's blocks in buffer cache are invalidated

SQL> alter tablespace test2 offline;

Tablespace altered.

SQL> !/usr/sbin/fuser /u01/oradata/TEST817/test2_01.dbf

/u01/oradata/TEST817/test2_01.dbf:

SQL> alter tablespace test2 online;

Tablespace altered.

SQL> !/usr/sbin/fuser /u01/oradata/TEST817/test2_01.dbf

/u01/oradata/TEST817/test2_01.dbf: 29064o 390o

And exit and log on again, to get a new OS process id for example

SQL> exit

Disconnected from Oracle8i Enterprise Edition Release 8.1.7.1.0 - Production

With the Partitioning option

JServer Release 8.1.7.1.0 - Production

bash-2.03$ sqlplus system/[EMAIL PROTECTED]

SQL*Plus: Release 8.1.7.0.0 - Production on Wed Jun 25 21:39:10 2003

Connected to:

Oracle8i Enterprise Edition Release 8.1.7.1.0 - Production

With the Partitioning option

JServer Release 8.1.7.1.0 - Production

SQL> select server from v$session where sid = (select sid from v$mystat where rownum = 1);

SERVER

DEDICATED SQL> select p.spid

from v$process p, v$session s

where s.sid = (select sid from v$mystat where rownum = 1)

and p.addr = s.paddr;

2 3 4

SPID

29070 -- new OS PID for my connection

I havent done anything in this session, let's see who have the datafile open

SQL> !/usr/sbin/fuser /u01/oradata/TEST817/test2_01.dbf

/u01/oradata/TEST817/test2_01.dbf: 390o

Wonder who is it?

SQL> !ps -ef | grep 390

ora817 390 1 0 Dec 19 ? 1:18 ora_dbw0_TEST817

ora817 29072 29068 0 21:39:44 pts/3 0:00 /bin/bash -c ps -ef | grep 390

ora817 29074 29072 0 21:39:44 pts/3 0:00 /bin/bash -c ps -ef | grep 390

Now do a select from table in my tablespace (it's not buffered because I took tablespace offline/online)

SQL> select * from t;

Let's see who has opened the file

SQL> !/usr/sbin/fuser /u01/oradata/TEST817/test2_01.dbf

/u01/oradata/TEST817/test2_01.dbf: 29070o 390o

Now I'll log off to see whether the file remains opened

SQL> exit

Disconnected from Oracle8i Enterprise Edition Release 8.1.7.1.0 - Production

With the Partitioning option

JServer Release 8.1.7.1.0 - Production

bash-2.03$ /usr/sbin/fuser /u01/oradata/TEST817/test2_01.dbf

/u01/oradata/TEST817/test2_01.dbf: 390o

Of course it doesn't, because when exiting, my server process also dies (along with it's file handlers). But DBWR still has it open

bash-2.03$ sqlplus system/[EMAIL PROTECTED]

SQL*Plus: Release 8.1.7.0.0 - Production on Wed Jun 25 21:41:04 2003

Connected to:

Oracle8i Enterprise Edition Release 8.1.7.1.0 - Production

With the Partitioning option

JServer Release 8.1.7.1.0 - Production

Another try

SQL> select server from v$session where sid = (select sid from v$mystat where rownum = 1);

SERVER

DEDICATED SQL> select p.spid

from v$process p, v$session s

where s.sid = (select sid from v$mystat where rownum = 1)

and p.addr = s.paddr;

2 3 4

SPID

29079

I logged on, let's see if my session automatically opens the file

SQL> !/usr/sbin/fuser /u01/oradata/TEST817/test2_01.dbf

/u01/oradata/TEST817/test2_01.dbf: 390o

No, since I haven't done any (unbuffered) reads from this file.

But let's try to read:

SQL> select * from t;

SQL> !/usr/sbin/fuser /u01/oradata/TEST817/test2_01.dbf

/u01/oradata/TEST817/test2_01.dbf: 390o

Still nothing, because the blocks are in buffer cache, thus nothing to be read from file itself

SQL> exit

Disconnected from Oracle8i Enterprise Edition Release 8.1.7.1.0 - Production

With the Partitioning option

JServer Release 8.1.7.1.0 - Production

bash-2.03$ uname -a

SunOS blade.nt 5.8 Generic_108528-09 sun4u sparc SUNW,Sun-Blade-100

bash-2.03$

By the way, additional processes such are CKPT and SMON will open the file on their time.

Happy experimenting! :)

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tanel Poder
  INET: [EMAIL PROTECTED]

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Wed Jun 25 2003 - 15:01:49 CDT