Archives of the TeradataForum

Message Posted: Thu, 17 Jul 2003 @ 15:34:13 GMT

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

I wouldn't try to collect statistics on any of the DBC tables until there's a lot more understanding about what it's supposed to accomplish.

Some tables are non-hashed tables. This means that the table's data is not distributed by hash code, but rather AMP local. Collecting statistics on these tables is completely meaningless - the only SQL use of these tables involves full-file scans. Some tables that are non- hashed include DBC.ACCTG, DBC.DATABASESPACE.

Tables like DBC.ACCLOGTBL, DBC.EVENTLOG and the ResUsage tables are history tables that record things that have already happened (rows being inserted into these tables will be PI inserts). If this V2R4 change has permitted the collection of statistics on these tables, then it might help report queries that you write - but having statistics won't do anything for things that are currently running or to be submitted.

To say that collecting statistics on these tables helped logons is questionable and I would like to see a lot more detail (it's more likely that other things were happening on the system and have been confused with better logon performance).

Tables like DBC.SESSIONTBL is too volatile for collected statistics to make any sense. At one point, you might have just a couple of sessions and 20 minutes later there could be thousands. At what point do you collect statistics?

Tables like DBC.DBASE, DBC.TVM, DBC.TVFIELDS, DBC.INDEXES, DBC.ACCESSRIGHTS and DBC.ACCLOGRULETBL might benefit from collected statistics, but I suspect that the parser/optimizer's use of the various system tables is largely fixed and it will always use the system tables in the same way - whether statistics exist or not.

In addition, these tables are small enough that they are probably always found in the cache. For the various ways in which they are used, there is probably little performance difference between the possible workplans.

Does anybody know why DR41612 was implemented or what it was supposed to accomplish? I don't have access to the document and was wondering if the ability to collect statistics on DBC might not actually be a regression.

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