Archives of the TeradataForum

Message Posted: Mon, 06 Apr 2015 @ 16:03:57 GMT

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

I believe you are describing DBC.AccessRights. You can have contention problems with this with active systems around creating and dropping objects.

ETL processes frequently create tables and drop tables (work, error, log, etc.). With this you action, you are inserting (for create table) more than 20 rights per table. When the ETL process cleans-up after itself it will remove those access rights as the object is removed (dropped).

The DBC.AccessRights table is hashed distributed by primary index on UserID and DatabaseName. Consequently multiple concurrent ETL processes utilizing common work/error databases as well as the same UserID can create contention. I believe in a later version of Teradata (perhaps 15.00) the HASH is changing for this table to mitigate contention issues. Until that level is use, you may want to manage the contents of the DBC.AccessRights table

One action you can take in the mean time is to reduce the size of values in these databases by removing access at the object level that is already covered or resident at the database level (insert, update, delete, select, drop table, statistics, etc.) can be and are frequently granted at the database level. A query to build a revoke statement for the object level privilege where a the more global (database level) privilege exists would be helpful in trimming the fat out of DBC.AccessRights.

Also removing (create trigger, drop trigger, dump, restore, index, references) can be trimmed back also as most processes would not take advantage of those privileges at the object level.

Monitoring and managing orphaned objects can greatly help as well. If the tables are not ever accessed following creation (i.e. abandoned ETL load/error tables) they should be removed or migrated to another database not in a database with "warm or hot" create-type activity as it will minimize the hash block and potential contention.

Kyle Prescott
DBA Manager
Unum-GHDS
Chattanooga, TN

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