Professional Oracle DBA Interview & Real-Time Q&A
1️⃣ Will archive logs generate during Online Index Rebuild?
Q: Will archive logs be generated while rebuilding an index online?
A: Yes.
-
ALTER INDEX … REBUILD ONLINEgenerates redo. -
If the database is in ARCHIVELOG mode, redo will produce archive logs.
-
Even during online rebuild, DML activity is captured in redo.
How to reduce redo generation?
ALTER INDEX emp_idx REBUILD ONLINE NOLOGGING;
⚠ Important:
-
NOLOGGINGreduces redo generation. -
However, the index becomes unrecoverable from redo during media recovery.
-
Always take a backup after using NOLOGGING.
2️⃣ What is _btree_bitmap_plans parameter?
Q: What is _btree_bitmap_plans?
A:
It is a hidden parameter that controls whether the optimizer can convert B-tree indexes into bitmap plans during query optimization.
Used when:
-
Boolean predicates
-
Multiple AND/OR conditions
-
Data warehouse environments
Example:
WHERE gender='M'
AND status='A'
Optimizer may:
-
Convert multiple B-tree indexes into bitmap
-
Perform bitmap AND/OR operations
⚠ Production Note:
-
It is an undocumented (hidden) parameter.
-
Should only be modified under Oracle Support guidance.
3️⃣ Incremental RMAN Backup Running Longer Than Usual
Q: My incremental backup is taking longer than usual. Why?
Possible Reasons:
1️⃣ Increased block changes
-
More DML since last level 0/1 backup
-
Check
v$backup_datafile
2️⃣ Block Change Tracking disabled
SELECT status FROM v$block_change_tracking;
3️⃣ Network issue between DB server and Media server
-
NIC configuration change
-
Network latency
-
Check with OS/Network team
4️⃣ I/O performance degradation
-
Check AWR for I/O wait events
5️⃣ FRA full or slow disk
4️⃣ Why Voting Disks are Odd in Number?
Q: Why must voting disks be in odd numbers?
A:
Oracle Clusterware requires more than 50% availability to maintain quorum.
Example:
-
2 voting disks → Need 2 (100%) to survive
-
3 voting disks → Need 2 (majority)
-
5 voting disks → Need 3
Odd numbers ensure:
-
Clear majority
-
Avoid split-brain scenarios
5️⃣ How long does SQL_ID stay in Shared Pool?
Q: Is there a fixed time?
A: No fixed time.
SQL remains in shared pool:
-
Until aged out due to memory pressure
-
Or manually flushed
Factors:
-
Shared pool size
-
Cursor usage frequency
-
Memory pressure
Check:
SELECT sql_id, executions
FROM v$sql
WHERE sql_id='&sqlid';
6️⃣ Why does PLAN_HASH_VALUE change?
Reasons:
1️⃣ Statistics change
2️⃣ Data volume change
3️⃣ New index creation
4️⃣ Parameter change
5️⃣ Adaptive features
Optimizer generates multiple plans and chooses lowest cost.
7️⃣ Why does CHILD_NUMBER change?
A new child cursor is created due to:
-
Different bind values (Bind Peeking)
-
Histogram present
-
Different optimizer parameters
-
Different NLS settings
-
Different bind length
-
Bind-aware cursor
-
Cardinality feedback
Important Concept:
✅ Same SQL_ID
✅ Same PLAN_HASH_VALUE
❌ Different CHILD_NUMBER possible
Example:
-
No index on column
-
Change
optimizer_index_cost_adj -
Plan remains Full Table Scan
-
But new child cursor created
8️⃣ Histogram and Skewed Data Example
Table: EMP
Rows: 1M
Gender:
-
M → 98%
-
F → 2%
With histogram:
SELECT * FROM emp WHERE gender=:b1;
If:
-
:b1='M'→ Full Table Scan -
:b1='F'→ Index Scan
Different plans due to skew.
9️⃣ SQL Profile vs SQL Plan Baseline
SQL Profile
-
Improves optimizer estimates
-
Reactive tuning
-
Doesn’t force plan
-
Uses auxiliary statistics
Used when:
-
Bad cardinality estimates
-
Need quick fix
SQL Plan Baseline (SPM)
-
Controls which plans are allowed
-
Prevents plan regression
-
Forces approved plan
-
Proactive stability control
Check:
SELECT sql_handle, plan_name, enabled, accepted, fixed
FROM dba_sql_plan_baselines;
When to use?
| Situation | Use |
|---|---|
| Immediate tuning required | SQL Profile |
| Prevent plan regression | SQL Baseline |
| Multiple PHVs exist | SQL Baseline |
| Upgrade stability | SQL Baseline |
🔟 Post 11g → 12c Migration Plan Stability
Problem:
No automatic guarantee all SQL will perform same after upgrade.
Recommended Solution:
1️⃣ Capture 11g plans into baseline
2️⃣ Import into 12c using SPM
3️⃣ Compare plans:
DBMS_XPLAN.DISPLAY_CURSOR
4️⃣ Accept new plan only if better
This avoids SLA break.
1️⃣1️⃣ Dirty Buffer & DBWR Behavior
Q: Can DBWR write uncommitted data to datafiles?
A: Yes.
Key Points:
-
Dirty buffers may contain committed & uncommitted data.
-
DBWR writes them if:
-
Free buffer shortage
-
Checkpoint
-
Tablespace offline
-
BUT:
✔ Users cannot read uncommitted data (Read Consistency)
✔ Undo ensures rollback
✔ Redo ensures recovery
Crash Recovery Process
1️⃣ Roll forward using redo
2️⃣ Roll back uncommitted using undo
Final state = Only committed data.
1️⃣2️⃣ Low vs High Cardinality
Low Cardinality:
Few distinct values
Example: Gender (M/F)
High Cardinality:
Many distinct values
Example: Employee_ID
1️⃣3️⃣ Capture vs Use SQL Plan Baseline
-
optimizer_capture_sql_plan_baselines = TRUE
→ Automatically capture new plans -
optimizer_use_sql_plan_baselines = TRUE
→ Use stored baselines during optimization
1️⃣4️⃣ Session Lock Scenario
Session A:
UPDATE emp SET sal=1000 WHERE empno=1;
-- no commit
Session B:
UPDATE emp SET sal=2000 WHERE empno=1;
Result:
-
Session B waits
-
It is a ROW LOCK
-
Not a latch
Difference:
| Latch | Lock |
|---|---|
| Protects SGA structures | Protects data rows |
| Short duration | Transaction-based |
| Memory concurrency | Data consistency |
1️⃣5️⃣ Connection Pool Timeout Issue
Error:
Timeout waiting for idle object
Unable to get connection
Causes:
-
Connection pool exhausted
-
Long-running queries
-
MaxActive too low
-
Sessions not released
-
App using 1 connection per query
Checks:
SELECT COUNT(*) FROM v$session WHERE status='ACTIVE';
Check OS:
-
ulimit -u -
max user processes
After increasing max active connections, issue resolved.
