Oracle Multi-Threaded Server

[Queyon]

We've changed the database to run in Multi-Threaded Server, but our PL/SQL Developer and TOAD have problem to perform query against the database in this mode. They were working fine before the change.
What's wrong?

Queyon

[sreddy]

Check your terminal networking and are there any conflicts. Are you getting any error ?

May be your local names is looking for dedicated server connection where you don't have a dedicated connection configured on the database after you are gone to MTS...

[KenEwald]

I'm curious why anyone would need MTS now-a-days.

Memory is much cheaper than the time it takes to configure MTS and deal with all the issues it creates.

Just curious...

[sreddy]

Originally posted by KenEwald
Memory is much cheaper than the time it takes to configure MTS and deal with all the issues it creates.

MTS useful when you don't have enough CPU power to have more number of processes on the database to support users. Typical Webservers use web server connection pooling mechanism and occupies number of database processes (dedicated mode). Those porcesses are confgured at Web server level and you don't have any control on the database...

I don't think it difficult to configure it though... You might need to have both MTS and dedicated connections to have better control on the applications sessions.

[KenEwald]

Hadn't thought about the additional user process load on the cpu. I think I'd still opt for a bigger box and keep the points of failure down. Of course every situation is different, and it probably is applicable in this one.

Yea, our web dev team multi-plexes at the web server ... so we use dedicated sessions.

Good points.

[Shestakov]

Ken. "every situation is different"

I have 1 sever with up to 1200 sessions in the same time.
-- average pga/uga memory size 3-4 MB (up to 9MB)
-- total phizical memory 6 GB
-- db cache 0.5GB
-- others applications up to 1GB
----------------------------------------------------------------
can i use dedicated server or not?

If i will use dedicated server mode then memory map will be like -->
total PGA no less then 4MB x 1200 ~= 4.8 GB (!!!)
+ 0.5 GB to db cache
+ 0.12 GB to shared pool
+ oracle software + others pools (no less then 0.2GB)
+ other applications 1GB
+ OS file buffer pools up to 0.07GB
+ ...
----------------------------------------------------------------
conclusion: i have to use mts and does'n matter i like this
desision or not.

(NOTE : i don't like mts)

[sreddy]

Originally posted by KenEwald
I think I'd still opt for a bigger box and keep the points of failure down.

CEO says: in the process of cutting down costs every approaval has to go thru CFO... What option DBA/SYSADMIN left with ?

[KenEwald]

True.

[Queyon]

But some third party software, like TOAD and PL/SQL Developer, don't work properly with MTS. Some internal errors were generated in those softwares.

Queyon

[pando]

Originally posted by Queyon
But some third party software, like TOAD and PL/SQL Developer, don't work properly with MTS. Some internal errors were generated in those softwares.

Queyon

sorry but that only happens to you