| Lesson 7 | CPU usage and Oracle database performance |
| Objective | Describe how Oracle manages CPU usage. |
CPU usage Database Performance
An Oracle instance consists of a number of background processes. These background processes (pmon, smon, dbwr) are executing programs within the OS, and they require CPU services in order to perform their requested tasks. Demand for the CPU within the OS is a very important external influence on Oracle database tuning. Only one process may use the CPU at any given instance, and the OS has a method for slicing processes, and giving the illusion of multiprocessing.
Many hosts contain multiple CPUs (i.e., SMP and MPP systems), but these also only allow one task to use the CPU at any time.
-
Task Box (Left Side)
- Represents a process or task that is being started on the host.
- This task is sent to the OS for CPU scheduling.
-
Clipboard with a Numbered List (Middle)
- Represents a queue or scheduling list.
- The numbers (1, 2, 3, 4) likely denote the order of execution.
- This is indicative of task scheduling and prioritization.
-
Three Laptops (Right Side)
- Represent client machines that may be running tasks.
- These tasks may be executing within a multi-user environment.
-
Descriptive Text at the Bottom:
- "As new tasks are started on the host, the OS will allocate CPU cycles based upon the 'dispatching priority' that has been assigned to the process."
- This suggests that the Oracle database system (or an OS scheduler) determines how CPU cycles are allocated based on a priority system.
- The Operating System Scheduler assigns CPU time to processes based on their dispatching priority.
- In Oracle Database, this concept applies to Oracle background processes and user sessions.
- Oracle’s Database Resource Manager (DBRM) can control CPU usage by allocating resources based on priority levels.
-
Process Scheduling in Oracle
- Oracle uses an internal process scheduler for allocating CPU time.
- High-priority tasks (like critical queries) receive more CPU time.
-
Multi-User Environments
- Multiple client sessions (represented by laptops) can run queries or transactions.
- The database server decides which queries are executed first based on priority.
-
Database Resource Manager (DBRM)
- In Oracle 19c, Database Resource Manager (DBRM) allows DBAs to limit CPU usage for users or groups.
- It assigns consumer groups to prioritize workload execution.
-
Oracle Background Processes and Dispatching Priority
- Oracle processes like LGWR (Log Writer), DBWR (Database Writer), and PMON (Process Monitor) are prioritized for system stability.
- The image illustrates CPU scheduling and task prioritization within an Oracle database system or OS.
- It highlights dispatching priorities, which determine the execution order of processes.
- Oracle Database Resource Manager (DBRM) can be used to manage CPU allocation efficiently.
Oracle Instance and Background Processes
An Oracle instance is a combination of memory structures (like the System Global Area, or SGA) and background processes that manage the database. The background processes you mentioned, PMON (Process Monitor), SMON (System Monitor), and DBWR (Database Writer)—are critical for database operation:
These processes run as separate programs within the OS and rely on CPU resources to execute their tasks. Their efficiency directly impacts database performance, making CPU demand a key factor in Oracle tuning.
CPU Scheduling and the OS: The OS manages CPU allocation through a scheduler, which "time-slices" processes to create the illusion of multiprocessing on a single CPU. This is known as context switching, where the OS rapidly switches between processes, allowing each to execute for a brief period. Only one process can use a CPU core at any given moment. If the CPU is oversaturated (e.g., too many processes vying for time), this can lead to contention, delays, and degraded database performance. Multi-CPU Systems: 1) SMP and 2) MPP
In systems with multiple CPUs, such as Symmetric Multiprocessing (SMP) or Massively Parallel Processing (MPP), the OS can distribute processes across available CPU cores. This parallelism improves throughput because multiple processes can run simultaneously, one per core. However, within a single CPU core, the limitation remains: only one task executes at a time.
For Oracle, this means:
Still, even in SMP or MPP systems, poor resource management (e.g., CPU oversubscription or I/O bottlenecks) can negate the benefits of multiple CPUs.
CPU as an External Influence on Tuning
CPU demand is indeed a critical external factor in Oracle database tuning. Key considerations include:
In practice, tools like Oracle Enterprise Manager (OEM), Automatic Workload Repository (AWR) reports, or OS utilities (e.g., top or vmstat on Unix/Linux) are used to monitor CPU usage and guide tuning efforts.
We will examine this in detail in a later module. The next lesson wraps up the module.
- PMON: Cleans up failed user processes, freeing resources like locks and memory.
- SMON: Handles instance recovery (e.g., after a crash) and performs tasks like coalescing free space.
- DBWR: Writes modified (dirty) data blocks from the buffer cache to disk.
These processes run as separate programs within the OS and rely on CPU resources to execute their tasks. Their efficiency directly impacts database performance, making CPU demand a key factor in Oracle tuning.
CPU Scheduling and the OS: The OS manages CPU allocation through a scheduler, which "time-slices" processes to create the illusion of multiprocessing on a single CPU. This is known as context switching, where the OS rapidly switches between processes, allowing each to execute for a brief period. Only one process can use a CPU core at any given moment. If the CPU is oversaturated (e.g., too many processes vying for time), this can lead to contention, delays, and degraded database performance. Multi-CPU Systems: 1) SMP and 2) MPP
In systems with multiple CPUs, such as Symmetric Multiprocessing (SMP) or Massively Parallel Processing (MPP), the OS can distribute processes across available CPU cores. This parallelism improves throughput because multiple processes can run simultaneously, one per core. However, within a single CPU core, the limitation remains: only one task executes at a time.
For Oracle, this means:
- Background processes and user sessions can run on separate cores, reducing contention.
- Tuning parameters like PARALLEL_MAX_SERVERS or adjusting process priorities can help leverage multiple CPUs effectively.
Still, even in SMP or MPP systems, poor resource management (e.g., CPU oversubscription or I/O bottlenecks) can negate the benefits of multiple CPUs.
CPU as an External Influence on Tuning
CPU demand is indeed a critical external factor in Oracle database tuning. Key considerations include:
- CPU Utilization: High utilization might indicate a need to optimize queries, reduce process overhead, or add more CPU resources.
- Wait Events: CPU bottlenecks often manifest as wait events like "CPU time" or "runnable" states in Oracle performance views (e.g., V$SESSION_WAIT).
- Context Switching: Excessive switching between Oracle processes and other OS tasks can introduce latency, requiring OS-level tuning (e.g., adjusting process priorities or reducing load).
- Multi-Core Optimization: Ensuring Oracle is configured to exploit multiple CPUs effectively (e.g., via parallel execution) is crucial in SMP/MPP environments.
In practice, tools like Oracle Enterprise Manager (OEM), Automatic Workload Repository (AWR) reports, or OS utilities (e.g., top or vmstat on Unix/Linux) are used to monitor CPU usage and guide tuning efforts.
We will examine this in detail in a later module. The next lesson wraps up the module.