Understanding the relationship between Teradata’s parsing engine (PE) and system performance requires knowledge of the PE’s tasks within the Teradata architecture.
Part of the Teradata Internals and Operations guide.
The Teradata parsing engine manages access rights, parsing the statement, optimizing the SQL, creating processing steps, and dispatching to the AMPs.
Knowledge of the specific dependencies between roles and access rights in a Teradata system is unnecessary to address our issue. Instead, it is important to note that individual object permissions are saved in the DBC.AccessRights table.
When creating a table, various permissions will be assigned to your user account (the one used for logging into the system).
CREATE TABLE <DATABASE>.< TABLE>(
PK INTEGER NOT NULL
) PRIMARY INDEX (PK);
To verify the statement above, we will utilize the view DBC.AllRights rather than DBC.AccessRights for ease of use. This view utilizes object names instead of the object identifiers internally utilized by Teradata, simplifying the process for us.
SELECT
UserName,DatabaseName,TableName,AccessRight,GrantorName,CreatorName
FROM
dbc.allrights
WHERE
DatabaseName = '' AND tablename = '';
| UserName | DatabaseName | TableName | AccessRight | GrantorName | CreatorName |
| <USER> | <DATABASE> | <TABLE> | ST | <USER> | <USER> |
| <USER> | <DATABASE> | <TABLE> | DG | <USER> | <USER> |
| <USER> | <DATABASE> | <TABLE> | RS | <USER> | <USER> |
| <USER> | <DATABASE> | <TABLE> | U | <USER> | <USER> |
| <USER> | <DATABASE> | <TABLE> | DP | <USER> | <USER> |
| <USER> | <DATABASE> | <TABLE> | RF | <USER> | <USER> |
| <USER> | <DATABASE> | <TABLE> | IX | <USER> | <USER> |
| <USER> | <DATABASE> | <TABLE> | R | <USER> | <USER> |
| <USER> | <DATABASE> | <TABLE> | I | <USER> | <USER> |
| <USER> | <DATABASE> | <TABLE> | D | <USER> | <USER> |
| <USER> | <DATABASE> | <TABLE> | DT | <USER> | <USER> |
| <USER> | <DATABASE> | <TABLE> | CG | <USER> | <USER> |
The result set above displays that the DBC.AccessRights table stores 12 permission records, including permissions for updating, retrieving, deleting, restoring, and dropping triggers, among others, following the creation of a table.
Get the complete Teradata tuning book, free
Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis
206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.
Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.
About the free bookThe number of permissions stored varies based on the Teradata release and potentially other parameters.
Grants are typically retrievable from the cache, but when the cache cannot satisfy the necessary permits, the parsing engine must reference the DBC.AccessRights table (refer to the DDL provided):
CREATE SET TABLE DBC.AccessRights
(
UserId BYTE(4) NOT NULL,
DatabaseId BYTE(4) NOT NULL,
...
) PRIMARY INDEX (UserId,DatabaseId);
Hashing is utilized in DBC tables to distribute table records amongst all AMPs. However, skewing may pose an issue, as is the case with DBC.AccessRights where the primary index is {UserId, DatabaseId}.
Your daily batch process may result in a vast number of tables being generated in the staging area by a single user, causing significant skewing in the DBC.AccessRights table.
Privilege checks can contribute to parsing time. Establish the actual bottleneck with measured workload evidence before attributing a system-wide slowdown to access-rights skew.
How can this problem be reduced in scope?
Review privileges before changing them
Creating a table does not make every explicit grant redundant. Ownership, creator privileges, roles and inherited access must be evaluated separately. UserName matching GrantorName is not evidence that a grant can safely be removed.
In an isolated test, revoking all rights on a table created in another database removed the creator’s SELECT access. Do not generate blanket REVOKE ALL statements or directly delete rows from DBC.AccessRights as a parsing optimization.
First measure parsing time and inspect the relevant access paths. If a privilege redesign is justified, have the DBA review each required permission and test the change with the application account. Preserve grants and dependencies when choosing between DELETE and DROP/CREATE.
Need help with query performance or platform costs?
Diagnose plans, skew, spool pressure and expensive workloads. Agree a baseline and acceptance criteria before making changes.
Get the complete Teradata tuning book, free
Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis
206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.
Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.
About the free book
Good post! our table is skewed 99%. I guess the real question is, why is Teradata inserting these redundant rows and why is such a bad PI in use? (in Teradata 15 they change it to just user_id and partition the dbid)
PRIMARY INDEX ( UserId )
PARTITION BY ( RANGE_N((ID2BIGINT(DatabaseId )) MOD 1073741824 BETWEEN 0 AND 1073741823 EACH 1 ),
RANGE_N(ID2BIGINT(TVMId ) BETWEEN 0 AND 4294967295. EACH 1 )ADD 2 );