Oracle Database and PL/SQL: Architecture, SQL, and Performance

These notes treat Oracle Database and PL/SQL as parts of one system rather than isolated commands. The 2021 publication frame is preserved, while release-sensitive sections were reviewed against current Oracle documentation in August 2026. The goal is not to memorize syntax, but to understand what a SQL statement becomes in memory, on disk, in the redo stream, inside the optimizer, and under concurrent load.

Unit 1: Oracle's Runtime Model

Instance and database

In Oracle, an instance and a database are different things.

The instance consists of the SGA in memory and background processes. It disappears when the server stops. The database is the persistent set of datafiles, control files, redo logs, and related files. An instance opens a database; a database by itself does not execute queries.

The distinction becomes explicit in RAC: multiple instances can open the same database. The persistent data is shared while more than one instance performs work against it.

SGA and PGA

SGA — shared
├── Database Buffer Cache
├── Shared Pool
│   ├── Library Cache
│   └── Data Dictionary Cache
├── Redo Log Buffer
├── Large Pool
└── other components

PGA — private to a process/session workload
├── sort area
├── hash area
└── cursor and execution state

The Buffer Cache holds copies of database blocks. Oracle reads blocks, not individual rows. This is why logical I/O counts are often more useful than returned-row counts during performance analysis.

The Shared Pool contains parsed SQL, PL/SQL, and dictionary metadata. Reusing the same SQL with bind variables reduces hard parsing.

The Redo Log Buffer holds change records before LGWR persists them.

Background processes

Key processes include:

DBWn   writes dirty blocks to datafiles
LGWR   writes redo to online redo logs
CKPT   updates checkpoint information
SMON   performs system work such as instance recovery
PMON   cleans resources left by failed processes/sessions
ARCn   archives filled redo logs
MMON   feeds monitoring and AWR infrastructure
LREG   registers services with the listener

Why COMMIT can be fast

COMMIT does not wait for every dirty data block to reach its datafile. Durability requires the corresponding redo to be persisted.

DML
 ↓
dirty block in Buffer Cache
 ↓
change record in Redo Log Buffer
 ↓
COMMIT
 ↓
LGWR persists redo
 ↓
DBWn may write the data block later

This is write-ahead logging. Redo becomes durable before the changed block must reach its final datafile location, allowing crash recovery to reapply changes that were committed but not yet written to the datafile.

Remember: LGWR establishes transaction durability; DBWn persists changed database blocks.

Connection and session

A connection is the communication path between a client and Oracle server processing. A session is the database user context carried over that path.

In dedicated server mode, a server process is associated with a connection. Shared server can multiplex work over server processes. In application servers, connection pools such as HikariCP mainly avoid paying connection-establishment cost for every request.

A larger pool is not automatically faster. An oversized pool can increase database concurrency, PGA use, latch/mutex pressure, and I/O queueing. Pool size should be derived from the amount of concurrent database work the system can sustain, not merely from the number of application threads.

Release line

As of August 2026, two releases are especially relevant to production planning:

19c    Long Term Support. Premier Support is planned through
       December 2029 and Extended Support through December 2032.

21c    Innovation Release with a shorter lifecycle.

26ai   Current Long Term Support generation. Oracle AI Database
       26ai Enterprise Edition for Linux x86-64 on premises became
       generally available in January 2026 with RU 23.26.1.

Oracle uses the 26ai product name together with 23.26.x technical version numbers. For production decisions, track certification, COMPATIBLE, Release Update level, and support dates rather than relying on the marketing name alone.

Unit 2: Physical, Logical, and Multitenant Structure

Physical files

Datafile       table and index blocks
Control file   database structure, checkpoint, and file metadata
Online redo    change stream
Archive log    archived copy of filled redo
SPFILE         instance parameters
Password file  privileged administrator authentication

Control files and redo members are critical. Two copies located in the same physical failure domain do not provide real redundancy.

Logical storage

Database
└── Tablespace
    └── Segment
        └── Extent
            └── Block

A tablespace is the logical management layer. A segment is the storage allocated to an object such as a table or index. An extent is a group of blocks allocated to a segment. A block is Oracle's fundamental I/O unit.

A segment remains within one tablespace; a tablespace can span multiple datafiles.

SYSTEM and SYSAUX contain system metadata. UNDO stores older versions and rollback information. TEMP supports sorts, hashes, and other temporary work. Application objects should live in dedicated user tablespaces rather than SYSTEM.

Data dictionary

Common scopes:

USER_*   objects owned by the current user
ALL_*    objects accessible to the current user
DBA_*    database-wide metadata; requires privilege
V$       dynamic state of the local instance
GV$      dynamic state across RAC instances

DBA_TABLES describes persistent metadata; V$SESSION describes current activity. Troubleshooting requires keeping those two types of information distinct.

CDB and PDB

Modern Oracle deployment is multitenant:

CDB
├── CDB$ROOT
├── PDB$SEED
├── APP1
└── APP2

The CDB provides shared infrastructure. A PDB is the application-facing database boundary. A PDB can have its own users, tablespaces, and objects while sharing instance infrastructure with the CDB.

A common user can exist across containers. A local user exists only within its PDB. Application users should normally be local users.

ALTER SESSION SET CONTAINER = app1;
CREATE USER app IDENTIFIED BY "...";

Portability, cloning, and isolation are the practical advantages of the architecture. Many application databases can share infrastructure without requiring one full instance for each database.

UNDO mode

Local UNDO gives a PDB greater independence for relocation and recovery. It is the preferred model for current multitenant deployments.

Unit 3: Data Types, Schema Objects, and Constraints

Character data

VARCHAR2 is Oracle's primary variable-length character type. CHAR is fixed length. CLOB stores large text.

name        VARCHAR2(100 CHAR)
code        CHAR(3 CHAR)
description CLOB

BYTE and CHAR length semantics are not the same. In UTF-8, a character may consume more than one byte. Character semantics are often safer for user-facing text.

With MAX_STRING_SIZE=STANDARD, SQL VARCHAR2 is limited to 4000 bytes. With EXTENDED, the limit is 32767 bytes. It is not 32 MB. Larger text belongs in a LOB.

Numeric data

NUMBER(p,s) provides decimal precision and scale. Use NUMBER for money. BINARY_FLOAT and BINARY_DOUBLE follow IEEE 754 floating-point behavior and are better suited to scientific calculations than exact monetary arithmetic.

amount NUMBER(12,2)
ratio  NUMBER(7,6)

Date and time

Oracle DATE stores time down to seconds as well as the date.

DATE
TIMESTAMP
TIMESTAMP WITH TIME ZONE
TIMESTAMP WITH LOCAL TIME ZONE
INTERVAL YEAR TO MONTH
INTERVAL DAY TO SECOND

Use half-open ranges for date filtering:

WHERE event_time >= DATE '2026-08-22'
  AND event_time <  DATE '2026-08-23'

