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.
