Madhu I'm no expert on this, just another DBA wrestling with the same issues. In my mind, the Java connection pool does the same function as MTS, in concept at least. It takes many users and concentrates them to just a few connections. In other words, let's suppose you have an overall pool of 10,000 users. You service them with a Java pool of 1,000 connections. Then you have MTS configured with 100 servers. My question is why not just configure the Java pool for 100 connections and skip MTS? Maybe there is an excellent reason and I'm too dumb to see it. In your specific situation, since you have a big beast of a server, my dumb question is: why should you need to resort to MTS to avoid the limitation in the number of files you could have open? It just struck me as maybe there was an O.S. kernel tuning parameter that needed tweaked. Again, this isn't based on any knowledge, just a gut feeling. Myself I would tend to lean on the system administrators to "fix their problem" at least as a start.
Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -----Original Message----- Sent: Thursday, June 26, 2003 3:00 PM To: Multiple recipients of list ORACLE-L TANEL, A wonderful demonstration !!! we are already using the Java connection pool, DO you think we still have to use MTS ?? or which is better ?? Dennis, We really have a big beast ( 1.2T OLTP ) here in terms of hardware resources. Its not the problem with memory or CPU or I/O. John, Thanks for reply, as you pointed more # of files is big problem, but we already tried to minimize it , What is your point on Java connection pooling (VS) MTS ??? Any pointers will be appreciated !! Thanks, Madhu Reddy X13944 -----Original Message----- Sent: Wednesday, June 25, 2003 3:45 PM To: Multiple recipients of list ORACLE-L 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Reddy, Madhusudana 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS 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).