Oracle Database Administration I cheat sheet

An instance = memory structures (SGA) + background processes; a database = physical files (datafiles, control files, online redo logs). One database can be mounted by multiple instances only in RAC.

SGA components: shared pool (library cache + row cache), database buffer cache, redo log buffer, large pool, Java pool, streams pool; SGA_MAX_SIZE caps total SGA, SGA_TARGET enables automatic shared memory management.

PGA is per-server-process memory: sort area, hash area, bitmap merge area; PGA_AGGREGATE_TARGET enables automatic PGA management and is advisory to work areas.

Key background processes: DBWn writes dirty buffers, LGWR writes redo, CKPT updates checkpoints, SMON does instance/transaction recovery, PMON cleans up failed processes, MMON/MMNL manage AWR/ASH, ARCn archives redo.

A server process handles a single client connection; a shared server configuration routes sessions through dispatchers and shared server processes instead.

Buffer cache states: pinned, dirty, free/unused, clean; a buffer is dirty when its change SCN exceeds the block's on-disk SCN. Logical reads (consistent gets/db block gets) come from the cache, physical reads from disk.

A database block is the smallest I/O unit (DB_BLOCK_SIZE, set at creation and not changeable for the SYSTEM tablespace); an extent is a set of contiguous blocks; a segment is a set of extents for one object.

Logical hierarchy: database > tablespace > segment > extent > block. Physical hierarchy: control files, datafiles, redo log files, archived logs, parameter file, password file.

Tablespace types: permanent (SYSTEM, SYSAUX, USERS), undo (UNDOTBS), temporary (TEMP); a locally managed tablespace uses a bitmap in each datafile and tracks extents as AUTOALLOCATE or UNIFORM.

DBA_TABLESPACES shows tablespace status/contents; DBA_DATA_FILES and DBA_TEMP_FILES show file sizes; DBA_FREE_SPACE shows free extents; DBA_SEGMENTS shows segment sizes.

AUTOEXTEND MAXSIZE and the tablespace's MAXSIZE clause cap growth; a datafile reaching its limit raises ORA-01653 (unable to extend table) or ORA-01652 (unable to extend temp segment).

ASSM (SEGMENT SPACE MANAGEMENT AUTO) uses bitmaps in the segment header and data block; MSSM uses freelists and PCTFREE/PCTUSED. ASSM reduces buffer busy waits and supports automatic segment growth.

Oracle Managed Files (DB_CREATE_FILE_DEST, DB_CREATE_ONLINE_LOG_DEST_n) let the database name and place files automatically instead of requiring explicit file names.

Segment types include table, index, LOB, undo, temporary, cluster, and nested table; a table with a LOB column creates a separate LOB segment and LOB index.

Dropping a table moves it to the recycle bin (unless PURGE is used), so its space is not released until PURGE RECYCLEBIN or the table is purged.

CREATE USER requires a password (or EXTERNALLY/GLOBALLY), a default tablespace, and a temporary tablespace; the user gets the CONNECT role only if granted.

Privilege types: system privileges (CREATE SESSION, CREATE TABLE, SELECT ANY TABLE), object privileges (SELECT, INSERT, UPDATE, DELETE, EXECUTE, ALTER, INDEX), and roles (named sets of privileges).

GRANT ... WITH ADMIN OPTION lets a grantee grant the system privilege or role onward; WITH GRANT OPTION applies to object privileges. Revoking a system privilege does not cascade; revoking an object privilege cascades.

A profile controls PASSWORD_LIFE_TIME, PASSWORD_GRACE_TIME, PASSWORD_REUSE_MAX, PASSWORD_REUSE_TIME, PASSWORD_LOCK_TIME, FAILED_LOGIN_ATTEMPTS, SESSIONS_PER_USER, IDLE_TIME, and CONNECT_TIME.

DEFAULT profile applies to users without an explicit profile; changing a profile affects all users assigned to it, and the change takes effect at the next session or password check.

