We are using Netscape Application Server 4.0 sp6 to connect to Oracle 8.1.7 using ORACLE_OCI. Should Oracle be running in Dedicated or
Shared (Multithreaded ) mode. Will there be a performance problem
if I use Dedicated mode. Please let me know. Also how could one
change from dedicated to multithreaded mode without a reinstall of the database. Any help on this would be really appreciated.
You don't have to reinstall.
I changed from dedicated to shared about 5 months ago and haven't had any problems - there are a few things you should note.
All users connecting in will use the shared pool for sort operations and the like so you should setup large_pool_size = x as the users will now use this for their sorts etc. and this will prevent ORA-XXX unable to allocate yyy bytes of shared memory.
Also, what happens now is that you start your listener and then start your server (at this point the server registers with the listener).
I'll just double check the changes I made in the initsid.ora and tnsnames.ora and post them.
How many users do you tend to have logged on max?
Average user connection will require ~ 160k for transactions.
Assuming y connections = 160*y K that user connections will need in memory for sorting etc.
Create a large_pool_size = 160*y K, the user connections can share this memory allocation for their sorts etc.
Previously it would use the UGA in the shared_pool.
There is also a large_pool_min_aloc variable which will allocate a minimum of xk for each connection from the large_pool.
Hope this helps,
Just stop server,
Stop, start listener
Bingo I hope
Thanks for your reply.Well the application is used by some 10000 users but I think there would be something like 1000 users logged in together.
What are the configuration parameters we must modify ? Please let us know. Thanks for your help.
OK - make a backup copy of both your initsid.ora and listener.ora before doing this.
Make the following changes to your initsid.ora
large_pool_size = 160M
MTS_DISPATCHERS= "(ADDRESS=(PROTOCOL=TCP)(HOST=xx.yy.zzz))(DISPATCH ERS=4)"
LOCAL_LISTENER= "(ADDRESS = (PROTOCOL = TCP)(HOST = xx.yy.zzz)(PORT = 1521))"
[xx.yy.zzz = your host name or IP Address - I assume with NAS your connections are coming in over TCP/IP and I assume your LISTENER is on the same machine as the server]
Change your listener.ora to the following:
Well I made the changes you suggested and restarted the listener and the database and it seems the database is now running in shared mode.
Thanks a lot for this. Wanted to clarify a couple of things :
1. Somebody suggested we should have the following included in the
listener.ora LISTENER entry you mentioned.
(server = shared)
Is it ok to have it here or can we do without it?
2. When I query select server from v$session I see a number of dedicated servers the number of which stays the same all the time.
Also I do not see any record for SHARED server here.
If I query v$shared_server, I can see the request and byte statistics.
3. We are getting several 'Unable to prepare internal statement object' sql exceptions in our log files. Does this have to do with the database
4. How do we enable connection pooling on sharedservers ?
1) I don't think it makes a difference if the (server=shared) entry is made in the listener.ora, it should be shared by default.
2) If you 'select username,program,server from v$session' you should see dedicated server connections for your background processes ie PMON, SMON etc (this is the way it should be).
Also, I don't see any 'shared' sever connections - it shows up as 'none'. I haven't worried about this, everything is processing correctly.
3) Is there an ORA number associated with the 'Unable to prepare internal statement object' - I have never seen this one before.
Is it consistent and causing problems? What is the application doing at the time it shows up?
4) The MTS setup is a form of connection pooling - in this context do you mean that NAS will open a set number of connections to the database and use these for any processing, thereby not having to drop and recreate client connections all the time?
I have found that NAS on our system doesn't work like that - it doesn't appear to reuse connections. I therefore setup a profile with a timeout of 15 minutes and put the NAS user in this profile.
Thanks a lot again for your reply. Well the problem we are currently facing is that at peak load hours the Enterprise Java Beans of our
application fail to respond and this is happenning in the findByPrimaryKey method where there is a lookup query. It seems
the EJB is not able to open a new connection to the database.
I believe when the maximum number of connections per dispatcher are reached, new connection requests have to wait until a user disconnects even though some of the connections may be idle.
Is there a parameter value for the maximum number of connections
that can be opened in the database simultaneously. How can we change this value upward ?
Also the 'Unable to prepare internal statement object' is a java.sql.Exception and does not have an ORA number associated.
Use the combination of STATUS and LAST_CALL_ET in V$SESSION. Check the status for INACTIVE (you may find that they all show up as inactive if they are not processing anything).
Then check the LAST_CALL_ET as this is the number of seconds since the session was last active.