Oracle Shared Server (MTS) vs Dedicated mode

============================
General
============================
The Server can be configured to run at Dedicated or Shared mode,
The Client can be also configured, to run at Dedicated or Shared mode.
So you can have a server configured to run in Shared mode running sessions configured to run in a dedicated mode.

============================

How to tell if a server is running in Dedicated or Shared/MTS mode?
============================
Need to check:
 - server configuration.
 - client configuration
 - the actual sessions status.

=========================
Check server configuration
=========================
check parameters:
- dispatcheres
- shared_servers

dispatchers configures dispatcher processes in the shared server architecture.
max_dispatchers is optional, and must be larger than the number of dispatchers.
shared_servers 0, meaning that shared server is not on. 
                                     1 meaning that shared server is on, or that there are dispatchers.

SELECT name, value 

  FROM V$PARAMETER 

 WHERE name LIKE '%dispatcher%';

NAME              VALUE

---------------- -------------------------------

dispatchers      (PROTOCOL=TCP) (SERVICE=igtXDB)

max_dispatchers

SELECT COUNT(*) FROM V$DISPATCHER;

  COUNT(*)

----------

         1

SELECT name, value FROM V$PARAMETER 
WHERE name LIKE '%shared_servers%'

NAME             VALUE
---------------- ----------
shared_servers   1

=========================
Check client configuration

=========================
In tnsnames.ora
conn_str =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = my_host)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orainst)
    )
  )

=========================
Check the actual sessions status
=========================
See in what configuration sessions are running.

SQL> SELECT DISTINCT (server) FROM V$SESSION ;

SERVER
---------
DEDICATED


=========================
Short Oracle Reference

=========================
Per Oracle documentation: Oracle® Database Net Services Reference Guide 10G

What is parameter SERVER?
Use the parameter SERVER to instruct the listener to connect the client to a specific type of service handler.
service handler handles client requests: either by DEDICATED server or by SHARED server.

What is a service handler?
Service handler is a process that acts a connection point from the listener to the database server. 
A service handler can be a dispatcher or dedicated server.

What is a dispatcher?
A process that enables many clients to connect to the same server without the need for a dedicated server process for each client. 
A dispatcher handles and directs multiple incoming network session requests to shared server processes. 

What is a shared server

A database server that is configured to allow many user processes to share very few server processes, so the number of users that can be supported is increased. 
With shared server configuration, many user processes connect to a dispatcher. 
The dispatcher directs multiple incoming network session requests to a common queue. 
An idle shared server process from a shared pool of server processes picks up a request from the queue. 
This means that a small pool of server processes can serve a large number of clients. 
Contrast with dedicated server.

What is a dedicated server?

A server process that is dedicated to one client connection. 
Contrast with shared server.

Useful DBA SQLs. Compile and Recompile Objects.

=======================
Compile Invalid Objects.
=======================

=======================
Packages
=======================

ALTER PACKAGE XXX COMPILE SPECIFICATION;

ALTER PACKAGE XXX COMPILE BODY;

To see compilation errors:
SELECT * FROM USER_ERRORS;


SELECT object_type, 'ALTER '||
       DECODE(object_type,'PACKAGE BODY','PACKAGE',object_type)||
       ' ' ||owner||'.'||object_name||' COMPILE '||
       decode(object_type,'PACKAGE BODY','BODY','')||';' "ALTER ... COMPILE"
FROM DBA_OBJECTS
WHERE status='INVALID'
  AND object_type NOT IN('JAVA CLASS','JAVA SOURCE')
  AND (object_type != 'SYNONYM' AND OWNER!='PUBLIC')
  AND owner <> 'SYS'

UNION ALL

SELECT object_type, 'ALTER TRIGGER '||' "'||owner||'.'||objest_name||'" COMPILE; '
FROM  DBA_OBJECTS 
WHERE status='INVALID' 
  AND object_type in('TRIGGER')

UNION ALL

SELECT object_type, 'ALTER '||object_type||' "'||owner||'.'||object_name||'" COMPILE; '
FROM  DBA_OBJECTS 
WHERE status='INVALID' 
  AND object_type in('JAVA CLASS','JAVA SOURCE')

UNION ALL

SELECT object_type, 'ALTER ' || OWNER || ' ' || OBJECT_TYPE ||' '||OBJECT_NAME || ' COMPILE;'
FROM DBA_OBJECTS
WHERE STATUS='INVALID'
  AND OBJECT_TYPE='SYNONYM'
  AND owner = 'PUBLIC'
--order by object_type, name
ORDER BY 1,2;



=======================
Synonyms
=======================

ALTER SYNONYM MY_SYNONYM COMPILE;