SYS.DBMS_STATS.AUTO_SAMPLE_SIZE is a parameter that can be used in the Oracle DBMS_STATS package to specify that Oracle should automatically determine the appropriate sample size when gathering statistics on a database object.
When AUTO_SAMPLE_SIZE is used, Oracle analyzes the object being sampled and selects an appropriate sample size based on its characteristics. This can help to ensure that accurate statistics are gathered while minimizing the amount of time and system resources required to perform the operation.
It is highly recommended that from Oracle Database 11g on wards that the default AUTO_SAMPLE_SIZE is used for ESTIMATE_PRECENT. This is especially important because the newer histogram types (HYBRID and Top-Frequency) can only be created if an auto sample size is used.
Here is an example of how to use SYS.DBMS_STATS.AUTO_SAMPLE_SIZE 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 => SYS.DBMS_STATS.AUTO_SAMPLE_SIZE, degree => DBMS_STATS.AUTO_DEGREE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE); END; /
In this example, the estimate_percent parameter is set to SYS.DBMS_STATS.AUTO_SAMPLE_SIZE, indicating that Oracle should automatically determine the appropriate sample size when gathering statistics for the "EMPLOYEES" table. The other parameters used in this example are similar to those used in the previous example I provided.