Simon Krenger

So here is another post I keep mainly for my own reference, since I regularly need to gather new schema statistics. The information here is based on the Oracle documentation for DBMS_STATS, where all the information is available.

So if you want to COMPUTE the statistics (which means to actually consider every row and not just estimate the statistics), use the following syntax:

EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'SIMON', estimate_percent => NULL);

However, you can also just specify the name of the schema:

EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'SIMON');

This will use the constant DBMS_STATS.AUTO_SAMPLE_SIZE to have Oracle determine the appropriate sample size for good statistics. Find all other information (available parameters, usage) in the Oracle documentation.