Essential Oracle Database Interview Questions and Answers: Tuning, Backup, Recovery, and Administration

SQL Tuning Topics

1. Types of table joins: hash join, merge join, nested loop (cluster join), index join.

2. Viewing execution plan without third‑party tools:

set autot on
explain plan set statement_id = &item_id for &sql;
select * from table(dbms_xplan.display);

or use

EXPLAIN PLAN FOR SELECT * FROM EMP;
SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE'));

3. Cost‑Based Optimizer (CBO) vs Rule‑Based Optimizer (RBO): RBO uses fixed rules, CBO uses statistics; optimizer_mode=choose selects CBO when statistics exist, otherwise RBO.

4. Identifying high‑resource SQL: query v$sql where disk_reads > 1000 or ( executions > 0 and buffer_gets/executions > 30000).

5. Tracing a session’s SQL:

exec dbms_system.set_sql_trace_in_session(sid,serial,&sql_trace);
select sid,serial from v$session where sid = (select sid from v$mystat where rownum = 1);
exec dbms_system.set_ev(&sid,&serial,&event_10046,&level_12,'');

6. Primary metric for SQL tuning: response time measured by DB block gets, consistent gets, physical reads, and sorts.

7. Index types and impact: B‑tree, bitmap, function, partitioned (local/global); indexes improve SELECT/UPDATE/DELETE performance but can slow INSERT.

8. When indexes do not improve performance: using the wrong index or unsuitable query patterns.

9. Bind variables: replace literal values, reduce hard parses, lower CPU usage, but prevent histogram use and make SQL tuning harder.

10. Fixing execution plan: enable query rewrite, star transformation, set optimizer_features_enable='9.2.0', create and use stored outline.

11. Sorting memory in 8i vs 9i and role of temporary tablespace.

Database Fundamentals

1. pctused and pctfree control block reuse and freelist inclusion.

2. Relationship of table, segment, extent, and block.

3. Tablespace vs datafile relationship: a tablespace can contain multiple datafiles; datafiles store the physical objects.

4. Locally Managed Tablespaces (LMT), Dictionary Managed Tablespaces (DMT), and Automatic Segment Space Management (ASSM) characteristics.

5. Purpose of rollback segments: provide undo data for transaction rollback, recovery, and read consistency.

6. Log functions: redo logs record changes; archived logs provide offline copies for recovery.

7. SGA components: db_cache, shared_pool, large_pool, java_pool and their roles.

8. Oracle background processes: DBWR (writes dirty buffers), LGWR (writes redo), SMON (checks consistency), PMON (cleans failed processes), CHPT (checkpoint), ARCn (archives logs), CJQ (job scheduler), RECO (distributed transaction recovery).

Backup and Recovery

1. Backup classifications: logical (exp/imp) and physical (RMAN, hot backup, cold backup).

2. Meaning of archiving and its role in recovery.

3. Restoring a dropped table with proper backups:

startup mount;
alter database recover automatic until time '2004-08-04:10:30:00';
alter database open resetlogs;

4. RMAN features: physical‑backup‑like functionality, block‑level incremental, compression, backup sets, automation, scripting, bad‑block detection.

5. Standby database characteristics and protection modes: MAXIMIZE PROTECTION, MAXIMIZE AVAILABILITY, MAXIMIZE PERFORMANCE.

6. Short‑RTO backup strategy for a 50 GB database with 5 GB daily archiving: monthly level 0, weekly level 1, daily level 2 RMAN backups.

System Management

1. Diagnostic approach for performance issues: use STATSPACK, examine top events, V$SYSTEM_EVENT, V$SESSION_EVENT, V$SESSION_WAIT, identify high‑disk‑read or high buffer_gets/executions SQL via V$SQL.

2. Methods to diagnose I/O, CPU, performance: top/vmstat, STATSPACK, SQL_TRACE/TKPROF, event views, V$SQLAREA analysis.

3. Overview of STATSPACK: Oracle performance‑metric collection package, snapshots, analysis for tuning.

4. Index creation on large tables: schedule during low load, consider NOLOGGING (if no Data Guard), increase sort_area_size or PGA_AGGREGATE_TARGET.

5. RAID 10 vs RAID 5 characteristics and trade‑offs.