Download (direct link):
nice the running query or active session of another user • Performance Monitor, "Modify a user priority/account string" operation • PM/API SET SESSION ACCOUNT
Viewing Account Strings and Session Statistics
The DBC.AMPUsage view provides cumulative information about the use of each AMP for each user/database and account during every session. Each new name results in a new set of statistics; if the account is dynamically changed during processing, a separate row is generated for each account.
The underlying system table is DBC.Acctg. Updates to the table are made periodically on each affected processor, continually adding to what is already in the table until you reset the counters (see "Clearing DBC.Acctg Table with DBC.AMPUsage View" on page 4-15).
DBC.AMPUsage can provide aggregated information by AMP, username, and/or accountID. You can include Account String Expansion (ASE) variables in the account string to provide more granularity. This can increase the usefulness of DBC.AMPUsage data for capacity planning and performance tuning, and in charge-back and accounting software.
A few examples of possible types of charges include the following (for more details, see "AMPUsage" in the chapter titled "System Views: Usage and Examples" in Teradata RDBMS Data Dictionary):
Space occupied by the default database(s) this user employed during the session. If a default database is not defined for a user, the default is used as explained in "CREATE USER Default Values" on page 5-4.
Time consumed by the session under this account. If no account ID is specified at logon, the default is used as explained in "Finding the Default Account" on page 5-21. Also, the session may use several accounts during runtime.
Processing resources consumed by the session or sessions initiated under this account. You can design billing algorithms by individual or user profile account. Include ASE codes for performance tuning and capacity planning, so you can measure the amount of resources used for different workloads for different days and times. For details, see "Using Account String Expansion (ASE)" on page 5-27.
5 - 40
Teradata RDBMS Database AdministrationChapter 5: Setting Up Users, Profiles, Accounts and Accounting
System Accounting Functions
Using Account String Expansion (ASE)
You can increase the granularity of time-based reporting by incorporating ASE variables in account strings. For example, you can associate every session run by a particular user with the date and time of day, or with a logon timestamp.
Caution: It is important that you update DBC.Acctg on a regular basis to clear out values and reset the accumulators. Use Teradata Manager to first summarize and save historical data. (For more information and examples, see "Clearing DBC.Acctg Table with DBC.AMPUsage View" on page 4-15.)
ASE is an optional feature that acts on system variables associated with an account ID to collect statistics for a session. ASE uses DBC.AMPUsage as the recording mechanism, but by adding the capability of the system substitution variables, the amount of information collected can greatly increase.
At the finest granularity, ASE can be used to generate a summary row for each SQL request. You can direct ASE to generate, per AMP, a row for each user, each session, or per aggregation of the daily activity for a user. You can also generate individual rows and use Teradata Manager to provide the summary functions. Possible summaries, for instance, are one summary row per day per hour, by user and/or by account.
With ASE, you can define system variables in one or more account IDs when you create or modify a user or profile. These variables allow you to more precisely measure the execution of a particular query. ASE does not modify the | AMP usage statistics gathering process.
The measurement rate may be specified by date (&D), time (&T), hour (&H), or | a combination thereof. Information can be written to AMPUsage based on the time the user logged on (&L). It can be directed to generate a row for each user or each session, or for an aggregation of the user's daily activities. At the finest granularity, ASE can generate a summary row for every SQL request. The collection activity occurs on all AMPs involved in processing an SQL request.
Teradata resolves the variables at logon or at SQL statement execution time. ASE has a negligible effect on PE performance. The cost incurred for analyzing the account string requires only a few microseconds.
However, the AMP does have the burden of additional logging in DBC.AMPUsage. Depending on the number of users and the ASE options chosen, the added burden may vary from slight to enough to degrade overall performance.
For example, when &T is specified, ASE logs a row to DBC.AMPUsage for every AMP for every request. This should not be a problem for long running DSS requests, but could be a performance issue for numerous small requests.