Lesson 8	Monitoring Shared Server
Objective	Use UNIX and Oracle Tools to monitor Shared Server Connections.

Use UNIX and Oracle Tools to monitor Oracle Shared Server Connections

To monitor Oracle Shared Server (formerly known as MTS) connections in Oracle 19c, you can use a combination of UNIX/Linux system monitoring tools and Oracle database tools. Below is a list of relevant tools and techniques:
UNIX/Linux Tools
These tools can help you monitor system-level resource usage and connections related to Oracle:

netstat:
- Use this to monitor active TCP/IP connections.
- Example:
```
netstat -an | grep <oracle_listener_port>
        
```
- Replace <oracle_listener_port> with your Oracle listener port (e.g., 1521).
lsof:
- Use this to identify processes and open files/sockets related to Oracle.
- Example:
```
lsof -i TCP:<oracle_listener_port>
        
```
top / htop:
- These tools provide a real-time view of CPU and memory usage by Oracle processes.
- Focus on ora_s000 (shared server processes) or ora_p000 (parallel processes).
vmstat and iostat:
- Use these for monitoring system resource usage such as CPU, memory, and I/O, which might be impacted by Shared Server connections.
sar:
- Collect, report, and save system resource usage statistics over time.

Oracle Database Tools
Oracle provides several built-in views and tools to monitor shared server connections:

V$QUEUE:
- Displays information about the request and response queues for shared servers.
- Query:
```
SELECT * FROM V$QUEUE;
```
V$SHARED_SERVER:
- Provides details about the activity of each shared server process.
- Query:
```
SELECT SERVER#, STATUS, SESSIONS FROM V$SHARED_SERVER;
```
V$CIRCUIT:
- Displays information about virtual circuits, including inbound and outbound traffic for shared server sessions.
- Query:
```
SELECT CIRCUIT, DISPATCHER, SERVER, QUEUE, WAIT_EVENT FROM V$CIRCUIT;
```
V$DISPATCHER:
- Shows information about dispatchers, including their status and the number of connections they handle.
- Query:
```
SELECT NAME, STATUS, MESSAGES, BYTES FROM V$DISPATCHER;
```
V$SESSION:
- Displays all current sessions. Filter for sessions using shared servers (PADDR will reference a shared server process).
- Query:
```
SELECT SID, USERNAME, SERVER FROM V$SESSION 
WHERE SERVER = 'SHARED';
```
V$DISPATCHER_RATE:
- Provides performance metrics for dispatchers, such as messages processed per second.
- Query:
```
SELECT * FROM V$DISPATCHER_RATE;
```
V$SHARED_SERVER_MONITOR:
- Offers a summary of shared server activity, such as maximum connections and current load.
- Query:
```
		
SELECT MAXIMUM_CONNECTIONS, CURRENT_CONNECTIONS, MAXIMUM_SESSIONS 
FROM V$SHARED_SERVER_MONITOR;
```
Enterprise Manager (EM):
- Use Oracle Enterprise Manager (Cloud Control or Database Control) to graphically monitor shared server connections and performance metrics.

Automated Monitoring with Scripts:
You can automate the monitoring of shared server processes using shell scripts combined with SQL queries.
# Example Script

#!/bin/bash
sqlplus -s / as sysdba <<EOF
SET LINESIZE 150;
SET PAGESIZE 100;
SELECT SERVER#, STATUS, SESSIONS FROM V$SHARED_SERVER;
SELECT NAME, STATUS, MESSAGES, BYTES FROM V$DISPATCHER;
EXIT;
EOF

Alerts and Diagnostics

AWR Reports:
- Use Automatic Workload Repository (AWR) reports to analyze historical performance of shared server processes.
- Command:
```
@?/rdbms/admin/awrrpt.sql
```
ASH Reports:
- Active Session History (ASH) can help you track session-level activity in shared servers.
- Command:
```
@?/rdbms/admin/ashrpt.sql
```
Oracle Trace Files:
- Check Oracle trace files and alert logs ($ORACLE_BASE/diag) for issues related to shared servers.

