NCM-DB cheat sheet

NCM-DB deployments separate a stateless SQL layer from a stateful storage layer; only the storage layer holds persistent data, so SQL nodes can be added or replaced without data migration.

The catalog (system metadata) is distributed and replicated across all nodes; catalog reads are served locally, while catalog writes require a distributed transaction commit.

Normalization to 3NF removes transitive dependencies; denormalization is chosen when read amplification or join cost dominates the workload and is measured, not assumed.

A primary key implies uniqueness plus NOT NULL; a unique constraint permits one NULL unless the column is also declared NOT NULL.

Referential integrity is enforced per statement by default; deferring a foreign key check to commit requires an explicitly declared deferrable constraint.

Cardinality estimation drives join order; stale or missing statistics produce nested-loop plans where a hash join is cheaper, so refresh statistics after bulk loads exceeding roughly 10 percent row change.

Partitioning by range on a time column enables partition pruning and partition-level archival; hash partitioning spreads write contention but prevents range pruning.

ACID isolation: READ COMMITTED sees each statement's snapshot; REPEATABLE READ holds one snapshot per transaction; SERIALIZABLE adds predicate conflict detection and may abort transactions.

Schema changes are versioned migration scripts applied idempotently; destructive changes (drop column) are split into a deprecation release and a removal release.

Installation order: verify OS kernel parameters and limits, create the service account, install binaries, initialize the data directory, then start and validate the instance before opening client access.

Key OS prerequisites are file-descriptor limits, memory overcommit policy, and shared memory or huge-page settings; mismatches surface as startup failures, not runtime errors.

Configuration precedence is command line over environment over instance configuration file over compiled defaults; verify effective values from the running instance, not the file on disk.

Memory split rule: allocate the buffer pool first from total RAM, reserve headroom for per-connection work memory and OS cache, and cap total connections so worst-case work memory fits.

Startup sequence is: read configuration, recover or validate the data directory, start background workers, then accept client connections; a failed recovery leaves the instance in a non-accepting state.

Graceful shutdown drains connections, checkpoints, and flushes; immediate shutdown skips draining and forces crash recovery on the next start.

Instance identity is defined by the data directory plus the listening port; two instances on one host require distinct directories and ports.

Configuration changes are classified as dynamic (apply at runtime) or static (require restart); record the class so change windows are sized correctly.

Log every configuration change with old value, new value, operator, and timestamp; undocumented tuning is the most common cause of unreproducible incidents.

Storage hierarchy: tablespace contains segments, a segment contains extents, an extent contains contiguous blocks, and a block is the unit of I/O and caching.

Row storage: a row is written into a block with a header, row directory, and free space; updates larger than the free space cause row migration or chaining, which increases I/O per row read.

A tablespace maps logical objects to physical data files; moving an object between tablespaces relocates its segments without changing SQL that references the object.

Locally managed tablespaces track free extents in a bitmap inside the tablespace; dictionary-managed free-space tracking is legacy and adds contention on the catalog.

Index types: B-tree for equality and range on high-cardinality columns; bitmap for low-cardinality columns in read-mostly workloads; hash for equality only.

An index on a column with very low selectivity is often ignored by the optimizer; a composite index is usable when the leading column appears in the predicate.

LOB data is stored out of line by default beyond an inline threshold; inline storage reduces round trips for small values but bloats the base table row.

Segment space settings (PCTFREE, PCTUSED) trade update headroom against scan density; high PCTFREE is correct only for tables with frequent in-place growth.

Compression reduces block count and I/O but raises CPU per write; enable it on read-heavy, append-only or archive segments, not on hot OLTP update paths.

Logical processing order: FROM and joins, WHERE, GROUP BY, HAVING, window functions, SELECT list, DISTINCT, ORDER BY, then LIMIT or FETCH.

WHERE filters rows before grouping and cannot reference aggregates; HAVING filters groups after aggregation and may reference aggregates.

NULL semantics: comparisons with NULL yield UNKNOWN, so use IS NULL or IS NOT NULL; NOT IN with a NULL in the list returns no rows.

