17.00 - 17.05 - ASE Impact on PE and AMP Performance - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Database Administration

Advanced SQL Engine
Teradata Database
Release Number
June 2020
English (United States)
Last Update
  • ASE has little impact on PE performance. The cost incurred for analyzing the account string amounts to only a few microseconds.
  • ASE may have impact on AMP performance.

    The AMP has the burden of additional DBC.AMPUsage logging. Depending on the number of users and the ASE options selected, the added burden may vary from very slight to enough to degrade performance. In general, the &D, &H, and &L options do not have major effects on performance.

    Be cautious where you use the &T option. It can generate an AMPUsage row for virtually every Teradata SQL request. The &T option can have a much greater effect on performance. Therefore, do not use the &T option:

    • In default account ID strings
    • In conjunction with tactical queries
    • With TPump

      The &T option should not be a problem for long-running DSS requests, but could be a performance issue if users are running numerous small requests. &T is site-dependent; it should generate no more than 10 to 20 AMPUsage rows per minute.

      Because ASE causes the system to write more entries to DBC.AMPUsage, you must manage the table more often.

For more information on account string expansion, see Logging Resource Usage Data with Account String Variables.

AMPUsage Logging with ASE Parameters

AMPUsage logging may have both performance and data storage impacts.

The following table summarizes potential impact.

ASE Parameter Performance Impact Data Capacity Impact
None Negligible 1 row per account per AMP
&D Negligible 1 row per account per day per AMP
&H Negligible 1 row per account per hour per AMP
&D&H Negligible 1 row per account per hour per day per AMP
&L Negligible 1 row per session pool
&I Negligible 1 row per SQL request
&T Potentially non-negligible 1 row per query per AMP