The switch from dedicated server to shared server is easy. You begin by editing the init.ora file for your instance. You will need to either add in a line for dispatchers, or edit the existing line (When creating a database with DBCA on Windows, Oracle adds in a line even if you don't request dispatchers. So, be aware of the fact that you may just be changing that line).
At minimum, you will need:
dispatchers="(protocol=tcp)"
A more complete configuration would be:
dispatchers="(protocol=tcp)(dispatchers=2)(service=mydatabase)"
The (dispatchers=2) that you see tells oracle to spawn 2 dispatchers at startup.
Configuring dispatchers can get more elaborate. Unless you have experience and documentation for setting up a more advanced configuration, it is probably better to allow Oracle to give you default values for everything else. A complete list of possible dispatcher parameters is here:
dispatch_clause::=
(PROTOCOL = protocol) |
(ADDRESS = address) |
(DESCRIPTION = description )
[options_clause]
options_clause::=
(DISPATCHERS = integer |
SESSIONS = integer |
CONNECTIONS = integer |
TICKS = seconds |
POOL = {1 | ON | YES | TRUE | BOTH |
({IN | OUT} = ticks) | 0 | OFF | NO | FALSE | ticks} |
MULTIPLEX = {1 | ON | YES | TRUE | 0 | OFF | NO | FALSE | BOTH | IN | OUT} |
LISTENER = tnsname |
SERVICE = service |
INDEX = integer
)
There are some other parameters you will likely want to set as long as you are working on dispatchers. Here is an example:
###########################################
# DISPATCHERS
###########################################
dispatchers="(protocol=tcp)(dispatchers=2)(service=test)"
## shared servers on startup
shared_servers=2
## maximum shared server sessions
shared_server_sessions=200
## Maximum Shared Servers
max_shared_servers=20
## Maximum Dispatchers
max_dispatchers=20
Once you have changed your init.ora file and bounced your database, how can you tell if connections are going through dispatchers? There are a number of dynamic performance views that can tell you this, for example v$session, v$dispatcher, v$queue. To keep things easy, I just create my own view that gives me the data I like to see:
CREATE OR REPLACE FORCE VIEW SYSTEM.CURRENT_CONNECTIONS
(SID, SERIAL#, USERNAME, OSUSER, STATUS,
"SCNDS NOT ACTIVE", DISPATCHER)
AS
SELECT /* ©2004 by Edward Stoever, */
s.SID, s.serial#, s.username, s.osuser, s.status,
DECODE (s.username, NULL, 0, s.last_call_et) "SCNDS NOT ACTIVE",
NVL (d.NAME, 'none') "DISPATCHER"
FROM v$session s, v$dispatcher d
WHERE s.paddr = d.paddr(+)
ORDER BY status ASC, last_call_et ASC;
CREATE PUBLIC SYNONYM CURRENT_CONNECTIONS FOR SYSTEM.CURRENT_CONNECTIONS;
Sample output:
select * from system.current_connections;
SID SERIAL# USERNAME OSUSER STATUS SCNDS NOT ACTIVE DISPAT
------ ------- ------------ ------------ -------- ---------------- ------
15 167 SYSTEM STOEVER ACTIVE 0 none
11 10 GENERAL GURJOBS_TEST ACTIVE 12535 none
1 1 @ ORACLE ACTIVE 0 none
2 1 @ ORACLE ACTIVE 0 none
3 1 @ ORACLE ACTIVE 0 none
4 1 @ ORACLE ACTIVE 0 none
5 1 @ ORACLE ACTIVE 0 none
6 1 @ ORACLE ACTIVE 0 none
7 1 @ ORACLE ACTIVE 0 none
8 1 @ ORACLE ACTIVE 0 none
18 176 WTAILOR jbautista INACTIVE 12 D000
16 191 WTAILOR jbautista INACTIVE 132 D001
10 212 WTAILOR jbautista INACTIVE 134 D001
13 119 WEB_USER SYSTEM INACTIVE 314 D000
14 20 SYSTEM vlugo INACTIVE 721 D001
Now you can see who is connecting and how, either via a dispatcher or via a dedicated connection.
Does this mean that all connections from now on will be through a dispatcher? No. There are plenty of cases in which you will want or even require a connection that is dedicated. For example, to shutdown the database, a dedicated connection is required. Also, many processes are recource intensive and will perform better with a dedicated connection. To create a dedicated connection, you will need to edit the TNSNAMES.ORA file on the machine from which the connection originates. Here is an example:
### SHARED CONNECTION TO TEST DATABASE
TEST_SHARED =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = alpha2)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = SHARED)
(SERVICE_NAME = test)
)
)
### DEDICATED CONNECTION TO TEST DATABASE
TEST_DEDICATED =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = alpha2)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = test)
)
)
Now, to connect via a dispatcher, try this:
SQLPLUS scott/tiger@test_shared
to connect via a dedicated server process, try this:
SQLPLUS scott/tiger@test_dedicated
|
