Unfortunately you will not always have all rights on the database you would want or need to fulfill your tasks.
Especially when you have to do performance tuning on Queries and you do not have the right to use the Oracle Advisor you can get frustrated because the DBA is not available as you only do stuff on a UAT or even SIT. "Sorry, the DBA is busy with PRD." Nice!
When in addition your company decided to switch off automated update of statistics for the Optimizer, you want to pull out your hair.
Why would someone do this you might ask. Well, one of the applications we are running is stating in its requirements that the optimizer should be switched off. It is updating stats on its own.
When you are developing new reports, this is of course not very nice. If you are suffering from this as well, the following script might be of interest for you.
You can run the script in SQLDeveloper and you will have to activate the DBMS_OUTPUT view to see its results.
Important is that you only update statistics based on this if it makes sense in your application.
For example it does not make sense to update the stats for one table to a lower value than the usual if it occasionally contains only few entries.
Sometimes it is better to leave the stats on a higher count than currently available in a checked table.
The following script lists all tables, which shows a stat value lower than the current count as stale.
All other table stats are listed as fresh.