Sagi, September 16, 2002 - 12:08 pm UTC
Thank for the explanation. I Got it.
Regards,
Sagi.
Sagi, September 16, 2002 - 12:34 pm UTC
I have one more confussiong. You said
"Now, since you have 5 dispatchers and since by definition only a SINGLE process/thread can ever listen on a given IP+PORT combination -- you would already have an issue (5 dispatchers need 5 ports!)"
Did you mean to say that in TNSNAMES.ora file, I whould have had 5 PORTs defined like:
mts_conn =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.11.225.203)(PORT=1528))
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.11.225.203)(PORT=1529))
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.11.225.203)(PORT=1530))
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.11.225.203)(PORT=1531))
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.11.225.203)(PORT=1532))
)
(CONNECT_DATA =
(SERVICE_NAME = ORCL) (server=shared)
)
)
or Should I modify INIT.ora as
mts_dispatchers="(address=(protocol=tcp)
(host=10.11.225.203)(port=1528))(dispatchers=1)"
mts_dispatchers="(address=(protocol=tcp)
(host=10.11.225.203)(port=1529))(dispatchers=1)"
mts_dispatchers="(address=(protocol=tcp)
(host=10.11.225.203)(port=1530))(dispatchers=1)"
mts_dispatchers="(address=(protocol=tcp)
(host=10.11.225.203)(port=1531))(dispatchers=1)"
mts_dispatchers="(address=(protocol=tcp)
(host=10.11.225.203)(port=1532))(dispatchers=1)"
Also I need to have my LISTNER.ora modified to listen on other PORTS ie. 1529 to 1532. Right?
Regards,
Sagi
September 16, 2002 - 7:40 pm UTC
No, LISTENER = one IP+PORT needed (one process, one thing)
5 dispatchers = you need 5 ports on a single IP since no more then one dispatcher can listen on any given PORT+IP address.
No modifications to the listener.ora file please! Nothing wrong with what you had here, you are trying to configure DISPATCHERS, not listeners.
why my init.ora is different?
zl, September 16, 2002 - 9:44 pm UTC
my init.ora is:
mts_dispatchers="TCP,5"
mts_max_dispatchers=20
mts_servers=10
mts_max_servers=80
mts_service=itsm
mts_listener_address="(ADDRESS=(PROTOCOL=TCP) (HOST=134.163.6.21))"
and the port is fixed, equal the define of listener.ora, no change need to tnsnames.ora.
September 17, 2002 - 7:51 am UTC
your init.ora is different cause you are not Sagi.
Why would you expect it to be the same?
Sagi, September 17, 2002 - 7:47 am UTC
Hi Tom!
I am sorry, could not get your point. You said:
"
No, LISTENER = one IP+PORT needed (one process, one thing)
5 dispatchers = you need 5 ports on a single IP since no more then one
dispatcher can listen on any given PORT+IP address.
No modifications to the listener.ora file please! Nothing wrong with what you
"
So you mean to say that I will have to modify like this
a) My INIT.ORA as
mts_dispatchers="(address=(protocol=tcp)
(host=10.11.225.203)(port=1528))(dispatchers=1)"
mts_dispatchers="(address=(protocol=tcp)
(host=10.11.225.203)(port=1529))(dispatchers=1)"
mts_dispatchers="(address=(protocol=tcp)
(host=10.11.225.203)(port=1530))(dispatchers=1)"
mts_dispatchers="(address=(protocol=tcp)
(host=10.11.225.203)(port=1531))(dispatchers=1)"
mts_dispatchers="(address=(protocol=tcp)
(host=10.11.225.203)(port=1532))(dispatchers=1)"
or
b) My TNSNAMES.ora should have
mts_conn =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.11.225.203)(PORT=1528))
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.11.225.203)(PORT=1529))
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.11.225.203)(PORT=1530))
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.11.225.203)(PORT=1531))
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.11.225.203)(PORT=1532))
)
(CONNECT_DATA =
(SERVICE_NAME = ORCL) (server=shared)
)
)
q1) Which one is correct. Bullet a or b?
q2) Since I wanted to have one dedicated port and the other one for MTS. I need to have my LISTNER.ora as below. Right?
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.11.225.203)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.11.225.203)(PORT = 1528))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)
)
Thanx in advance.
Regards,
Sagi.
September 17, 2002 - 8:14 am UTC
a and b are apples and zebras.
one has *nothing* to do with the other.
a) will be fine - ONCE you stop using 1528 which is already being used by your LISTENER
b) would break your system. clients would not be able to connect.
Your client uses the tnsnames.ora to find a listener. Your database, after it starts the dispatchers, registers their addresses with the listener.
Your client will connect to the listener. The listener is running on some fixed IP address with a well known Port. The listener will look at the pool of dispatchers it has available and assign one to your client. Your client will stop talking to the listener and will connect to the dispatcher.
Please -- read the networking guide. All of this is actually explained in there.
(and no, you don't need two ports to do dedicated versus shared either, you can but you don't NEED to. A single listener port can do both)
zl, September 17, 2002 - 12:00 pm UTC
you say "your init.ora is different cause you are not Sagi", but what's the meaning for "sagi"
September 17, 2002 - 12:45 pm UTC
Sagi is the person who posted the original init.ora -- they asked the question.
(.)(.)
Juan Guascarancas Pena, September 17, 2002 - 1:13 pm UTC
>>>> QUOTE:
To force the exact location of the dispatchers, add the PORT as follows:
mts_dispatchers="(address=(protocol=tcp)
(host=144.25.16.201)(port=5000))(dispatchers=1)"
mts_dispatchers="(address=(protocol=tcp)
(host=144.25.16.201)(port=5001))(dispatchers=1)"
>>>>> END QUOTE.
And this is the best way to set up Oracle to be used through a firewall. You must know which ports to allow...
A reader, December 20, 2002 - 8:39 pm UTC
Hi Tom,
I installed oracle 9iR2 and started the database.I found it is using MTS (shared server) configuration.
Here is the parameters
SQL> sho parameter mts
NAME TYPE VALUE
------------------------------------ ------- -------------------
mts_circuits integer 170
mts_dispatchers string (protocol=TCP)
mts_listener_address string
mts_max_dispatchers integer 5
mts_max_servers integer 20
mts_multiple_listeners boolean FALSE
mts_servers integer 1
mts_service string Zebra
mts_sessions integer 165
I want to use DEDICATED configuration.
How can I do that ?
Thanks
December 20, 2002 - 9:01 pm UTC
unset the parameters
or
put (server=dedicated) in the tnsnames.ora entry
A reader, December 21, 2002 - 7:27 am UTC
Hi ToM,
I set entry IN TNSNAMES.ORA but i am getting error message
SQL> conn gaurang/gaurang@ora9
ERROR:
ORA-12500: TNS:listener failed to start a dedicated server process
Warning: You are no longer connected to ORACLE.
Hre is my TNSNAMES entry--
ora9.WORLD=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = TCP)
(HOST = techsupport)
(PORT = 1521)
)
)
(CONNECT_DATA = (SID=zebra)(SERVICE_NAME=zebra)
(SERVER = DEDICATED)
)
)
If I remove (SERVER = DEDICATED) it is connecting to database is shared server mode.
Thanks.
December 21, 2002 - 9:05 am UTC
get rid of either the sid or the servicename -- you use either or, not both.
use net assistant to get a well formed TNSNAMES.ORA entry.
Reader
A reader, January 08, 2003 - 1:33 am UTC
if tnsnames.ora not presnt on client?
learningOracle, January 30, 2003 - 10:46 pm UTC
If the client machine does not have a tnsnames.ora (ex. a browser based application), how would one find out the mode they are connected in?
ie. connected in dedicated server mode or shared server mode?
Is there a tnsnames.ora on the server that would have this info?
or is it present in the oracle ini file, if so what parameter is it?
Thank you
January 31, 2003 - 7:34 am UTC
the client isn't the browser then.
The client is an application server. The browser is the client of the app server, the app server IS the client.
just select SERVER from v$session as shown above -- that is the only way to truly know. The tnsnames.ora is the way to request a connection, SERVER shows exactly how a session is established.
Roll of dispatcher and server process.
Reader, April 28, 2003 - 12:18 am UTC
Hi Tom
This might be a silly and very basic question but pls. let me ask.
In your book you have written that when user request for a connection, TNS service sends the request to Listener and Listener connects it to the dispatcher and then dispatcher sends the request to any free shared server process.
Suppose If we have 10 dispatcher and 10 shared server and 10 users are connected to 10 dispatchers and processing is going on for all the connections then what will happen with 11th user. Would it get connection or not?
Pls explain me the roll and functionality of dispatchers and shared server processs?
Thanks.
April 28, 2003 - 7:21 am UTC
you would in this configuration have a single dispatcher and 10 shared servers. I cannot imagine having parity between dispatchers and shared servers.
A single dispatcher is designed to handle hundreds of concurrent connections.
The dispatcher takes a request from a client and puts it on a queue.
The dispatcher sees responses in the queue and sends them to the client.
A shared server sees a request in the queue and processes it.
A shared server puts a response back on the queue.
Great, can you help me about this problem?
Jack, June 12, 2003 - 5:25 pm UTC
Hi Tom,
I use intermedia text with Oracle 8.1.7.4.
1. Do I need to set MTS for IPC in order to make intermedia text work? Or I just need to use TCP for MTS. Oracle told me there is no need to use IPC from 8.1.7 for MTS?
2. After I setup MTS and use "select username,server,status from v$session", it's shared server. But Toad in my local PC doesn't work now and also the Net8 Assistant service naming testing shows a problem to connect to server1 with error: ORA-12535 TNS:operation timed out.
tnsping works fine from local PC.
Here are my config files:
initORCL.ora:
db_name = "ORCL"
db_domain = SERVER1
instance_name = ORCL
service_names = ORCL.SERVER1
...
mts_dispatchers="(address=(protocol=tcp)(host=server1))(dispatchers=1)"
mts_max_dispatchers=10
mts_servers=2
mts_max_servers=10
mts_listener_address="(ADDRESS=(PROTOCOL=tcp)(host=server1)(port=1522))"
listener.ora:
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = server1)(PORT = 1522))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/app/oracle/product/8.1.7)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = ORCL.SERVER1)
(ORACLE_HOME = /u01/app/oracle/product/8.1.7)
(SID_NAME = ORCL)
)
)
tnsnames.ora:
ORCL.SERVER1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.8.25)(PORT = 1522))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = SERVER1)(PORT = 1522))
)
(CONNECT_DATA =
(SERVICE_NAME = ORCL.SERVER1
(SERVER = SHARED)
)
)
LISTENER_ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = SERVER1)(PORT = 1522))
)
(CONNECT_DATA =
(SERVICE_NAME = ORCL.SERVER1)
(SERVER = SHARED)
)
)
INST1_HTTP =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = SERVER1)(PORT = 1522))
)
(CONNECT_DATA =
(SERVER = SHARED)
(SERVICE_NAME = ORCL.SERVER1)
(PRESENTATION = </code>
http://admin <code>
)
)
extproc_connection_data =
(DESCRIPTION=
(ADDRESS_LIST =
(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC0))
)
(CONNECT_DATA=
(SID=PLSExtProc)
(PRESENTATION = RO)
)
)
Local tnsnames.ora:
SERVER1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.8.25)(PORT = 1522))
)
(CONNECT_DATA = (SERVICE_NAME = ORCL.SERVER1)(SERVER = SHARED))
)
We use firewall to open only port 1522. It works fine from web application.
Please help. Thanks a lot!
Jack
June 12, 2003 - 7:58 pm UTC
1) if they said specifically "no", that would be the answer. They are fairly accurate with setup and configuration issues.
2)
your firewall has 1522 open. great, tnsping can get to a listener.
HOWEVER, the dispatcher, the thing you really ultimately need to get to -- isn't (by definition cannot be) on 1522. It is on a wildcard port (some number, unknown, the OS gives it to us). You can LIMIT this range of ports, but that is about it.
You would be looking at setting up CMAN (connection manager) at that point to connect via shared server thru a firewall.
Thanks Tom, just want to confirm something?
Jack, June 13, 2003 - 11:38 am UTC
Hi Tom,
Thanks, I just want to confirm something from you:
1. MTS dispatcher must use different port number with Listener?
2. If I use a special port for dispatcher, such as 1525, and open that port in firewall, is that ok?
3. CMAN will fix the client PC Net8 connection. I don't need to change configuration files in server except updating the tnsnames.ora file with en entry using the CMAN.
Thanks,
Jack
June 13, 2003 - 12:29 pm UTC
1) yes, they are separate processes
2) probably
3) using cman will fix the port redirection that would otherwise occur. You run cman behind the firewall, it connects as normal on your behalf (it gets redirected). you use a single port to connect to it. you would just change the tns connect string, yes.
Thanks, it works with dedicated server for PC client.
Jack, June 13, 2003 - 1:01 pm UTC
Hi, Tom,
I changed the local PC's tnsnames.ora to use (CONNECT_DATA = (SERVICE_NAME = ORCL.SERVER1)(server=dedicated))
Now, the client PC(Toad and sqlplus etc.) can connect to server.
But just find something like below:
SQL> select username,server,status from v$session:
ELI SHARED INACTIVE
ELI NONE INACTIVE
ELI NONE INACTIVE
ELI SHARED INACTIVE
I remember in one of your post, you said the inactive with shared is not correct. I am searching your post now.
Thanks,
Jack
June 13, 2003 - 1:05 pm UTC
i've not seen inactive from a shared connection, no.
it would be especially confusing since you KNOW at least one must be active -- the one running the query.
(and oh, none of your connections there are dedicated, they are all shared...)
shared and dedicated
A reader, August 18, 2003 - 3:59 pm UTC
When would you suggest using a shared connection
as well as dedicated connections? Is this a common
configuration?
Thanx!
August 18, 2003 - 8:36 pm UTC
you use shared when you run out of "ompff" on the machine -- when you cannot get another dedicated server connection. when you need to connect one more session but cannot do so using a process/user
it takes about the same ram (shared server just moves the UGA from the PGA into the SGA) but less processes. It can reduce the resources on tha machine in that fashion -- but, the code path is longer, you don't want to use it till you have to.
Or, if you are using a feature that requires it -- like database link concentration for example...
mixed environment
Vipin, August 19, 2003 - 2:14 pm UTC
Hi Tom,
Suppose my system is 90% strictly OLTP and 10% "not much" of an OLTP (like it as a report rendering functionality which runs a big query), how can I go for mixed enviroment here.
Basically my doubt is how can we configure both MTS and decicated together.
August 19, 2003 - 6:12 pm UTC
it is always that way if you have mts setup? if you have mts setup, you can connect dedicated
just use server=dedicated in the tns setup
Will response time be noticeably slower?
Kamal Kishore, August 19, 2003 - 10:26 pm UTC
You said above:
<quote>
but, the code path is longer, you don't want to use it till you have
to.
</quote>
Would that mean that applications that used to connect using DEDICATED connection (and now switch to SHARED connection mode) will see a noticeable difference in application response time?
Is that a concern we should be worried about?
Thanks,
August 20, 2003 - 8:56 pm UTC
if you are on a machine that is running "top speed" with dedicated server -- going shared will only make it "slower".
shared server is for when you need to get that extra connection.
Vipin, August 20, 2003 - 9:36 am UTC
Hi Tom,
You said we have to use server=dedicated in tns, but how will ensure that for certain package executions I must use dedicated in my Application (like the one which I indicated as a report generation) and for all others I need to use MTS (say for online workflow activities.)
If I set up server = shared in the tnsnames, in Application server it will always use MTS (provided MTS_ parameters are also set in init.ora file) and server=dedicated will always use dedicated right? Or if I am wrong here please correct me.
August 21, 2003 - 8:15 am UTC
you will have two tns connect strings.
one with server=dedicated
one without.
they (the app) will pick which connection type they want based on which tns name they use.
ORA-24777 use of non-migratable database link not allowed
Kamal Kishore, August 20, 2003 - 10:15 pm UTC
Hi Tom,
The reason I asked the question about the switch to MTS configuration is because at client site they hit the error:
=========================================================
ORA-24777 use of non-migratable database link not allowed
Cause: The transaction, which needs to be migratable between sessions, tried to access a remote database from a non-multithreaded server process.
Action: Perform the work in the local database or open a connection to the remote database from the client. If the multithreaded server option is installed, connect to the Oracle instance through the dispatcher.
==========================================================
The only solution around this seems that MTS be configured in the database (as suggested) and the application establish a SHARED connection.
This would then mean that the applications that used to connect using DEDICATED server would now connect using SHARED server instead.
This switch seems inevitable. That is the reason I'm concerned.
Thanks,
August 21, 2003 - 5:51 pm UTC
what exactly are you doing that requires it? XA?
MTS???
Kamal Kishore, August 21, 2003 - 11:32 pm UTC
Hi Tom,
The web application uses the Microsoft Transaction Server (also called MTS) and MSDTC (Microsoft Transaction Coordinator) to establish a connection to Oracle.
What is happening is that the Microsoft MTS property is set to require transaction and then it calls a Oracle stored procedure. This stored procedure then calls another stored procedure in a remote database using database link. This is when we get the above mentioned error.
The end result is that Microsoft MTS (with transaction support enabled) with Oracle stored procedure calls using database link under the Oracle DEDICATED server setup - does not work. It requires that the connection be esablished using the Oracle SHARED server configuration.
We are currently looking into enabling Oracle SHARED server to circumvent this issue (or any other approach to avoid this error and still keep the DEDICATED server setup).
Thanks,
4 dispatchers on one ip+port
A reader, November 21, 2003 - 4:00 am UTC
hi
using 9.2.0
4 dispatchers working with One PROT ??????
Dispatchers='(ADDRESS=(PROTOCOL=TCP)(HOST=LC)(PORT=1430))(Dispatchers=4)'
SQL> Select * From v$dispatcher;
NAME NETWORK
---- --------------------------------------------------------
D000 (ADDRESS=(PROTOCOL=TCP)(HOST=LC)(PORT=1430))
D001 (ADDRESS=(PROTOCOL=TCP)(HOST=LC)(PORT=1430))
D002 (ADDRESS=(PROTOCOL=TCP)(HOST=LC)(PORT=1430))
D003 (ADDRESS=(PROTOCOL=TCP)(HOST=LC)(PORT=1430))
Thanx
November 21, 2003 - 4:51 pm UTC
sure? why not?
Re :
a reader, November 21, 2003 - 10:21 pm UTC
hi .. You said
"5 dispatchers = you need 5 ports on a single IP since no more then one dispatcher can listen on any given PORT+IP address."
Has this restriction been lifted in 9iR2.
Q2. Sir, If system is loaded with many concurrent connections [ PGA ] then we can use pga aggr target then should I go for shared Server.
Actulay I am very much confused with pga/uga/mts.
Thanx
November 21, 2003 - 10:46 pm UTC
platform?
q2) do you have expert one on one oracle?
Re:
a reader, November 22, 2003 - 1:46 am UTC
I am using winnt2000 / 91R2.
November 22, 2003 - 8:57 am UTC
things are different on windows, its all one big process with threads.
artificial deadlocks with MTS
a reader, November 22, 2003 - 1:47 am UTC
hello Sir,
using win2000/9ir2
I was reading the third chapter of your new book. In which you mentioned ..
" There is a potential for artificial deadlocks. These occur because once a shared server
starts processing a request, it will not return until it is finished with it. Hence, if the
server will block. Suppose the blocking session was idle for a period of time (not doing
any work in the database so it doesn’t have a shared server allocated to it) and all of
the other shared servers get blocked as well by this idle session (or any other session), then
when the session holding the lock attempts to commit or rollback (which would release the
locks), it will hang because there are no more free shared servers. This is an artificial
deadlock situation."
And I did this scenario, but couldn’t get the desired result.
SQL>
SQL> Select count(*) from a;
COUNT(*)
----------
1256
SQL> delete from a;
1256 rows deleted.
SQL> select count(*),server,status from v$session group by server,status;
COUNT(*) SERVER STATUS
---------- --------- --------
1 SHARED ACTIVE
6 DEDICATED ACTIVE
SQL> show parameter server
fal_server string
max_shared_servers integer 20 <<--
mts_max_servers integer 20
mts_servers integer 3
parallel .... deleted some text ...
shared_server_sessions integer 33
shared_servers integer 3
Then I made 20+ separate sql*plus sessions and issued this command 'delete from a'
They all got blocked.
SQL> select count(*),server,status from v$session group by server,status;
COUNT(*) SERVER STATUS
---------- --------- --------
5 NONE INACTIVE
20 SHARED ACTIVE
6 DEDICATED ACTIVE
Then in the original session I issued this command ...
"Rollback"
my session didn’t get blocked.
Where was I wong?
Thanks..
November 22, 2003 - 9:01 am UTC
you are using a mix of dedicated and shared servers.
You need to make sure you are using only shared servers for your testing -- I cannot tell that your current session (the blocker) was in fact using shared server.
re:
a reader, November 24, 2003 - 3:26 am UTC
sir,
All the sessions and blocker are connected through shared server
except 6 dedicated conn which are background processes.
November 24, 2003 - 8:17 am UTC
have the session that did the rollback do the query on v$session instead -- see if you don't have 21 shared connections
MTS Configuration - Dispatchers
Prasad Jayakumar, February 04, 2005 - 6:27 am UTC
Oracle Version : 8.1.7.4
My init.ora entry for MTS setup is
mts_dispatchers = "(ADDRESS=(protocol=TCP)(host=oracle1)(port=2000))(disp=1)"
mts_dispatchers = "(ADDRESS=(protocol=TCP)(host=oracle1)(port=2001))(disp=1)"
mts_max_dispatchers = 5
mts_max_servers = 20
mts_servers = 12
This creates two dispatchers initially.
After database startup, If I try to increase the number of dispatchers using the following,
alter system set mts_dispatchers='(ADDRESS=(protocol=TCP)(host=oracle1)(port=2010))(disp=1)'
I get this error
ORA-00105: dispatching mechanism not configured for network protocol (address=(protocol=tcp)(host=oracle1)(port=2010))
Why is it so? What's the way to increase the number of dispatcher without restart?
As such I have not changed the network protocol, only the port number is changed.
Thank you
Prasad
February 04, 2005 - 11:42 am UTC
ops$tkyte@ORA9IR2> alter system set max_dispatchers=10;
alter system set max_dispatchers=10
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified
you cannot do that online.
MTS Configuration - Dispatchers
Prasad Jayakumar, February 07, 2005 - 4:51 am UTC
Hi Tom,
Thanks for the follow up.
I didn't try to increase the maximum number of dispatchers. I was actually trying to add one more dispatcher.
alter system set
mts_dispatchers='(ADDRESS=(protocol=TCP)(host=oracle1)(port=2010))(disp=1)'
I get this error
ORA-00105: dispatching mechanism not configured for network protocol
(address=(protocol=tcp)(host=oracle1)(port=2010))
My current setting had max_dispatchers=5 and mts_dispatchers=2
I am getting ORA-00105 error, not ORA-02095
Thank you
February 07, 2005 - 5:35 am UTC
you can increase the disp=, but you haven't configured any dispatchers on that port before startup, so you won't be able to afterwards.
dispatchers init parameter
Parag Jayant Patankar, May 26, 2005 - 11:29 am UTC
Hi Tom,
I am trying to understand shared server configuration. For this reason, I tried to configure 5 dispatchers from port no 60000 onwards. I have configured following parameters in init file for oracle 9iR2 on aix 5.2
dispatchers="(address=(protocol=tcp)(host=mumsa128200))(dispatchers=1)(port=60000)"
dispatchers="(address=(protocol=tcp)(host=mumsa128200))(dispatchers=1)(port=60001)"
dispatchers="(address=(protocol=tcp)(host=mumsa128200))(dispatchers=1)(port=60002)"
dispatchers="(address=(protocol=tcp)(host=mumsa128200))(dispatchers=1)(port=60003)"
dispatchers="(address=(protocol=tcp)(host=mumsa128200))(dispatchers=1)(port=60004)"
large_pool_size=10000000
but when I doing select
20:50:01 SQL> select network from v$dispatcher
20:50:06 2 /
NETWORK
--------------------------------------------------------------------------------
(ADDRESS=(PROTOCOL=tcp)(HOST=mumsa128200.in.net.intra)(PORT=58404))
(ADDRESS=(PROTOCOL=tcp)(HOST=mumsa128200.in.net.intra)(PORT=58406))
(ADDRESS=(PROTOCOL=tcp)(HOST=mumsa128200.in.net.intra)(PORT=58407))
(ADDRESS=(PROTOCOL=tcp)(HOST=mumsa128200.in.net.intra)(PORT=58408))
(ADDRESS=(PROTOCOL=tcp)(HOST=mumsa128200.in.net.intra)(PORT=58409))
it is showing different port nos than init parameters.
Q1. Will you pl tell me why ?
Q2. When I try to set port no starting from 59000 it is giving me following error
ERROR:
ORA-12505: TNS:listener could not resolve SID given in connect descriptor
Is it because it can not get thse port nos or because of something else ?
Q3. Is it possible to find out oracle using which are port nos ?
regards & thanks
pjp
shared server connections
Parag Jayant Patankar, May 27, 2005 - 10:28 am UTC
Hi Tom,
Thanks for your answer to me regarding port no. After doing necessary changes it is working fine. As I am new to shared server configuration, I am asking this question to clear my concept. I had configured shared server parameters in init, tnsnames and tried to connect 3 different sessions using shared tnsnames.ora entry but I see only one connection ( I had executed some sqls to just check that status is getting changed or not ) with "SHARED"
1* select username, server from v$session where username is not null
19:48:09 SQL> /
USERNAME SERVER
------------------------------ ---------
PARAG SHARED
PARAG NONE
PARAG NONE
PARAG NONE
19:49:53 SQL> show parameter servers
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
max_shared_servers integer 20
mts_max_servers integer 20
mts_servers integer 10
parallel_max_servers integer 80
parallel_min_servers integer 0
shared_servers integer 10
Q1. Will you pl tell me why other session connections are showing "NONE" ?
Q2. While referring "Reference" manual it is saying that "SERVER" column, it may also have value "PSEDUO". Can you explain what it is ?
regards & thanks
pjp
May 27, 2005 - 12:30 pm UTC
q1) they are inactive and not using a server right now. only when you are active will they show a server being used. right now they are "connected" but the server is "none"
q2) you'll see those when the server is killed for example, using various connection pool/concentration features of Oracle net.
Can you explain more ?
Parag Jayant Patankar, May 30, 2005 - 10:34 am UTC
Hi Tom,
Thanks for answering my question in this thread.
You have given me an answer =>
: They are inactive and not using a server right now. only when you are active will they show a server being used. right now they are "connected" but the server is "none".
Is it because only one dispatcher is getting used or because of something else ?
Suppose I try to connect to database ( sessions as shared servers ) and see all ther connections server as "SHARED" how can I do this ?
regards & thanks
pjp
May 30, 2005 - 11:10 am UTC
No, it is because the session is idle, it is not using a SERVER, so the SERVER is none. If you make that session active, it'll show a server as SHARED.
shared server connections, when idle show "none" as the server.
in order to see them all shared, have them all become active, make them run a long running query.
SQL Relay and Oracle's Shared Server process
Logan Palanisamy, June 03, 2005 - 3:12 pm UTC
Tom,
Someone is advocating that we use SQL Relay (</code>
http://sqlrelay.sourceforge.net/sqlrelay/ <code> to improve connection related delays.
My questions are:
1. Can we not use Oracle's Shared Server (MTS) configuration instead of SQL Relay?
2. Does "SQL Relay" offer anything more than MTS?
3. Does it make sense to use SQL Relay if the database is already configured for MTS?
Thanks.
June 03, 2005 - 5:20 pm UTC
it is a connection pool for middle tier software.
Are you writing middle tier software? they generally do need some sort of connection pooling software but most every middle tier "comes with" such a thing.
ORA-12571 - What?
Vikas, July 21, 2005 - 2:40 am UTC
Dear Mr Kyte
I while installing Oracle9iR2 (DBA Ent edn, with transactition processing Options) on one of the machines (Win XP), am facing a problem at the time of creaion of Default Oracle databse during the installation process.
I am frequently getting the ORA - 12571 error message, (TNS Packet Writer Failure). I ignored it and resumed my installation. Later on whebn i tried to create a New Database using DBCA, I am again getting the the same probelem and other problems related to it, such as...
ORA-03113(end of file on communication channel) I ignored...
ORA-24234 (service handler not initialized) I igored...
ORA-01041 (internal error, hostdef extension doesn't exist).. I ignored...
Can you pls tell...
Why we are facing these problems?
What actually is the reason behind these errors?
Is there something wrong with me installtaion files?
How can we resolve these problems?
Is there any way out to get out of this, or shall I go for fresh installation?
(Note: I was having Oracle9i previously installed on this machine but, we had Uninstalled the same using the Oracle9i Universal Installer. We have ever deleted/removed the oracleservices that were running on this machine using the Registry, but now when we are going on with the fresh insallation we are facing above problems.)
Pls help me out on the above issue.
Thanx,with kindest regards.
Vikas.
July 21, 2005 - 7:52 am UTC
please utilize support for installation/configuration issues.
Chris Wrigley, September 19, 2005 - 12:38 pm UTC
Tom, I have found this thread useful so far, thank you. I refer you to these comments you made;
"you use shared when you run out of "ompff" on the machine -- when you cannot get another dedicated server connection. when you need to connect one more session but cannot do so using a process/user. It takes about the same ram (shared server just moves the UGA from the PGA into
the SGA) but less processes. It can reduce the resources on tha machine in that fashion -- but, the code path is longer, you don't want to use it till you have to ..."
and
"if you are on a machine that is running "top speed" with dedicated server -- going shared will only make it "slower". shared server is for when you need to get that extra connection."
and
"things are different on windows, its all one big process with threads."
We are currently running in "dedicated server" mode on a Windows 2000 server, Oracle 9.2.0.5, but it's getting hammered with an ever-increasing session count and regularly climbs to over 320 plus sessions during peak. Avg uga memory is 169,132. Max is 3,331,572. Max open cursors is 10, Avg is 1.2
Our system's shared pool free memory drops radically throughout the day, slows the system and although rare, sometimes halts user sessions with "out of memory" errors.
Our server seems to be using roughly 3GB ram out of the 4GB installed although I believe Oracle itself can only use up to 2GB of memory on Windows.
The online Oracle FAQ confuses me by saying: "MTS (Multithreaded Server) is an Oracle server configuration that uses less memory." (ref: </code>
http://www.orafaq.com/glossary/faqglosm.htm
So will switching to MTS save us some memory or not?! (I'm thinking not). What's the best way of determining if making the switch is the best solution given the way we are using the Database?
Thank you for any info, it is much appreciated.
PS. I found this reference very useful for anyone interested in this thread:
http://www.oracleadvice.com/Tips/MTS.htm <code>
September 19, 2005 - 1:41 pm UTC
It can save on PGA memory, especially in older (8i and before) releases. The PGA is per process/thread, reduce the number of processes/threads and you reduce the PGA.
However, you cannot reduce the UGA (uga is user global area, session data). In 9i, pga memory can sometimes be deallocated when not needed anymore.
It can save a bit of ram.
BUT, To make the switch - you would have to configure a large_pool big enough to hold all of your UGA's at runtime, you would have to figure out the max number of concurrent sessions and the max amount of ram they would need and configure that much from the get go (meaning - this memory is always allocated to the large pool)
and if you undersize it - the large pool - users will get allocation errors there.
As for the shared pool - are you using bind variables?
You can get more ram available to windows using /awe and other magic switches or by using the 64bit windows.
coexists shared server and dedicated server
Jianhui, April 12, 2006 - 9:58 pm UTC
Hi Tom,
I remember that I read from your website before about that shared server and dedicated server can be configurated to coexist in one instance, so some OLTP clients can connect to shared server, some report applications can use dedicated server.
I 'm just wonder how to configurate this, could you briefly describe it or post a link? How does a listener know whether to hand over client's request to a dispatcher or to spawn a new dedicated server for this client? It seems something must be set in the client side to tell listener saying "hey, i need dedicated server for my connection or the other way", right?
Thank you always.
April 13, 2006 - 7:35 am UTC
Every Oracle database always supports dedicated server connections.
You may optionally also configure the ability to connect via shared servers - always IN ADDITION to being able to connect via dedicated servers.
You can put server=dedicated or server=shared in the tns connect entry, you can set up differently named services - and use the service name in the connect string.
All in All, you would read:
</code>
http://docs.oracle.com/docs/cd/B19306_01/network.102/b14212/dispatcher.htm#sthref1177 <code>
how to minimize shared server CPU overhead
jianhui, May 09, 2006 - 11:41 am UTC
Hi Tom,
By design, shared server architecture adds extra work load on CPU because of dispatcher queuing and shared server session context switch, we have seen 10-15% cpu load increase comparing before and after we implemented shared server in 9205. However, there is very limited information of how to tune the number of shared servers and dispatchers in order to minimize this extra cpu load, do you have any suggestion for me of how to tune this? Or are they even related?
Second question is about the latch on virtual circuit queues, I have seen number of latch gets equal to almost exactly two times of sum(v$queue.totalq), it seems reasonalble since each queue item gets both read and write latchs. For common queue, it makes sense to acquire latch because it's shared, and for write operation on dispatcher queue, it makes perfect sense too. However, since dispatcher queue is owned by the dispatcher, i dont understand why reading data from dispatcher queue will require a latch? Could you explain why?
Best Regards,
i.e.
SQL> select totalq, latch#, gets, misses, sleeps from
2 (select sum(totalq) totalq from v$queue) q,
3 ( select latch#, gets, misses, sleeps from v$latch where name='virtual circuit queues') l
4 /
TOTALQ LATCH# GETS MISSES SLEEPS
---------- ---------- ---------- ---------- ----------
1871163160 186 3742994804 76775586 3081
SQL> /
TOTALQ LATCH# GETS MISSES SLEEPS
---------- ---------- ---------- ---------- ----------
1871477432 186 3743623471 76792369 3082
SQL> select 1871477432-1871163160, 3743623471-3742994804 from dual;
1871477432-1871163160 3743623471-3742994804
--------------------- ---------------------
314272 628667
SQL> select 628667/314272 from dual;
628667/314272
-------------
2.00039138
SQL> select 3742994804/1871163160 from dual;
3742994804/1871163160
---------------------
2.00035726
May 09, 2006 - 12:20 pm UTC
You may well see a 10-15% (or more) DECREASE in cpu as well - given that OS itself spends lots of time managing lots of processes - depends on how and what you test.
But drives home the point that you would not use shared server unless you have to, until you have to, until you have a workload that says "do it"
It is not a matter of tuning the number of dispatchers and shared servers - it is just a fact that the code path is MUCH longer - instead of:
Hey, dedicated server, do this...
It is
Hey, dispatcher, put this into a queue in the SGA.
And then some shared server, please pick it up and do it, attaching my session information (that the dedicated server already had..)
And then once you did it, put the answer back into a queue.
So that I can be notified it is there by the dispatcher...
And it will retrieve it and unpack it from the queue and send it back to me...
so, you tune the number of dispatchers based on concurrent client connections (one will do for hundreds or even a thousand or more connections, don't need many). And the shared servers based on the degree of concurrency you want (HOW MANY ACTIVE SESSIONS you want at a time).
Someone has to write into the dispatcher queue, and someone else reads from the dispatcher queue.
Shared servers and dispatchers
A reader, November 03, 2006 - 12:32 pm UTC
Tom,
I am on 10gr2.
a) Is it possible to have contention for dispatcher? I guess my system would have to loaded real heavily before that happens.
b) When I increase the number of shared_servers, should I also increase the SGA since the UGA is now in SGA? Is there any guideline/best practice on how much to increase the SGA?
c) Is there a way to find out if I need to create more dispatchers and shared servers?
d) Is there a guideline/best practice on approximate ratio of dispatchers to shared servers?
Thanks for you help as always...
November 03, 2006 - 1:13 pm UTC
1) correct, it would.
2) the number of shared servers not as relevant as the number of shared server sessions - they consume UGA
3) monitoring of wait events.
4) nope, depends on concurrent usage.
To: A reader
Michel Cadot, November 03, 2006 - 2:04 pm UTC
For a quick view you can have a look at busy and idle columns in v$dispatcher and v$shared_server views but they are cumulative counters so wait events are better.
Michel
Shared Server and CPU usage
Emad Kehail, February 03, 2008 - 9:25 am UTC
Hello Tom,
I have lately configured Shared Servers on the Oracle Production server. Most of the time, the Oracle Database were working fine, but when we reached the the peak working hours, the CPU suddenly jumped to 100% and the server machine almost dead!
I switched back to dedicated server, and did the following simulation on my PC, and I could reproduce the problem.
Here are the details.
Dispatchers were configured with connection pooling as follows:
sys@orcl> show parameter disp
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dispatchers string (protocol=TCP)(disp=1)(tick=1)
(pool=(in=10)(out=10))(con=3)(
sess=50)
max_dispatchers integer 5
mts_dispatchers string (protocol=TCP)(disp=1)(tick=1)
(pool=(in=10)(out=10))(con=3)(
sess=50)
mts_max_dispatchers integer 5
The above configuration were done using Oracle Database Configuration Assistant.
Then I have asked my coworker to run multiple SQL*Plus sessions from his PC and connect to Oracle Database installed on my PC.
He configured a batch fine (doload.bat) which invokes SQL*Plus and issue the following SELECT statement:
select *
from all_objects;
He ran his script and could connect to Oracle as shared processes. This was clear by checking the column SERVER in the v$session view.
Now we have 48 shared sessions in the server.
Anyhow, I tried to connect to Oracle using SQL*Plus from my PC and using a connection string as follows:
system/oracle@test
I have done thins 4 times expecting Oracle Server to reject the connections after 50 since my dispatcher should allow only 50 sessions!
Two sessions where connected as shared servers while the rest of them connected as DEDICATED!
I have exited all of them by closing SQL*Plus abnormally, and also the same was done my coworker.
I have queried v$session view, and I have found all the shared sessions are still exists!. I tried to kill some of them, and queried the v$session again and I have got this result:
sys@orcl> select username, server, status
2 from v$session
USERNAME SERVER STATUS
------------------------------ --------- --------
DEDICATED ACTIVE
DEDICATED ACTIVE
DEDICATED ACTIVE
DEDICATED ACTIVE
DEDICATED ACTIVE
DEDICATED ACTIVE
DEDICATED ACTIVE
DEDICATED ACTIVE
SCOTT PSEUDO KILLED
SYS DEDICATED INACTIVE
SYS DEDICATED ACTIVE
SCOTT PSEUDO KILLED
SCOTT PSEUDO KILLED
SYS DEDICATED INACTIVE
SYSTEM NONE INACTIVE
SYS DEDICATED INACTIVE
SCOTT PSEUDO KILLED
SCOTT NONE INACTIVE
SCOTT PSEUDO KILLED
SCOTT NONE INACTIVE
SCOTT NONE INACTIVE
SCOTT PSEUDO KILLED
SCOTT NONE INACTIVE
SCOTT PSEUDO KILLED
SCOTT NONE INACTIVE
SCOTT NONE INACTIVE
SCOTT NONE INACTIVE
SCOTT NONE INACTIVE
SCOTT NONE INACTIVE
SCOTT PSEUDO KILLED
SCOTT NONE INACTIVE
SCOTT NONE INACTIVE
SCOTT NONE INACTIVE
SCOTT NONE INACTIVE
SCOTT PSEUDO KILLED
SCOTT NONE INACTIVE
SCOTT NONE INACTIVE
SCOTT NONE INACTIVE
SCOTT NONE INACTIVE
SCOTT NONE INACTIVE
SCOTT NONE INACTIVE
SCOTT NONE INACTIVE
SCOTT NONE INACTIVE
SCOTT NONE INACTIVE
SCOTT NONE INACTIVE
SCOTT NONE INACTIVE
SCOTT NONE INACTIVE
SCOTT NONE INACTIVE
SCOTT PSEUDO KILLED
SCOTT NONE INACTIVE
SCOTT NONE INACTIVE
SCOTT PSEUDO KILLED
SCOTT PSEUDO KILLED
SCOTT NONE INACTIVE
SCOTT PSEUDO KILLED
SCOTT NONE INACTIVE
SCOTT PSEUDO KILLED
SCOTT NONE INACTIVE
SCOTT NONE INACTIVE
SCOTT NONE INACTIVE
SCOTT PSEUDO KILLED
SCOTT NONE INACTIVE
62 rows selected.
I am wondering why Oracle allowed the system user to logon as DEDICATED when the shared sessions used (50 sessions)!.
Moreover, the CPU on my PC, where Oracle is installed, is suddenly at high usage (60%). There is not any activity at my PC or Oracle now but the CPU is still at high load and when I arrange the processes in the Windows Task manager I can see Oracle is consuming all of this CPU Power.
I have ran the following statements against Oracle:
sys@orcl> ed
Wrote file afiedt.buf
1 SELECT NAME "NAME", SUBSTR(NETWORK,1,23) "PROTOCOL", OWNED,
2 STATUS "STATUS", (BUSY/(BUSY + IDLE)) * 100 "%TIME BUSY"
3* FROM V$DISPATCHER
sys@orcl> /
NAME PROTOCOL OWNED STATUS %TIME BUSY
---- ----------------------- ---------- ---------------- ----------
D000 (ADDRESS=(PROTOCOL=tcp) 50 REFUSE 1.56459497
sys@orcl> ed
Wrote file afiedt.buf
1 SELECT D.NAME, Q.QUEUED, Q.WAIT, Q.TOTALQ,
2 DECODE(Q.TOTALQ,0,0,(Q.WAIT/Q.TOTALQ)/100) "AVG WAIT"
3 FROM V$QUEUE Q, V$DISPATCHER D
4* WHERE D.PADDR = Q.PADDR
sys@orcl> /
NAME QUEUED WAIT TOTALQ AVG WAIT
---- ---------- ---------- ---------- ----------
D000 0 9 1429 .000062981
Finally, the POOLs in the SGA are configured as follows:
sys@orcl> show parameter pool_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
global_context_pool_size string
java_pool_size big integer 16777216
large_pool_size big integer 159383552
olap_page_pool_size integer 33554432
shared_pool_size big integer 25165824
I really can not figure where is the problem, why the CPU is loaded while there is not activity, also, while these sessions are still appear in the v$session.
Looking forward for your help
February 04, 2008 - 3:54 pm UTC
.. but when we reached the the
peak working hours, the CPU suddenly jumped to 100% and the server machine
almost dead!
...
you know, I don't know why people are "surprised" by 100% utilization most of the time...
During peak..
We request our machine...
To do more...
Than it physically is capable of doing.
... I have done thins 4 times expecting Oracle Server to reject the connections
after 50 since my dispatcher should allow only 50 sessions! ...
you lost me here - totally. You have 50 x 5, but even so, you are doing 48 at a time - then they go away.
so what?
... I have queried v$session view, and I have found all the shared sessions are
still exists! ...
I don't see any there - but if they did exist, that would mean your friend that created the script did it wrong - and the sqlplus sessions are not exiting, they are just waiting to exit (eg: task manager, look for the sqlplus's - they will be there)
and when you kill - they stay there until the client acknowledges they were killed, they then go away. So, fix your script.
Are you saying that while the sessions where idle, Oracle the server was consuming 100% of the cpu.
Configure MTS
Ankit, April 20, 2009 - 9:46 am UTC
Hi Tom,
My DB which has a DB Link Configured is facing a problem
we get
ORA-24777 - use of non-migratable database link not allowed
I referred Metalink Doc ID: 397555.1, according to which we have to configure MTS to make the DB link work.
The current ORACLE version of DB is 10.2.0.3
Could you please suggest some resources which explains the configuration for 10g, i found many documnets but mostly referred to older versions of Oracle, also there are some deprecated parameters included in those configuration.
April 21, 2009 - 2:12 pm UTC
please utilize support for configuration/installation stuff.... Open a service request.
if the existing material is deficient, they would be the ones to update it.
Ankit, April 20, 2009 - 1:05 pm UTC
Please ignore my last statement, i got the Shared server documents from Oracle Documnets :)
dispatcher listen timer
aliyar, January 07, 2010 - 1:29 am UTC
Dear Tom ,
Thanks for your great site for DBA world
i have taken awr report .. that shows top wait event is " dispatcher listen timer " ... also ADDM report says that due tp the above wait DB performance affected
could you please help me to resolve the above issue
DB is running in windows
Thanks
Aliyar
January 11, 2010 - 8:23 am UTC
it is a dispatcher startup thing, highly (extremely) unlikely to be affecting you since the dispatchers would already be up and running when your application is going.
Thanks for your valuable support
Aravindkumar Annamalai, January 07, 2010 - 2:07 am UTC
Hi,
My database has been performaing very slow for the last one month.i just generated a AWR report and ADDM report ,.
The ADDM report shows below the two categories:
1.Host Operating system was experiencing significant paging but no particular root causecould be detected.Investigate processes that do not belong to this instance running on the host that are consuming siginficant amount of virtual memory .Also consider more physical memory to the host.
2.Dispatcher listen timer in wait class "network".
And I'm getting the alerts as "Metrics "Database Time Spent Waiting (%)" is at 100 for event class "Network"
Could please let me know what could be the reason for above wait events .How to fix this issue.
DB is running in Windows.
Regards,
Aravindkumar Annamalai
January 11, 2010 - 8:25 am UTC
sounds like an overloaded machine
you have too much going on, the paging would be indicative of that - you are over committing memory (using more than you have) and the dispatcher wait is a wait because the network is so busy, the dispatcher could not open a socket listening endpoint in a reasonable amount of time - hence it goes to sleep, waits a bit, and tries again.
Look at your OS, what is going on on the machine.
shared
A reader, August 18, 2010 - 7:50 am UTC
Tom:
1. What view do you use to see if database is running in dedicated or shared mode?
2. It also seems you can have a db that accepts both and the client tns file can select which mdoe to connect.
is this correct.
August 19, 2010 - 1:50 am UTC
databases do not run in shared or dedicated modes. There is no such "mode"
a database always allows for dedicated server connections - there is no way NOT to support them (so, no need to check - it is "done")
a database may support shared server connections, but to see if the database is accepting shared server connections - do a
$ lsnrctl services
you'll see if the listener is setup and ready to establish a shared server connection or not to the server.
a database can have a mixture of shared and dedicated connections - yes. But it is not a "mode", it is not a "status of the database"
Random Dispatcher port assigned by Netca
Santosh Vijayan, July 10, 2018 - 6:45 am UTC
Dispatcher is configured in database to pick up 5 ports. There is no hardcoded ports in parameter. Given that not all ports may be open or have firewall issue, how does netca dynamically assign ports to dispatcher. How does co-ordination happens between NetCA and OS ?. Does Netca checks for available port and assign to Dispatcher?.
Can you provide some details on how this work?
Thanks
Santosh