RE: VLDBA's: gather stats on a large table

Hi Mark

>I have a 7bn row, weekly partitioned IOT that adds
>hundreds of millions of rows each week. A five
>percent stats job estimate takes 20 hrs every weekend.

5 percent is far too high. For one of my customers that also have a very = large table I setup the gathering with 0.1 percent. It works fine.

>Given that I only insert to the latest partition, all
>others are RO, and have no global indexes, how should
>I gather stats?=20

As usually it depends... If for the optimizer it's important to have = correct information at global level about some "max values" that are = stored in the last partition, you eventually have to refresh the global = stats frequently. Otherwise, what I do, is simply keeping the = partition-level stats up-to-date (i.e. I gather only the stats in the = last partition) and then, from time to time, gather the global stats as = well. Generally it makes no sense to refresh the global stats if only 1 = percent of the rows stored in the table has changed!

>As the app doesnt specify the partition in selects,=20
>I need to maintain global stats, which means I need
>to rescan all 7bn rows each time.

Does the application take advantage of partition pruning? If yes, even = if the SQL are not referencing the partitions directly, it's important = to have partition-level stats.

HTH
Chris

--
http://www.freelists.org/webpage/oracle-l