Skip to Main Content
  • Questions
  • What is best way to collect GLOBAL STATS of a table with 4 billion records

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Sudheer.

Asked: July 15, 2016 - 3:36 pm UTC

Last updated: July 16, 2016 - 5:13 am UTC

Version: Oracle 11G

Viewed 1000+ times

You Asked

Hi Tom

I need to take global stats collection for a table with 4 billion records. This is a partitioned table but I need to collect STATS globally. This table was last analyzed in Sept 2015. As of now following is Table statistics

Actual Number of Rows in Table 3,751,706,524
NUM_ROWS as per STATS 2,801,172,997


What would be the best way to collect the STATS with minimal downtime?

Can I perform any activities on this table when the STATS collection is going on?

Thank You
Sudheer

and Connor said...

Collecting stats does not lock the table, so activity is fine. Obviously collecting the stats will run some big queries on the table, so ensure you have a reasonable size for undo_retention.

Make sure you collect stats with estimate_percent being dbms_stats.auto_sample_size. When you do this, we can avoid some of the huge sorting operations needed to calculate distinct values for columns.

More details here

https://blogs.oracle.com/optimizer/entry/how_does_auto_sample_size

If it still runs too slowly, look at tinkering with the degree parameter.

Longer term you might want to consider

- incremental statistics (check the docs for this)

- manually setting them. On huge tables, most stats can be deduced rather than calculated - eg high water mark for dates is often today, the number of rows added per day is unchanging etc...and dbms_stats.set_table_stats is a lot more efficient than scanning the whole table.

Hope this helps.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

TABLE STATS

Sudheer Radhakrishnan, July 18, 2016 - 5:46 pm UTC

Thanks for your reply

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.