Oracle Database 19c: Performance Management and Tuning Advanced Practice Exam: Hard Questions 2025

You are diagnosing a high-CPU OLTP system where AWR shows DB CPU as the top DB time component, but Top Timed Events shows very little wait time. ASH indicates many sessions spending time on 'on CPU' with frequent hard parses. The shared pool is not obviously undersized (library cache hit ratio high), and there is no parse error spike. Which action is MOST likely to reduce CPU consumption with the least risk of regressions?

A batch job runs for 2 hours nightly. You are asked to prove whether the slowdown started after a specific application deployment and whether it is due to plan changes or resource contention. You have AWR enabled and SQL Monitor was not manually used. Which approach is BEST to isolate the root cause using built-in diagnostics?

You suspect sporadic spikes in 'log file sync' during peak OLTP periods. AWR shows average 'log file sync' is low overall, but 99th percentile response time is impacted. You want to confirm whether spikes correlate with commit concurrency vs. redo I/O latency. Which method provides the MOST actionable evidence in Oracle Database 19c with minimal overhead?

A query joins a 50M-row FACT table to a 200K-row DIM table. There is a selective predicate on DIM that returns ~100 rows. Despite indexes, the optimizer chooses a HASH JOIN with full scan on FACT, causing a large TEMP spill. You verify statistics are fresh and histograms exist on DIM filter columns. Which change is MOST appropriate to encourage a nested loop plan without relying on unstable hints?

A critical query uses a function-based predicate: WHERE UPPER(last_name) = :b1. You create a function-based index on UPPER(last_name) and gather stats. The plan still shows a full table scan. You confirm the SQL uses bind variables, and last_name has a highly skewed distribution. What is the MOST likely reason and best fix?

A reporting query uses multiple CTEs (WITH clause) and is significantly slower after an application change that added an extra reference to one CTE. The execution plan now shows the CTE being re-executed multiple times, increasing I/O and CPU. You cannot change schema objects, but you can adjust the SQL text. What is the BEST approach to prevent repeated evaluation while keeping results correct?

You are tuning a query that intermittently uses drastically different plans depending on bind values, causing occasional timeouts. AWR shows the same SQL_ID but multiple child cursors with different plans. You want stable performance across varying bind values without changing application code. Which solution is MOST appropriate?

An OLTP database experiences intermittent 'free buffer waits' and elevated 'db file sequential read' during bursts. The buffer cache hit ratio is high, and SGA is already large. Investigation shows frequent scans of a moderately sized table (tens of MB) by a background job, evicting hot OLTP blocks. You cannot rewrite the job immediately. Which change MOST directly protects the OLTP working set?

A data load shows high 'direct path read temp' and 'direct path write temp' waits, with TEMP usage near limits. The SQL includes large sorts and hash joins. PGA is capped due to server constraints, but you can change database configuration and the load strategy. Which approach is MOST effective to reduce TEMP I/O without increasing PGA significantly?

You need to tune a single long-running statement that finishes but consumes excessive elapsed time and I/O. The statement uses parallelism and runs for 20+ minutes. You want near-real-time visibility into row source progress, waits, and parallel slave activity without enabling SQL trace. Which tool and workflow is MOST appropriate?