How does DBMS_STATS work? I want to know what really happens when collecting statistics using DBMS_STATS. How does Oracle get the number of rows from a table?
Basically, to get the number of rows and other information that DBMS_STATS calculates, Oracle has to read that table. So if your table is 1 million rows, then DBMS_STATS will read all 1 million rows of data. This can be quite time-consuming for larger objects. For this, you should ESTIMATE rather than COMPUTE statistics on the object. With ESTIMATE, Oracle does not read the entire table, but a sample of the table. You can define how much by telling Oracle to use 10% or 20%, or whatever value, of the table. Or, you can let Oracle decide how much of the table to use by specifying the AUTO_SAMPLE_SIZE option as follows: