Performance

A . Dependent indexes become UNUSABLE.False. SHRINK SPAC E COMPACT does not affect index usability; only SHRINK SPACE without COMPACT may require inde x maintenance if CASCADE is omitted. B ..... starting toward the end of the ORDERS segment.False. Rows are compacted toward the beginning of the segment, not the end. C . Only queries are allowed .... False. Both queries and DML are allowed during SHRINK SPACE COMPACT as it’s an online operation. D . The high-water mark (HWM) of ORDERS is adjusted.False. COMPACT moves rows but doesn’t adjust the HWM; the full SHRINK SPACE command is needed for HWM adjustment. E . Queries and DML statements are allowed .... True. This is an online operation in 23ai, allowing concurrent access. F ...... starting from the beginning of the ORDERS segment.True. Rows are moved to fill free space from the segment’s start.

A .True. Migration updates index ROWIDs. B .True. Updates can migrate rows if space is insufficient. C .True. Large updates can chain rows across blocks. D .False. Chained rows may span blocks but aren’t required to. E .False. Inserts don’t migrate; they chain if too large initially.

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. 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 o bjects. Mechanics:Controlled by STATISTICS_LEVEL=TYPICAL (default) and t he DEFAULT_MAINTENANCE_PLAN. It prioritizes objects with >10% cha nges (stale stats) or no stats. Practical Use:Ensures stats are current without manual in tervention, critical for dynamic workloads. Edge Case:Disabled if STATISTICS_LEVEL=BASIC or the job is manua lly disabled via DBMS_AUTO_TASK_ADMIN.DISABLE. B . Optimizer Statistics are collected in real-time as data is inserted, deleted, or updated. 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. 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 mana gement. D . Optimizer Statistics are collected by the Statistics Advisor. 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. 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 purpo ses — monitoring vs. optimization.