Start with the actual statement, binds, elapsed time and runtime row counts. Compare estimated and actual cardinalities before changing indexes.
Illustrative scenario
A query is fast for one customer but slow for another.
Explain your reasoning
Apply the answer to this scenario: How could skew affect the plan? State your assumptions, the expected outcome and how you would check it before changing a real system.
An estimated plan describes intended execution; runtime statistics reveal what actually happened for the executed cursor.
Illustrative scenario
A join estimate says ten rows but execution processes millions.
Explain your reasoning
Apply the answer to this scenario: Which operator first shows the large estimation error? State your assumptions, the expected outcome and how you would check it before changing a real system.
Common follow-up
Which operator first shows the large estimation error?
Check the applicable Oracle licensing and permissions, then choose the time interval and workload evidence carefully.
Illustrative scenario
A team wants historical performance diagnostics.
Explain your reasoning
Apply the answer to this scenario: What permitted alternatives are available in this environment? State your assumptions, the expected outcome and how you would check it before changing a real system.
Common follow-up
What permitted alternatives are available in this environment?
It can reduce repeated PL/SQL-to-SQL context switches, but set-based SQL should be considered first.
Illustrative scenario
A batch validates many records before writing results.
Explain your reasoning
Apply the answer to this scenario: How would you bound collection memory? State your assumptions, the expected outcome and how you would check it before changing a real system.
It batches supported DML using collection elements; it is not a general replacement for every procedural loop.
Illustrative scenario
Insert many validated rows with fewer context switches.
Explain your reasoning
Apply the answer to this scenario: How do you identify failed elements with SAVE EXCEPTIONS? State your assumptions, the expected outcome and how you would check it before changing a real system.
Common follow-up
How do you identify failed elements with SAVE EXCEPTIONS?
For cursor fetches, it bounds the fetched collection size per iteration and helps control memory.
Illustrative scenario
A large validation cursor cannot safely fit in one collection.
Explain your reasoning
Apply the answer to this scenario: Why is exiting before processing the last fetched batch a bug? State your assumptions, the expected outcome and how you would check it before changing a real system.
Common follow-up
Why is exiting before processing the last fetched batch a bug?
Capture the deadlock evidence, identify the resources and lock order, then shorten transactions or make access ordering consistent.
Illustrative scenario
Two sessions update the same accounts in opposite order.
Explain your reasoning
Apply the answer to this scenario: Why is retrying without changing the pattern insufficient? State your assumptions, the expected outcome and how you would check it before changing a real system.
Common follow-up
Why is retrying without changing the pattern insufficient?
A row-level trigger can attempt to read or modify a table whose row change is still in progress.
Illustrative scenario
A trigger queries its own table to calculate a cross-row total.
Explain your reasoning
Apply the answer to this scenario: Would a statement-level or compound-trigger design be appropriate? State your assumptions, the expected outcome and how you would check it before changing a real system.
Common follow-up
Would a statement-level or compound-trigger design be appropriate?
Preserve useful error context, avoid leaking sensitive data and propagate failure unless the operation can safely recover.
Illustrative scenario
A batch encounters an unknown database failure.
Explain your reasoning
Apply the answer to this scenario: Why is WHEN OTHERS THEN NULL dangerous? State your assumptions, the expected outcome and how you would check it before changing a real system.
Inspect compiler errors and dependency changes before recompiling in a controlled environment.
Illustrative scenario
A deployment changes a referenced column.
Explain your reasoning
Apply the answer to this scenario: Does recompiling fix an incompatible interface? State your assumptions, the expected outcome and how you would check it before changing a real system.
It changes atomicity, increases overhead and complicates restart and reconciliation.
Illustrative scenario
A financial batch partially commits before failing.
Explain your reasoning
Apply the answer to this scenario: What is the intended recoverable unit of work? State your assumptions, the expected outcome and how you would check it before changing a real system.
When the workload and lifecycle align with partition keys, it can support pruning and maintenance. It does not automatically make every query faster.
Illustrative scenario
A date-filtered archive query reads a small time range.
Explain your reasoning
Apply the answer to this scenario: How would you confirm pruning? State your assumptions, the expected outcome and how you would check it before changing a real system.
It can change semantics, depend on session settings and prevent an expected access path.
Illustrative scenario
A date is compared with an untyped text literal.
Explain your reasoning
Apply the answer to this scenario: How would you use explicit types and binds? State your assumptions, the expected outcome and how you would check it before changing a real system.
A plan influenced by one bind value may not suit a very different distribution. Diagnose actual cursor behaviour rather than assuming one plan fits all.
Illustrative scenario
One customer owns most rows.
Explain your reasoning
Apply the answer to this scenario: Which runtime evidence would support a skew diagnosis? State your assumptions, the expected outcome and how you would check it before changing a real system.
Common follow-up
Which runtime evidence would support a skew diagnosis?
Define a stable work key, durable progress and idempotent effects, with a clear transaction boundary.
Illustrative scenario
A job stops after processing part of a file.
Explain your reasoning
Apply the answer to this scenario: How can you tell whether the last unit committed? State your assumptions, the expected outcome and how you would check it before changing a real system.
It detects a changed row using a version or original state rather than assuming no other writer intervened.
Illustrative scenario
Two users edit the same business record.
Explain your reasoning
Apply the answer to this scenario: How should the application handle a failed version check? State your assumptions, the expected outcome and how you would check it before changing a real system.
Common follow-up
How should the application handle a failed version check?
They separate data values from statement structure and reduce injection risk for those values.
Illustrative scenario
A search filter contains quotes or SQL-like text.
Explain your reasoning
Apply the answer to this scenario: How do you validate dynamic object names? State your assumptions, the expected outcome and how you would check it before changing a real system.
Measure the expensive operation, then consider set-based SQL, bulk operations and reduced repeated lookups.
Illustrative scenario
A loop executes one lookup query for each input record.
Explain your reasoning
Apply the answer to this scenario: What correctness tests must survive the rewrite? State your assumptions, the expected outcome and how you would check it before changing a real system.
19. How do you investigate a failed scheduled job?
Inspect the job run details, error context, credentials and dependencies using authorized access.
Illustrative scenario
A nightly job starts but fails before writing its result.
Explain your reasoning
Apply the answer to this scenario: How do you safely rerun only the affected work? State your assumptions, the expected outcome and how you would check it before changing a real system.
Cover normal results, missing and duplicate data, exceptions, transaction ownership and concurrent updates.
Illustrative scenario
A package introduces new customer validation rules.
Explain your reasoning
Apply the answer to this scenario: How will existing callers react to changed errors? State your assumptions, the expected outcome and how you would check it before changing a real system.
Common follow-up
How will existing callers react to changed errors?