Dynamic transformation and differentiation of Oracle Shared and private mode (reprint)

has not been dedicated to share the interchange to find out this article http://blog.csdn.net/tianlesoftware/archive/2010/06/26/5695784.aspx, let me practice a, do understand a lot. Most of the following are transferred from the link, the collection, for later easy to find learning.

the difference between sharing and a dedicated server:

Dedicated server (dedicated): One client connection corresponds to a server process

Shared Server (SHARE): Multiple client connections correspond to one server process, and there is a process scheduler on the server side to manage. It must use net services. This means that TNS must be configured. It is suitable for high concurrency, small amount of things, if this time using the sharing mode, can greatly reduce the high concurrency for the Oracle Server resource consumption.

Shared servers have some of the following drawbacks:

1) The code path of the shared server is longer than the dedicated server, so it is inherently slower than a dedicated server.

2) There is the possibility of an artificial deadlock, because it is serial, and as long as a connection is blocked, all users on that server process are blocked and are highly likely to deadlock.

3) There is the possibility of an exclusive transaction, because if a session has a transaction that takes too long to run, it exclusively shares the resource, and the other user can only wait, while the dedicated server, each client is a session.

4) Shared server mode restricts certain database features, such as: You cannot start and shut down instances individually, you cannot perform media recovery, you cannot use log Miner, and the sql_trace is meaningless (because it is a share instead of the current session).

MTS reduced memory is actually the memory required for each user to connect to the operating system process in dedicated server mode, but it uses the large_pool of the SGA to allocate UGA, pay Paul, and the reduced memory is minimal. If the connection and disconnection of a user session is frequent, the cost of creating and deleting the database process is very high, preferably in a shared server mode (otherwise, connection pooling technology should be used). Using Shared server mode is of little significance if the client is used for a lifetime (during the session life cycle) for a single connection. Because most of the time, a session is connected to a server process and the server process cannot be shared.

some descriptions of the shared service initialization parameters:
Shared_servers: Specifies the number of shared server process starts when instance is started, do not set this parameter too large, no one starts the database instance
Will take more time, and the shared_servers will be dynamically adjusted according to the load after Oracle starts. If 0, indicates that the database does not start the shared services mode. This parameter is
The shared server must be configured, and only this parameter is required.

--Modify Parameters:alter system set Shared_servers=1;

Max_shared_servers:oracle the largest shared server process that can be used at the same time. Do not set this parameter to less than Shared_servers if dynamically modified
Shared_servers greater than max_shared_servers,oracle overrides the Max_shared_servers value, you need to modify the max_shared_servers at this point.
At the same time can not be greater than processes. This parameter is intended for use in large resource operations (batch processing), in order to reserve some process for the DBA task (Rman Backup),

Shared_server_sesions: Specifies the number of shared server sessions that are allowed in total. If this parameter is set, then do not put this value above sessions, if there is no
Set this value so that it can be used as long as there is an idle session. Setting this value is reserved for the private connection user sessions.

Dispatchers (scheduling process): Configures dispatcher process. If you do not set this parameter, a TCP protocol-based dispatcher is automatically set if the Shared_servers,oracle is set.
Also need to see how many connections the operating system supports a dispatcher can handle

Sql> select * from V$dispatcher;

Max_dispatchers: Sets the number of dispatchers that can run concurrently at the same time, which must be greater than or equal to dispatchers, less than processes. This parameter will also be overwritten by dispatchers.

To close the scheduling process:
The first is to inquire into the dispatchers name:select name,network from V$dispatcher;
Then close the scheduling process: ALTER SYSTEM SHUTDOWN IMMEDIATE ' D000 ';

Circuits (Virtual loop): Specifies the total quantity of virtual circuits.
Sql> select * from V$circuit;


To turn off sharing mode:
Set the Shared_servers parameter to 0 (alter system set shared_servers=0;), then all shared connections to the database will not succeed, but the shares that are not freed
The connection continues to remain connected until it disconnects. If both Shared_servers and Max_shared_servers are set to 0 (alter system set max_shared_servers=0;),
Then the shared connection will be terminated. If all the shared connections are disconnected, you can use the ALTER system set dispatcher= '; Clear the dispatcher to prevent the next boot
The database also opens the shared connection mode.

to determine whether Oracle is a shared or private mode:

1. Show parameter shared_server;(Note: 8i should be: Show parameter mts_servers;)
Sql> Show parameter shared_server;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
Max_shared_servers integer 0
Shared_server_sessions integer
Shared_servers integer 0--0 for private mode

2. View V$session View
Sql> Select Username,server,program from v$session where username are not null;

USERNAME SERVER Program
--------- --------- -------------------
GWM NONE
SYS SHARED Plsqldev.exe
SYS SHARED Plsqldev.exe
SYS dedicated Sqlplus.exe--dedicated mode

3. View monitoring: Lsnrctl service
$ LSNRCTL Service

Lsnrctl for Ibm/aix RISC system/6000:version 10.2.0.1.0-production on 08-mar-
2011 14:28:08

Copyright (c) 1991, 2005, Oracle. All rights reserved.

Connecting to (Address= (PROTOCOL=TCP) (host=) (port=1521))
Services Summary ...
Service "TELEMT" has 1 instance (s).
Instance "telemt", status ready, have 2 handler (s) for the This service ...
Handler (s):
"Dedicated" Established:6 refused:0 State:ready
LOCAL SERVER
"D000" established:51 refused:0 current:3 max:1022 state:blocked
DISPATCHER <MACHINE:OSSDB2, pid:504060>
(Address= (PROTOCOL=TCP) (Host=loopback) (port=53932))
Service "TELEMT_XPT" has 1 instance (s).
Instance "telemt", status ready, have 2 handler (s) for the This service ...
Handler (s):
"Dedicated" Established:6 refused:0 State:ready
LOCAL SERVER
"D000" established:51 refused:0 current:3 max:1022 state:blocked
DISPATCHER <MACHINE:OSSDB2, pid:504060>
(Address= (PROTOCOL=TCP) (Host=loopback) (port=53932))
The command completed successfully

4. View Tnsnames.ora file。 Such as:
Igisdb =
(DESCRIPTION =
(Address_list =
(ADDRESS = (PROTOCOL = TCP) (HOST = 134.74.21.51) (PORT = 1521))
)
(Connect_data =
(service_name = telemt)
(SERVER = dedicated)
)
)

This is a telemt instance that is connected in dedicated private mode. Write on (server = shared) is using Shared server mode, but then shared_server_process needs
On (that is, the shared_servers parameter of the Oracle server is set to shared mode), otherwise it will fail to connect to Oracle (ORA-12520:TNS: Listener cannot find the requested server type
To the available handlers).
If this paragraph is not written, then the system will automatically adjust according to the server mode, but according to the measured results, even if the server is defined as a shared server mode, Shared_server_process did not hit
, the connection found in v$session is still server = dedicated. So basically we're going to be able to do this without writing, but sometimes we have to connect with our shared services.
Server mode, it is possible for the system to think that it should be connected using shared servers, so it is best to declare server = dedicated to connect using a dedicated server.

when the database is started, if Shared_servers is not specified, but dispatchers is set, then Oracle considers the shared server to be started and sets
Shared_servers is 1. When the database is started, shared_servers is not set, and dispatchers is not set, even if dispatchers is modified later, it cannot be started.
Shared server, the database must be restarted.

Http://www.cnblogs.com/lanzi/archive/2011/03/08/1977317.html

Dynamic transformation and differentiation of Oracle Shared and private mode (reprint)