- server process can be either of the following:
-
A dedicated server process, which services only one user process
-
A shared server process, which can service multiple user processes
- Dispatcher can support multiple client connections concurrently. Each client connection is bound to a virtual circuit.
- Virtual circuit is a piece of shared memory used by the dispatcher for client database connection requests and replies.
- Dedicated Servers, Shared Servers, and Database Resident Connection Pooling
| Dedicated Server | Shared Server | Database Resident Connection Pooling |
|---|---|---|
| When a client request is received, a new server process and a session are created for the client. | When the first request is received from a client, the Dispatcher process places this request on a common queue. The request is picked up by an available shared server process. The Dispatcher process then manages the communication between the client and the shared server process. | When the first request is received from a client, the Connection
Broker picks an available pooled server and hands off the client
connection to the pooled server. If no pooled servers are available, the Connection Broker creates one. If the pool has reached its maximum size, the client request is placed on the wait queue until a pooled server is available. |
| Releasing database resources involves terminating the session and server process. | Releasing database resources involves terminating the session. | Releasing database resources involves releasing the pooled server to the pool. |
| Memory requirement is proportional to the number of server processes and sessions. There is one server and one session for each client. | Memory requirement is proportional to the sum of the shared servers and sessions. There is one session for each client. | Memory requirement is proportional to the number of pooled servers and their sessions. There is one session for each pooled server. |
| Session memory is allocated from the PGA. | Session memory is allocated from the SGA. | Session memory is allocated from the PGA. |
### Initialization Parameters for Shared Server
[oracle@localhost ~]$ sqlplus / as sysdba SQL> alter system set shared_servers=55; System altered. SQL> show parameter max_shared_servers; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ max_shared_servers integer 5 SQL> show parameter shared_server_sessions; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ shared_server_sessions integer SQL> show parameter dispatchers; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ dispatchers string (PROTOCOL=TCP) max_dispatchers integer SQL> show parameter max_dispatchers NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ max_dispatchers integer SQL> show parameter circuits; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ circuits integer
- DISPATCHERS Initialization Parameter Attributes
| Attribute | Description |
|---|---|
ADDRESS |
Specify the network protocol address of the endpoint on which the dispatchers listen. |
DESCRIPTION |
Specify the network
description of the endpoint on which the dispatchers listen, including
the network protocol address. The syntax is as follows:
(DESCRIPTION=(ADDRESS=...)) |
PROTOCOL |
Specify the network protocol for which the dispatcher generates a listening endpoint. For example:
(PROTOCOL=tcp)See the Oracle Database Net Services Reference for further information about protocol address syntax. |
- how many dispatchers this configuration should have. It is optional and defaults to 1.
- Network attributes of each dispatcher of this configuration. They are all optional.
SQL> select * from v$queue; PADDR TYPE QUEUED WAIT TOTALQ -------- ---------- ---------- ---------- ---------- 00 COMMON 0 0 0 3B3D5040 DISPATCHER 0 0 0 SQL> set lines 2000 SQL> col network format a65; SQL> select name,network,status from v$dispatcher; NAME NETWORK STATUS ---- ----------------------------------------------------------------- ---------------- D000 (ADDRESS=(PROTOCOL=tcp)(HOST=localhost.localdomain)(PORT=43399)) WAIT SQL> alter system shutdown immediate 'D000'; System altered. SQL> -- disable shared server -- SQL> ALTER SYSTEM SET DISPATCHERS = ''; System altered. SQL> ALTER SYSTEM SET SHARED_SERVERS = 50; System altered.
- Shared Server Data Dictionary Views
| View | Description |
|---|---|
V$DISPATCHER |
Provides information on the dispatcher processes, including name, network address, status, various usage statistics, and index number. |
V$DISPATCHER_CONFIG |
Provides configuration information about the dispatchers. |
V$DISPATCHER_RATE |
Provides rate statistics for the dispatcher processes. |
V$QUEUE |
Contains information on the shared server message queues. |
V$SHARED_SERVER |
Contains information on the shared servers. |
V$CIRCUIT |
Contains information about virtual circuits, which are user connections to the database through dispatchers and servers. |
V$SHARED_SERVER_MONITOR |
Contains information for tuning shared server. |
V$SGA |
Contains size information about various system global area (SGA) groups. May be useful when tuning shared server. |
V$SGASTAT |
Contains detailed statistical information about the SGA, useful for tuning. |
V$SHARED_POOL_RESERVED |
Lists statistics to help tune the reserved pool and space within the shared pool. |
SQL> select * from v$sga; NAME VALUE -------------------- ---------- Fixed Size 1344840 Variable Size 348129976 Database Buffers 100663296 Redo Buffers 6008832 SQL> select * from v$sgastat;
SQL> desc v$dispatcher_config; Name Null? Type ----------------------------------------- -------- ---------------------------- CONF_INDX NUMBER NETWORK VARCHAR2(1024) DISPATCHERS NUMBER CONNECTIONS NUMBER SESSIONS NUMBER POOL VARCHAR2(4) TICKS NUMBER INBD_TIMOUT NUMBER OUTBD_TIMOUT NUMBER MULTIPLEX VARCHAR2(4) LISTENER VARCHAR2(1200) SERVICE VARCHAR2(512) SQL> select network,dispatchers,connections,pool from v$dispatcher_config; NETWORK DISPATCHERS CONNECTIONS POOL --------------------------------------------- ----------- ----------- ---- (ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP)) 1 1022 OFF
- Enabling Database Resident Connection Pooling using PL/SQL Packages
| Parameter Name | Description |
|---|---|
MINSIZE |
The minimum number of pooled servers in the pool. The default value is 4. |
MAXSIZE |
The maximum number of pooled servers in the pool. The default value is 40. The connection pool reserves 5% of the pooled servers for authentication, and at least one pooled server is always reserved for authentication. When setting this parameter, ensure that there are enough pooled servers for both authentication and connections. |
INCRSIZE |
The number of pooled servers by which the pool is incremented if servers are unavailable when a client application request is received. The default value is 2. |
SESSION_CACHED_CURSORS |
The number of session cursors to cache in each pooled server session. The default value is 20. |
INACTIVITY_TIMEOUT |
The maximum time, in seconds, the pooled server can stay idle in the
pool. After this time, the server is terminated. The default value is
300. This parameter does not apply if the pool is at MINSIZE. |
MAX_THINK_TIME |
The maximum time of inactivity, in seconds, for a client after it
obtains a pooled server from the pool. After obtaining a pooled server
from the pool, if the client application does not issue a database call
for the time specified by MAX_THINK_TIME, the pooled server is freed and the client connection is terminated. The default value is 120. |
MAX_USE_SESSION |
The number of times a pooled server can be taken and released to the pool. The default value is 500000. |
MAX_LIFETIME_SESSION |
The time, in seconds, to live for a pooled server in the pool. The default value is 86400. |
NUM_CBROK |
The number of Connection Brokers that are created to handle client requests. The default value is 1. Creating multiple Connection Broker processes helps distribute the load of client connection requests if there are a large number of client applications. |
MAXCONN_CBROK |
The maximum number of connections that each Connection Broker can handle. The default value is 40000. But if the maximum connections allowed by the platform on which the database is installed is lesser than the default value, this value overrides the value set using MAXCONN_CBROK.Set the per-process file descriptor limit of the operating system sufficiently high so that it supports the number of connections specified by MAXCONN_CBROK. |
SQL> EXECUTE DBMS_CONNECTION_POOL.START_POOL(); PL/SQL procedure successfully completed. SQL> EXECUTE DBMS_CONNECTION_POOL.ALTER_PARAM ('','MINSIZE','10'); PL/SQL procedure successfully completed. SQL> EXECUTE DBMS_CONNECTION_POOL.ALTER_PARAM ('','MAXCONN_CBROK','50000'); PL/SQL procedure successfully completed. SQL> EXECUTE DBMS_CONNECTION_POOL.RESTORE_DEFAULTS(); PL/SQL procedure successfully completed.
- Data Dictionary Views for Database Resident Connection Pooling
| View | Description |
|---|---|
DBA_CPOOL_INFO |
Contains information about the connection pool such as the pool status, the maximum and minimum number of connections, and timeout for idle sessions. |
V$CPOOL_CONN_INFO |
Contains information about each connection to the connection broker. |
V$CPOOL_STATS |
Contains pool statistics such as the number of session requests, number of times a session that matches the request was found in the pool, and total wait time for a session request. |
V$CPOOL_CC_STATS |
Contains connection class level statistics for the pool. |
SQL> desc dba_cpool_info
Name Null? Type
----------------------------------------- -------- ----------------------------
CONNECTION_POOL VARCHAR2(128)
STATUS VARCHAR2(16)
MINSIZE NUMBER
MAXSIZE NUMBER
INCRSIZE NUMBER
SESSION_CACHED_CURSORS NUMBER
INACTIVITY_TIMEOUT NUMBER
MAX_THINK_TIME NUMBER
MAX_USE_SESSION NUMBER
MAX_LIFETIME_SESSION NUMBER
NUM_CBROK NUMBER
MAXCONN_CBROK NUMBER
SQL> select status,minsize,maxsize from dba_cpool_info;
STATUS MINSIZE MAXSIZE
---------------- ---------- ----------
ACTIVE 10 40
- Oracle Database Background Processes
| Process Name | Description |
|---|---|
| Database writer (DBWn) | The database writer writes modified blocks from the database buffer
cache to the data files. Oracle Database allows a maximum of 36 database
writer processes (DBW0-DBW9 and DBWa-DBWj). The DB_WRITER_PROCESSES initialization parameter specifies the number of DBWn
processes. The database selects an appropriate default setting for this
initialization parameter or adjusts a user-specified setting based on
the number of CPUs and the number of processor groups.For more information about setting the DB_WRITER_PROCESSES initialization parameter, see the Oracle Database Performance Tuning Guide. |
| Log writer (LGWR) | The log writer process writes redo log entries to disk. Redo log entries are generated in the redo log buffer of the system global area (SGA). LGWR writes the redo log entries sequentially into a redo log file. If the database has a multiplexed redo log, then LGWR writes the redo log entries to a group of redo log files. See Chapter 12, "Managing the Redo Log" for information about the log writer process. |
| Checkpoint (CKPT) | At specific times, all modified database buffers in the system global area are written to the data files by DBWn. This event is called a checkpoint. The checkpoint process is responsible for signalling DBWn at checkpoints and updating all the data files and control files of the database to indicate the most recent checkpoint. |
| System monitor (SMON) | The system monitor performs recovery when a failed instance starts up again. In an Oracle Real Application Clusters database, the SMON process of one instance can perform instance recovery for other instances that have failed. SMON also cleans up temporary segments that are no longer in use and recovers terminated transactions skipped during system failure and instance recovery because of file-read or offline errors. These transactions are eventually recovered by SMON when the tablespace or file is brought back online. |
| Process monitor (PMON) | The process monitor performs process recovery when a user process fails. PMON is responsible for cleaning up the cache and freeing resources that the process was using. PMON also checks on the dispatcher processes (described later in this table) and server processes and restarts them if they have failed. |
| Archiver (ARCn) | One or more archiver processes copy the redo log files to archival storage when they are full or a log switch occurs. Archiver processes are the subject of Chapter 13, "Managing Archived Redo Logs". |
| Recoverer (RECO) | The recoverer process is used to resolve distributed transactions that are pending because of a network or system failure in a distributed database. At timed intervals, the local RECO attempts to connect to remote databases and automatically complete the commit or rollback of the local portion of any pending distributed transactions. For information about this process and how to start it, see Chapter 35, "Managing Distributed Transactions". |
| Dispatcher (Dnnn) | Dispatchers are optional background processes, present only when the shared server configuration is used. Shared server was discussed previously in "Configuring Oracle Database for Shared Server". |
- Managing Processes for Parallel SQL Execution
- Degree of parallelism is the number of parallel execution servers that can be associated with a single operationSQL> alter session enable parallel dml; Session altered. SQL> alter session disable parallel ddl; Session altered. SQL> alter session enable parallel ddl; Session altered. SQL> alter session force parallel ddl parallel 10; Session altered. SQL> show parameter parallel_max_servers; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ parallel_max_servers integer 40
- Terminating Sessions
SQL> -- as user HR login from SQL Deveolper -- SQL> select username,sid, serial#,status 2 from v$session 3 where username='HR'; USERNAME SID SERIAL# STATUS ------------------------------ ---------- ---------- -------- HR 53 35 INACTIVE SQL> alter system kill session '53,35'; System altered. SQL> -- as user HR login again from terminal with new session -- SQL> select username,sid, serial#,status,server 2 from v$session 3 where username='HR'; USERNAME SID SERIAL# STATUS SERVER ------------------------------ ---------- ---------- -------- --------- HR 52 31 INACTIVE DEDICATED HR 53 35 KILLED PSEUDO
- Process and Session Data Dictionary Views
| View | Description |
|---|---|
V$PROCESS |
Contains information about the currently active processes |
V$SESSION |
Lists session information for each current session |
V$SESS_IO |
Contains I/O statistics for each user session |
V$SESSION_LONGOPS |
Displays the status of various operations that run for longer than 6 seconds (in absolute time). These operations currently include many backup and recovery functions, statistics gathering, and query execution. More operations are added for every Oracle Database release. |
V$SESSION_WAIT |
Displays the current or last wait for each session |
V$SESSION_WAIT_HISTORY |
Lists the last ten wait events for each active session |
V$WAIT_CHAINS |
Displays information about blocked sessions |
V$SESSTAT |
Contains session statistics |
V$RESOURCE_LIMIT |
Provides information about current and maximum global resource utilization for some system resources |
V$SQLAREA |
Contains statistics about shared SQL areas. Contains one row for each SQL string. Provides statistics about SQL statements that are in memory, parsed, and ready for execution |
SQL> desc v$process; Name Null? Type ----------------------------------------- -------- ---------------------------- ADDR RAW(4) PID NUMBER SPID VARCHAR2(24) PNAME VARCHAR2(5) USERNAME VARCHAR2(15) SERIAL# NUMBER TERMINAL VARCHAR2(30) PROGRAM VARCHAR2(48) TRACEID VARCHAR2(255) TRACEFILE VARCHAR2(513) BACKGROUND VARCHAR2(1) LATCHWAIT VARCHAR2(8) LATCHSPIN VARCHAR2(8) PGA_USED_MEM NUMBER PGA_ALLOC_MEM NUMBER PGA_FREEABLE_MEM NUMBER PGA_MAX_MEM NUMBER SQL> select pid,pname,username from v$process; PID PNAME USERNAME ---------- ----- --------------- 1 2 PMON oracle 3 PSP0 oracle 4 VKTM oracle 5 GEN0 oracle 6 DIAG oracle
............
happy oracle,
No comments:
Post a Comment