A user with the UNLIMITED TABLESPACE system privilege or a quota on a tablespace can create segments; otherwise ORA-01950 (no privileges on tablespace) occurs.

DBA_USERS shows account status (OPEN, LOCKED, EXPIRED, LOCKED(TIMED)); ALTER USER ... ACCOUNT LOCK/UNLOCK and PASSWORD EXPIRE manage account state.

Roles can be default or non-default; SET ROLE enables a non-default role. Roles cannot be granted to other roles if they contain object privileges only in some cases-system-privilege roles are grantable.

Startup stages: NOMOUNT reads the parameter file and starts the instance; MOUNT reads the control files; OPEN opens datafiles and redo logs.

SHUTDOWN modes: NORMAL waits for sessions to end, TRANSACTIONAL waits for active transactions, IMMEDIATE rolls back uncommitted transactions and disconnects users, ABORT is immediate and requires recovery at next startup.

The parameter file is a PFILE (text, init.ora) or SPFILE (binary, spfile.ora, server-side). CREATE SPFILE FROM PFILE or the reverse converts; SPFILE changes via ALTER SYSTEM ... SCOPE=MEMORY|SPFILE|BOTH.

A static parameter (e.g., DB_BLOCK_SIZE, DB_NAME) requires a restart; a dynamic parameter can be changed with ALTER SYSTEM and SCOPE=MEMORY.

Database creation with CREATE DATABASE requires a STARTUP NOMOUNT first, a CREATE DATABASE statement specifying the datafiles, logfiles, and character set, then running catalog scripts (catalog.sql, catproc.sql).

DBCA can create a database interactively or silently; it generates the SPFILE, password file, and runs the catalog scripts automatically.

A password file (orapw) authenticates SYSDBA/SYSOPER remotely; REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE and the OSDBA group membership govern this.

The alert log and trace files in the diagnostic destination (BACKGROUND_DUMP_DEST/DIAGNOSTIC_DEST) record startup, shutdown, and errors.

Table types: heap (default), index-organized (IOT), clustered, partitioned, external (read-only via ORACLE_LOADER/ORACLE_DATAPUMP), and temporary (global or private).

Constraints: NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK; a primary key implies NOT NULL and UNIQUE; FOREIGN KEY ... ON DELETE CASCADE or SET NULL controls child rows.

A B-tree index is the default; bitmap indexes suit low-cardinality columns; a function-based index requires the function to be deterministic; an invisible index is not used by the optimizer unless hinted.

A view is a stored query; a materialized view stores results and can refresh ON COMMIT or ON DEMAND; a synonym is an alias for a schema object.

Sequences generate numbers via NEXTVAL and CURRVAL; caching reduces contention; a sequence is not transactional and gaps are expected.

DML: INSERT, UPDATE, DELETE, MERGE; a transaction ends with COMMIT, ROLLBACK, or DDL (which commits implicitly).

TRUNCATE is DDL: it removes all rows, releases space to the tablespace, and cannot be rolled back; DELETE is DML and can be rolled back.

A partitioned table partitions rows by RANGE, LIST, HASH, or COMPOSITE; each partition can have its own tablespace and local indexes.

Undo records the before-image of changed data for rollback and read consistency; redo records the after-image for recovery. Undo is protected by redo.

UNDO_MANAGEMENT=AUTO with an undo tablespace; UNDO_RETENTION sets the target retention; UNDO_TABLESPACE names the active undo tablespace.

An undo segment can be ACTIVE (in use), EXPIRED (past retention), or UNEXPIRED (within retention but not in use); ORA-01555 (snapshot too old) occurs when a needed undo is overwritten.

A transaction gets an undo segment and a transaction ID; a savepoint allows partial rollback; COMMIT writes a commit record to redo and makes changes durable.

Redo log files are written in a circular fashion; LGWR writes on commit, on log buffer one-third full, and every three seconds. A log switch moves to the next group; ARCn archives the full group in ARCHIVELOG mode.

