RE: VLDBA's: gather stats on a large table
- From: "Christian Antognini" <Christian.Antognini@xxxxxxxxxxxx>
- To: <mark.teehan@xxxxxxxx>
- Date: Tue, 12 Apr 2005 09:23:53 +0200
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