Summary

Performance Optimization: Establishing and tearing down database connections is resource-intensive. Connection pooling minimizes this overhead by reusing established connections for multiple client requests.
Resource Efficiency: Each database connection consumes server resources, including memory and CPU. Connection pooling limits the maximum number of connections, helping to prevent resource exhaustion.
Scalability: In high-traffic environments, connection pooling allows applications to scale better by managing connections efficiently.
Reduced Latency: Connection reuse reduces latency for clients, as the time to establish a new connection is avoided.

View the Oracle Dispatcher Activity through the Oracle dictionary

You can view Oracle Dispatcher activity using views in the Oracle Data Dictionary. Oracle provides several dictionary views that give insight into the operations and performance of Dispatchers, which are critical components in shared server configurations.
Key Views to Monitor Dispatcher Activity

V$DISPATCHER
- This view provides information about each dispatcher process, including its status and the number of connections it is handling.
- Important columns:
  - NAME: Dispatcher name (e.g., D000, D001).
  - NETWORK: Network protocol used by the dispatcher.
  - MESSAGES: Total messages processed by the dispatcher.
  - BYTES: Total bytes processed by the dispatcher.
  - BUSY: Cumulative time the dispatcher was busy.
- Example Query:
```
SELECT NAME, NETWORK, MESSAGES, BYTES, BUSY, IDLE
FROM V$DISPATCHER;
```
V$DISPATCHER_RATE
- Provides rate-based statistics for dispatcher processes.
- Important columns:
  - NAME: Dispatcher name.
  - BYTES_PER_SECOND: Bytes transferred per second.
  - MESSAGES_PER_SECOND: Messages transferred per second.
- Example Query:
```
SELECT NAME, BYTES_PER_SECOND, MESSAGES_PER_SECOND
FROM V$DISPATCHER_RATE;
```
V$QUEUE
- Displays information about the request and response queues for dispatchers and servers in shared server architecture.
- Important columns:
  - QUEUE: Name of the queue (e.g., dispatcher or server queue).
  - TYPE: Type of the queue (dispatcher, server, etc.).
  - QSIZE: Current size of the queue.
  - WAIT: Number of requests waiting in the queue.
- Example Query:
```
SELECT QUEUE, TYPE, QSIZE, WAIT
FROM V$QUEUE;
```
V$CIRCUIT
- Provides detailed information about the virtual circuits established for shared server processes.
- Important columns:
  - DISPATCHER: The dispatcher associated with the circuit.
  - BYTES_RECEIVED: Bytes received for the circuit.
  - BYTES_SENT: Bytes sent for the circuit.
  - STATUS: Status of the circuit (e.g., active or inactive).
- Example Query:
```
SELECT CIRCUIT, DISPATCHER, BYTES_RECEIVED, BYTES_SENT, STATUS
FROM V$CIRCUIT;
```
V$SHARED_SERVER_MONITOR
- Provides aggregated statistics for shared server and dispatcher activities.
- Important columns:
  - MAXIMUM_CONNECTIONS: Maximum number of connections reached.
  - SERVERS_STARTED: Number of shared servers started.
  - DISPATCHERS_STARTED: Number of dispatchers started.
- Example Query:
```
SELECT MAXIMUM_CONNECTIONS, SERVERS_STARTED, DISPATCHERS_STARTED
FROM V$SHARED_SERVER_MONITOR;
```

Additional Considerations

Performance Monitoring: You can use Oracle Enterprise Manager (OEM) or AWR/ADDM reports to complement dictionary view analysis for more detailed insights and historical data trends.
Shared Server Configuration: Ensure that shared server mode is enabled (SHARED_SERVERS parameter > 0) to observe meaningful dispatcher activity.

By leveraging these views, you can effectively monitor dispatcher activity, diagnose performance bottlenecks, and optimize shared server configurations in Oracle databases.