A checkpoint advances the checkpoint SCN and makes DBWn write dirty buffers; FAST_START_MTTR_TARGET bounds recovery time by controlling checkpoint frequency.

Isolation levels: READ COMMITTED (default, statement-level consistency) and SERIALIZABLE (transaction-level consistency); READ ONLY is available for queries.

A distributed transaction uses two-phase commit (prepare then commit); a transaction ID and SCN identify it in DBA_2PC_PENDING.

NOARCHIVELOG mode allows only closed-database backups and recovery only to the last backup; ARCHIVELOG mode allows online backups and point-in-time recovery.

RMAN backup types: full (all blocks), incremental level 0 (base), incremental level 1 (differential vs. last level 1, cumulative vs. last level 0); a backup set or image copy.

RMAN commands: BACKUP, RESTORE, RECOVER, LIST BACKUP, REPORT OBSOLETE, DELETE OBSOLETE, CROSSCHECK, VALIDATE; a recovery catalog is optional.

Flashback technologies: Flashback Query (AS OF SCN/TIMESTAMP), Flashback Table, Flashback Drop (recycle bin), Flashback Database (needs flashback logs and FLASHBACK ON), and Flashback Transaction.

Flashback Database requires ARCHIVELOG mode, a fast recovery area, and DB_FLASHBACK_RETENTION_TARGET; you must MOUNT to run FLASHBACK DATABASE.

A fast recovery area (DB_RECOVERY_FILE_DEST, DB_RECOVERY_FILE_DEST_SIZE) stores archived logs, backups, and flashback logs; RMAN retention policy (REDUNDANCY or RECOVERY WINDOW) governs deletion.

Media recovery applies redo and undo; complete recovery uses all redo, incomplete recovery stops at a target SCN, time, or log sequence and requires OPEN RESETLOGS.

A control file autobackup (CONFIGURE CONTROLFILE AUTOBACKUP ON) lets RMAN restore the control file and SPFILE without a catalog.

A connect descriptor contains ADDRESS (PROTOCOL, HOST, PORT) and CONNECT_DATA (SERVICE_NAME or SID); a net service name maps to a connect descriptor.

tnsnames.ora holds client-side net service names; listener.ora holds listener configuration; sqlnet.ora holds naming methods (NAMES.DIRECTORY_PATH) and security.

The listener receives connections and hands them to the database; the LOCAL_LISTENER parameter points the instance at its listener; a dynamic service registration uses the PMON process.

Easy Connect syntax: host:port/service_name, e.g., sqlplus user/pw@host:1521/orclpdb; no tnsnames.ora entry is needed.

Connection modes: dedicated (one server process per session) vs. shared (dispatchers and shared servers); SERVER=DEDICATED or SHARED in CONNECT_DATA.

tnsping tests name resolution and listener reachability but does not verify database login; a failed login with a working tnsping points to credentials, service name, or listener registration.

Common errors: ORA-12541 (no listener), ORA-12514 (service not registered), ORA-12154 (could not resolve the connect identifier), ORA-12560 (protocol adapter error).

The listener control utility (lsnrctl) supports STATUS, SERVICES, START, STOP, and RELOAD; the listener log records connection attempts.

V$ views are dynamic performance views backed by memory; V$SESSION, V$SQL, V$SQLAREA, V$SYSSTAT, V$SYSTEM_EVENT, V$SESSION_WAIT, and V$ACTIVE_SESSION_HISTORY are core.

Wait event classes: User I/O, System I/O, Concurrency, Application, Commit, Network, Configuration, Scheduler, Cluster, Administrative; DB time = DB CPU + non-idle wait time.

Top wait events commonly include db file sequential read (single-block index reads), db file scattered read (multiblock full scans), log file sync (commit waits), and enq: TX - row lock contention.

The buffer cache hit ratio = 1 - (physical reads / (db block gets + consistent gets)); a high ratio does not prove good performance, so use it with wait analysis.

