Two types of server processes are used to serve user
- Dedicated server process = default
- shared server process = need to change initialization parameter to enable it.
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.
shared server can be configured for
A. Connection Pooling = Dispatcher uses time-out protocol connections to service more active connection rather than inactive connections.
B. session multiplexing = combines multiple sessions for transmission over a single network connection in order to conserve the operating system's resources.
Database Resident Connection Pooling [DRCP]
DRCP complements middle-tier connection pools that share connections between threads in a middle-tier 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.
Example of Memory Usage for Dedicated Server, Shared Server, and Database Resident Connection Pooling Consider an application in which the memory required for each session is 400 KB and the memory required for each server process is 4 MB. The pool size is 100 and the number of shared servers used is 100.
If there are 5000 client connections, the memory used by each configuration is as
follows ->
■ Dedicated Server Memory used = 5000 X (400 KB + 4 MB) = 22 GB
■ Shared Server Memory used = 5000 X 400 KB + 100 X 4 MB = 2.5 GB Out of the 2.5 GB, 2 GB is allocated from the SGA.
■ DRCP Memory used = 100 X (400 KB + 4 MB) + (5000 X 35KB)= 615 MB
Configuring Shared Server
Initialization parameter
SHARED_SERVERS: Specifies the initial number of shared servers to start and the minimum number of shared servers to keep. This is the only required parameter for using shared servers.
MAX_SHARED_SERVERS: Specifies the maximum number of shared servers that can run simultaneously.
SHARED_SERVER_SESSIONS: Specifies the total number of shared server user sessions that can run simultaneously. Setting this parameter enables you to reserve user sessions for dedicated servers.
Shared server is enabled by setting the SHARED_SERVERS initialization parameter to a value greater than 0. Shared server can be started dynamically by setting the SHARED_SERVERS parameter to a nonzero value with the ALTER SYSTEM statement, or SHARED_SERVERS can be included at database startup in the initialization parameter file.
Assume a database is being used by a telemarketing center staffed by 1000 agents. On average, each agent spends 90% of the time talking to customers and only 10% of the time looking up and updating records. To keep the shared servers from being terminated as agents talk to customers and then spawned again as agents access the database, a DBA specifies that the optimal number of shared servers is 100. However, not all work shifts are staffed at the same level. On the night shift, only 200 agents are needed. Since SHARED_SERVERS is a dynamic parameter, a DBA reduces the number of shared servers to 20 at night, thus allowing resources to be freed up for
other tasks such as batch jobs.
Decreasing the Number of Shared Server Processes
ALTER SYSTEM SET SHARED_SERVERS = 2
Dispatcher Configuration
Monitor the following views to determine the load on the dispatcher processes:
■ V$QUEUE
■ V$DISPATCHER
■ V$DISPATCHER_RATE
To increase the number of dispatchers for the TCP/IP protocol from 2 to 3, and decrease the number of dispatchers for the TCP/IP with SSL protocol from 2 to 1, you can issue the following statement →
ALTER SYSTEM SET DISPATCHERS = '(PROT=tcp)(DISP=3)', '(PROT-tcps)(DISP=1)';
Configuring DRCP
Oracle Database includes a default connection pool called SYS_DEFAULT_CONNECTION_POOL. By default, this pool is created, but not started. For starting it issue the following command →
SQL> EXECUTE DBMS_CONNECTION_POOL.START_POOL();
Once started, the connection pool remains in this state until it is explicitly stopped.
To access the DRCP from client machine we need to use following command →
oraclehost.company.com:1521/books.company.com:POOLED
or
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp) (HOST=hostname)
(PORT=1575))(CONNECT_DATA=(SERVICE_NAME=orcl)
(SERVER=POOLED)))
Disable connection pooling →
SQL> EXECUTE DBMS_CONNECTION_POOL.STOP_POOL();
Configuration Parameters for DRCP
MINSIZE
MAXSIZE
INCRSIZE
SESSION_CACHED_CURSORS
INACTIVITY_TIMEOUT
MAX_THINK_TIME
MAX_USE_SESSION
MAX_LIFETIME_SESSION
NUM_CBROK
MAXCONN_CBROK
Example of changing connection pool parameters →
SQL> EXECUTE DBMS_CONNECTION_POOL.ALTER_PARAM ('','MINSIZE','5');
Change connection pool to default value by →
SQL> EXECUTE DBMS_CONNECTION_POOL.RESTORE_DEFAULTS();
Oracle Database Background Processes
Database writer (DBWn) = This process writes data blocks from database buffer cache to data files. There are max 20 process
Log writer (LGWR) = It writes redo log buffer to disk.
Checkpoint (CKPT) = The checkpoint process is responsible for signalling DBWn at checkpoints and updating all the datafiles 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.
Process monitor (PMON) = The process monitor performs process recovery when a user process fails.
Archiver (ARCn) One or more archiver processes copy the redo log files to archival storage when they are full or a log switch occurs.
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.
Dispatcher (Dnnn) = Dispatchers are optional background processes, present only when the shared server configuration is used.
Global Cache Service (LMS) = In an Oracle Real Application Clusters environment, this process manages resources and provides inter-instance resource control.
Altering Parallel Execution for a Session
Some useful commands →
ALTER SESSION DISABLE PARALLEL DDL;
ALTER SESSION ENABLE PARALLEL DML;
ALTER SESSION FORCE PARALLEL DDL PARALLEL 10;
Terminating Sessions
ALTER SYSTEM KILL SESSION '8,16';
Identify which system to terminate using following command →
SELECT SID, SERIAL#, STATUS FROM V$SESSION WHERE USERNAME = 'RAM';
SID SERIAL# STATUS
----- --------- --------
8 5 ACTIVE
10 60 INACTIVE
To kill a inactive session issue the following command →
SELECT SID,SERIAL#,STATUS,SERVER FROM V$SESSION WHERE USERNAME = 'RAM';
SID SERIAL# STATUS SERVER
----- -------- --------- ---------
8 16 INACTIVE DEDICATED
12 63 INACTIVE DEDICATED
2 rows selected.
ALTER SYSTEM KILL SESSION '8,16';
Statement processed.
Some important information on processes are stored in the following views →
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$SYSSTAT = 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
No comments :
Post a Comment