JOIN rules: INNER returns matches only; LEFT returns all left rows with NULL extension; a predicate on the right table in the WHERE clause silently converts a LEFT JOIN to an INNER JOIN.

A correlated subquery is evaluated per outer row; rewriting it as a join or a window function usually removes the repeated scan.

Window functions compute across a partition without collapsing rows; they are evaluated after WHERE and GROUP BY but before ORDER BY and LIMIT.

EXISTS stops at the first match and is preferred over IN for large subquery result sets; NOT EXISTS is NULL-safe where NOT IN is not.

A query plan is read bottom-up for nested-loop and hash joins: the first scanned row source is the driving table; check estimated versus actual rows for misestimation.

Transactions end with COMMIT or ROLLBACK; DDL statements generally auto-commit in the current session and cannot be rolled back with the surrounding DML.

Recovery point objective (RPO) sets backup frequency and log shipping; recovery time objective (RTO) sets restore parallelism and standby readiness.

A consistent physical backup requires the data files plus archived redo logs covering the backup window; without the logs the backup restores only to its start point.

Point-in-time recovery applies a base backup, then rolls forward archived and online redo logs to a target timestamp, then opens the database for use.

Incremental backups capture changed blocks since a prior level; a level-0 base plus level-1 differentials restores faster than a chain of many small cumulative levels.

Log mode: in ARCHIVELOG mode the instance can recover to the last committed transaction after media failure; in NOARCHIVELOG mode recovery is limited to the last consistent backup.

A standby is kept synchronized by continuously applying redo; a synchronous standby guarantees zero data loss but adds commit latency equal to network round trip.

Failover promotes the standby and reconfigures clients; switchover is a planned, reversible role exchange with no data loss.

Backup validation is a restore test, not a successful backup job; an untested backup is an unverified assumption about RPO.

Retention policy must keep at least one full backup plus every log needed to reach the oldest retained full; deleting logs early breaks the recovery chain.

Tune by measurement order: identify the top wait event, attribute it to a statement or workload, change one variable, then re-measure over an equivalent interval.

Buffer pool hit ratio = 1 minus (physical reads divided by logical reads); a low ratio suggests insufficient cache or a scan-heavy workload, not automatically more memory.

A wait event names what a session is blocked on; CPU time is not a wait, so high CPU with low waits points to inefficient execution rather than contention.

Execution plan regressions are diagnosed by comparing estimated rows to actual rows per operator; the largest misestimate usually marks the faulty join order.

Bind variables allow cursor reuse and reduce parse cost but can cause plan skew when data distribution is uneven; adaptive cursor sharing or a hint resolves the outlier.

Index maintenance: rebuild only on measured fragmentation or after bulk deletes; automatic statistics gathering plus a post-load analyze covers most cases.

Lock contention is found from blocking-session chains; the root blocker, not the waiting sessions, must be resolved or terminated.

Monitoring baselines matter more than thresholds: alert on deviation from the same time-of-day and workload profile across weeks.

Connection pooling bounds server-side sessions; sizing the pool above the CPU core count by more than a small factor usually adds context switching, not throughput.

Least privilege: grant roles, not individual object privileges; a user receives only the privileges required for the current task and loses them when the role is revoked.

Authentication proves identity; authorization decides access. A shared application account defeats per-user auditing and must be replaced by per-user credentials with a connection pool.

Password policy enforces minimum length, complexity, expiry, and reuse history; account lockout after repeated failures mitigates online guessing.

Privilege types: system privileges act on the instance, object privileges act on a named object, and role grants bundle both and can be granted with or without admin option.

PUBLIC is a default role granted to every user; any privilege granted to PUBLIC is effectively granted to all accounts and must be audited explicitly.

Auditing records who did what and when; standard auditing captures statement or privilege use, while fine-grained auditing captures row-level access by predicate.

Audit records must be written to a location the audited user cannot modify; storing them in the same tablespace as application data allows tampering.

Encryption at rest protects data files and backups; encryption in transit (TLS) protects credentials and results on the network. Both are required for compliance scopes.

Revoking a role does not invalidate existing sessions; the change takes effect at the next login or when the session is explicitly refreshed.

