title: Oracle Gather Table Statistics Time
body: <p>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.</p>
  
  <p>The Oracle version is </p>
  
  <pre><code>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
  </code></pre>
  
  <p>The gather stats syntax is </p>
  
  <pre><code>dbms_stats.gather_table_stats('OWNER', 'TABLE_NAME', estimate_percent =&gt; 100, method_opt =&gt; 'for all columns size auto', cascade =&gt; true);
  </code></pre>
  
  <p>We cannot change the parameters of the above gather stats command as the application vendor insists as these parameters be used.</p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p><strong>Will a ALTER TABLE Shrink reduce the overall time taken to collect statistics on the table? Is it recommended?</strong></p>