Archives of the TeradataForum
Message Posted: Fri, 13 Jan 2012 @ 15:48:31 GMT
| Subj: || || Re: Generate collect statistics statement |
| From: || || Joe Gleason |
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.