Introduction

Optimizer statistics are part of every performance discussion in Oracle Autonomous AI Lakehouse and Fusion Data Intelligence (FDI). They help the optimizer estimate row counts, understand column data distribution, evaluate indexes and partitions, and choose efficient execution plans.

Autonomous AI Lakehouse automates much of the statistics management work for common warehouse loading patterns. FDI also manages statistics for objects in the managed warehouse schema. However, many customer environments include custom schemas with extension tables, materialized views, reporting tables, and partitioned history tables. Those objects still need a clear statistics strategy.

This article explains where Autonomous AI Lakehouse and FDI manage statistics automatically, where customer-managed objects require attention, and how to use targeted table gathers, incremental statistics for partitioned tables, high-frequency statistics, and a weekly schema job as a safety net.

What Autonomous AI Lakehouse and FDI Manage Automatically

For Autonomous AI Lakehouse workloads, Oracle reduces much of the manual work traditionally associated with optimizer statistics. In common warehouse load patterns, especially SQL-based direct path loads with DBMS_CLOUD or INSERT /*+ APPEND */ used by the FDI-managed pipeline, optimizer statistics are gathered automatically as data is loaded.

The FDI-managed pipeline manages statistics only for tables owned by the OAX$DW schema. If you have tables or materialized views created in a separate custom schema, such as OAX_USER, you need to manage statistics for those objects.

This distinction matters for FDI customers who extend the delivered model using custom schemas, enrichment objects, or reporting structures. Those objects might be part of the same analytics workflow, but they’re outside the managed statistics path for OAX$DW.

When to Gather Statistics Manually

Manual statistics gathering is useful when data changes outside the automatic path.

Common examples include:

  • Large conventional DML operations: INSERT, UPDATE, DELETE, and/or MERGE
  • Tables that are truncated and rebuilt
  • Customer-managed extension tables
  • Materialized views
  • Reporting tables refreshed shortly before dashboards are used

In those cases, gather statistics for custom tables that are stale at the end of the ETL process. Change OWNER and TABLE_NAME for your environment.

BEGIN
   DBMS_STATS.GATHER_TABLE_STATS(
      OWNNAME          => '<OWNER>',
      TABNAME          => '<TABLE_NAME>',
      OPTIONS          => 'GATHER AUTO',
      ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE,
      METHOD_OPT       => 'FOR ALL COLUMNS SIZE 254',
      CASCADE          => DBMS_STATS.AUTO_CASCADE,
      DEGREE           => DBMS_STATS.AUTO_DEGREE,
      NO_INVALIDATE    => DBMS_STATS.AUTO_INVALIDATE
   );
END;
/

The goal isn’t to gather statistics for every table after every ETL run. It’s to refresh statistics for data that changed enough to affect the cost-based optimizer decisions to build an effective execution plan before reporting workloads begin.

Check for Stale Statistics

Before gathering statistics, check what the statistics state and availability:

SELECT owner,
       table_name,
       partition_name,
       num_rows,
       stale_stats,
       last_analyzed
FROM   all_tab_statistics
WHERE  owner = '<OWNER>'
AND    stale_stats = 'YES'
ORDER  BY table_name, partition_name;

If STALE_STATS is YES, the optimizer’s view of the object might no longer match the current data. If LAST_ANALYZED is old relative to the latest refresh, review that table before the next reporting window.

High-Frequency Statistics

For Oracle Autonomous AI Lakehouse environments where data in important tables changes throughout the day, high-frequency statistics gathering can reduce the time that optimizer statistics remain stale between regular collection cycles.

Oracle Autonomous AI Lakehouse high-frequency statistics gathering runs periodically during the day, inside and outside maintenance windows, as a background task. In Autonomous AI Lakehouse, the default setup starts the task every 900 seconds (15 minutes) and allows it to run for up to 3600 seconds (1 hour).

This practice is applicable for fast-moving warehouse tables, near-real-time reporting extensions, or FDI custom objects that receive frequent changes during business hours. It’s a supplement to targeted statistics management, not a replacement for good load design or post-ETL statistics gathering when reports require current statistics at a specific time.

Partitioned Tables

Large, partitioned fact and history tables statistics management requires additional planning. In Autonomous AI Lakehouse and FDI extension workloads, these tables are often refreshed on a partition basis, while reports still need accurate global table statistics.

For large, partitioned tables, use table-level incremental statistics preferences. This allows later post-ETL or weekly statistics jobs to refresh statistics on partitions where data has changed and maintain global table statistics incrementally, instead of forcing repeated full-table statistics gathers.

Recommended table-level preferences for your partitioned tables: Change OWNER and TABLE_NAME for your custom schema and partitioned table name.