NCM-DB deployments separate a stateless SQL layer from a stateful storage layer; only the storage layer holds persistent data, so SQL nodes can be added or replaced without data migration.

The catalog (system metadata) is distributed and replicated across all nodes; catalog reads are served locally, while catalog writes require a distributed transaction commit.

Normalization to 3NF removes transitive dependencies; denormalization is chosen when read amplification or join cost dominates the workload and is measured, not assumed.

A primary key implies uniqueness plus NOT NULL; a unique constraint permits one NULL unless the column is also declared NOT NULL.

Referential integrity is enforced per statement by default; deferring a foreign key check to commit requires an explicitly declared deferrable constraint.

Cardinality estimation drives join order; stale or missing statistics produce nested-loop plans where a hash join is cheaper, so refresh statistics after bulk loads exceeding roughly 10 percent row change.

Partitioning by range on a time column enables partition pruning and partition-level archival; hash partitioning spreads write contention but prevents range pruning.

ACID isolation: READ COMMITTED sees each statement's snapshot; REPEATABLE READ holds one snapshot per transaction; SERIALIZABLE adds predicate conflict detection and may abort transactions.

Schema changes are versioned migration scripts applied idempotently; destructive changes (drop column) are split into a deprecation release and a removal release.

Installation order: verify OS kernel parameters and limits, create the service account, install binaries, initialize the data directory, then start and validate the instance before opening client access.

Key OS prerequisites are file-descriptor limits, memory overcommit policy, and shared memory or huge-page settings; mismatches surface as startup failures, not runtime errors.

Configuration precedence is command line over environment over instance configuration file over compiled defaults; verify effective values from the running instance, not the file on disk.

Memory split rule: allocate the buffer pool first from total RAM, reserve headroom for per-connection work memory and OS cache, and cap total connections so worst-case work memory fits.

Startup sequence is: read configuration, recover or validate the data directory, start background workers, then accept client connections; a failed recovery leaves the instance in a non-accepting state.

Graceful shutdown drains connections, checkpoints, and flushes; immediate shutdown skips draining and forces crash recovery on the next start.

Instance identity is defined by the data directory plus the listening port; two instances on one host require distinct directories and ports.

Configuration changes are classified as dynamic (apply at runtime) or static (require restart); record the class so change windows are sized correctly.

Log every configuration change with old value, new value, operator, and timestamp; undocumented tuning is the most common cause of unreproducible incidents.

Storage hierarchy: tablespace contains segments, a segment contains extents, an extent contains contiguous blocks, and a block is the unit of I/O and caching.

Row storage: a row is written into a block with a header, row directory, and free space; updates larger than the free space cause row migration or chaining, which increases I/O per row read.

A tablespace maps logical objects to physical data files; moving an object between tablespaces relocates its segments without changing SQL that references the object.

Locally managed tablespaces track free extents in a bitmap inside the tablespace; dictionary-managed free-space tracking is legacy and adds contention on the catalog.

Index types: B-tree for equality and range on high-cardinality columns; bitmap for low-cardinality columns in read-mostly workloads; hash for equality only.

An index on a column with very low selectivity is often ignored by the optimizer; a composite index is usable when the leading column appears in the predicate.

LOB data is stored out of line by default beyond an inline threshold; inline storage reduces round trips for small values but bloats the base table row.

Segment space settings (PCTFREE, PCTUSED) trade update headroom against scan density; high PCTFREE is correct only for tables with frequent in-place growth.

Compression reduces block count and I/O but raises CPU per write; enable it on read-heavy, append-only or archive segments, not on hot OLTP update paths.

Logical processing order: FROM and joins, WHERE, GROUP BY, HAVING, window functions, SELECT list, DISTINCT, ORDER BY, then LIMIT or FETCH.

WHERE filters rows before grouping and cannot reference aggregates; HAVING filters groups after aggregation and may reference aggregates.

NULL semantics: comparisons with NULL yield UNKNOWN, so use IS NULL or IS NOT NULL; NOT IN with a NULL in the list returns no rows.

