Oracle DB — Cheat Sheet
Tags: oracle, database, sql, plsql, rdbms, performance, indexing
Level: intermediate
TL;DR: Quick-reference guide covering Oracle Database architecture, query optimization, execution plans, multitenancy concepts, and PL/SQL performance patterns for technical interviews.
Key concepts
Physical storage maps datafiles to OS blocks, while logical storage organizes data into blocks, extents, segments, and tablespaces.
Memory architecture divides into SGA (shared buffer cache, shared pool, redo log) and PGA (private process session memory).
Core background processes include DBWn (dirty blocks), LGWR (redo logs), CKPT (checkpoints), and SMON/PMON (instance recovery and cleanup).
Multitenant architecture isolates Pluggable Databases (PDBs) inside a shared root Container Database (CDB) instance.
Multi-Version Concurrency Control (MVCC) ensures queries see consistent data snapshots: readers never block writers and writers never block readers.
Undo segments handle transaction rollback and read consistency; redo logs record change vectors to guarantee crash durability.
The Cost-Based Optimizer (CBO) calculates access paths and join methods using statistics collected via DBMS_STATS.
B-Tree indexes optimize high-cardinality OLTP searches, whereas Bitmap indexes target low-cardinality analytical queries.
Partitioning divides massive tables logically and physically, allowing partition pruning to skip irrelevant data during scans.
PL/SQL combines SQL with procedural constructs, using bulk operations like BULK COLLECT and FORALL to minimize context switches.
Typical questions and ready answers
Q: What is the difference between TRUNCATE and DELETE in Oracle?R: TRUNCATE is a DDL operation that resets the high-water mark, deallocates extents, generates minimal redo, and cannot be rolled back. DELETE is a DML statement that removes rows individually, generates undo for rollback, fires delete triggers, and preserves allocated space.
Q: How do you troubleshoot a slow-running SQL query in Oracle?R: I inspect the actual execution plan using DBMS_XPLAN.DISPLAY_CURSOR with allstats to compare estimated versus actual row counts. Then I check for missing or invalid indexes, stale object statistics, and review wait events in V$SESSION and V$ACTIVE_SESSION_HISTORY.
Q: What is the difference between the SGA and the PGA?R: The SGA is shared memory accessible to all server processes, housing the shared pool, buffer cache, and redo buffer. The PGA is private, non-shared memory allocated to individual server processes for session data, sort areas, and hash joins.
Q: Why would you use BULK COLLECT and FORALL in PL/SQL?R: I use them to eliminate the overhead of row-by-row context switching between the PL/SQL engine and the SQL engine. BULK COLLECT retrieves multiple rows into memory arrays at once, while FORALL executes batched DML statements in a single roundtrip.
Q: What is the difference between an index unique scan, index range scan, and full table scan?R: An index unique scan finds at most one row using equality on unique keys. An index range scan accesses contiguous leaf blocks for bounded ranges or non-unique keys. A full table scan reads all blocks under the high-water mark via multi-block I/O.
Pitfalls and common mistakes
Omitting bind variables triggers excessive hard parsing in the shared pool, exhausting CPU and library cache latching.
Sorting with ORDER BY after applying ROWNUM filters data prior to sorting; use subqueries or the OFFSET/FETCH clause instead.
Applying functions directly to indexed columns in WHERE clauses disables standard B-Tree index lookups unless a function-based index exists.
Stale or missing table statistics cause the Cost-Based Optimizer to choose inefficient access paths and join orders.
Unindexed foreign key columns cause table-level share locks on the child table during parent updates, triggering deadlocks.
Processing row-by-row data with cursors instead of using set-based SQL operations or bulk array processing.
Snippets and commandsSQL
```-- Retrieve actual execution plan with runtime statistics
SELECT /*+ GATHER_PLAN_STATISTICS */ *
FROM employees
WHERE department_id = 10;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));
-- PL/SQL bulk fetch and batch update
DECLARE
TYPE t_emp_ids IS TABLE OF employees.employee_id%TYPE;
v_ids t_emp_ids;
BEGIN
SELECT employee_id BULK COLLECT INTO v_ids
FROM employees
WHERE department_id = 20;
FORALL i IN 1..v_ids.COUNT
UPDATE employees
SET salary = salary * 1.05
WHERE employee_id = v_ids(i);
COMMIT;
END;
/
-- Identify active sessions and current wait bottlenecks
SELECT sid, serial#, username, status, event, wait_class, seconds_in_wait
FROM v$session
WHERE status = 'ACTIVE' AND type != 'BACKGROUND';
```
To review
[ ] SGA memory structures: Shared Pool, Buffer Cache, Redo Log Buffer
[ ] PGA work areas: Sort Area, Hash Area, Private SQL Area
[ ] Execution plan metrics: cost, actual cardinality, predicate information, access vs filter
[ ] AWR and ASH report generation and diagnostic interpretation
[ ] Oracle Multitenant CDB and PDB management and service names
[ ] PL/SQL collections, record types, cursor variables, and autonomous transactions
References
Oracle Database Documentation (Database Concepts, SQL Language Reference)
Oracle Database Performance Tuning Guide
Expert Oracle Database Architecture (Thomas Kyte)
