Archives of the TeradataForum

Message Posted: Fri, 13 Jan 2012 @ 15:48:31 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

I have implemented a solution that is somewhat of a compromise. I am executing a series of stored procedures whose purpose is to check the perm space used by tables on a nightly basis. If the space usage changes by more than 10%, then stats are collected on the table. This is all table driven and a pretty slick little app that I received from JK a while back. If a table does not grow by more than 10% in a month's time, I will go ahead and collect anyway, usually on a Sunday night, when the system is not being utilized much. After implementing this, and analyzing the stats collection results, I realized my tables are not growing very rapidly based on the nature of our workloads. I may only have to collect stats on a dozen tables a week based on growth. Knowing this, I went ahead, and with a wide brush, collected stats on all columns in all tables. Of course this was very resource intensive the first time but has not been an issue since. I would rather have stats collected when the table is being ETL'd, but since that is out of my control, this is the next best thing. This also helps when BO reports are added or changed in production without any additional stats analysis or application. Not a great practice but this is the real world and it happens.

This may not be an acceptable solution for many of your systems out there, but it has worked very nicely for us. I believe the JK procedures are documented somewhere in the forum archives.

Joe

	<Prev	Next>		<<First	<Prev	Next>	Last>>