Archives of the TeradataForum

Message Posted: Sun, 27 Oct 2002 @ 17:21:13 GMT

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

I can think of a couple of things related to your poor performance when querying DBC.ACCTG:

- It's a table that needs to be purged on a regular basis and unless you're doing so, it's probably gotten quite large (depending on if/how you're using Account String Expansion [ASE] - see the Database Administration manual for more information).

- The table DBC.ACCTG is a non-hashed table and although you can SELECT from it, the performance is less than stellar (if for no other reason, all user operations with a non-hashed table always involve a full table scan).

I've done a couple of performance monitoring/chargeback systems and I've always started by establishing the rules for using ASE and then how I'm going to do a roll-up of DBC.ACCTG into a summary table, which is then immediately followed by the purge of DBC.ACCTG. With my implementations, I summarize/purge DBC.ACCTG once every 24-hours. In one case, we investigated the option of purging twice a day because of the amount of detail being collected.

With ASE, you can get details about CPU and I/O consumption, on an AMP-by-AMP basis, for each statement submitted by a User (this level of detail depends on your ASE variables). In table DBC.ACCTG, the column CPU contains the number of CPU-seconds consumed by any given 'AMP' (in increments of 0.01 seconds). The column 'IO' contains the number of I/O requests attempted (meaning that the request could have been satisfied by either caching or by a physical I/O). In the chargeback systems that I have done, we applied charges for CPU-seconds consumed and allocated Perm Space, but ignored I/O.

Since DBC.ACCTG contains resource utilization on an AMP-by-AMP basis, you can also use it to get a feel for the distribution of the workload amongst the AMPs. With the exception of PI operations, the more skewed the workload, the greater the performance impact.

The best part of ASE is that:

- It's already part of the system, it's powerful and easy to use

- It has minimal impact on the system, as long as you keep table DBC.ACCTG cleaned-up.

I've seen several homegrown attempts to duplicate what ASE can do and all of them had a much greater impact on the system and none of them gave the clear-cut visibility into how the system was being used as I was able to extract from ASE.

FWIW: The system tables, such as DBC.ACCTG, don't have statistics on them nor is there a simple way of collecting them. In addition, statistics are meaningless when dealing with non-hashed tables.

If you're looking for more information than what can be found in DBC.ACCTG and willing to do some programming, then you should look at the Performance Monitor API (see the PM/API Reference manual).

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