JOIN rules: INNER returns matches only; LEFT returns all left rows with NULL extension; a predicate on the right table in the WHERE clause silently converts a LEFT JOIN to an INNER JOIN.

A correlated subquery is evaluated per outer row; rewriting it as a join or a window function usually removes the repeated scan.

Window functions compute across a partition without collapsing rows; they are evaluated after WHERE and GROUP BY but before ORDER BY and LIMIT.

EXISTS stops at the first match and is preferred over IN for large subquery result sets; NOT EXISTS is NULL-safe where NOT IN is not.

A query plan is read bottom-up for nested-loop and hash joins: the first scanned row source is the driving table; check estimated versus actual rows for misestimation.

Transactions end with COMMIT or ROLLBACK; DDL statements generally auto-commit in the current session and cannot be rolled back with the surrounding DML.

Recovery point objective (RPO) sets backup frequency and log shipping; recovery time objective (RTO) sets restore parallelism and standby readiness.

A consistent physical backup requires the data files plus archived redo logs covering the backup window; without the logs the backup restores only to its start point.

Point-in-time recovery applies a base backup, then rolls forward archived and online redo logs to a target timestamp, then opens the database for use.

Incremental backups capture changed blocks since a prior level; a level-0 base plus level-1 differentials restores faster than a chain of many small cumulative levels.

Log mode: in ARCHIVELOG mode the instance can recover to the last committed transaction after media failure; in NOARCHIVELOG mode recovery is limited to the last consistent backup.

A standby is kept synchronized by continuously applying redo; a synchronous standby guarantees zero data loss but adds commit latency equal to network round trip.

Failover promotes the standby and reconfigures clients; switchover is a planned, reversible role exchange with no data loss.

Backup validation is a restore test, not a successful backup job; an untested backup is an unverified assumption about RPO.

Retention policy must keep at least one full backup plus every log needed to reach the oldest retained full; deleting logs early breaks the recovery chain.

Tune by measurement order: identify the top wait event, attribute it to a statement or workload, change one variable, then re-measure over an equivalent interval.

Buffer pool hit ratio = 1 minus (physical reads divided by logical reads); a low ratio suggests insufficient cache or a scan-heavy workload, not automatically more memory.

A wait event names what a session is blocked on; CPU time is not a wait, so high CPU with low waits points to inefficient execution rather than contention.

Execution plan regressions are diagnosed by comparing estimated rows to actual rows per operator; the largest misestimate usually marks the faulty join order.

Bind variables allow cursor reuse and reduce parse cost but can cause plan skew when data distribution is uneven; adaptive cursor sharing or a hint resolves the outlier.

Index maintenance: rebuild only on measured fragmentation or after bulk deletes; automatic statistics gathering plus a post-load analyze covers most cases.

Lock contention is found from blocking-session chains; the root blocker, not the waiting sessions, must be resolved or terminated.

Monitoring baselines matter more than thresholds: alert on deviation from the same time-of-day and workload profile across weeks.

Connection pooling bounds server-side sessions; sizing the pool above the CPU core count by more than a small factor usually adds context switching, not throughput.

Least privilege: grant roles, not individual object privileges; a user receives only the privileges required for the current task and loses them when the role is revoked.

Authentication proves identity; authorization decides access. A shared application account defeats per-user auditing and must be replaced by per-user credentials with a connection pool.

Password policy enforces minimum length, complexity, expiry, and reuse history; account lockout after repeated failures mitigates online guessing.

Privilege types: system privileges act on the instance, object privileges act on a named object, and role grants bundle both and can be granted with or without admin option.

PUBLIC is a default role granted to every user; any privilege granted to PUBLIC is effectively granted to all accounts and must be audited explicitly.

Auditing records who did what and when; standard auditing captures statement or privilege use, while fine-grained auditing captures row-level access by predicate.

Audit records must be written to a location the audited user cannot modify; storing them in the same tablespace as application data allows tampering.

Encryption at rest protects data files and backups; encryption in transit (TLS) protects credentials and results on the network. Both are required for compliance scopes.

Revoking a role does not invalidate existing sessions; the change takes effect at the next login or when the session is explicitly refreshed.

