Printing all table preferences affecting dbms_stats.gather_table_stats

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!

Advertisement