Episode 4. Performance tuning. Day 4 of your 10 day sprint.

Welcome. This is the episode interviewers probe deepest, because tuning without method is guessing. Your method is the drill order.

Say it first: Top wait event first, top SQL second, one hypothesis, then test.

Concepts. AWR gives the top five timed events over a window. ASH drills into who waited and on what SQL. ADDM proposes findings but you verify each one. Plans come from DBMS XPLAN display cursor, compared before and after. Nothing counts without a baseline and a quantified result.

Scenario. Order latency doubles after a Tuesday deploy. Understand: which service degraded and the exact start time. Prepare: AWR covering before and after the deploy, plus the config change list. Identify: a plan flip on the top SQL versus new contention; compare plans first. Support: pin the baseline or fix the SQL, verify latency recovery, add a plan-stability monitor.

Say this bridge once. Say: In Oracle I tune with AWR and ASH, and in a managed world I pull the same signals from Performance Insights and CloudWatch; the order never changes.

Your Day 4 lab: take any sample AWR, name the top wait in one sentence, drill to the top SQL, and write the one hypothesis you would test.
Teaser: Tomorrow, Episode 5, backup recovery and patching. The restore that proves the backup.

Second scenario. Parses explode with library cache waits. Understand: connection pattern and deploy timing. Prepare: bind usage, literal counts, cursor sharing stats. Identify: literal SQL flooding the shared pool; test forced cursor sharing or bind fixes on one node. Support: roll the fix, watch parse time fall, add literal-detection alerting.

Pause and recite the drill order without notes: waits, SQL, hypothesis, test. Again.

Close with a STAR seed: a tuning win with a before and after number. You need three of these banked. Done.