Tuning

This article is a practical tuning script library. Each script targets a specific, common real-world performance scenario — not abstract examples, but the situations that actually appear at 2am during an incident. Scripts are annotated with where they run, what the output means, and what action to take. Reference Environment Single Instance: Host: ora19c01.prod.example.com DB Name: ORCL19C Oracle: 19.22.0.0 (Oracle Linux 8) Memory: 256 GB RAM, 32 vCPU Storage: Local NVMe (non-Exadata) CDB Environment: Host: cdb01.prod.example.com (Exadata VM) CDB Name: PRODCDB PDBs: PDB_OLTP, PDB_DWH, PDB_REPORTING Oracle: 19.22.0.0 Memory: 512 GB RAM (Exadata X8M-2 node) Scenario 1: “The Database Was Slow from 09:00 to 09:30 This Morning” Situation: Application team reports intermittent slowness on cdb01. You need to identify what happened between 09:00 and 09:30. ...

The private interconnect is the central nervous system of an Oracle RAC cluster. Poor interconnect performance cascades directly into gc cr block 2-way, gc current block 2-way, and related wait times. Before tuning any Oracle parameter, the interconnect hardware and OS configuration must be validated. This article covers the full stack — from NIC configuration to Oracle’s use of the network. Interconnect Architecture Options Technology Bandwidth Latency Use Case 1 GbE 1 Gb/s ~100–200μs Small, low-traffic clusters only 10 GbE 10 Gb/s ~20–50μs Standard current minimum 25 GbE 25 Gb/s ~10–20μs High-throughput OLAP/mixed RoCE (RDMA over Ethernet) 25–100 Gb/s ~1–5μs Modern Exadata, high-performance InfiniBand (EDR) 100 Gb/s <1μs Pre-X8M Exadata, HPC For new deployments, 25 GbE minimum is the practical recommendation. On Exadata X8M+, RoCE is the default interconnect technology, delivering near-InfiniBand latency over standard Ethernet infrastructure. ...

Oracle Real Application Testing (RAT) is the authoritative tool for validating changes to an Oracle database before production deployment. It enables you to replay a real production workload against a test system and measure the performance impact of changes such as database upgrades, parameter changes, hardware migrations, or schema modifications. This article walks through both RAT components — Database Replay and SQL Performance Analyzer (SPA) — with step-by-step procedures. RAT Component Overview Component What It Tests Best For Database Replay Entire production workload (concurrent sessions, timing, interactions) Database upgrades, hardware changes, OS upgrades SQL Performance Analyzer (SPA) Individual SQL statements from AWR SQL Tuning Set Optimizer changes, parameter tuning, index changes Both require the Oracle Real Application Testing licence option in addition to Enterprise Edition. ...

Your content starts here…