Optimizing Oracle 19c SQL: Key Takeaways

• Define Clear Tuning Goals: Prioritize what matters – low response time for OLTP vs. high throughput for analytics. Tuning means hitting measurable benchmarks, not guessing.
• Design for Performance: Build scalability in from day one. Use connection pooling and write queries to “parse once, run many times.” Always use bind variables instead of literals to share cursors and avoid costly hard parses.
• Know the Optimizer: Oracle’s Cost-Based Optimizer picks plans by estimating cardinality (row counts) and cost (I/O+CPU). Accurate stats (and histograms for skew) are essential. The optimizer’s cost = I/O + CPU + memory, so even small misestimates can flip join types or access paths.
• Leverage Adaptive 19c Features: Oracle 19c can fix bad plans on-the-fly. For example, it can switch a nested-loop to a hash join mid-execution if row counts are higher than expected. It even re-optimizes SQL automatically if the first execution showed wrong cardinality. In practice this means fewer manual tune cycles – just enable OPTIMIZER_ADAPTIVE_PLANS=TRUE (the default).
• Keep Statistics Fresh: Let the database do the heavy lifting. Oracle 19c introduces real-time and high-frequency statistics gathering, so the optimizer rarely has stale data. In 19c a light-weight job runs every 15 minutes by default to update stats for tables that changed. Ensure AUTOMATIC_STATISTICS_UPDATE is ON and consider manual FULL gathers when massive data loads occur.
• Use Automated Advisors: Oracle’s tuning advisors are powerful. The SQL Tuning Advisor can analyze top SQL and even generate SQL Profiles or rewrite recommendations. The SQL Access Advisor will suggest the right indexes, materialized views or partitioning for a workload. Best of all, 19c’s Automatic Indexing can create and test indexes for you, making them visible only if they improve performance.
• Lock in Good Plans: Enable SQL Plan Baselines (SQL Plan Management) on critical queries to “whitelist” good plans. When a new plan appears, Oracle will only use it if it’s been validated. The new SPM Evolve task in 19c runs frequently to test any new plans behind the scenes and automatically accept the best one. This guards against plan regressions after stats or version changes.
• Monitor and Diagnose: Keep an eye on queries in real time. Oracle 19c automatically tracks SQL statements that run in parallel or take >5 seconds of CPU/I/O. Use DBMS_SQLTUNE.REPORT_SQL_MONITOR and DBMS_XPLAN to see exactly what’s happening. In 19c, developers can view their own SQL monitor reports without special privileges – a great way to troubleshoot slow queries on the fly.

Practical Tips: Always use bind variables to enable soft parses (reducing library-cache latches). And enable AWR/ADDM (Requires additional license pack) to find top-consuming SQL, then feed those statements to the SQL Tuning Advisor.