Oracle DBA Interview Questions: Performance, HA and Scenarios

Oracle DBA Certification 16 min min read Updated: Nov 16, 2026 Advanced
Oracle DBA Interview Questions: Performance, HA and Scenarios
Advanced Topic 3 of 3

Performance Tuning

1. A query that was fast is now slow. What do you check?

First compare the execution plan with the earlier one (plan hash value). Check whether statistics changed, whether the data volume grew, whether an index is missing or invalid and whether other sessions are blocking it. If a plan regression is confirmed, restore statistics or use a SQL plan baseline.

2. What are optimizer statistics and why do they matter?

Information about the data such as row counts, distinct values and histograms. The cost-based optimizer uses it to choose a plan. Missing or stale statistics lead to bad plans.

3. How do you read an execution plan?

Read from the most indented operation outward, check the row counts, look for unwanted full scans and big joins and compare estimated rows (E-Rows) with actual rows (A-Rows).

4. What are bind variables and why are they important?

Placeholders for values in SQL. They let Oracle share one parsed cursor across executions, reducing hard parses, CPU use and shared pool pressure, and they help prevent SQL injection.

5. When does a full table scan beat an index?

When the query returns a large part of the table, or the table is small. Reading everything in multiblock reads can be cheaper than many single-block index lookups.

6. What is a bitmap index and when do you use it?

An index suited to columns with few distinct values, usually in reporting or warehouse systems. It is a poor choice for tables with heavy concurrent DML.

7. What is the difference between AWR, ASH and ADDM?

AWR keeps hourly snapshots of statistics. ASH samples active sessions every second for short-term analysis. ADDM analyses AWR data and gives findings and recommendations. They need the Diagnostics Pack licence.

8. What does the wait event 'log file sync' mean?

Sessions are waiting for LGWR to write redo after a commit. Causes are slow redo storage or too many small commits.

9. How do you find and resolve a blocking session?

Query v$session for blocking_session, check what the blocker is doing, ask the owner to commit or roll back, and only as a last step run ALTER SYSTEM KILL SESSION.

10. What causes a deadlock?

Two sessions each hold a lock the other needs. Oracle detects it and raises ORA-00060 in one of them. The fix is in the application, by locking rows in a consistent order.

11. What is a SQL plan baseline?

A stored accepted plan for a statement. The optimizer uses only accepted plans and tests new ones before allowing them, which prevents plan regressions after statistics changes or upgrades.

12. Is a high buffer cache hit ratio a sign of good performance?

Not necessarily. A poor query that rereads the same blocks makes the ratio look high. Judge performance by wait events, DB time and the workload instead.

High Availability and Maintenance

13. What is Data Guard?

Technology that keeps standby databases synchronised with a primary by shipping and applying redo. It provides disaster recovery with switchover and failover.

14. What is the difference between switchover and failover?

Switchover is a planned role swap with no data loss. Failover is an emergency promotion of a standby when the primary is lost.

15. Explain the Data Guard protection modes.

Maximum Performance (asynchronous, default), Maximum Availability (synchronous, no data loss while the standby is reachable) and Maximum Protection (no data loss at all, primary stops if no standby is reachable).

16. What is RAC and what does it give you?

Several instances on separate servers open the same database on shared storage. It gives high availability, scalability and rolling maintenance. Clients connect through a SCAN name and services.

17. What is ASM?

Oracle's storage manager that groups disks into disk groups, stripes and mirrors data and rebalances automatically.

18. How do you patch an Oracle database?

Download the Release Update and latest OPatch, back up, stop the services, run opatch apply on the Oracle Home, start the database and run datapatch -verbose, then check DBA_REGISTRY_SQLPATCH.

19. How would you upgrade from 19c?

Use AutoUpgrade: run analyze mode, fix findings, rehearse on a clone, create a restore point and run deploy mode. Afterward verify the registry, recompile invalid objects, test applications and take a backup.

Scenario Questions

20. The database hangs and nobody can connect. What do you do?

Check if the instance is up and the listener is running, then read the alert log. A common cause is a full archive destination or recovery area. Check v$recovery_file_dest, back up and delete archives, or add space. Also check for a runaway session.

21. A tablespace is 98% full. What do you do?

Find the biggest segments, check growth. If it is legitimate, add a datafile or enable autoextend with a limit. If it is waste (old data), purge or partition. Then add monitoring at 85%.

22. A developer dropped an important table 10 minutes ago.

Check the recycle bin and run FLASHBACK TABLE ... TO BEFORE DROP. If it was purged, use RMAN table recovery or flashback to a time before the drop.

23. The CPU is 100%. How do you investigate?

Find the top sessions and SQL using ASH or v$sql ordered by CPU time, review their plans, check for missing indexes or bad statistics, and for many hard parses. Consider Resource Manager to protect critical workloads.

24. How do you protect a production database against a disaster?

ARCHIVELOG with regular tested RMAN backups stored off-site, a standby database with Data Guard, monitoring and alerting, patching and a written and rehearsed runbook.

Final advice: In interviews, always say how you would verify your fix and how you would prevent the problem from coming back. That separates a good DBA from a great one.

Conclusion

Senior interviews focus on how you think under pressure: gather facts, form a hypothesis, test safely, fix, verify and document. Use the projects as your real stories.

Quick Summary: Plan, statistics, waits and locks for tuning. Data Guard and backups for safety. Always verify and prevent.

Get Newsletter

Subscibe to our newsletter and we will notify you about the newest updates on Edugators