The AUTOSTATS feature in Oracle 19c enables automatic collection of optimizer statistics. This is critical for ensuring that the optimizer has up-to-date information to generate optimal execution plans. Here’s how to enable AUTOSTATS and manage its functionality:

Enable Auto Stats in Oracle 19c

1. Check Current Status: Run the following query to see if automatic statistics collection is enabled:

SELECT name, value
FROM v$parameter
WHERE name = 'optimizer_statistics_enable';

2. Enable Automatic Statistics: Use this command to enable the feature:

ALTER SYSTEM SET optimizer_statistics_enable = TRUE SCOPE=BOTH;

3. Enable Statistics Gathering for Specific Schemas: Automatic statistics collection is tied to maintenance windows. To ensure specific schemas are included:

EXEC DBMS_AUTO_TASK_ADMIN.ENABLE(
client_name => 'auto optimizer stats collection',
operation => NULL,
window_name => NULL
);

Verify and Tune Automatic Statistics

• Check if the DBMS_STATS.GATHER_* procedures are executed automatically:

SELECT client_name, status
FROM dba_autotask_client
WHERE client_name = 'auto optimizer stats collection';

If auto stats collection is enabled but not working, ensure that:

The Scheduler is enabled:

EXEC DBMS_SCHEDULER.ENABLE('SYS.AUTO_STATS_ADVISOR_TASK');

The job queue process is active:

ALTER SYSTEM SET job_queue_processes = 10 ;

4.Customize Settings for Gather Stats: Use DBMS_STATS to fine-tune the behavior, for instance:

EXEC DBMS_STATS.SET_GLOBAL_PREFS('AUTO_STATS_TARGET', 'ALL');

5.Monitor and Review Statistics: After enabling, periodically check if statistics are up-to-date:

SELECT table_name, last_analyzed
FROM user_tables
WHERE table_name = 'YOUR_TABLE_NAME';