Gather statistics on some Oracle tables take a long time. These tables have a record count ranging from 2 Million Records to 9 Million Records. The tables have around 5-6 indexes on each one of them.
The Oracle version is
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
"CORE 10.2.0.1.0 Production"
TNS for IBM/AIX RISC System/6000: Version 10.2.0.1.0 - Productio
NLSRTL Version 10.2.0.1.0 - Production
The gather stats syntax is
dbms_stats.gather_table_stats('OWNER', 'TABLE_NAME', estimate_percent => 100, method_opt => 'for all columns size auto', cascade => true);
We cannot change the parameters of the above gather stats command as the application vendor insists as these parameters be used.
Please let me know if there is anything we could do to reduce the time taken by the Gather Statistics JOB. I noticed that the JOB when runs, causes the app performance to reduce a little and this is not acceptable.
I also noticed that some of the tables occupy a lot of space on the Disk but the real data (by doing a estimate of record count multiplied by average row length) is a lot lesser. Looks like the tables need to be Compacted/Shrunk/High Water Mark Reset etc.
Some tables for example occupy 9 GB on Disk but Real Data is shown to be 1.2 GB . . . Almost 70% Space Wasted in Fragmentation.
Will a ALTER TABLE Shrink reduce the overall time taken to collect statistics on the table? Is it recommended?
Yes, shrinking space will help. If you can afford to take the application down for a while, and the tables aren't going to just bounce right back to their previous size, then shrinking space is always a good idea.
Other than that, if parameters can't change there's not much you can do to improve things. Setting the DEGREE parameter can signficantly improve performance in some cases. I know you said you can't change any parameters, but I don't see how they could complain about that one. Although it may make the job run faster it would probably impact the system performance even more (but for a shorter period of time).
The best solution might be to upgrade to 11g, where any sane application would use estimate_percent => dbms_stats.auto_sample_size. Statistics collection in 11g is much better than 10g. With features like improved auto sample algorithms, incremental statistics, setting statistics preferences, and concurrent statistics, gathering statistics is often much faster and more accurate.