NCM-DB deployments separate a stateless SQL layer from a stateful storage layer; only the storage layer holds persistent data, so SQL nodes can be added or replaced without data migration.

The catalog (system metadata) is distributed and replicated across all nodes; catalog reads are served locally, while catalog writes require a distributed transaction commit.

Normalization to 3NF removes transitive dependencies; denormalization is chosen when read amplification or join cost dominates the workload and is measured, not assumed.

A primary key implies uniqueness plus NOT NULL; a unique constraint permits one NULL unless the column is also declared NOT NULL.

Referential integrity is enforced per statement by default; deferring a foreign key check to commit requires an explicitly declared deferrable constraint.

Cardinality estimation drives join order; stale or missing statistics produce nested-loop plans where a hash join is cheaper, so refresh statistics after bulk loads exceeding roughly 10 percent row change.

Partitioning by range on a time column enables partition pruning and partition-level archival; hash partitioning spreads write contention but prevents range pruning.

ACID isolation: READ COMMITTED sees each statement's snapshot; REPEATABLE READ holds one snapshot per transaction; SERIALIZABLE adds predicate conflict detection and may abort transactions.

Schema changes are versioned migration scripts applied idempotently; destructive changes (drop column) are split into a deprecation release and a removal release.

Installation order: verify OS kernel parameters and limits, create the service account, install binaries, initialize the data directory, then start and validate the instance before opening client access.

Key OS prerequisites are file-descriptor limits, memory overcommit policy, and shared memory or huge-page settings; mismatches surface as startup failures, not runtime errors.

Configuration precedence is command line over environment over instance configuration file over compiled defaults; verify effective values from the running instance, not the file on disk.

Memory split rule: allocate the buffer pool first from total RAM, reserve headroom for per-connection work memory and OS cache, and cap total connections so worst-case work memory fits.

Startup sequence is: read configuration, recover or validate the data directory, start background workers, then accept client connections; a failed recovery leaves the instance in a non-accepting state.

Graceful shutdown drains connections, checkpoints, and flushes; immediate shutdown skips draining and forces crash recovery on the next start.

Instance identity is defined by the data directory plus the listening port; two instances on one host require distinct directories and ports.

Configuration changes are classified as dynamic (apply at runtime) or static (require restart); record the class so change windows are sized correctly.

Log every configuration change with old value, new value, operator, and timestamp; undocumented tuning is the most common cause of unreproducible incidents.

Storage hierarchy: tablespace contains segments, a segment contains extents, an extent contains contiguous blocks, and a block is the unit of I/O and caching.

Row storage: a row is written into a block with a header, row directory, and free space; updates larger than the free space cause row migration or chaining, which increases I/O per row read.

A tablespace maps logical objects to physical data files; moving an object between tablespaces relocates its segments without changing SQL that references the object.

Locally managed tablespaces track free extents in a bitmap inside the tablespace; dictionary-managed free-space tracking is legacy and adds contention on the catalog.

Index types: B-tree for equality and range on high-cardinality columns; bitmap for low-cardinality columns in read-mostly workloads; hash for equality only.

An index on a column with very low selectivity is often ignored by the optimizer; a composite index is usable when the leading column appears in the predicate.

LOB data is stored out of line by default beyond an inline threshold; inline storage reduces round trips for small values but bloats the base table row.

Segment space settings (PCTFREE, PCTUSED) trade update headroom against scan density; high PCTFREE is correct only for tables with frequent in-place growth.

Compression reduces block count and I/O but raises CPU per write; enable it on read-heavy, append-only or archive segments, not on hot OLTP update paths.

Logical processing order: FROM and joins, WHERE, GROUP BY, HAVING, window functions, SELECT list, DISTINCT, ORDER BY, then LIMIT or FETCH.

WHERE filters rows before grouping and cannot reference aggregates; HAVING filters groups after aggregation and may reference aggregates.

NULL semantics: comparisons with NULL yield UNKNOWN, so use IS NULL or IS NOT NULL; NOT IN with a NULL in the list returns no rows.

