Optimizer Statistics drive the cost-based optimizer’s query plans. Let’s dissect each option:

A. Optimizer Statistics are collected automatically by an automatic maintenance job that runs during predefined maintenance windows.

Explanation:True. Oracle 23ai uses the AutoTask framework to gather stats automatically during maintenance windows (e.g., nightly 10 PM–2 AM). The GATHER_STATS_PROG job, managed by DBMS_AUTO_TASK_ADMIN, collects stats for stale or missing objects.

Mechanics:Controlled by STATISTICS_LEVEL=TYPICAL (default) and the DEFAULT_MAINTENANCE_PLAN. It prioritizes objects with >10% changes (stale stats) or no stats.

Practical Use:Ensures stats are current without manual intervention, critical for dynamic workloads.

Edge Case:Disabled if STATISTICS_LEVEL=BASIC or the job is manually disabled via DBMS_AUTO_TASK_ADMIN.DISABLE.

B. Optimizer Statistics are collected in real-time as data is inserted, deleted, or updated.

Explanation:False. Stats aren’t updated in real-time; this would be too resource-intensive. Instead, Oracle tracks changes (e.g., via DBA_TAB_MODIFICATIONS) and updates stats periodically via AutoTask or manually. Real-time stats exist in 23ai for specific cases (e.g., GATHER_TABLE_STATS with REAL_TIME_STATS), but it’s not the default.

Why Incorrect:Real-time collection would degrade performance for OLTP systems, contradicting Oracle’s batch approach.

C. Optimizer Statistics can be manually collected at multiple levels using DBMS_STATS.GATHER_*_STATS PL/SQL procedures.

Explanation:True. The DBMS_STATS package offers granular control: GATHER_TABLE_STATS, GATHER_SCHEMA_STATS, GATHER_DATABASE_STATS, etc., allowing stats collection for tables, schemas, or the entire database.

Mechanics:Example: BEGIN DBMS_STATS.GATHER_TABLE_STATS('HR', 'EMPLOYEES'); END;. Options like ESTIMATE_PERCENT and DEGREE fine-tune the process.

Practical Use:Used for immediate stats updates post-DML or for custom schedules outside maintenance windows.

Edge Case:Overuse can lock stats (e.g., FORCE=TRUE), requiring careful management.

D. Optimizer Statistics are collected by the Statistics Advisor.

Explanation:False. The Statistics Advisor (new in 23ai) analyzes and recommends stats improvements but doesn’t collect them. Collection is still via DBMS_STATS or AutoTask.

Why Incorrect:It’s a diagnostic tool, not an executor.

E. Optimizer Statistics are collected automatically by Automatic Workload Repository (AWR) Snapshot.

Explanation:False. AWR snapshots capture performance metrics (e.g., wait times), not optimizer stats. Stats collection is a separate process via AutoTask or manual commands.

Why Incorrect:AWR and stats collection serve distinct purposes—monitoring vs. optimization.