Skip to Main Content
  • Questions

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

Breadcrumb

XMAS_TREE

The AskTOM team is taking a break over the holiday season, so we're not taking questions or responding to comments. Please have a happy and safe festive season and as always, thanks for being a member of the AskTOM community.

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)

We're not taking comments currently, so please try again later if you want to add a comment.

Comments

TABLE STATS

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

Thanks for your reply
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Performance

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