JOIN rules: INNER returns matches only; LEFT returns all left rows with NULL extension; a predicate on the right table in the WHERE clause silently converts a LEFT JOIN to an INNER JOIN.

A correlated subquery is evaluated per outer row; rewriting it as a join or a window function usually removes the repeated scan.

Window functions compute across a partition without collapsing rows; they are evaluated after WHERE and GROUP BY but before ORDER BY and LIMIT.

EXISTS stops at the first match and is preferred over IN for large subquery result sets; NOT EXISTS is NULL-safe where NOT IN is not.

A query plan is read bottom-up for nested-loop and hash joins: the first scanned row source is the driving table; check estimated versus actual rows for misestimation.

Transactions end with COMMIT or ROLLBACK; DDL statements generally auto-commit in the current session and cannot be rolled back with the surrounding DML.

Recovery point objective (RPO) sets backup frequency and log shipping; recovery time objective (RTO) sets restore parallelism and standby readiness.

A consistent physical backup requires the data files plus archived redo logs covering the backup window; without the logs the backup restores only to its start point.

Point-in-time recovery applies a base backup, then rolls forward archived and online redo logs to a target timestamp, then opens the database for use.

Incremental backups capture changed blocks since a prior level; a level-0 base plus level-1 differentials restores faster than a chain of many small cumulative levels.

Log mode: in ARCHIVELOG mode the instance can recover to the last committed transaction after media failure; in NOARCHIVELOG mode recovery is limited to the last consistent backup.

A standby is kept synchronized by continuously applying redo; a synchronous standby guarantees zero data loss but adds commit latency equal to network round trip.

Failover promotes the standby and reconfigures clients; switchover is a planned, reversible role exchange with no data loss.

Backup validation is a restore test, not a successful backup job; an untested backup is an unverified assumption about RPO.

Retention policy must keep at least one full backup plus every log needed to reach the oldest retained full; deleting logs early breaks the recovery chain.

Tune by measurement order: identify the top wait event, attribute it to a statement or workload, change one variable, then re-measure over an equivalent interval.

Buffer pool hit ratio = 1 minus (physical reads divided by logical reads); a low ratio suggests insufficient cache or a scan-heavy workload, not automatically more memory.

A wait event names what a session is blocked on; CPU time is not a wait, so high CPU with low waits points to inefficient execution rather than contention.

Execution plan regressions are diagnosed by comparing estimated rows to actual rows per operator; the largest misestimate usually marks the faulty join order.

Bind variables allow cursor reuse and reduce parse cost but can cause plan skew when data distribution is uneven; adaptive cursor sharing or a hint resolves the outlier.

Index maintenance: rebuild only on measured fragmentation or after bulk deletes; automatic statistics gathering plus a post-load analyze covers most cases.

Lock contention is found from blocking-session chains; the root blocker, not the waiting sessions, must be resolved or terminated.

Monitoring baselines matter more than thresholds: alert on deviation from the same time-of-day and workload profile across weeks.

Connection pooling bounds server-side sessions; sizing the pool above the CPU core count by more than a small factor usually adds context switching, not throughput.

Least privilege: grant roles, not individual object privileges; a user receives only the privileges required for the current task and loses them when the role is revoked.

Authentication proves identity; authorization decides access. A shared application account defeats per-user auditing and must be replaced by per-user credentials with a connection pool.

Password policy enforces minimum length, complexity, expiry, and reuse history; account lockout after repeated failures mitigates online guessing.

Privilege types: system privileges act on the instance, object privileges act on a named object, and role grants bundle both and can be granted with or without admin option.

PUBLIC is a default role granted to every user; any privilege granted to PUBLIC is effectively granted to all accounts and must be audited explicitly.

Auditing records who did what and when; standard auditing captures statement or privilege use, while fine-grained auditing captures row-level access by predicate.

Audit records must be written to a location the audited user cannot modify; storing them in the same tablespace as application data allows tampering.

Encryption at rest protects data files and backups; encryption in transit (TLS) protects credentials and results on the network. Both are required for compliance scopes.

Revoking a role does not invalidate existing sessions; the change takes effect at the next login or when the session is explicitly refreshed.