BEGIN
  DBMS_STATS.SET_TABLE_PREFS(
    ownname => '<OWNER>',
    tabname => '<TABLE_NAME>',
    pname   => 'GRANULARITY',
    pvalue  => 'AUTO'
  );

  DBMS_STATS.SET_TABLE_PREFS(
    ownname => '<OWNER>',
    tabname => '<TABLE_NAME>',
    pname   => 'INCREMENTAL',
    pvalue  => 'TRUE'
  );

  DBMS_STATS.SET_TABLE_PREFS(
    ownname => '<OWNER>',
    tabname => '<TABLE_NAME>',
    pname   => 'INCREMENTAL_LEVEL',
    pvalue  => 'PARTITION'
  );

  DBMS_STATS.SET_TABLE_PREFS(
    ownname => '<OWNER>',
    tabname => '<TABLE_NAME>',
    pname   => 'INCREMENTAL_STALENESS',
    pvalue  => 'USE_STALE_PERCENT'
  );

  DBMS_STATS.SET_TABLE_PREFS(
    ownname => '<OWNER>',
    tabname => '<TABLE_NAME>',
    pname   => 'STALE_PERCENT',
    pvalue  => '10'
  );
END;
/

These preferences tell Autonomous AI Lakehouse to maintain statistics incrementally at the partition level and to use the stale percentage threshold when deciding whether changed partitions need fresh statistics. With STALE_PERCENT set to 10, a partition is considered stale when row changes exceed the configured threshold.

Use this selectively for large partitioned tables. Don’t set incremental statistics schema-wide unless most tables in the schema are large partitioned tables that follow this refresh pattern.

Weekly Schema Job as a Safety Net

Even with Autonomous AI Lakehouse automation, a weekly schema-level job is a useful safety net to ensure statistics are up to date.

This job shouldn’t be the primary statistics strategy after every ETL run. Use it during a quiet maintenance window to catch stale or missing statistics that weren’t handled earlier.

Example weekly scheduled job: Change OWNER to your custom schema name.

BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name        => 'WEEKLY_SCHEMA_STATS_JOB',
    job_type        => 'PLSQL_BLOCK',
    job_action      => q'[
      BEGIN
        DBMS_STATS.GATHER_SCHEMA_STATS(
          ownname          => '<OWNER>',
          options          => 'GATHER AUTO',
          estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
          method_opt       => 'FOR ALL COLUMNS SIZE 254',
          cascade          => DBMS_STATS.AUTO_CASCADE,
          degree           => DBMS_STATS.AUTO_DEGREE,
          no_invalidate    => DBMS_STATS.AUTO_INVALIDATE
        );
      END;
    ]',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'FREQ=WEEKLY;BYDAY=SUN;BYHOUR=2;BYMINUTE=0;BYSECOND=0',
    job_class       => 'TPURGENT',
    enabled         => TRUE,
    comments        => 'Weekly schema statistics using GATHER AUTO.'
  );
END;
/

For large, partitioned tables, the weekly schema job uses the table-level preferences configured earlier. That allows changed partitions and global statistics to be handled more efficiently.

Practical Playbook

Start with the automation built into Autonomous AI Lakehouse and FDI. Then identify objects outside the managed path.

After direct path warehouse loads, such as DBMS_CLOUD loads or INSERT /*+ APPEND */, expect Autonomous AI Lakehouse to gather optimizer statistics as part of the direct load path.

After major conventional DML, use DBMS_STATS.GATHER_TABLE_STATS for targeted table-level statistics gathering.

For FDI customers, pay special attention to customer-managed tables used for extensions, custom schemas, materialized views, enrichment and partitioned tables.

For large, partitioned tables, configure table-level incremental statistics preferences so Oracle can maintain partition-level and global statistics more efficiently.

Use high-frequency statistics for objects that change throughout the day and need a shorter stale-statistics window.

Run a weekly DBMS_STATS.GATHER_SCHEMA_STATS job with OPTIONS => ‘GATHER AUTO’ as a safety net during a quiet maintenance window.

Avoid over-gathering. Statistics collection uses resources too, and “more often” is not always “better.”

Summary

For Autonomous AI Lakehouse and FDI workloads, the right strategy isn’t to gather statistics everywhere after every load. The right strategy is to understand which objects are managed automatically, which objects are customer-managed, and which refresh patterns require additional statistics handling.

Use targeted table statistics after ETL when reporting depends on recently changed custom objects. Use incremental statistics preferences for large partitioned tables. Consider high-frequency statistics for objects that change throughout the day. Add a weekly schema-level job with GATHER AUTO as a safety net.

The goal is to gather the right statistics at the right point in the data refresh cycle, so reporting and analytics workloads start with optimizer statistics that reflect the current data.

Call to Action

For Autonomous AI Lakehouse and FDI environments, statistics management works best when it’s tied to the refresh pattern, not treated as a generic maintenance task.

Identify the customer-managed objects that matter most to reporting and downstream analytics. Confirm which ones are already covered by the managed load path, which ones become stale after ETL, and which ones need special handling because they’re partitioned or updated throughout the day.

Start with one high-impact refresh flow, validate the before-and-after statistics state, and apply the smallest reliable approach: targeted table gathering, incremental statistics preferences, high-frequency statistics, or a weekly safety-net job. Then reuse that pattern for similar schemas and workloads.

For more information