Each dispatcher defined in the init.ora file becomes a UNIX process

The statement is correct for Oracle 19c when the database is configured to use the shared server architecture. Here's why:
Key Points:

Dispatcher Processes as UNIX Processes:
- In Oracle's shared server mode, dispatcher processes are responsible for managing and distributing incoming client requests to shared server processes.
- Each dispatcher defined in the DISPATCHERS initialization parameter will spawn a corresponding UNIX process or Windows thread when the database instance starts.
How Dispatchers Are Defined:
- Dispatchers are defined in the init.ora or spfile file using the DISPATCHERS parameter. For example:
- ```
DISPATCHERS="(PROTOCOL=TCP)(DISPATCHERS=3)"
        
```
- This configuration creates three dispatcher processes for the TCP protocol.
Viewing Dispatcher Processes in UNIX:
- Once the database starts, you can see dispatcher processes at the OS level. Dispatcher process names typically begin with the letter D followed by a number (e.g., D000, D001, etc.).
- Use commands like ps or top on UNIX/Linux systems to see these processes:
- ```
ps -ef | grep ora_d000
        
```
Related Initialization Parameters:
- DISPATCHERS: Configures the number of dispatcher processes and their protocol.
- MAX_DISPATCHERS: Defines the maximum number of dispatcher processes that can be spawned.
- SHARED_SERVERS: Specifies the number of shared server processes.
Dispatcher Naming Convention:
- At the OS level, dispatcher processes will appear with a naming convention such as ora_d000_<SID>, where:
- - d000 is the first dispatcher.
  - <SID> is the Oracle system identifier.

Additional Notes:

If the database is using dedicated server mode, dispatcher processes will not be created, and client connections will be handled by individual dedicated server processes.
Ensure that shared server mode is configured and the DISPATCHERS parameter is set for dispatcher processes to be active.

This behavior is consistent across Oracle versions, including Oracle 19c, in environments where shared server configurations are implemented.

The v$queue and v$dispatcher data dictionary views will indicate if the number of dispatchers is too low.
View the Code below to see what the v$queue data dictionary view looks like.

SQL> select * from v$queue;

PADDR    TYPE           QUEUED       WAIT     TOTALQ
-------- ---------- ---------- ---------- ----------
00       COMMON              0          0         15
00       OUTBOUND            0          0          0
420548A4 DISPATCHER          0          1         15
42054B0C DISPATCHER          0          0          0
42054D74 DISPATCHER          0          0          0

View the code below to see what the v$dispatcher data dictionary view looks like.

SQL> select * from v$dispatcher;

NAME  NETWORK                                                                   
                                                       PADDR    STATUS          
ACC   MESSAGES      BYTES     BREAKS      OWNED    CREATED       IDLE       BUS
Y     LISTENER
----- --------------------------------------------------------------------------
------------------------------------------------------ -------- ----------------
 --- ---------- ---------- ---------- ---------- ---------- ---------- ---------
- ----------
D000  (ADDRESS=(PARTIAL=yes)(PROTOCOL=ipc))                                     
                                                       450660D4 WAIT            
 YES        202      13320          0          0          2    4681834        17
4          0
D001  (ADDRESS=(PARTIAL=yes)(PROTOCOL=ipc))                                     
                                                       4506633C WAIT            
 YES        313      21904          0          0          4    4681771        24
0          0
D002  (ADDRESS=(PARTIAL=yes)(PROTOCOL=tcp))                                     
                                                       450665A4 WAIT            
 YES     811151   92165767          0         17       3931    4449027     23298
3          0
D003  (ADDRESS=(PARTIAL=yes)(PROTOCOL=tcp))                                     
                                                       4506680C WAIT            
 YES     660608   72314081          0         18       3166    4490706     19130
0

The next lesson explains how to identify contention in Oracle Shared Server

Monitoring Shared Server - Quiz

Before moving on to the next lesson, click the Quiz link below to check your mastery of connection pooling and shared server monitoring.
Monitoring Shared Server - Quiz