NCM-DB deployments separate a stateless SQL layer from a stateful storage layer; only the storage layer holds persistent data, so SQL nodes can be added or replaced without data migration.

The catalog (system metadata) is distributed and replicated across all nodes; catalog reads are served locally, while catalog writes require a distributed transaction commit.

Normalization to 3NF removes transitive dependencies; denormalization is chosen when read amplification or join cost dominates the workload and is measured, not assumed.

A primary key implies uniqueness plus NOT NULL; a unique constraint permits one NULL unless the column is also declared NOT NULL.

Referential integrity is enforced per statement by default; deferring a foreign key check to commit requires an explicitly declared deferrable constraint.

Cardinality estimation drives join order; stale or missing statistics produce nested-loop plans where a hash join is cheaper, so refresh statistics after bulk loads exceeding roughly 10 percent row change.

Partitioning by range on a time column enables partition pruning and partition-level archival; hash partitioning spreads write contention but prevents range pruning.

ACID isolation: READ COMMITTED sees each statement's snapshot; REPEATABLE READ holds one snapshot per transaction; SERIALIZABLE adds predicate conflict detection and may abort transactions.

Schema changes are versioned migration scripts applied idempotently; destructive changes (drop column) are split into a deprecation release and a removal release.

Installation order: verify OS kernel parameters and limits, create the service account, install binaries, initialize the data directory, then start and validate the instance before opening client access.

Key OS prerequisites are file-descriptor limits, memory overcommit policy, and shared memory or huge-page settings; mismatches surface as startup failures, not runtime errors.

Configuration precedence is command line over environment over instance configuration file over compiled defaults; verify effective values from the running instance, not the file on disk.

Memory split rule: allocate the buffer pool first from total RAM, reserve headroom for per-connection work memory and OS cache, and cap total connections so worst-case work memory fits.

Startup sequence is: read configuration, recover or validate the data directory, start background workers, then accept client connections; a failed recovery leaves the instance in a non-accepting state.

Graceful shutdown drains connections, checkpoints, and flushes; immediate shutdown skips draining and forces crash recovery on the next start.

Instance identity is defined by the data directory plus the listening port; two instances on one host require distinct directories and ports.

Configuration changes are classified as dynamic (apply at runtime) or static (require restart); record the class so change windows are sized correctly.

Log every configuration change with old value, new value, operator, and timestamp; undocumented tuning is the most common cause of unreproducible incidents.

Storage hierarchy: tablespace contains segments, a segment contains extents, an extent contains contiguous blocks, and a block is the unit of I/O and caching.

Row storage: a row is written into a block with a header, row directory, and free space; updates larger than the free space cause row migration or chaining, which increases I/O per row read.

A tablespace maps logical objects to physical data files; moving an object between tablespaces relocates its segments without changing SQL that references the object.

Locally managed tablespaces track free extents in a bitmap inside the tablespace; dictionary-managed free-space tracking is legacy and adds contention on the catalog.

Index types: B-tree for equality and range on high-cardinality columns; bitmap for low-cardinality columns in read-mostly workloads; hash for equality only.

An index on a column with very low selectivity is often ignored by the optimizer; a composite index is usable when the leading column appears in the predicate.

LOB data is stored out of line by default beyond an inline threshold; inline storage reduces round trips for small values but bloats the base table row.

Segment space settings (PCTFREE, PCTUSED) trade update headroom against scan density; high PCTFREE is correct only for tables with frequent in-place growth.

Compression reduces block count and I/O but raises CPU per write; enable it on read-heavy, append-only or archive segments, not on hot OLTP update paths.

Logical processing order: FROM and joins, WHERE, GROUP BY, HAVING, window functions, SELECT list, DISTINCT, ORDER BY, then LIMIT or FETCH.

WHERE filters rows before grouping and cannot reference aggregates; HAVING filters groups after aggregation and may reference aggregates.

NULL semantics: comparisons with NULL yield UNKNOWN, so use IS NULL or IS NOT NULL; NOT IN with a NULL in the list returns no rows.

