threshold for implementing MTS question..

The server is running Windows 2003 advanced server, 8GB RAM, 8 3Ghz Xeon processors, /fastdetect and /PAE switches are in boot.ini

Database is 9.2.0.6, shared pool 400 MB, buffer cache 800MB, large pool 8MB SGA max size 1505 MB - Typical number of connected users is between 75 and 125, running in dedicated server mode. Aggregate PGA target is 150MB, and application connects to database via ODBC. Processes is set at 150, I was thinking of increasing this but when I looked at the max utilization when these errors were happening it was only 136.

The database has been running since Sept with no problems, until a few days ago when people suddenly could not connect. Looking at listener.log showed a lot of TNS-12540: TNS:internal limit restriction exceeded and TNS-00510: Internal limit restriction exceeded messages, along with some TNS-12500 and 12560's. The number of users connected was between 120 and 130, and nothing out of the ordinary was being done. I bounced the DB to clear the problem and it has not happened since, and no connection errors are showing in listener.log

Since many connections are idle for long periods of time, I was considering implementing MTS (or maybe DCD), but I thought that one shouldn't need this (MTS) until connected users hit closer to the 1000 mark.

Has anyone here switched to MTS, and if so, what threshold did you use to make this decision?

Or should I be looking elsewhere to find the problem and/or cure?

thanks! Received on Tue Jan 30 2007 - 15:48:20 CST