7 Replies Latest reply: Dec 27, 2012 3:55 PM by 977635 RSS

    gather table stats hangs for hours...

      Solaris 10, RDBMS EE, running D/G.

      I think I must be delusional but I have a single table in our production database that I can not seem to gather stats on.
      I've tried on three separate attempts and after 6 hours, I have to kill the job before it impacts the performance of the database during normal work hours.
      Last night, it ran for six hours and while it was running, I could see in Toad that it was actually processing, and spinning the whole time.
      I also confirmed there was nothing else holding any locks on the table.

      Here's the stats:

      I have a single table that has approx. 6 million records.
      The table has 540 columns of which 20 of them are LOBs. (not that it matters, but just for info)
      The table itself has approx. 6mil records, and is about 13.4G.
      I have the same table in our test database but it is only about 4.5G and only has about 2mil records.
      The gathering of stats in test only takes approx. 5 minutes.

      command I am using is:
      exec dbms_stats.gather_table_stats ('ARADMIN','T2444',cascade=>TRUE,estimate_percent=>dbms_stats.auto_sample_size,granularity=>'ALL',method_opt=>'for all columns size auto',degree=>8);
      Any ideas why this takes so long to run?
      One thing odd is that while I specify degree of parallelism to 8, when I look in Toad, I see 16 processes running for the gather stats job.