Wrapping the column in TRUNC(event_time) can prevent use of an ordinary B-tree index. If that expression is the real access path, a function-based index may be appropriate.

JSON, XML, and VECTOR

Native JSON allows document data to participate in relational transactions. XMLTYPE remains relevant in older enterprise integration. VECTOR stores high-dimensional embeddings and is covered later.

Tables and constraints

CREATE TABLE employee (
    id       NUMBER GENERATED ALWAYS AS IDENTITY,
    email    VARCHAR2(200 CHAR) NOT NULL,
    salary   NUMBER(12,2),
    dept_id  NUMBER,
    CONSTRAINT pk_employee PRIMARY KEY (id),
    CONSTRAINT uk_employee_email UNIQUE (email),
    CONSTRAINT ck_employee_salary CHECK (salary >= 0),
    CONSTRAINT fk_employee_dept FOREIGN KEY (dept_id) REFERENCES department(id)
);

Constraints do not replace application validation; they sit underneath it. Multiple applications, scripts, and administrative tools may touch the same data, so database constraints are the final integrity boundary.

A PRIMARY KEY is unique and not null. UNIQUE enforces uniqueness with different NULL semantics. A FOREIGN KEY preserves referential integrity. A CHECK constraint enforces a row-local condition.

Foreign-key columns often need indexes in real workloads. Oracle does not create those indexes automatically. Missing indexes can cause broad scans and locking pressure when parent rows are updated or deleted.

Sequence and identity

Sequences are designed for concurrent key generation:

CREATE SEQUENCE seq_order CACHE 100;

A sequence does not guarantee gapless numbers. Cache loss, rollback, and concurrent sessions create gaps. Regulatory document numbers that must be gapless should not be modeled as ordinary sequence values.

Identity columns remove visible sequence handling from application DDL but solve the same class of key-generation problem.

Views and materialized views

A view is stored query logic and normally stores no rows. A materialized view stores the result physically and can precompute expensive aggregations.

With QUERY REWRITE, the optimizer may redirect eligible queries to a materialized view. FAST REFRESH has prerequisites such as materialized view logs and should be designed deliberately.

Temporary tables and synonyms

A global temporary table has a persistent definition and temporary data scoped to a transaction or session. A private temporary table also has a temporary definition.

A synonym abstracts an object name. Public synonyms affect a database-wide namespace and should be used sparingly.

Unit 4: DML, Transactions, and Concurrency

Core DML

INSERT INTO target (...) VALUES (...);
UPDATE target SET ... WHERE ...;
DELETE FROM target WHERE ...;
MERGE INTO target t USING source s ON (...) ...;

MERGE expresses update/insert behavior in one statement. The source still has to be designed so that a target row is not matched ambiguously by multiple source rows.

RETURNING INTO returns changed values to PL/SQL without a second query.

Transaction boundaries

first DML
  ↓
transaction
  ↓
COMMIT     makes work durable
ROLLBACK   undoes work
SAVEPOINT  creates a partial rollback point

DDL has implicit commit behavior, which is particularly important in deployment and maintenance scripts.

Read consistency

Oracle's MVCC model normally allows readers and writers to proceed without blocking one another. A query reconstructs the required older block version from UNDO to preserve a consistent view.

READ COMMITTED is the default: each SQL statement gets its own statement-level snapshot. SERIALIZABLE provides a more stable transaction view but can reject conflicting writes. Oracle does not provide dirty reads.

Locking

SELECT * FROM job_queue
WHERE status = 'READY'
FOR UPDATE SKIP LOCKED;

FOR UPDATE is pessimistic locking. NOWAIT fails immediately instead of waiting. WAIT n limits wait time. SKIP LOCKED lets queue consumers take different rows without waiting behind rows already claimed by another worker.

Optimistic locking does not hold a row while a user is thinking; it checks a version at update time:

UPDATE document
SET body = :body, version = version + 1
WHERE id = :id AND version = :expected;

Zero affected rows means another transaction changed the row first.

Deadlocks

A deadlock occurs when transactions form a wait cycle. Oracle detects the cycle and aborts one statement with an error. The application must decide what to do with the remaining transaction state.

The simplest prevention rule is consistent lock ordering: update shared resources in the same order along every code path.

Hot rows and lock-free reservations

Modern Oracle releases support RESERVABLE numeric columns for workloads such as stock and balances where many sessions contend on the same row. Changes are represented as reservations and checked for validity at commit time, reducing conventional row-lock contention.

Use the feature when the measured problem is truly hot-row update contention. It is not a substitute for sound data modeling.

Unit 5: SELECT, JOIN, NULL, and Subqueries

Logical processing order

SQL is not logically evaluated in the order in which it is written:

FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY

This explains, for example, why a SELECT alias is generally unavailable in WHERE but can be used in ORDER BY.

JOIN

INNER JOIN       matched rows only
LEFT JOIN        all rows from the left
RIGHT JOIN       all rows from the right
FULL OUTER JOIN  all rows from both sides
CROSS JOIN       Cartesian product

With an outer join, moving a filter from ON to WHERE can change semantics:

LEFT JOIN department d
  ON d.id = e.dept_id AND d.active = 'Y'

is not equivalent to:

LEFT JOIN department d ON d.id = e.dept_id
WHERE d.active = 'Y'

The second form rejects NULL-extended rows and can effectively collapse the outer join toward inner-join behavior.

NULL

NULL means unknown, not zero or an empty value.

NULL = NULL      is not TRUE
x IS NULL        is the correct test
COUNT(*)         counts rows
COUNT(x)         counts rows where x is not NULL

Oracle historically treats the zero-length character string as NULL, which can surprise code moved between database engines.

The NOT IN trap

If the subquery can return NULL, NOT IN can make the predicate unknown for every candidate row. For anti-join intent, NOT EXISTS is usually safer:

WHERE NOT EXISTS (
    SELECT 1 FROM department d
    WHERE d.id = e.dept_id
)

Subqueries and EXISTS

A scalar subquery returns one value. A correlated subquery references the outer row. Modern optimizers can transform many IN and EXISTS forms into similar physical plans, so semantics and NULL behavior matter more than old performance folklore.

CTE

WITH divides a complex query into named steps:

WITH dept_avg AS (
  SELECT dept_id, AVG(salary) avg_salary
  FROM employee
  GROUP BY dept_id
)
SELECT e.*
FROM employee e
JOIN dept_avg d ON d.dept_id = e.dept_id
WHERE e.salary > d.avg_salary;

A CTE is not automatically a temporary table. The optimizer may inline or materialize it depending on the plan.

Recursive queries and CONNECT BY

Hierarchical data can be traversed with ANSI recursive subquery factoring or Oracle's CONNECT BY syntax.

SELECT LEVEL, name
FROM employee
START WITH manager_id IS NULL
CONNECT BY NOCYCLE PRIOR id = manager_id;

NOCYCLE protects against malformed cycles. LEVEL, CONNECT_BY_ISLEAF, and SYS_CONNECT_BY_PATH are useful for hierarchy reporting.