The library cache hit ratio reflects parse reuse; high hard-parse counts suggest missing bind variables or insufficient shared pool.

STATISTICS_LEVEL (BASIC, TYPICAL, ALL) controls collection of many statistics and the automatic advisories; TYPICAL is the default.

The automatic advisors include the SQL Tuning Advisor, SQL Access Advisor, Memory Advisor (SGA/PGA), and Segment Advisor; DBMS_ADVISOR and DBMS_SQLTUNE invoke them.

An AWR snapshot is taken every 60 minutes by default (MMON); AWR reports compare two snapshots; ASH samples active sessions every second.

The alert log (in DIAGNOSTIC_DEST/diag/rdbms///trace) records startup, shutdown, errors, and administrative changes; XML-formatted alert logs are also produced.

Background and user trace files go to the trace directory; a server process trace can be produced with ALTER SESSION SET EVENTS or DBMS_MONITOR.

The Automatic Diagnostic Repository (ADR) is the root of the diagnostic destination; ADRCI (ADR command interpreter) lists and packages incidents and problems.

An incident is a single occurrence of a critical error; a problem groups related incidents; the Incident Packaging Service (IPS) creates a zip for Oracle Support.

The Health Monitor (DBMS_HM) runs checks such as DB Structure Integrity, Data Block Integrity, and Redo Integrity; results appear in V$HM_RUN and V$HM_FINDING.

SQL Tuning Advisor analyzes a SQL statement and recommends indexes, statistics, or SQL profiles; the SQL Access Advisor recommends indexes and materialized views for a workload.

Automated maintenance tasks (Automatic Optimizer Statistics Gathering, Automatic Segment Advisor, Automatic SQL Tuning Advisor) run in maintenance windows managed by the Scheduler.

The Data Recovery Advisor (LIST FAILURE, ADVISE FAILURE, REPAIR FAILURE in RMAN) detects and repairs data failures such as missing or corrupt files.

An instance = memory structures (SGA) + background processes; a database = physical files (datafiles, control files, online redo logs). One database can be mounted by multiple instances only in RAC.

SGA components: shared pool (library cache + row cache), database buffer cache, redo log buffer, large pool, Java pool, streams pool; SGA_MAX_SIZE caps total SGA, SGA_TARGET enables automatic shared memory management.

PGA is per-server-process memory: sort area, hash area, bitmap merge area; PGA_AGGREGATE_TARGET enables automatic PGA management and is advisory to work areas.

Key background processes: DBWn writes dirty buffers, LGWR writes redo, CKPT updates checkpoints, SMON does instance/transaction recovery, PMON cleans up failed processes, MMON/MMNL manage AWR/ASH, ARCn archives redo.

A server process handles a single client connection; a shared server configuration routes sessions through dispatchers and shared server processes instead.

Buffer cache states: pinned, dirty, free/unused, clean; a buffer is dirty when its change SCN exceeds the block's on-disk SCN. Logical reads (consistent gets/db block gets) come from the cache, physical reads from disk.

A database block is the smallest I/O unit (DB_BLOCK_SIZE, set at creation and not changeable for the SYSTEM tablespace); an extent is a set of contiguous blocks; a segment is a set of extents for one object.

Logical hierarchy: database > tablespace > segment > extent > block. Physical hierarchy: control files, datafiles, redo log files, archived logs, parameter file, password file.

Tablespace types: permanent (SYSTEM, SYSAUX, USERS), undo (UNDOTBS), temporary (TEMP); a locally managed tablespace uses a bitmap in each datafile and tracks extents as AUTOALLOCATE or UNIFORM.

DBA_TABLESPACES shows tablespace status/contents; DBA_DATA_FILES and DBA_TEMP_FILES show file sizes; DBA_FREE_SPACE shows free extents; DBA_SEGMENTS shows segment sizes.

AUTOEXTEND MAXSIZE and the tablespace's MAXSIZE clause cap growth; a datafile reaching its limit raises ORA-01653 (unable to extend table) or ORA-01652 (unable to extend temp segment).

ASSM (SEGMENT SPACE MANAGEMENT AUTO) uses bitmaps in the segment header and data block; MSSM uses freelists and PCTFREE/PCTUSED. ASSM reduces buffer busy waits and supports automatic segment growth.

Oracle Managed Files (DB_CREATE_FILE_DEST, DB_CREATE_ONLINE_LOG_DEST_n) let the database name and place files automatically instead of requiring explicit file names.

Segment types include table, index, LOB, undo, temporary, cluster, and nested table; a table with a LOB column creates a separate LOB segment and LOB index.

Dropping a table moves it to the recycle bin (unless PURGE is used), so its space is not released until PURGE RECYCLEBIN or the table is purged.

CREATE USER requires a password (or EXTERNALLY/GLOBALLY), a default tablespace, and a temporary tablespace; the user gets the CONNECT role only if granted.

Privilege types: system privileges (CREATE SESSION, CREATE TABLE, SELECT ANY TABLE), object privileges (SELECT, INSERT, UPDATE, DELETE, EXECUTE, ALTER, INDEX), and roles (named sets of privileges).

GRANT ... WITH ADMIN OPTION lets a grantee grant the system privilege or role onward; WITH GRANT OPTION applies to object privileges. Revoking a system privilege does not cascade; revoking an object privilege cascades.

A profile controls PASSWORD_LIFE_TIME, PASSWORD_GRACE_TIME, PASSWORD_REUSE_MAX, PASSWORD_REUSE_TIME, PASSWORD_LOCK_TIME, FAILED_LOGIN_ATTEMPTS, SESSIONS_PER_USER, IDLE_TIME, and CONNECT_TIME.

DEFAULT profile applies to users without an explicit profile; changing a profile affects all users assigned to it, and the change takes effect at the next session or password check.

A user with the UNLIMITED TABLESPACE system privilege or a quota on a tablespace can create segments; otherwise ORA-01950 (no privileges on tablespace) occurs.

DBA_USERS shows account status (OPEN, LOCKED, EXPIRED, LOCKED(TIMED)); ALTER USER ... ACCOUNT LOCK/UNLOCK and PASSWORD EXPIRE manage account state.

Roles can be default or non-default; SET ROLE enables a non-default role. Roles cannot be granted to other roles if they contain object privileges only in some cases-system-privilege roles are grantable.

Startup stages: NOMOUNT reads the parameter file and starts the instance; MOUNT reads the control files; OPEN opens datafiles and redo logs.

SHUTDOWN modes: NORMAL waits for sessions to end, TRANSACTIONAL waits for active transactions, IMMEDIATE rolls back uncommitted transactions and disconnects users, ABORT is immediate and requires recovery at next startup.

The parameter file is a PFILE (text, init.ora) or SPFILE (binary, spfile.ora, server-side). CREATE SPFILE FROM PFILE or the reverse converts; SPFILE changes via ALTER SYSTEM ... SCOPE=MEMORY|SPFILE|BOTH.

A static parameter (e.g., DB_BLOCK_SIZE, DB_NAME) requires a restart; a dynamic parameter can be changed with ALTER SYSTEM and SCOPE=MEMORY.

Database creation with CREATE DATABASE requires a STARTUP NOMOUNT first, a CREATE DATABASE statement specifying the datafiles, logfiles, and character set, then running catalog scripts (catalog.sql, catproc.sql).

DBCA can create a database interactively or silently; it generates the SPFILE, password file, and runs the catalog scripts automatically.

A password file (orapw) authenticates SYSDBA/SYSOPER remotely; REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE and the OSDBA group membership govern this.

The alert log and trace files in the diagnostic destination (BACKGROUND_DUMP_DEST/DIAGNOSTIC_DEST) record startup, shutdown, and errors.

Table types: heap (default), index-organized (IOT), clustered, partitioned, external (read-only via ORACLE_LOADER/ORACLE_DATAPUMP), and temporary (global or private).

Constraints: NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK; a primary key implies NOT NULL and UNIQUE; FOREIGN KEY ... ON DELETE CASCADE or SET NULL controls child rows.

A B-tree index is the default; bitmap indexes suit low-cardinality columns; a function-based index requires the function to be deterministic; an invisible index is not used by the optimizer unless hinted.

A view is a stored query; a materialized view stores results and can refresh ON COMMIT or ON DEMAND; a synonym is an alias for a schema object.

Sequences generate numbers via NEXTVAL and CURRVAL; caching reduces contention; a sequence is not transactional and gaps are expected.

DML: INSERT, UPDATE, DELETE, MERGE; a transaction ends with COMMIT, ROLLBACK, or DDL (which commits implicitly).

TRUNCATE is DDL: it removes all rows, releases space to the tablespace, and cannot be rolled back; DELETE is DML and can be rolled back.

A partitioned table partitions rows by RANGE, LIST, HASH, or COMPOSITE; each partition can have its own tablespace and local indexes.

Undo records the before-image of changed data for rollback and read consistency; redo records the after-image for recovery. Undo is protected by redo.

UNDO_MANAGEMENT=AUTO with an undo tablespace; UNDO_RETENTION sets the target retention; UNDO_TABLESPACE names the active undo tablespace.

An undo segment can be ACTIVE (in use), EXPIRED (past retention), or UNEXPIRED (within retention but not in use); ORA-01555 (snapshot too old) occurs when a needed undo is overwritten.

A transaction gets an undo segment and a transaction ID; a savepoint allows partial rollback; COMMIT writes a commit record to redo and makes changes durable.

Redo log files are written in a circular fashion; LGWR writes on commit, on log buffer one-third full, and every three seconds. A log switch moves to the next group; ARCn archives the full group in ARCHIVELOG mode.

A checkpoint advances the checkpoint SCN and makes DBWn write dirty buffers; FAST_START_MTTR_TARGET bounds recovery time by controlling checkpoint frequency.

Isolation levels: READ COMMITTED (default, statement-level consistency) and SERIALIZABLE (transaction-level consistency); READ ONLY is available for queries.

A distributed transaction uses two-phase commit (prepare then commit); a transaction ID and SCN identify it in DBA_2PC_PENDING.

NOARCHIVELOG mode allows only closed-database backups and recovery only to the last backup; ARCHIVELOG mode allows online backups and point-in-time recovery.

RMAN backup types: full (all blocks), incremental level 0 (base), incremental level 1 (differential vs. last level 1, cumulative vs. last level 0); a backup set or image copy.

RMAN commands: BACKUP, RESTORE, RECOVER, LIST BACKUP, REPORT OBSOLETE, DELETE OBSOLETE, CROSSCHECK, VALIDATE; a recovery catalog is optional.

Flashback technologies: Flashback Query (AS OF SCN/TIMESTAMP), Flashback Table, Flashback Drop (recycle bin), Flashback Database (needs flashback logs and FLASHBACK ON), and Flashback Transaction.

Flashback Database requires ARCHIVELOG mode, a fast recovery area, and DB_FLASHBACK_RETENTION_TARGET; you must MOUNT to run FLASHBACK DATABASE.

A fast recovery area (DB_RECOVERY_FILE_DEST, DB_RECOVERY_FILE_DEST_SIZE) stores archived logs, backups, and flashback logs; RMAN retention policy (REDUNDANCY or RECOVERY WINDOW) governs deletion.

Media recovery applies redo and undo; complete recovery uses all redo, incomplete recovery stops at a target SCN, time, or log sequence and requires OPEN RESETLOGS.

A control file autobackup (CONFIGURE CONTROLFILE AUTOBACKUP ON) lets RMAN restore the control file and SPFILE without a catalog.

A connect descriptor contains ADDRESS (PROTOCOL, HOST, PORT) and CONNECT_DATA (SERVICE_NAME or SID); a net service name maps to a connect descriptor.

tnsnames.ora holds client-side net service names; listener.ora holds listener configuration; sqlnet.ora holds naming methods (NAMES.DIRECTORY_PATH) and security.

The listener receives connections and hands them to the database; the LOCAL_LISTENER parameter points the instance at its listener; a dynamic service registration uses the PMON process.

Easy Connect syntax: host:port/service_name, e.g., sqlplus user/pw@host:1521/orclpdb; no tnsnames.ora entry is needed.

Connection modes: dedicated (one server process per session) vs. shared (dispatchers and shared servers); SERVER=DEDICATED or SHARED in CONNECT_DATA.

tnsping tests name resolution and listener reachability but does not verify database login; a failed login with a working tnsping points to credentials, service name, or listener registration.

Common errors: ORA-12541 (no listener), ORA-12514 (service not registered), ORA-12154 (could not resolve the connect identifier), ORA-12560 (protocol adapter error).

The listener control utility (lsnrctl) supports STATUS, SERVICES, START, STOP, and RELOAD; the listener log records connection attempts.

V$ views are dynamic performance views backed by memory; V$SESSION, V$SQL, V$SQLAREA, V$SYSSTAT, V$SYSTEM_EVENT, V$SESSION_WAIT, and V$ACTIVE_SESSION_HISTORY are core.

Wait event classes: User I/O, System I/O, Concurrency, Application, Commit, Network, Configuration, Scheduler, Cluster, Administrative; DB time = DB CPU + non-idle wait time.

Top wait events commonly include db file sequential read (single-block index reads), db file scattered read (multiblock full scans), log file sync (commit waits), and enq: TX - row lock contention.

The buffer cache hit ratio = 1 - (physical reads / (db block gets + consistent gets)); a high ratio does not prove good performance, so use it with wait analysis.

The library cache hit ratio reflects parse reuse; high hard-parse counts suggest missing bind variables or insufficient shared pool.

STATISTICS_LEVEL (BASIC, TYPICAL, ALL) controls collection of many statistics and the automatic advisories; TYPICAL is the default.

The automatic advisors include the SQL Tuning Advisor, SQL Access Advisor, Memory Advisor (SGA/PGA), and Segment Advisor; DBMS_ADVISOR and DBMS_SQLTUNE invoke them.

An AWR snapshot is taken every 60 minutes by default (MMON); AWR reports compare two snapshots; ASH samples active sessions every second.

The alert log (in DIAGNOSTIC_DEST/diag/rdbms///trace) records startup, shutdown, errors, and administrative changes; XML-formatted alert logs are also produced.

Background and user trace files go to the trace directory; a server process trace can be produced with ALTER SESSION SET EVENTS or DBMS_MONITOR.

The Automatic Diagnostic Repository (ADR) is the root of the diagnostic destination; ADRCI (ADR command interpreter) lists and packages incidents and problems.

An incident is a single occurrence of a critical error; a problem groups related incidents; the Incident Packaging Service (IPS) creates a zip for Oracle Support.

The Health Monitor (DBMS_HM) runs checks such as DB Structure Integrity, Data Block Integrity, and Redo Integrity; results appear in V$HM_RUN and V$HM_FINDING.

SQL Tuning Advisor analyzes a SQL statement and recommends indexes, statistics, or SQL profiles; the SQL Access Advisor recommends indexes and materialized views for a workload.

Automated maintenance tasks (Automatic Optimizer Statistics Gathering, Automatic Segment Advisor, Automatic SQL Tuning Advisor) run in maintenance windows managed by the Scheduler.

The Data Recovery Advisor (LIST FAILURE, ADVISE FAILURE, REPAIR FAILURE in RMAN) detects and repairs data failures such as missing or corrupt files.