External Performance   «Prev  Next»
Lesson 2 Components of OS tuning
ObjectiveList OS components that can be tuned.

OS Components that can be tuned in Oracle

One of the most critical things for an Oracle DBA to remember is that the Oracle database is subject to resource limitations. Because an Oracle database runs within a hardware environment, external factors can greatly impact Oracle performance. The external environment for a database includes:
  1. The operating system software
  2. The network
  3. The physical disks
  4. The memory and CPU on the host

These external components are often referred to as the operating system environment, or OS for short.
An Oracle database does not operate in a vacuum. The database instance consists of a region in the memory of the computer, a section of disk devices, and a set of processes that run on the database server.

Advanced Oracle SQL Tuning
OS Component Overview
OS Overview
The image represents the architecture of an Oracle Database System running on a Unix-based operating system, depicting the interaction between various components.
Key Components in the Image:
  1. SGA Memory (Shared Global Area)
    • This is a crucial memory structure used in Oracle databases.
    • It stores data blocks, SQL execution plans, and shared SQL areas.
    • It plays a key role in optimizing performance by reducing disk I/O.
  2. Oracle Processes (PMON, SMON)
    • PMON (Process Monitor): Handles process recovery when a user process fails.
    • SMON (System Monitor): Performs crash recovery and cleans up temporary segments.
  3. Disk Storage
    • Represents the physical storage where database files (datafiles, redo logs, and control files) are stored.
    • Oracle retrieves and writes data to disk as needed.
  4. Unix I/O Cache
    • The operating system-level cache that helps optimize disk I/O performance.
    • Reduces direct disk access by caching frequently accessed data.
  5. Network Clients
    • Represents external users or applications connecting to the Oracle database over a network.
    • These clients send SQL queries and receive results via network communication.

System Workflow:
  1. Network clients interact with Oracle by sending queries.
  2. Oracle processes (PMON, SMON, etc.) manage transactions and sessions.
  3. The SGA stores frequently accessed data to reduce disk I/O.
  4. When data is required, it is fetched from Unix I/O Cache or Disk Storage.
  5. Unix I/O Cache optimizes access by reducing the number of direct disk reads.
OS Overview

  1. An Oracle instance is a memory region in the computer and a set of executing processes. The SGA memory is allocated within the OS environment at database start time, and may become paged-out if necessary.
  2. The main database processes are started within the OS environment at database start time, and these processes continue to run within the CPU while the database is running.
  3. An Oracle database has disk storage. This disk storage is mapped to the database server, and a request for Oracle data ultimately translated into an OS request to perform an I/O operation against the disk.
  4. The OS services the request and then passes the blocks into the OS buffer cache.
  5. In a client-server environment, there are remote clients that connect to the database server to get information. These clients depend upon the network transport layer to ensure that the database requests are routed quickly to the server.

Oracle 19c Operating System Components That Can Be Tuned

In Oracle 19c, "performance tuning" is a critical aspect of database administration. Several operating system (OS) components can be tuned to optimize database performance. These components are primarily related to CPU, memory, disk I/O, and network tuning.
1. CPU Tuning
a. CPU Affinity and Scheduling
  • CPU Affinity: Bind Oracle processes to specific CPUs using taskset (Linux) or Windows CPU affinity.
  • Oracle Resource Manager (DBRM): Assign CPU percentages to database users and workloads.
  • Linux Cgroups (Control Groups): Restrict Oracle database CPU usage.

Tuning Tip: 
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'DEFAULT_PLAN';

- This enables CPU-based workload management.
b. Process Priorities
  • Set process priorities using nice (Linux) or Priority Boost (Windows).
  • Use the _high_priority_processes parameter to boost specific background processes like LGWR and DBWR.

Tuning Tip
(Higher Priority for Log Writer - LGWR): 
ALTER SYSTEM SET "_high_priority_processes"='LGWR' SCOPE=SPFILE;
- Requires database restart.
2. Memory Tuning
a. SGA (System Global Area) and PGA (Program Global Area)
  • Optimize SGA and PGA memory based on system resources.
  • Use Automatic Memory Management (AMM) or Automatic Shared Memory Management (ASMM).

Tuning Tip: 
ALTER SYSTEM SET MEMORY_TARGET = 8G SCOPE=SPFILE;
ALTER SYSTEM SET MEMORY_MAX_TARGET = 10G SCOPE=SPFILE;

- Controls total memory allocation dynamically.
b. HugePages (Linux)
  • Enable HugePages to reduce memory overhead and increase performance.
  • Enable HugePages:

  1. Check HugePages Availability: 
    grep Huge /proc/meminfo
  2. Set HugePages in /etc/sysctl.conf: 
          vm.nr_hugepages = 1024

3. Disk I/O and Filesystem Tuning
a. ASM (Automatic Storage Management) Optimization
  1. Use ASM for Oracle storage instead of traditional filesystems.
  2. Adjust ASM allocation unit size (AU_SIZE) for large workloads.

Tuning Tip: 
ALTER DISKGROUP DATA SET ATTRIBUTE 'AU_SIZE' = '4M';

b. Filesystem Options
  1. Use XFS or EXT4 (Linux) or NTFS (Windows) for best performance.
  2. Enable Direct I/O and Asynchronous I/O.

- Linux Mount Options (`/etc/fstab`): 
/dev/sdb1 /u01 ext4 defaults,noatime,nodiratime,async 0 0
- `noatime, nodiratime` improve disk I/O performance.
c. I/O Scheduler Optimization (Linux) - Use `deadline` or `noop` scheduler for Oracle databases.
- Set Scheduler (`/etc/default/grub`): 
GRUB_CMDLINE_LINUX="elevator=noop"

- Then run: `update-grub && reboot`
4. Network Tuning
a. TCP/IP Stack Optimization
  1. Increase default packet size for better performance.
  2. Linux Tuning (/etc/sysctl.conf):
net.core.rmem_max = 16777216
net.core.wmem_max = 16777216
net.ipv4.tcp_window_scaling = 1

- Apply changes: `sysctl -p`
b. Oracle Net Services Optimization
- Tune SQL*Net parameters for high-performance connections.
- Optimize SQL*Net (`sqlnet.ora`): 
SQLNET.SEND_TIMEOUT = 10
SQLNET.RECV_TIMEOUT = 10
5. Parallel Execution and Query Performance
a. Parallel Query Execution
- Use parallel query processing to speed up execution.
Tuning Tip: 
ALTER SESSION ENABLE PARALLEL QUERY;
ALTER SESSION SET PARALLEL_DEGREE_POLICY = AUTO;

b. Adaptive Query Optimization
- Enable Adaptive Plans and Statistics in Oracle 19c.
Tuning Tip: 
ALTER SYSTEM SET OPTIMIZER_ADAPTIVE_PLANS = TRUE;
ALTER SYSTEM SET OPTIMIZER_ADAPTIVE_STATISTICS = TRUE;

Conclusion Tuning these OS components in Oracle 19c ensures better CPU utilization, memory management, disk I/O optimization, and network efficiency.
Note that the Oracle database background processes are ordinary processes to the OS environment. They have to wait for CPU services just like all other processes on the database server. The next lesson looks at the similarities between OS tuning and database tuning.

SEMrush Software 2 SEMrush Banner 2