Unit 6: Functions, Aggregation, and Analytic SQL

Avoid implicit conversion

WHERE event_time >= DATE '2026-01-01'

is more robust than:

WHERE event_time >= '01-JAN-26'

The latter depends on session NLS settings. The same rule applies to numeric conversions. Implicit conversion can create both correctness problems and index-access problems.

Character, date, and regex functions

SUBSTR, INSTR, REPLACE, TRIM, UPPER, LOWER, REGEXP_*, ADD_MONTHS, LAST_DAY, TRUNC, EXTRACT, TO_CHAR, and TO_DATE form the core toolset.

Case conversion and collation for Turkish characters such as i/İ depend on globalization settings and should be tested explicitly in multilingual systems.

Regular expressions are powerful but expensive. If a simpler LIKE or a purpose-built function-based index represents the real access path, use the simpler mechanism.

Aggregate versus analytic

An aggregate collapses rows into groups. An analytic function preserves rows and adds a window calculation.

SELECT name, dept_id, salary,
       AVG(salary) OVER (PARTITION BY dept_id) AS dept_avg
FROM employee;

Many row-by-row correlated calculations can be expressed as one analytic pass.

Ranking

ROW_NUMBER   unique sequence per row
RANK         ties share a rank and leave gaps
DENSE_RANK   ties share a rank without gaps

For deterministic ROW_NUMBER, the ORDER BY must break ties completely.

LAG and LEAD

These functions access neighboring rows without self-joins:

SELECT month, amount,
       LAG(amount) OVER (ORDER BY month) previous_amount,
       amount - LAG(amount) OVER (ORDER BY month) delta
FROM monthly_sales;

ROWS and RANGE

ROWS defines windows by physical row positions. RANGE includes peer rows based on ordering values. Functions such as LAST_VALUE can return surprising results under the default window, so the intended frame should be explicit.

ROLLUP, CUBE, and GROUPING SETS

These constructs compute subtotals in a single grouping operation. They are usually clearer and often cheaper than rereading the same data through multiple UNION ALL branches.

PIVOT and UNPIVOT

PIVOT turns row values into columns; UNPIVOT does the reverse. Dynamic column sets may require dynamic SQL or transformation in the application layer.

Set operations, ROWNUM, and ROWID

UNION removes duplicates, while UNION ALL concatenates results without duplicate elimination. INTERSECT returns common rows; MINUS returns rows from the first query that are absent from the second. When both express the same requirement, UNION ALL is cheaper because it avoids duplicate elimination.

ROWNUM is assigned as rows are produced. This is why classic top-N queries place ordering in an inner query. From 12c onward, FETCH FIRST is usually clearer.

ROWID represents a row's physical/logical storage address and can provide very fast single-row access. It is not a business key. Row movement, export/import, and table reorganization can change it; applications should not persist it as identity.

FETCH FIRST and pagination

SELECT ...
ORDER BY id
FETCH FIRST 100 ROWS ONLY;

Deep OFFSET pagination remains expensive because skipped rows still have to be located and ordered. Keyset pagination is more stable for large data sets:

WHERE id > :last_id
ORDER BY id
FETCH FIRST 100 ROWS ONLY;

Unit 7: Index Structures

Indexes have a write cost

An index can accelerate reads, but every relevant INSERT, UPDATE, and DELETE must maintain it. Unused indexes consume more than disk: they create redo, undo, cache activity, and write latency.

B-tree

The B-tree is the default structure for equality and range access.

CREATE INDEX ix_employee_dept_salary
ON employee(dept_id, salary);

Column order matters in a composite index. An (a,b) index naturally supports access paths starting with a. The simplistic rule "put the most selective column first" is incomplete; real query patterns, join predicates, range predicates, and ordering requirements must be considered together.

Covering access

If all columns required by a query can be obtained from the index, table access may be avoided. Adding every column to an index, however, merely moves cost into writes and cache footprint.

Function-based index

CREATE INDEX ix_lastname_upper ON employee(UPPER(last_name));

The query expression must be compatible with the indexed expression. Function-based indexing is not a substitute for understanding the query shape.

Bitmap index

Bitmap indexes are effective for low-cardinality analytic dimensions. They are not a default choice for DML-heavy OLTP because bitmap locking can create broad write contention.

Reverse key, invisible indexes, and IOT

A reverse key index can distribute inserts of monotonically increasing keys across leaf blocks, reducing right-edge hot spots, but sacrifices normal range scans.

An invisible index is useful for testing optimizer behavior before actually dropping an index.

An index-organized table stores row data in the primary-key index itself. It favors primary-key access while changing the cost of other access paths.

NULL and B-tree indexes

A conventional single-column B-tree does not contain an entry when all indexed key components are NULL. This matters for IS NULL access. A design may use an additional non-null expression or a function-based index when NULL lookup is a real requirement.

Routine rebuild is not maintenance

Oracle B-trees maintain balance as they change. Rebuilding every index on a calendar schedule is not sound maintenance. Rebuild only when a measured condition justifies it.

Automatic indexing

Automatic Indexing can observe workload, test candidate indexes, and implement useful ones under supported deployment and licensing conditions. It is best treated as a measured advisor/automation system, not as a replacement for schema design.

Unit 8: Partitioning

Why partition?

Partitioning presents one logical table to the application while dividing storage and optimizer access into smaller units.

Major benefits include:

• partition pruning,
• fast lifecycle maintenance by range,
• partition-level availability,
• parallel processing,
• archival operations.

Types

RANGE       ordered domains such as dates
INTERVAL    automatically extends a range design
HASH        distributes values across buckets
LIST        discrete business values
COMPOSITE   combines two schemes
REFERENCE   aligns child partitioning with the parent

Partition pruning

Read performance improves only when predicates let the optimizer eliminate irrelevant partitions. Hiding the partition key behind an unsuitable function, or not filtering on it at all, can remove the expected benefit.

Check real plans using partition operations and PSTART/PSTOP.

Local and global indexes

Local index partitions align with table partitions and simplify maintenance. Global indexes support access patterns that cross partition boundaries but require more care during partition maintenance.

EXCHANGE PARTITION

ALTER TABLE sales
EXCHANGE PARTITION p2025
WITH TABLE sales_archive_2025
INCLUDING INDEXES WITHOUT VALIDATION;

When structures are compatible, exchange can detach or attach very large data sets through metadata operations rather than row-by-row movement.

Unit 9: Optimizer, Statistics, and Execution Plans

The optimizer's task

The same SQL result can be produced through many physical plans. Oracle's cost-based optimizer uses statistics to estimate the cheapest one.

The first question is not "is there an index?" but how many rows does the optimizer expect, and why?

Statistics

Important inputs include:

• row and block counts,
• number of distinct values,
• NULL counts,
• min/max values,
• histograms,
• index statistics.

EXEC DBMS_STATS.GATHER_TABLE_STATS(
  ownname => 'APP', tabname => 'EMPLOYEE', cascade => TRUE);