JOIN rules: INNER returns matches only; LEFT returns all left rows with NULL extension; a predicate on the right table in the WHERE clause silently converts a LEFT JOIN to an INNER JOIN.

A correlated subquery is evaluated per outer row; rewriting it as a join or a window function usually removes the repeated scan.

Window functions compute across a partition without collapsing rows; they are evaluated after WHERE and GROUP BY but before ORDER BY and LIMIT.

EXISTS stops at the first match and is preferred over IN for large subquery result sets; NOT EXISTS is NULL-safe where NOT IN is not.

A query plan is read bottom-up for nested-loop and hash joins: the first scanned row source is the driving table; check estimated versus actual rows for misestimation.

Transactions end with COMMIT or ROLLBACK; DDL statements generally auto-commit in the current session and cannot be rolled back with the surrounding DML.

Recovery point objective (RPO) sets backup frequency and log shipping; recovery time objective (RTO) sets restore parallelism and standby readiness.

A consistent physical backup requires the data files plus archived redo logs covering the backup window; without the logs the backup restores only to its start point.

Point-in-time recovery applies a base backup, then rolls forward archived and online redo logs to a target timestamp, then opens the database for use.

Incremental backups capture changed blocks since a prior level; a level-0 base plus level-1 differentials restores faster than a chain of many small cumulative levels.

Log mode: in ARCHIVELOG mode the instance can recover to the last committed transaction after media failure; in NOARCHIVELOG mode recovery is limited to the last consistent backup.

A standby is kept synchronized by continuously applying redo; a synchronous standby guarantees zero data loss but adds commit latency equal to network round trip.

Failover promotes the standby and reconfigures clients; switchover is a planned, reversible role exchange with no data loss.

Backup validation is a restore test, not a successful backup job; an untested backup is an unverified assumption about RPO.

Retention policy must keep at least one full backup plus every log needed to reach the oldest retained full; deleting logs early breaks the recovery chain.

Tune by measurement order: identify the top wait event, attribute it to a statement or workload, change one variable, then re-measure over an equivalent interval.

Buffer pool hit ratio = 1 minus (physical reads divided by logical reads); a low ratio suggests insufficient cache or a scan-heavy workload, not automatically more memory.

A wait event names what a session is blocked on; CPU time is not a wait, so high CPU with low waits points to inefficient execution rather than contention.

Execution plan regressions are diagnosed by comparing estimated rows to actual rows per operator; the largest misestimate usually marks the faulty join order.

Bind variables allow cursor reuse and reduce parse cost but can cause plan skew when data distribution is uneven; adaptive cursor sharing or a hint resolves the outlier.

Index maintenance: rebuild only on measured fragmentation or after bulk deletes; automatic statistics gathering plus a post-load analyze covers most cases.

Lock contention is found from blocking-session chains; the root blocker, not the waiting sessions, must be resolved or terminated.

Monitoring baselines matter more than thresholds: alert on deviation from the same time-of-day and workload profile across weeks.

Connection pooling bounds server-side sessions; sizing the pool above the CPU core count by more than a small factor usually adds context switching, not throughput.

Least privilege: grant roles, not individual object privileges; a user receives only the privileges required for the current task and loses them when the role is revoked.

Authentication proves identity; authorization decides access. A shared application account defeats per-user auditing and must be replaced by per-user credentials with a connection pool.

Password policy enforces minimum length, complexity, expiry, and reuse history; account lockout after repeated failures mitigates online guessing.

Privilege types: system privileges act on the instance, object privileges act on a named object, and role grants bundle both and can be granted with or without admin option.

PUBLIC is a default role granted to every user; any privilege granted to PUBLIC is effectively granted to all accounts and must be audited explicitly.

Auditing records who did what and when; standard auditing captures statement or privilege use, while fine-grained auditing captures row-level access by predicate.

Audit records must be written to a location the audited user cannot modify; storing them in the same tablespace as application data allows tampering.

Encryption at rest protects data files and backups; encryption in transit (TLS) protects credentials and results on the network. Both are required for compliance scopes.

Revoking a role does not invalidate existing sessions; the change takes effect at the next login or when the session is explicitly refreshed.