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
(c) Copyright 2000 Oracle Corporation. All rights reserved.
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
(c) Copyright 2000 Oracle Corporation. All rights reserved.
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;
A
----------
1
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
(c) Copyright 2000 Oracle Corporation. All rights reserved.
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;
A
----------
1
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).