Gathering statistics is not a universal fix for slow SQL. Unnecessary or badly timed statistics changes can produce new plan regressions.

Histograms

When column values are skewed, a simple average selectivity estimate can be wrong. Histograms carry distribution information. They should be created based on predicate usage and skew, not indiscriminately on every column.

Access paths

Full Table Scan
Index Unique Scan
Index Range Scan
Index Full Scan
Index Fast Full Scan
Rowid access
Partition access

A full table scan is not inherently a bad plan. If a large fraction of a table is needed, sequential multiblock I/O may be cheaper than thousands of random index-to-table lookups.

Join methods

Nested loops are strong when the outer set is small and inner lookup is cheap. Hash joins are common for larger equality joins. Sort merge joins are useful with suitable ordering or inequality conditions.

A poor join method is often the consequence of a poor cardinality estimate.

Bind variables and plan selection

Bind variables reduce parsing and shared-pool churn:

SELECT * FROM orders WHERE customer_id = :id;

Highly skewed data can make one plan unsuitable for every bind value. Bind peeking and adaptive cursor sharing attempt to manage this tension. Measure the actual distribution and child-cursor behavior before forcing a plan.

Actual execution statistics

SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));

Estimated plans alone are not enough. When possible, compare actual rows with estimated rows. Large divergence points directly to selectivity/cardinality problems.

Hints

Hints are controlled interventions, not first-line tuning. A hint that encodes today's data distribution can become tomorrow's regression.

Plan stability

SQL Plan Baselines can preserve known-good plans. SQL Profiles can improve optimizer estimates. SQL Patches can influence a statement without editing application code. None of them replaces root-cause analysis.

SQL Monitor

SQL Monitor is valuable for long-running or parallel SQL because it shows where time and rows actually accumulate. AWR, ASH, SQL Monitor, and some tuning features have Diagnostic/Tuning Pack licensing implications that must be checked for the target deployment.

Unit 10: PL/SQL Fundamentals, Cursors, and Bulk Processing

Why PL/SQL exists

SQL is set-oriented: it describes the data result. PL/SQL adds control flow, error handling, and modular procedural code. Good PL/SQL does not rewrite work that SQL already performs efficiently as a set operation.

DECLARE
    v_total NUMBER;
BEGIN
    SELECT SUM(amount) INTO v_total
    FROM orders
    WHERE order_date >= TRUNC(SYSDATE,'MM');

    DBMS_OUTPUT.PUT_LINE(v_total);
EXCEPTION
    WHEN OTHERS THEN
        RAISE;
END;
/

A block can contain DECLARE, BEGIN, EXCEPTION, and END; only the executable block is essential.

%TYPE and %ROWTYPE

v_salary employee.salary%TYPE;
v_row    employee%ROWTYPE;

These declarations derive types from the schema rather than duplicating them in code. When a column type changes, dependent PL/SQL can be recompiled against the new definition.

Loop or SQL?

This pattern creates unnecessary context switching:

FOR r IN (SELECT id FROM employee WHERE dept_id = 10) LOOP
    UPDATE employee SET salary = salary * 1.05 WHERE id = r.id;
END LOOP;

The set-oriented form is simpler:

UPDATE employee
SET salary = salary * 1.05
WHERE dept_id = 10;

Rule: use one set-based SQL statement first; use PL/SQL when the requirement is genuinely procedural.

Cursors

Oracle manages an implicit cursor for each DML statement and SELECT INTO. Attributes such as SQL%ROWCOUNT and SQL%FOUND expose the result.

An explicit cursor is useful when a result set must be traversed procedurally:

CURSOR c_dept(p_id NUMBER) IS
    SELECT id, name FROM employee WHERE dept_id = p_id;

A cursor FOR loop removes most manual OPEN/FETCH/CLOSE boilerplate.

REF CURSOR

A REF CURSOR can return an open result set to the caller. It is useful for dynamic server-side APIs, although modern JDBC applications can often expose a normal SQL result set more directly.

Collections

PL/SQL provides three core collection families: associative arrays, nested tables, and VARRAYs. An associative array is primarily an in-memory key/value structure. A nested table can also integrate with SQL types and can grow dynamically. A VARRAY preserves order and has a declared maximum size. Methods such as COUNT, FIRST, LAST, NEXT, PRIOR, EXTEND, TRIM, and DELETE manage collection state.

The choice is not merely syntax: PGA footprint, SQL interoperability, ordering, and maximum-size requirements all matter.

BULK COLLECT and FORALL

Crossing between the PL/SQL engine and SQL execution engine for every row is expensive. BULK COLLECT fetches batches; FORALL performs batched DML.

SELECT id BULK COLLECT INTO v_ids
FROM job_queue
WHERE status = 'READY'
FETCH FIRST 1000 ROWS ONLY;

FORALL i IN 1..v_ids.COUNT
    UPDATE job_queue
    SET status = 'DONE'
    WHERE id = v_ids(i);

Bulk collection consumes PGA. Large workloads should be processed in bounded batches rather than loading an unbounded result into memory.

Pipelined table functions

A pipelined function can return rows as they are produced instead of materializing the full result in PL/SQL memory. Use it for real transformations, not merely to wrap a query in procedural code.

Unit 11: Exceptions, Procedures, Functions, Packages, and Triggers

Exceptions

An Oracle error propagates unless it is handled. WHEN OTHERS THEN NULL does not solve the error; it makes the failure invisible.

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        ...
    WHEN DUP_VAL_ON_INDEX THEN
        ...
    WHEN OTHERS THEN
        ...
        RAISE;

SQLCODE, SQLERRM, and DBMS_UTILITY.FORMAT_ERROR_BACKTRACE are useful diagnostic tools.

RAISE_APPLICATION_ERROR

Business-rule violations can be surfaced with an application error:

RAISE_APPLICATION_ERROR(-20001, 'Insufficient balance');

The message is not a logging strategy. Production diagnostics should still carry transaction and correlation context.

Autonomous transactions

An autonomous transaction can persist information even if the caller rolls back. This can be useful for error logging. It should not be used casually to detach business-state changes from the transaction that owns them.

Procedures and functions

A procedure performs an operation. A function returns a value. A function with surprising DML side effects is harder to reason about and harder to use safely from SQL.

CREATE OR REPLACE FUNCTION tax(p_amount NUMBER)
RETURN NUMBER DETERMINISTIC IS
BEGIN
    RETURN p_amount * 0.20;
END;
/

DETERMINISTIC must only be declared when the function truly returns the same result for the same inputs. It is a correctness contract, not a generic optimization flag.

RESULT_CACHE can cache stable, frequently read results in the SGA. PRAGMA UDF can reduce SQL-to-PL/SQL call overhead. SQL Macros can go further by expanding SQL text rather than performing a PL/SQL call at execution time.

Packages

A package specification is the public contract; the package body is the implementation. Packages group related procedures, functions, types, and constants behind one namespace.

Good package design favors:

