Re: nfile parameter problem

Hii,

I'm gonna send a reply, which i sent privately before:

> Why is the dedicated server for Java pooled connections ??
> and why not MTS even with JPCs ??

Because MTS was invented during the golden age of client-server applications where every single user interface opened up its own connection (or several) to the database. The more connections you got, the more memory you had to have allocated. Some boxes simply ran out of memory while 95% of actual server processes were idle. Thus, here came the idea of sharing the server processes (note that in 9i you have shared_servers parameter instead of mts_server_processes).
So there's a tradeoff, instead of having 1000 server processes using huge amounts of memory, you actually got 50 shared servers with lot's less memory. But you also have some CPU overhead, you need to have dispatchers, which coordinate the traffic between clients and server processes.

On the other hand, in (my) theory MTS might use even less CPU in OLTP configurations where lot's of small requests are executed, because one Oracle server process might be able to execute several client requests, thus using up all of the 100 ms timeslice given to this process, instead of yielding and causing OS level context switches. But I haven't tested it, others might enlighten us.

But since MTS includes a lot of "new" code, dedicated servers would be more stable. And since your application server supports connection pooling (thus allowing reducing number of Oracle connections), it'd be safer to use only one pooling mechanism, instead of two. (JCP vs. JCP+MTS). The simpler the better.

Now I'm going to drink some wine with my friend, so I'm unable to respond until tomorrow.
<oh, this one should stay private ;) >

Cheers,
Tanel.

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Thursday, June 26, 2003 11:56 PM

> 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).
>

-- 
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).