Performance History Database – PHD

PHD is a full-function Performance History Database and can be seen as the “data warehouse of the data warehouse”, containing comprehensive and detailed data about the performance, transactions and activities that occur in and around the Teradata environment. PHD provides the user with data and insight of the following conditions:

System Perspective – allowing analysis and understanding of the warehouse platform and its components (CPU, disk activity, memory usage, interconnect network activity etc).

Application Perspective – (query volume, concurrency etc) to determine the impact of application activity on system resources.

User Perspective - (query intensity, response times, data accessed etc) to identify individual activity to identify performance problems, etc.

Space Perspective - to assess the amount of available storage space that is consumed by various categories of space usage (systems storage, user storage, temporary storage etc)

Workload Perspective – to evaluate the conflicting demands of competing groups of activities such as ETL, ad hoc, reporting, etc.

System & Data Access Perspective - to determine which users are referencing / updating which database objects and in what manner they do it.

Data Sources
PHD is being integrated with Ward’s Visual Edge performance analysis tool, to provide a comprehensive capability to Teradata users who wish to be able to look at the most complete view of their data warehouse. PHD is a collection of over 30 distinct tables with over 250 columns of information.

PHD’s integrated and documented data model contains all of the information necessary to support analysis and reporting for each of the perspectives mentioned above. Additionally, the database provides the foundation for quantifiably measuring performance tuning efforts and is the basis for comprehensive Capacity Planning. Data is drawn from a variety of sources including Teradata tables and OS-based sources;

• Teradata Resusage – system-wide resource consumption.
• DBC.Acctg – for user level data
• DBC.EventLog – access data
• DBC.DBQL – for query logging information
• DBC.DatabaseSpace for space utilisation data
• AMP Worker Task utilisation data
• Priority Scheduler Monitor data

PHD Maintenance

PHD includes a standard set of ETL processes as well as a regular maintenance / purge process that is implemented in over 100 macros and views. The ETL process resolves the complexity and inconsistencies across the data sources and prepares it for inclusion in the PHD database. During this preparation process, the data is transformed into a standardized and integrated set of tables in usable format according to predefined transformation rules.