Oracle 11g introduced the ability to control the behaviour of the
dbms_stats package by setting preferences on the database, schema, and table level. These affect the way
dbms_stats goes about doing its work. This feature has been extensively documented, I found the post by Maria Colgan exceptionally good at explaining the mechanism.
I often have to check table preferences in case statistics look a little suspicious but there isn’t an Oracle supplied way that I’m aware of to print all table preferences so I came up with a little script to do so. It currently supports Oracle 19c exclusively but it’s super easy to update the code for other releases.
Extending the code is very straight-forward. Simply create a new variable like
v_prefs_12102 and copy/paste all the possible values from the 12c documentation for
dbms_stats.get_prefs(). Feel free to fork my repository and submit a pull request with the changes, I’m happy to accept contributions.