What is the impact if Stats Gather runs in business hours.
Hi
I have a Production Database in which Stats gather is running every alternative day as per the business requirement.
This Stats gather job starts at 7 PM. As data growing in this Database Time taken for this Job also increasing.
Now a days, This job is taking more than 14 hours to Analyze the Production Schema! It is completing at
9 AM in next day by the time business time starts.
Below is the script which we are using.
EXEC DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>'${schema_name}',estimate_percent=>'',method_opt => 'FOR ALL COLUMNS SIZE
AUTO',cascade => TRUE,options=>'GATHER');
1. Is it recommended to run the stats gather in business hours?
2. How can We tune this Schema gather, so that this will take less time.
Appreciate the help.
Thanks in Advance.