• a small, stable public API,
• implementation details hidden in the body,
• minimal session-global state,
• transaction ownership left to the caller,
• explicit SQL and PL/SQL dependencies.

Putting COMMIT into every reusable procedure damages composability. Transaction boundaries belong to the owner of the business operation.

Triggers

A trigger runs automatically in response to an event. Its power comes with a cost: behavior can be hidden from the caller.

Appropriate cases include:

• carefully scoped audit requirements,
• INSTEAD OF DML on views,
• legacy integrity behavior,
• selected DDL/system events.

Core workflow logic hidden across many triggers becomes difficult to test and migrate.

Mutating table

A row-level trigger that queries the table currently being modified can hit the mutating-table restriction. Compound triggers help coordinate row-level collection with statement-level processing.

Prefer constraints where possible

If a rule can be expressed as NOT NULL, UNIQUE, CHECK, or FOREIGN KEY, use the declarative constraint instead of a trigger. Constraints are visible to the optimizer and administration tools; procedural trigger behavior is more opaque.

Unit 12: Dynamic SQL and Database Security

EXECUTE IMMEDIATE

Dynamic SQL is appropriate when the SQL structure itself must vary at runtime.

v_sql := 'SELECT COUNT(*) FROM employee WHERE dept_id = :1';
EXECUTE IMMEDIATE v_sql INTO v_count USING p_dept;

Values should be passed as binds, not concatenated into SQL. Bind variables reduce SQL injection risk and repeated parsing.

Object names cannot be bound

Dynamic table or column names require an allow-list:

IF p_table NOT IN ('EMPLOYEE','DEPARTMENT') THEN
    RAISE_APPLICATION_ERROR(-20001,'Invalid table');
END IF;

v_sql := 'SELECT COUNT(*) FROM ' ||
         DBMS_ASSERT.SIMPLE_SQL_NAME(p_table);

DBMS_ASSERT is a validation utility, not a substitute for a safe dynamic-SQL design.

DBMS_SQL

When the number or types of columns and binds are themselves unknown until runtime, DBMS_SQL provides lower-level control. Prefer EXECUTE IMMEDIATE when the dynamic shape is simple and known.

Object types, PRAGMA, and conditional compilation

Oracle object types can combine data and behavior in a schema type and can be used with collection types such as VARRAY and nested tables. They are useful in selected interfaces, but they are not an automatic replacement for ordinary relational modeling.

A PRAGMA gives the compiler or runtime special instructions. AUTONOMOUS_TRANSACTION, EXCEPTION_INIT, UDF, and INLINE each address a different concern. A pragma should make a measured requirement explicit, not hide behavior.

Conditional compilation through PLSQL_CCFLAGS and $IF can keep version- or deployment-specific branches in one source tree. If branches proliferate, they are often a sign of architectural divergence that deserves a higher-level solution.

Users, privileges, and roles

Apply least privilege.

System privilege   database-level capability such as CREATE SESSION
Object privilege   SELECT/INSERT/EXECUTE on a specific object
Role               a named set of privileges

Privileges such as SELECT ANY TABLE expand authority across the database and are rarely appropriate for an application runtime account.

Separate schema ownership from runtime application identities. The runtime account should not normally possess DDL authority.

Profiles

Profiles can enforce password and resource policies. In applications that use connection pools, session and idle limits must be tested against pool behavior to avoid artificial disconnect storms.

Unified Auditing

Auditing records who did what, where, and when. Logging every possible action without a threat or compliance model can produce large volumes with little investigative value. Focus on high-risk access, security events, and regulatory requirements.

VPD

Virtual Private Database can add predicates based on user/session context, enforcing row-level access in the database rather than relying on every application query to remember the filter.

TDE, masking, and redaction

Transparent Data Encryption protects data at rest. It does not replace TLS in transit. Data Redaction can mask query results based on policy without modifying the stored value.

SQL Firewall

In Oracle AI Database 26ai, SQL Firewall is built into the database kernel. It can capture expected SQL and connection contexts for selected accounts, generate allow-lists, and log or block SQL outside the baseline.

SQL Firewall is defense in depth. It does not replace bind variables, least privilege, or secure application design. Current licensing requirements must also be checked before use.

Unit 13: Space Management, UNDO, and Flashback

Blocks and row layout

Oracle stores rows in blocks. If an updated row no longer fits in its original block, row migration can occur. If a row is too large for one block from the start, row chaining may be required. Both can increase block access.

PCTFREE reserves space for rows expected to grow through updates. With modern ASSM, Oracle manages free-space structures automatically.

Segment space

Extent growth, tablespace usage, and autoextend settings must be monitored. Autoextend is not capacity planning; it can simply postpone failure until the underlying storage is full.

Compression

Table and index compression can reduce I/O but may add CPU cost and have edition/license implications depending on the feature. Measure compression on representative data instead of relying on theoretical ratios.

What UNDO does

UNDO serves three core purposes:

1. rollback,
2. consistent reads,
3. part of the flashback feature set.

A long query may require an older block version. If the required undo has already been reused, ORA-01555: snapshot too old can occur.

The answer is not always "make UNDO larger." Query duration, undo generation rate, retention goals, and excessive commit frequency all matter.

Flashback Query

SELECT * FROM orders
AS OF TIMESTAMP SYSTIMESTAMP - INTERVAL '10' MINUTE
WHERE id = 100;

Flashback Query reads an earlier consistent image. Flashback Table can rewind an object. Flashback Database provides broader rewind capability when its prerequisite infrastructure has been configured.

Flashback is not backup. It does not replace RMAN or Data Guard for storage loss, site loss, or broken recovery chains.

Unit 14: Backup, Recovery, and Data Movement

RPO and RTO

Define two goals before choosing tools:

• RPO: acceptable data loss.
• RTO: acceptable service outage.

"We back up every night" is not a recovery architecture. Backup frequency, archive retention, and standby design must be derived from RPO and RTO.

ARCHIVELOG

In ARCHIVELOG mode, filled online redo logs are archived. This is foundational for online backup and point-in-time recovery in production systems.

RMAN

RMAN is Oracle's physical backup and recovery tool.

full backup
incremental level 0 / level 1
archive log backup
control file / SPFILE backup
backup validation
restore / recover

RMAN understands Oracle block structures and recovery dependencies.

Restore is not recover

Restore retrieves files from backup. Recover applies redo/archive logs to advance those files to a target SCN or time.

Understanding this distinction is fundamental to recovery.

FRA

The Fast Recovery Area is a managed location for archive logs, flashback logs, and backup files. If undersized, archive pressure can ultimately affect production. It must be sized against actual redo and retention behavior.

Validate the backup

A successful backup job does not prove recoverability. Use validation, test restores, and regular recovery drills.

Data Pump

expdp and impdp perform logical movement of schemas, tables, and metadata. They are not replacements for physical RMAN backup.

SQL*Loader and external tables

Large flat-file loads are often better handled by SQL*Loader or external tables than by row-by-row application inserts. Direct path, constraint, index, and redo behavior must be considered together.

