# Oracle Database and PL/SQL: Architecture, SQL, and Performance > A layered technical reference to Oracle Database and PL/SQL, from architecture, SQL, transactions and the optimizer to performance, RAC, Data Guard, modernization, JSON, and vector search. - Author: Muhammet Ali Köker - Language: en - Canonical: https://alikoker.com.tr/en/oracle-database-plsql-architecture-sql-performance - Translation: https://alikoker.com.tr/oracle-veritabani-plsql-mimari-sql-performans - Published: 2021-01-01T00:00:00+03:00 - Modified: 2026-08-22T05:07:00+03:00 - Type: article 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 ```text 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: ```text 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. ```text 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: ```text 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 ```text 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 ```text 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: ```text 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: ```text 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. ```sql 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. ```sql 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. ```sql amount NUMBER(12,2) ratio NUMBER(7,6) ``` ### Date and time Oracle `DATE` stores time down to seconds as well as the date. ```text 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: ```sql 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 ```sql 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: ```sql 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 ```sql 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 ```text 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 ```sql 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: ```sql 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: ```text 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 ```text 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: ```sql LEFT JOIN department d ON d.id = e.dept_id AND d.active = 'Y' ``` is not equivalent to: ```sql 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. ```text 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: ```sql 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: ```sql 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. ```sql 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 ```sql WHERE event_time >= DATE '2026-01-01' ``` is more robust than: ```sql 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. ```sql 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 ```text 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: ```sql 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 ```sql 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: ```sql 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. ```sql 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 ```sql 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 ```text 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 ```sql 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. ```sql 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 ```text 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: ```sql 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 ```sql 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. ```sql 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 ```sql 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: ```sql 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: ```sql 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: ```sql 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. ```sql 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. ```sql 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: ```sql 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. ```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. ```sql 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: ```sql 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. ```text 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 ```sql 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. ```text 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: ```text 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. ```sql 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: ```sql 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 ```text 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. ```text 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. ```text 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. ```text 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 ```text 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 ```text 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. ```text 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. ```text 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**. ```text 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: ```text 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: ```text 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: ```text 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. ```sql 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: ```text 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. ```sql 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: ```text HNSW In-Memory Neighbor Graph; low latency, memory intensive IVF Neighbor Partitions; narrows search through vector clusters ``` ```sql 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: ```text 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. ## Cite This Work Köker, M. A. (2021). Oracle Database and PL/SQL: Architecture, SQL, and Performance. alikoker.com.tr. https://alikoker.com.tr/en/oracle-database-plsql-architecture-sql-performance - BibTeX: https://alikoker.com.tr/en/oracle-database-plsql-architecture-sql-performance.bib - RIS: https://alikoker.com.tr/en/oracle-database-plsql-architecture-sql-performance.ris - CSL-JSON: https://alikoker.com.tr/en/oracle-database-plsql-architecture-sql-performance.csl.json