Which command is used to gather statistics on stale tables in a schema for ADW?

The command that is used to gather statistics on stale tables in a schema for Oracle Autonomous Data Warehouse (ADW) is DBMS_STATS.GATHER_SCHEMA_STATS. This command specifically targets the collection of statistics at the schema level and is capable of determining stale tables automatically. When executed, it scans the tables within the specified schema and collects necessary statistics that the query optimizer uses to generate efficient execution plans.

Gathering statistics is crucial in an Oracle database because outdated or missing statistics can lead to inefficient query execution, ultimately affecting performance. The DBMS_STATS package is designed to help maintain the data and optimize the performance of Oracle databases by allowing users to collect, manage, and update statistics efficiently.

The other options do not serve the same purpose. For instance, while DBMS_STATS.ANALYZE_TABLE is a valid command for analyzing a specific table to gather statistics, it does not operate at the schema level and would require individual table specifications. The command DBMS_STATS.UPDATE_SCHEMA_STATS is not a recognized command within the DBMS_STATS package, and hence does not apply. Finally, DBMS_STATS.COLLECT_STATS is also not a standard command—it is more typical to reference it as GATHER_STATS. Therefore, the correct choice emphasizes the command that efficiently captures statistics