user1014212 wrote:1) enable SQL_TRACE
Stats collection running long for almost 12 hours from 6PM to 6AM daily and stats collection autotask is getting STOPPED after the window duration.
DBA_TABLES.last_analyzed is not getting updated after 2AM in the morning. I believe it is scanning some big tables, but not able to pin point the table/index.
And we've some a bunch of 100GB tables in the DB. Is there a way to find out which table it's gathering stats between 2AM and 6AM and not getting completed?
Thanks for your time!
I would suggest to find out those tables with huge Table & last_analyzed is Null , these tables would be possible tables responsible for long run.
Only AWR ot other SQL Hist stats tables can help you to get details.
To fix this issue you should run stats gathering manually in day time (not in maintenance window) either with auto stats or with little estimate_percent (10 or 5)
After getting first successful analyze (manually) , maintenance stats will pick up new incremental stats thereafter