Transportable tablespaces

For very large data sets, transportable tablespaces can move datafiles and transfer metadata instead of logically exporting every row. Platform compatibility and self-contained checks remain important.

Database links

A database link exposes remote objects to local SQL. It is convenient, but adds network latency, credential exposure, distributed-transaction behavior, and remote-failure dependency. It is not a default integration mechanism for high-volume service boundaries.

Unit 15: Observability and Performance

Performance is often a wait problem

A database session either consumes CPU or waits for something. CPU percentage alone does not explain response time.

Typical wait classes include:

User I/O
System I/O
Concurrency
Commit
Network
Application
Configuration
Cluster

A high wait event is not automatically a defect. Its contribution to DB time and workload context matter.

V$SESSION and V$SQL

For a live incident, find active sessions and expensive SQL first.

SELECT sid, serial#, username, event, wait_class, sql_id
FROM v$session
WHERE status = 'ACTIVE';

Do not rank SQL only by duration per execution. A 2 ms statement executed millions of times can consume more resources than a 20-second statement run once per day.

Useful ratios include:

• elapsed time per execution,
• CPU per execution,
• buffer gets per execution,
• disk reads per execution,
• rows per execution.

AWR and ASH

AWR preserves performance snapshots. ASH samples active sessions. Together they answer "which SQL and wait dominated this time range?"

AWR/ASH use is subject to Oracle Diagnostic Pack licensing requirements.

SQL Trace and TKPROF

For detailed single-session analysis:

EXEC DBMS_MONITOR.SESSION_TRACE_ENABLE(
  session_id => :sid,
  serial_num => :serial,
  waits      => TRUE,
  binds      => TRUE);

Tracing can generate substantial volume in production. Enable it narrowly and for a bounded duration.

ADDM and advisors

Advisor output is a recommendation, not a verdict. An index or SQL Profile recommendation should be evaluated against write cost and the full workload.

Resource Manager

Resource Manager can constrain CPU, parallelism, and execution behavior by consumer group. It is useful when reporting or batch workloads must not destroy OLTP latency.

A practical diagnostic order

1. Define the affected time window
2. Identify DB time and dominant wait classes
3. Find the SQL consuming the resource
4. Inspect actual plans and cardinality errors
5. Separate I/O, lock, parse, network, and CPU causes
6. Change one thing
7. Re-measure with the same metric

"Add an index" is not a diagnosis.

Unit 16: RAC, ASM, Data Guard, and GoldenGate

What RAC solves

Oracle RAC allows multiple instances to open the same database concurrently. Its primary goals are local high availability and scale for suitable workloads.

Instance 1 ─┐
Instance 2 ─┼─ shared database storage
Instance 3 ─┘

RAC is not disaster recovery. All instances serve the same database. Regional protection requires an independent copy.

Cache Fusion

If the current block is in another instance's buffer cache, RAC can transfer the block over the interconnect instead of forcing a disk round trip.

This is fast, but not as cheap as local memory. Constant movement of the same hot blocks between instances creates global cache contention.

gc wait events are central to RAC diagnosis.

Interconnect

The interconnect should be low latency, high bandwidth, and redundant. In RAC, network latency can become database latency directly.

Services

Applications should connect through services rather than hard-coded instance names. Services support workload placement and controlled relocation during maintenance and failure.

FAN, Fast Connection Failover, and Application Continuity-class capabilities determine how the application experiences an outage, not just whether the database process survives.

ASM

Automatic Storage Management is Oracle's database-oriented volume and file-management layer.

Disk Group
├── disk/failure group
├── striping
├── redundancy
└── online rebalance

Redundancy choices such as EXTERNAL, NORMAL, and HIGH must match the underlying storage architecture. With external redundancy, the storage platform must provide the actual protection.

Naming two logical disk groups +DATA and +FRA does not prove physical failure isolation. If both sit on the same storage failure domain, the names provide no disaster protection.

Data Guard

Data Guard ships redo from a primary to one or more standby databases.

Primary ── redo ──> Standby

A physical standby is maintained through redo apply at the block level. A logical standby offers more structural flexibility through SQL Apply. A snapshot standby can temporarily become writable for testing and later return to the standby role.

Protection modes

Maximum Performance
Maximum Availability
Maximum Protection

The choice is an RPO/latency trade-off. Synchronous transport to a distant standby can put WAN latency into the commit path.

Switchover and failover

A switchover is a planned role reversal. A failover promotes a standby when the primary is unavailable. Fast-Start Failover can automate promotion under configured conditions through broker/observer components.

RAC versus Data Guard

RAC         multiple instances against one database
Data Guard  separate database copy kept current through redo

RAC addresses node/local service failure. Data Guard addresses database/site failure. They can be combined.

GoldenGate

GoldenGate performs logical, selective change replication and is used for heterogeneous integration, low-downtime migration, and active-active designs.

Data Guard   redo-oriented disaster recovery
GoldenGate   logical change replication and integration

They solve different problems and may coexist.

Unit 17: Exadata, Autonomous, and Distributed Oracle

What makes Exadata different

Exadata is not merely faster storage. Database servers and storage servers are co-designed. Smart Scan and related mechanisms can push filtering and selected processing into the storage layer, reducing data transferred to database nodes.

High-bandwidth fabric, flash tiers, storage indexes, and offload mechanisms can materially help large scans and dense consolidation workloads.

Exadata does not make bad SQL correct. A query that reads unnecessary rows and columns still performs unnecessary work on expensive hardware.

Autonomous Database

Autonomous Database moves provisioning, patching, backup, scaling, and selected tuning tasks into a managed service. In return, direct operating-system and parameter control is reduced.

The decision is broader than DBA headcount: networking, data residency, cost, vendor dependency, compliance, and operating model all matter.

Sharding and Globally Distributed Database

Sharding horizontally distributes data across independent database shards. Unlike RAC, the shards do not share one storage image.

shard key → target shard → local transaction

A query without the shard key may fan out across shards. Sharding therefore changes the application data model before it changes infrastructure scale.

It is useful for extreme scale, geographic placement, and regional failure isolation. On a moderate application, unnecessary distribution simply increases the failure surface.

MAA

Maximum Availability Architecture is a framework for meeting RPO/RTO goals across failure domains. RAC, Data Guard, Application Continuity, Exadata, and logical replication can address different failure classes in the same architecture.

Unit 18: Oracle Forms, APEX, and Modernization

Why Forms still matters

Oracle Forms is no longer the usual choice for a new application, but many long-lived enterprise systems still contain critical business rules in Forms triggers.

The first modernization step is not "rewrite it." It is find where the business rules actually live.

Form
├── block
│   ├── record
│   └── item
├── trigger
├── program unit
└── library

A Forms trigger is not a database trigger. Similar names must not hide the difference in execution layer.

The core Forms risk

When business logic is scattered across UI triggers:

