Oracle Processes such as Database Writer, Log Writer, System Monitor, Process Monitor, Archiver

In Oracle Database 13c, the shared server architecture allows multiple client connections to be serviced by a pool of shared server processes. This is in contrast to the dedicated server architecture, where each client connection is serviced by its own dedicated server process. Identifying Oracle processes running in shared server mode is critical for performance tuning, troubleshooting, and system resource management.

1. Identification Through Oracle Enterprise Manager (OEM):
   
   1. Log In: Open the Oracle Enterprise Manager Console and log in.
   2. Navigate: Go to the 'Performance' tab.
   3. Sub-menu: Click on 'Shared Servers' from the sub-menu.
   4. Review: Here, you'll see all shared server processes along with their statuses and metrics.
2. Identification Through SQL*Plus or Other SQL Interfaces:
   
   1. Log In: Connect to the database using SQL*Plus as a user with appropriate administrative privileges.
      

      sqlplus / as sysdba
      

   2. Shared Server Processes: To list the currently active shared server processes, execute the following SQL query:
      

      SELECT name, status FROM v$shared_server;
      

      
      This will display the shared server processes and their statuses.
   3. Dispatchers: To list the dispatcher processes which route requests to shared server processes, execute:
      

      SELECT name, status FROM v$dispatcher;
      

      
      This will display the dispatcher processes and their statuses.
   4. Active Sessions: To identify sessions that are using shared server processes, execute:
      

      SELECT sid, serial#, server 
      FROM v$session 
      WHERE server = 'SHARED';
      

      
      This will list the sessions that are currently being serviced by shared server processes.
   5. Performance Metrics: For further insights into the performance metrics related to shared servers, query the `v$shared_server_monitor` view:

      SELECT * FROM v$shared_server_monitor;
      

You can also identify Oracle shared server processes at the operating system level. The specific commands vary depending on the OS.

1. Linux/Unix: Use the `ps` command to list Oracle processes and then grep for shared server processes.
   

   ps -ef | grep ora_s[0-9]*_[YOUR_DB_NAME]
   

2. Windows: Use Task Manager or the `tasklist` command-line utility to look for Oracle processes, though identification might not be as straightforward as in Unix/Linux systems.

Identifying Oracle processes running on a shared server is a crucial task for any Oracle DBA. Utilize the above methods based on Oracle Enterprise Manager, SQL queries, and operating system utilities to pinpoint these processes effectively. By doing so, you can ensure efficient resource utilization and optimal performance of your Oracle 13c database system.

Identify other Oracle processes that may be running on your server. The processes that you have learned about so far in this module are the major ones that you need to be concerned about when managing an Oracle instance. These include:

1. The Database Writer
2. The Log Writer
3. The System Monitor
4. The Process Monitor
5. The Archiver

There are other processes however, that you may see running, depending on how you have configured your database. Here are some of them:

A latch is a simple, low-level serialization mechanism that coordinates multiuser access to shared data structures, objects, and files. Latches protect shared memory resources from corruption when accessed by multiple processes. Specifically, latches protect data structures from the following situations:

1. Concurrent modification by multiple sessions
2. Being read by one session while being modified by another session
3. Deallocation (aging out) of memory while being accessed

Typically, a single latch protects multiple objects in the SGA. For example, background processes such as DBW and LGWR allocate memory from the shared pool to create data structures. To allocate this memory, these processes use a shared pool latch that serializes access to prevent two processes from trying to inspect or modify the shared pool simultaneously. After the memory is allocated, other processes may need to access shared pool areas such as the library cache, which is required for parsing. In this case, processes latch only the library cache, not the entire shared pool. Unlike enqueue latches such as row locks, latches do not permit sessions to queue. When a latch becomes available, the first session to request the latch obtains exclusive access to it. The phenomenon of latch spinning occurs when a process repeatedly requests a latch in a loop, whereas latch sleeping occurs when a process releases the CPU before renewing the latch request. Typically, an Oracle process acquires a latch for an extremely short time while manipulating or looking at a data structure. For example, while processing a salary update of a single employee, the database may obtain and release thousands of latches. The implementation of latches is operating system-dependent, especially in respect to whether and how long a process waits for a latch. An increase in latching means a decrease in concurrency. For example, excessive hard parse operations create contention for the library cache latch. The V$LATCH view contains detailed latch usage statistics for each latch, including the number of times each latch was requested and waited for.

Try the following quiz to test your knowledge of these processes.
Other Processes - Quiz