Books
in black and white
Main menu
Share a book About us Home
Books
Biology Business Chemistry Computers Culture Economics Fiction Games Guide History Management Mathematical Medicine Mental Fitnes Physics Psychology Scince Sport Technics
Ads

Teradata RDBMS Database Administration - NCR

NCR Teradata RDBMS Database Administration - NCR , 2004. - 616 p.
Download (direct link): teradatadatabaseadmin2004.pdf
Previous << 1 .. 77 78 79 80 81 82 < 83 > 84 85 86 87 88 89 .. 218 >> Next


5 - 40 Teradata RDBMS Database Administration

Chapter 5: Setting Up Users, Profiles, Accounts and Accounting

System Accounting Functions

Example

The following query selects all account IDs with a RUSH priority code, and whether the name associated with an account is an individual user or a profile:

SELECT AccountName,Name,UserOrProfile FROM DBC.AccountInfo WHERE AccountName LIKE '$R%' ORDER BY AccountName ;

In this example, the view returns:

AccountName Name UserOrProfile

$R_AR1022 $R_P123 0 $R_P123 0 $R_P24 5 0 $R P3450

AcctsRecv DBC

SysAdmin

SysDBA

SystemFe

Profile

User

User

User

User

DBC.AMPUsage

The DBC.AMPUsage view provides information about the usage of each AMP for each user and account. It also tracks the activities of any console utilities. By user, account, or console utility session, DBC.AMPUsage stores information about:

CPU time consumed

Number of read/write (I/O) operations generated

Note: AMPUsage reports logical I/Os explicitly requested by the database software, even if the requested segment is in cache and no physical I/O is performed.

DBC.AMPUsage uses the DBC.Acctg table to provide aggregated information by username, accountID, and AMP. Updates to the table are made periodically during each AMP step on each processor affected by the step. (This means if there are long-running steps, AMPUsage numbers show large increases periodically, instead of continuous incremental additions.) The data is collected and continually added to what is already in the table until you reset the counters to zero (see "Example 2: Resetting DBC.Acctg Counters with DBC.AMPUsage" on page 5-38).

You can use the information provided by DBC.AMPUsage to:

Bill an account for system resource use.

Determine what resources were used, by user and account ID, after hours as well as during the day.

Summarize and archive the information and zero it out on a per shift, per day, or per week basis.

5 - 40

Teradata RDBMS Database Administration Chapter 5: Setting Up Users, Profiles, Accounts and Accounting

System Accounting Functions

Determine if one or more tables has skewed row distribution across AMPs

Determine which session caused reduced performance.

Derive capacity needs to plan for expansion.

DBC.AmpUsage does not record the activity of parsing the query, or of processing on a query basis.

You can use query logging to capture query text, step information, and elapsed processing time, and to differentiate queries submitted by SQL-generating products that do not provide a variety of user ids and account ids in the logon string. (For instructions and a description of the data capture options, see Chapter 14: "Tracking Processing Behavior with the Database Query Log

For a look at up-to-the-moment activity in near-real-time, you can use the Teradata Performance Monitor, as discussed in "PM/API Dynamic Data" on page D-6 and"Teradata Performance Monitor" on page D-8.

This SQL statement requests totals for CPU time and I/O for user DBA01. The totals are aggregates of all resources.

SELECT UserName (FORMAT 1X (16)')

,AccountName (FORMAT 1X (12)')

,SUM (CpuTime)

,SUM (DiskIO)

FROM DBC.AMPUsage

WHERE UserName = 'DBA01'

GROUP BY 1, 2

ORDER BY 3 DESC ;

For this example, AMPUsage returns the following rows:

UserName AccountName SUM (CpuTime) SUM (DiskIO)

(DBQL)".)

Example 1: Totalling CPU Time and I/O by User

DBA01

DBA01

DBA01

$M$P9210 $H$P9210 $R$P9210

6,336.76 4,387.14 1.28

505,636 303,733 166

5 - 40 Teradata RDBMS Database Administration

Chapter 5: Setting Up Users, Profiles, Accounts and Accounting

System Accounting Functions

Example 2: Resetting DBC.Acctg Counters with DBC.AMPUsage

You should use DBC.AMPUsage view to update or remove rows in the underlying DBC.Acctg table.

For example, to reset counters for ALL rows or selected rows:

UPDATE DBC.AMPUsage Set CPUTime = 0 ,DiskIO = 0 ,ALL ;

For detailed information on these and all the system views, see Teradata RDBMS Data Dictionary. For more information on how to use DBC.AMPUsage and other views to find problems and improve performance, see "Tools for Troubleshooting Teradata RDBMS" on page 12-2 and Teradata RDBMS Performance Optimization.

5 - 40

Teradata RDBMS Database Administration Chapter 5: Setting Up Users, Profiles, Accounts and Accounting

System Accounting Functions

System Accounting Functions

Teradata RDBMS) system accounting serves three important administrative functions:

Charge-back billing (for equitable cost allocation)

Capacity planning (to anticipate your needs)

Resource control (to identify performance anomalies)

For more information on how to identify and analyze session behavior and apply resource control, see Chapter 13: "Database Analysis" and Chapter 15: "Tools for Managing Resources".

Charge-back Billing

You may need to charge users for their use of Teradata RDBMS resources. Charge-back billing permits equitable cost allocation of system resources across all users.
Previous << 1 .. 77 78 79 80 81 82 < 83 > 84 85 86 87 88 89 .. 218 >> Next