Re: threshold for implementing MTS question..

On Jan 31, 6:48 am, GS <g..._at_canada.com> wrote:
> 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!

You hit ORA-12500 maybe too early considering you have 8GB RAM. What's the complete error? I'm looking for something like this:

TNS-12500: TNS:listener failed to start a dedicated server process  TNS-12540: TNS:internal limit restriction exceeded   TNS-12560: TNS:protocol adapter error
   TNS-00510: Internal limit restriction exceeded     32-bit Windows Error: 8: Exec format error

Next time when the error occurs, quickly check Virtual Bytes in perfmon for oracle.exe process before you bounce.

Yong Huang Received on Wed Jan 31 2007 - 06:46:23 CST