When you are preparing for an Oracle DBA interview — or troubleshooting a production issue at 2 AM — definitions alone are not enough. You need clarity. You need context. And most importantly, you need to understand how Oracle behaves under pressure.
This second part of our Oracle DBA glossary (Read Part 1 here) dives into essential concepts that often appear in interviews and real-world incidents alike: adaptive execution plans, redo log behavior, ASM disk groups, flashback technologies, optimizer decisions, and more. These are not just theoretical terms — they directly influence performance, availability, and recovery.
In this guide, I will break down each concept with:
-
What it technically means
-
Why it exists architecturally
-
How it behaves in production
-
Common mistakes DBAs make
-
Interview-ready explanation tips
Lets go deeper than definitions.
Adaptive Execution Plans
Adaptive Execution Plans allow Oracle to change join methods during runtime based on actual row counts. This feature was introduced to address cardinality misestimation by the optimizer.
For example, the optimizer may choose a Nested Loop join expecting a small dataset. But if runtime statistics show millions of rows instead, Oracle can switch to a Hash Join dynamically.
Production Perspective
Adaptive plans reduce risk from poor statistics but do not eliminate the need for proper stats gathering. If execution plans frequently change between runs, it may indicate unstable statistics.
Cost-Based Optimizer (CBO)
The CBO evaluates multiple execution plans and chooses the one with the lowest estimated cost. It depends entirely on table and index statistics.
Without fresh statistics, even well-indexed queries can degrade dramatically.
Production Perspective
Many performance issues boil down to:
-
Stale statistics
-
Skewed data
-
Incorrect histograms
Always verify statistics before blaming the optimizer.
Interview Tip
If asked about Rule-Based Optimizer — clarify it is obsolete and modern Oracle relies entirely on CBO.
Explain Plan vs SQL Trace (TKPROF)
Explain Plan shows estimated execution steps. SQL Trace shows actual runtime behaviour.
ALTER SESSION SET SQL_TRACE = TRUE;
The trace file can be analyzed using TKPROF to identify real CPU usage, physical reads, and wait events.
Production Perspective
Explain Plan may say "cost 5" but actual execution could consume heavy I/O. Always validate with real trace or SQL Monitor.
Redo Log Switch & ARCH Process
A redo log switch happens when the current redo log fills up and Oracle moves to the next group. The ARCH process then archives the full log.
If redo logs are too small, frequent switches cause:
-
Excessive checkpoints
-
"log file switch (checkpoint incomplete)" waits
Production Perspective
In high-transaction systems, aim for 15–30 minutes per log switch. Too frequent switching stresses the system.
Interview Tip
Redo logs protect committed data. Without archiving, recovery options are limited.
Flash Recovery Area (FRA)
The FRA centralizes backup and recovery files — archived logs, flashback logs, RMAN backups.
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 200G;
Production Risk
If FRA becomes full:
-
ARCH process stalls
-
Database may hang
Monitoring V$RECOVERY_FILE_DEST is mandatory.
Interview Tip
FRA simplifies space management but requires proactive sizing.
Read Consistency Mechanism
Oracle guarantees that queries see data as it existed at the start of execution. This is achieved using Undo segments.
If Undo retention is insufficient, users encounter:
ORA-01555: snapshot too old
Production Perspective
Long-running queries + high DML + small undo tablespace = ORA-01555 errors.
Interview Tip
Read consistency is block-level and powered by undo, not locking.
(read more here : Troubleshooting issues with UNDO Tablespace)
ASM Disk Groups
ASM manages disks as logical groups, automatically striping and optionally mirroring data.
Redundancy levels:
-
EXTERNAL / NORMAL / HIGH
Production Perspective
Improper disk layout causes uneven I/O distribution. Always balance disks properly.
Interview Tip
ASM simplifies storage management compared to traditional file systems.
Automatic Segment Space Management (ASSM)
ASSM replaces freelists with bitmaps to manage free space within segments. It reduces block contention in high-insert workloads.
Production Perspective
High concurrency systems benefit significantly from ASSM.
Interview Tip
Freelists are legacy. Modern systems use ASSM.
Dynamic Performance Views (V$ Views)
These memory-based views provide real-time diagnostics.
Examples:
-
v$SESSION / v$SYSTEM_EVENT / v$SQL
Production Perspective
First step during performance issue: query V$SESSION for blocking sessions.
Archivelog Mode
When enabled, redo logs are archived before reuse.
Without ARCHIVELOG mode:
-
No full recovery
-
No standby databases
-
No point-in-time recovery
Production Perspective
Never run production in NOARCHIVELOG.
Interview Tip
ARCHIVELOG mode is mandatory for high availability.
Quick Takeaways
-
Adaptive plans adjust join methods at runtime.
-
CBO depends entirely on accurate statistics.
-
SQL Trace shows actual performance, not estimates.
-
Frequent log switches indicate redo sizing issues.
-
FRA mismanagement can freeze archiving.
-
Undo retention affects read consistency.
-
ARCHIVELOG mode is essential for recovery.
Conclusion
Oracle DBA interviews increasingly test not just definitions but operational thinking. Understanding how adaptive plans react to bad statistics, how redo logs influence checkpoint behaviour, or how undo powers read consistency demonstrates maturity as a DBA.
In production, these concepts are interconnected. A full FRA can halt archiving. Small redo logs can cause checkpoint storms. Poor statistics can mislead the optimizer. And insufficient undo can break reporting queries.
The difference between a junior DBA and a production-ready DBA is context. Knowing what a feature does is step one. Knowing when it fails - and how to diagnose it., is step two. Continue mastering these foundational concepts.
FAQs
1. Why do execution plans suddenly change in production?
Often due to stale statistics, data skew, or adaptive plan behavior.
2. How do I prevent frequent redo log switches?
Increase redo log size and monitor switch frequency.
3. What causes ORA-01555?
Insufficient undo retention during long-running queries.
4. Is Explain Plan enough for performance tuning?
No. Always validate with SQL Trace or SQL Monitor.
5. Why is ARCHIVELOG mandatory?
It enables full database recovery and standby replication.
Are you preparing for interviews or actively managing production systems?
Comment below:
-
Which topic do you find most confusing?
-
Have you faced a real-world issue involving redo logs or undo retention?
If this helped, share it with fellow DBAs preparing for interviews.
refer the part 1 of this series if you have missed it.
Click here - Essential Oracle Database Keywords for DBAs - Part 1
No comments:
Post a Comment