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.
As of August 2021 I have included the code to my “misc” Github repository, you can get the script either from the repository page or directly via this link.
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.
Happy troubleshooting!
Martins Blog 