• automated testing becomes difficult,
• another client must reimplement the same rules,
• transaction ownership becomes unclear,
• migration to APIs becomes expensive.

Portable business logic should therefore be moved toward PL/SQL packages or an explicit service layer before replacing screens.

APEX

APEX metadata lives in Oracle Database, with ORDS commonly serving the HTTP layer. Forms, reports, interactive grids, session state, and PL/SQL processes make it effective for rapid data-centric applications.

Its proximity to data is a strength. The same strength can become a weakness if business logic is scattered across page processes. A package-based service boundary remains valuable in APEX.

Modernization strategy

A big-bang rewrite has the highest uncertainty. A safer sequence is:

1. inventory dependencies
2. extract data and transaction rules
3. separate business logic from UI behavior
4. build characterization tests
5. define API boundaries
6. move capabilities incrementally
7. measure and retire old paths deliberately

This is why a strangler-style migration often fits large Forms estates.

When microservices help

Do not split a monolith into services by table count. Boundaries should follow business capability and transaction ownership. Data that is constantly joined and committed in one transaction should not be distributed across services merely to follow an architectural fashion.

Unit 19: JSON, Modern SQL, and New Data Models

Native JSON

Native JSON lets the database treat JSON as a first-class data type rather than opaque text.

Core tools include:

JSON_VALUE       scalar extraction
JSON_QUERY       JSON fragment
JSON_EXISTS      path existence
JSON_TABLE       relational projection of JSON
JSON_TRANSFORM   document update

JSON indexing

For a known, frequently queried path, a function-based index is targeted and compact. A JSON search index supports broader ad hoc search at greater storage and maintenance cost.

JSON-relational duality

A duality view presents the same stored data through two models:

application → JSON document view
                 ↕
           relational tables

The data is not copied into two independent stores. Relational constraints and relationships remain, while the application can work with a document representation. ETAG support helps with document-level optimistic concurrency.

Duality views do not eliminate every SQL-versus-document design trade-off, but they can remove the need to synchronize two persistent copies of the same business data.

XML

JSON dominates new general application integration, but XMLTYPE, XQuery, and XMLTable remain important where XSDs and older enterprise interfaces exist.

Prefer XMLQuery and XMLTable over legacy functions such as EXTRACTVALUE.

SQL BOOLEAN, domains, and annotations

Recent releases add SQL-language conveniences such as SQL BOOLEAN, data use case domains, and annotations.

Domains can centralize repeated type semantics and constraints. Annotations attach machine-readable metadata to schema objects.

Blockchain and immutable tables

These table types support workloads that require tamper-evident or restricted-change audit data. Blockchain tables cryptographically link rows; immutable tables enforce simpler restrictions on modification and deletion.

They do not automatically replace external WORM retention or compliance design. The threat model and administrative powers still matter.

MLE JavaScript

The Multilingual Engine allows JavaScript modules to run inside the database. This can reuse selected validation or transformation logic close to data.

It does not imply that the database should become an application server. For data-intensive set processing, SQL and PL/SQL usually remain the natural tools.

Property graph and SQL/PGQ

Relational tables can be exposed as vertices and edges and queried with SQL/PGQ. Fraud, relationship, dependency, and path analysis are natural use cases. Whether a dedicated graph store is needed depends on workload scale and operational requirements.

True Cache

True Cache is a read-only, mostly diskless cache tier in front of Oracle Database. It is kept current through redo and returns committed, consistent data; cache misses fetch blocks from the source database.

This differs from a generic application cache where the application owns invalidation and consistency logic. Lag tolerance, read routing, and failover behavior still have to match the application's correctness requirements.

Select AI

Select AI connects natural-language requests to SQL and generated explanations. It can be useful for exploration and analyst workflows. Generated SQL should not be allowed to perform critical production work without privilege, cost, and correctness controls.

Unit 20: Vector Search and AI Integration

Embeddings and VECTOR

An embedding model converts text, images, or other content into a high-dimensional numeric representation. Items that are semantically similar become close under the chosen distance function.

CREATE TABLE document_store (
    id        NUMBER PRIMARY KEY,
    title     VARCHAR2(500 CHAR),
    content   CLOB,
    embedding VECTOR(768, FLOAT32)
);

Vector dimensionality and element type must match the model.

Distance metrics

Common metrics include:

COSINE
EUCLIDEAN
DOT
MANHATTAN
HAMMING

There is no universal rule that cosine is always correct. Use the metric for which the embedding model was designed.

Exact and approximate search

An exact scan finds the true nearest neighbors but becomes expensive as the collection grows. Approximate nearest-neighbor search examines far fewer candidates for a controlled loss in recall.

SELECT id, title
FROM document_store
ORDER BY VECTOR_DISTANCE(embedding, :q, COSINE)
FETCH APPROXIMATE FIRST 20 ROWS ONLY;

HNSW and IVF

Oracle exposes two core vector-index families:

HNSW   In-Memory Neighbor Graph; low latency, memory intensive
IVF    Neighbor Partitions; narrows search through vector clusters

CREATE VECTOR INDEX ix_doc_hnsw ON document_store(embedding)
ORGANIZATION INMEMORY NEIGHBOR GRAPH
DISTANCE COSINE
WITH TARGET ACCURACY 95;

HNSW builds a graph of neighboring vectors. IVF partitions vector space around centroids. TARGET ACCURACY expresses the speed/quality target; actual recall must be measured on representative business data.

Oracle AI Database 26ai release updates add capabilities such as distributed/local HNSW, online vector-index build, quantization, and additional IVF maintenance options. Exact syntax and availability should be checked against the installed RU.

Hybrid Vector Index

A Hybrid Vector Index combines Oracle Text and vector indexing in one search structure. It can rank exact lexical matches and semantic similarity together.

This matters in enterprise retrieval where exact product codes, legal terms, or names must coexist with semantic relevance.

Where to generate embeddings

Two common models exist:

1. an application or external service creates embeddings and stores them in VECTOR,
2. a compatible model is loaded into the database and embeddings are produced near the data.

The second model can reduce data movement and improve privacy. It also moves model lifecycle, CPU/GPU cost, and version management closer to database operations.

RAG

Retrieval-augmented generation can be expressed as:

document
 ↓
chunk
 ↓
embedding
 ↓
vector / hybrid search
 ↓
most relevant chunks
 ↓
LLM context
 ↓
answer

The database can combine storage, metadata filters, authorization, and retrieval within one security and transaction boundary.

There is no universally correct chunk size. Small chunks improve precision but lose context; large chunks preserve context but reduce retrieval specificity. Overlap reduces boundary loss at the cost of a larger index.

Vector search is not a replacement for relational access

Exact key lookup, range predicates, transactions, referential integrity, and aggregation remain natural relational operations. Vector search adds semantic access; it does not replace the relational model.

Oracle's architectural advantage is the ability to combine relational predicates, JSON, text search, and vector retrieval within SQL and one transactional security model. That advantage matters only if it actually reduces data copies and cross-service consistency cost.

Rapid Review

