GATHER AUTO in DBMS_STATS

The "Options => 'GATHER AUTO'" parameter setting is used in Oracle's DBMS_STATS package to specify that the statistics gathering operation should use automatic settings for degree of parallelism and sample size.

When "Options => 'GATHER AUTO'" is used, Oracle will automatically determine the optimal settings for collecting statistics based on the size and complexity of the database objects being analyzed. This can help ensure that statistics are collected efficiently without consuming excessive system resources.

Here is an example of how to use "Options => 'GATHER AUTO'" with the DBMS_STATS.GATHER_TABLE_STATS procedure to collect statistics for a table named "EMPLOYEES":

BEGIN
   DBMS_STATS.GATHER_TABLE_STATS(
      ownname      => 'HR',
      tabname      => 'EMPLOYEES',
      estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
      degree       => DBMS_STATS.AUTO_DEGREE,
      method_opt   => 'FOR ALL COLUMNS SIZE AUTO',
      cascade      => TRUE,
      options      => 'GATHER AUTO');
END;
/

In this example, "Options => 'GATHER AUTO'" is included as a parameter to the DBMS_STATS.GATHER_TABLE_STATS procedure, indicating that automatic settings should be used for the statistics gathering operation. This allows Oracle to determine the best settings for the job based on the characteristics of the "EMPLOYEES" table.