An instance lives in memory; a database persists in files.

The SGA is shared; PGA is private execution memory.

COMMIT waits for durable redo, not for every dirty block to reach its datafile.

LGWR writes redo; DBWn writes database blocks.

Oracle reads blocks, not individual rows.

A tablespace is logical; a datafile is physical.

A segment owns object space; an extent is a group of blocks.

**V$ exposes current instance state; DBA_* exposes persistent dictionary metadata.**

A CDB provides infrastructure; a PDB is the application database boundary.

With EXTENDED string size, SQL VARCHAR2 tops out at 32767 bytes; large text belongs in a LOB.

Oracle DATE includes time to seconds.

A sequence does not guarantee gapless numbering.

Oracle does not automatically index foreign-key columns.

If a rule can be a declarative constraint, do not hide it in a trigger.

DELETE is transactional DML; TRUNCATE is DDL with different transaction behavior.

Consistent reads are reconstructed through UNDO.

In READ COMMITTED, each statement gets its own consistent view.

SKIP LOCKED is useful for competing queue consumers.

Optimistic locking compares versions instead of holding rows while users think.

Consistent resource ordering is the basic deadlock-prevention rule.

FROM is logically evaluated before WHERE; WHERE before SELECT.

Moving an outer-join filter into WHERE can eliminate NULL-extended rows.

NULL means unknown; NULL = NULL is not TRUE.

NOT IN plus NULL is dangerous; NOT EXISTS is safer for anti-join intent.

A CTE improves structure; it is not automatically materialized.

Aggregates reduce rows; analytic functions preserve them.

ROW_NUMBER, RANK, and DENSE_RANK differ on ties.

ROWS counts row positions; RANGE includes peer values.

Deep OFFSET pagination is expensive; keyset pagination often scales better.

Every index trades read speed for write and storage cost.

Composite-index column order should follow query patterns, not a single selectivity slogan.

Bitmap indexes are not a default OLTP choice.

Routine index rebuilds are not a maintenance strategy.

Partition pruning is the primary read-side benefit of partitioning.

Local indexes align with partitions; global indexes serve cross-partition access patterns.

EXCHANGE PARTITION can move large data sets through metadata rather than row-by-row copying.

A full table scan is not automatically a bad plan.

Cardinality estimation is central to optimizer quality.

A large actual-versus-estimated row mismatch explains many bad plans.

Bind variables reduce both injection risk and parse overhead.

A hint is a controlled intervention, not the first tuning step.

SQL Plan Baselines stabilize plans; they do not remove the root cause.

PL/SQL is for procedural requirements, not row-by-row rewrites of set operations.

BULK COLLECT and FORALL reduce SQL/PLSQL context switches.

WHEN OTHERS THEN NULL hides failure.

A function should return a value without surprising side effects.

A package specification is the contract; the body is the implementation.

Triggers create implicit behavior; keep them out of the center of a workflow unless required.

Bind values; allow-list dynamic object names.

DBMS_ASSERT helps validate identifiers; it does not replace a safe design.

ANY privileges are usually too broad for application runtime accounts.

TDE protects data at rest; it does not replace TLS.

SQL Firewall is defense in depth, not a replacement for bind variables and least privilege.

ORA-01555 is not merely "UNDO is too small"; query duration and undo generation rate matter together.

Flashback is not backup.

Restore retrieves files; recover advances them using redo.

A backup is not trustworthy until restore/recovery has been tested.

RPO is data-loss tolerance; RTO is outage tolerance.

AWR/ASH provide historical evidence; V$SESSION shows live activity.

Database response time is explained through CPU plus waits.

RAC runs multiple instances against one database; it is not disaster recovery.

Cache Fusion moves blocks between instance caches over the interconnect.

In RAC, interconnect latency can become database latency.

ASM manages storage; a disk-group name alone does not create failure isolation.

Data Guard maintains standby databases through redo; GoldenGate replicates logical changes.

Synchronous remote redo can put WAN round-trip latency into the commit path.

Exadata does not make bad SQL correct.

Sharding is a data-model decision before it is an infrastructure decision.

Forms modernization begins with trigger and dependency inventory.

Microservice boundaries should follow business and transaction boundaries, not tables.

Native JSON can participate in the same relational transaction boundary.

JSON-relational duality exposes one stored data set through document and relational models.

True Cache is a read-only Oracle cache kept current through redo.

VECTOR adds semantic retrieval; it does not replace primary-key or relational access.

HNSW is graph-based; IVF is partition/cluster-based approximate vector indexing.

Hybrid Vector Index combines lexical and semantic retrieval.

RAG quality depends on chunking, retrieval, filtering, and authorization as well as the model.

References

• Oracle. Oracle AI Database 26ai Documentation. https://docs.oracle.com/en/database/oracle/oracle-database/26/
• Oracle. Oracle Database 19c Documentation. https://docs.oracle.com/en/database/oracle/oracle-database/19/
• Oracle. Oracle AI Database SQL Language Reference. https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/
• Oracle. Oracle AI Database PL/SQL Language Reference. https://docs.oracle.com/en/database/oracle/oracle-database/26/lnpls/
• Oracle. Oracle AI Database Administrator's Guide. https://docs.oracle.com/en/database/oracle/oracle-database/26/admin/
• Oracle. Oracle AI Database Performance Tuning Guide. https://docs.oracle.com/en/database/oracle/oracle-database/26/tgdba/
• Oracle. Oracle Database Backup and Recovery User's Guide. https://docs.oracle.com/en/database/oracle/oracle-database/26/bradv/
• Oracle. Oracle Real Application Clusters Administration and Deployment Guide. https://docs.oracle.com/en/database/oracle/oracle-database/26/racad/
• Oracle. Oracle Data Guard Concepts and Administration. https://docs.oracle.com/en/database/oracle/oracle-database/26/sbydb/
• Oracle. Oracle Automatic Storage Management Administrator's Guide. https://docs.oracle.com/en/database/oracle/oracle-database/26/ostmg/
• Oracle. Oracle AI Database Security Guide. https://docs.oracle.com/en/database/oracle/oracle-database/26/dbseg/
• Oracle. Oracle SQL Firewall User's Guide. https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlfw/
• Oracle. JSON-Relational Duality Developer's Guide. https://docs.oracle.com/en/database/oracle/oracle-database/26/jsnvu/
• Oracle. Oracle AI Vector Search User's Guide. https://docs.oracle.com/en/database/oracle/oracle-database/26/vecse/
• Oracle. Oracle True Cache User's Guide. https://docs.oracle.com/en/database/oracle/oracle-database/26/odbtc/
• Oracle. Oracle Lifetime Support Policy: Technology Products. https://www.oracle.com/us/support/library/lifetime-support-technology-069183.pdf
• Thomas Kyte, Darl Kuhn. Expert Oracle Database Architecture, 3rd Edition. Apress.
• Steven Feuerstein, Bill Pribyl. Oracle PL/SQL Programming. O'Reilly Media.