Resource Usage Macros And Tables

Transcript

Teradata Database Resource Usage Macros and Tables Release 13.10 B035-1099-109A October 2011 The product or products described in this book are licensed products of Teradata Corporation or its affiliates. Teradata, Active Enterprise Intelligence, Applications Within, Aprimo, Aprimo Marketing Studio, Aster, BYNET, Claraview, DecisionCast, Gridscale, Managing the Business of Marketing, MyCommerce, Raising Intelligence, Smarter. Faster. Wins., SQL-MapReduce, Teradata Decision Experts, Teradata Labs Logo, Teradata Raising Intelligence Logo, Teradata Source Experts, WebAnalyst, and Xkoto are trademarks or registered trademarks of Teradata Corporation or its affiliates in the United States and other countries. Adaptec and SCSISelect are trademarks or registered trademarks of Adaptec, Inc. AMD Opteron and Opteron are trademarks of Advanced Micro Devices, Inc. EMC, PowerPath, SRDF, and Symmetrix are registered trademarks of EMC Corporation. GoldenGate is a trademark of Oracle. Hewlett-Packard and HP are registered trademarks of Hewlett-Packard Company. Intel, Pentium, and XEON are registered trademarks of Intel Corporation. IBM, CICS, RACF, Tivoli, and z/OS are registered trademarks of International Business Machines Corporation. Linux is a registered trademark of Linus Torvalds. LSI is a registered trademark of LSI Corporation. Microsoft, Active Directory, Windows, Windows NT, and Windows Server are registered trademarks of Microsoft Corporation in the United States and other countries. NetVault is a trademark or registered trademark of Quest Software, Inc. in the United States and/or other countries. Novell and SUSE are registered trademarks of Novell, Inc., in the United States and other countries. Oracle, Java, and Solaris are registered trademarks of Oracle and/or its affiliates. QLogic and SANbox are trademarks or registered trademarks of QLogic Corporation. SAS and SAS/C are trademarks or registered trademarks of SAS Institute Inc. SPARC is a registered trademark of SPARC International, Inc. Symantec, NetBackup, and VERITAS are trademarks or registered trademarks of Symantec Corporation or its affiliates in the United States and other countries. Unicode is a registered trademark of Unicode, Inc. in the United States and other countries. UNIX is a registered trademark of The Open Group in the United States and other countries. Other product and company names mentioned herein may be the trademarks of their respective owners. THE INFORMATION CONTAINED IN THIS DOCUMENT IS PROVIDED ON AN “AS-IS” BASIS, WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESS OR IMPLIED, INCLUDING THE IMPLIED WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE, OR NON-INFRINGEMENT. SOME JURISDICTIONS DO NOT ALLOW THE EXCLUSION OF IMPLIED WARRANTIES, SO THE ABOVE EXCLUSION MAY NOT APPLY TO YOU. IN NO EVENT WILL TERADATA CORPORATION BE LIABLE FOR ANY INDIRECT, DIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING LOST PROFITS OR LOST SAVINGS, EVEN IF EXPRESSLY ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. The information contained in this document may contain references or cross-references to features, functions, products, or services that are not announced or available in your country. Such references do not imply that Teradata Corporation intends to announce such features, functions, products, or services in your country. Please consult your local Teradata Corporation representative for those features, functions, products, or services available in your country. Information contained in this document may contain technical inaccuracies or typographical errors. Information may be changed or updated without notice. Teradata Corporation may also make improvements or changes in the products or services described in this information at any time without notice. To maintain the quality of our products and services, we would like your comments on the accuracy, clarity, organization, and value of this document. Please email: [email protected] Any comments or materials (collectively referred to as “Feedback”) sent to Teradata Corporation will be deemed non-confidential. Teradata Corporation will have no obligation of any kind with respect to Feedback and will be free to use, reproduce, disclose, exhibit, display, transform, create derivative works of, and distribute the Feedback and derivative works thereof without limitation on a royalty-free basis. Further, Teradata Corporation will be free to use any ideas, concepts, know-how, or techniques contained in such Feedback for any purpose whatsoever, including developing, manufacturing, or marketing products or services incorporating Feedback. Copyright © 2000 – 2011 by Teradata Corporation. All Rights Reserved. Preface Purpose This book describes, and provides procedures for, Teradata Database resource usage data and macros. Audience This book is intended for system programmers, system administrators, and other database specialists responsible for administering or managing Teradata Database. Supported Software Releases and Operating Systems This book supports Teradata® Database 13.10. Teradata Database 13.10 supports: • Microsoft Windows Server 2003 64-bit • SUSE Linux Enterprise Server 10 Teradata Database client applications can support other operating systems. Prerequisites You should be familiar with basic computer technology, Teradata Database, and the system console environment. It will be helpful to review or reference the following books: • Introduction to Teradata • Workload Management API: PM/API and Open API • Performance Management Resource Usage Macros and Tables 3 Preface Changes to This Book Changes to This Book Release Description Teradata Database 13.10 Removed the note and some text from the “Channel Traffic Columns” section in the “ResUsageSpma Table” chapter. October 2011 Teradata Database 13.10 August 2010 4 • Replaced instances of: • "VSS" with "TVS." • "collect period" and "collection period" with "gather period." • "32-bit" with "64-bit" to some of the extent driver I/O columns in "ResUsageSvpr Table." • "Windows and Linux" with "ALL." • "Teradata Manager" and "Teradata Dynamic Workload Manager" or "Teradata DWM" with appropriate references to Teradata Viewpoint. • Removed the following: • References to "MP-RAS," "xctl," "collection rate," and "Performance Monitor (PMON)." • The "Using the Active Row Filter Mode" topic from Chapter 2: “Planning Your Resource Usage Data.” • Appendix E: System Activity Reporter. • Combined Node Logging Rate and Vproc Logging Rate into a single Logging Rate in Chapter 3: “Resource Usage Procedures.” • Updated the following information: • The “About the "Invalid Platform" Column” section. • The SET RESOURCE syntax in Chapter 3: “Resource Usage Procedures.” • The description of the CollectIntervals column. • The descriptions of MailBoxDepth, MSGWORKTHREE and MSGWORKELEVEN in Chapter 7: “ResUsageSawt Table.”. • ResSpsView, ResSvprView, and ResGeneralInfoView views in Chapter 14: “Resource Usage Views.”. • The sample outputs for ResMemByGroup, ResMemMgmtOneNode, and ResMemMgmtByNode in Chapter 15: “Resource Usage Macros.”. • SpareCount columns 00-05 and 07-08 in Chapter 11: “ResUsageSps Table.”. • The NumSets description in Chapter 11: “ResUsageSps Table.”. • SpareCount columns 00-07 and SpareTmon columns 01-03 in Chapter 13: “ResUsageSvpr Table.”. • Changed the number of Performance Group (PGs) to 250. • Added views: ResSawtView and ResSpsView in Chapter 14: “Resource Usage Views.”. Resource Usage Macros and Tables Preface Changes to This Book Release Description Teradata Database 13.10 • Marked FilePRowNDel, FileSRowNDel, FilePRowNUpd, FileSRowNUpd, FileAPtRowNUpd, FileAPtRowNDel, HostWriteFails, and HostReadFails valid on all platforms. • Marked ProcWorkType[i]Sum and ProcWorkType[i]Max invalid on all platforms. August 2010 (continued) Teradata Database 13.0 April 2009 Resource Usage Macros and Tables • The Vproc5 and VprocType5 fields are now valid on all platforms. • ResUsageSpdsk table is available for use and reports detailed usage of pdisks. • Updated descriptions of summary mode to clarify what is being reported. • Updated descriptions for file system fields in the ResUsageSpma table. • Fields that were once marked as invalid on all platforms that are now available for use on some or all platforms have been updated. • QWaitTimeMax, QLengthMax, and ServiceTimeMax have been changed to gather type "track" (not "count") which means they no longer need to be divided by the log intervals to obtain the average. • WorkTypeMax field in both the ResUsageSps and ResUsageAwt table now have the gather type "track." • Removed references to xschmon utility. It is no longer supported. • Added new fields to ResUsageSps and ResUsageSpdsk. • Added new flow control fields and Worktype fields to ResUsageSawt. • Added new fields to ResUsageSvpr to support Teradata Dynamic Workload Management software and DBQL. • Added new PDE, FSYS, and Teradata Virtual Storage fields to ResUsageSps. • Added new MI fields to ResUsageSawt and ResUsageSvpr. • Updated or added logical device fields such as input and output traffic columns as well as response time columns to ResUsageSldv. • Changed NodeType to CHAR(8) to accommodate new node types with longer names. • The field PGId has a new data type of SMALLINT. Also, summary mode of the ResUsageSps table uses the triplet of the PGId, VprType, and PPId fields. • Removed instances of gather type "countshft" because the only difference between countshft and count is how data collection is implemented. • Clarified CPU normalization in SPMA table • Marked all the Extent I/O Driver columns in the SVPR table as invalid. 5 Preface Additional Information Release Description Teradata Database 13.0 • The SpareTmon00 field in every table tracks the Capacity on Demand (COD) value. • ResUsageSps.WorkTypeInUse values now have a gather type "count." The value must always be divided by the CollectIntervals value. • ResUsageSps.WorkTypeMax values reports a maximum of sampled values and not the actual maximum of all inuse AWTs. • Updated the definition of the ResGeneralInfo view and added the new ResSvprView view definition to Chapter 14. April 2009 (continued) Additional Information URL Description www.info.teradata.com/ Use the Teradata Information Products Publishing Library site to: • View or download a manual: 1 Under Online Publications, select General Search. 2 Enter your search criteria and click Search. • Download a documentation CD-ROM: 1 Under Online Publications, select General Search. 2 In the Title or Keyword field, enter CD-ROM, and click Search. • Order printed manuals: Under Print & CD Publications, select How to Order. www.teradata.com The Teradata home page provides links to numerous sources of information about Teradata. Links include: • Executive reports, case studies of customer experiences with Teradata, and thought leadership • Technical information, solutions, and expert advice • Press releases, mentions and media resources 6 www.teradata.com/t/TEN/ Teradata Customer Education designs, develops and delivers education that builds skills and capabilities for our customers, enabling them to maximize their Teradata investment. www.teradataatyourservice.com Use Teradata @ Your Service to access Orange Books, technical alerts, and knowledge repositories, view and join forums, and download software patches. developer.teradata.com/ Teradata Developer Exchange provides articles on using Teradata products, technical discussion forums, and code downloads. Resource Usage Macros and Tables Preface Additional Information To maintain the quality of our products and services, we would like your comments on the accuracy, clarity, organization, and value of this document. Please e-mail: [email protected]. Resource Usage Macros and Tables 7 Preface Additional Information 8 Resource Usage Macros and Tables Table of Contents Preface . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .3 Purpose . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .3 Audience . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .3 Supported Software Releases and Operating Systems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .3 Prerequisites . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .3 Changes to This Book . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .4 Additional Information . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .6 Chapter 1: Introduction. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15 Benefits of Using Resource Usage Data. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15 Accessing Resource Usage Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15 Setting Up and Maintaining Resource Usage Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15 Overview of Resource Usage Data. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16 Gathering Resource Usage Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16 Data Gathering . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17 Data Reporting . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17 Using Resource Usage Macros . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18 Application Programming Interfaces and Resource Usage Data . . . . . . . . . . . . . . . . . . . . . . . 18 Chapter 2: Planning Your Resource Usage Data . . . . . . . . . . . . . . . 19 Enabling Resource Usage Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19 Tables Based on Needed Reports . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19 Resource Usage Tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20 Setting the Logging Rate. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21 Logging Rate . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21 Determining the Logging Value . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22 Using Summary Mode . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22 Using Active Row Filter Mode. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23 Optimizing Resource Usage Logging . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23 The Cost of Logging. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23 Resource Usage Macros and Tables 9 Table of Contents Logging Cost Contributors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .24 Operational Methods. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .24 Chapter 3: Resource Usage Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . .27 Enabling RSS Logging. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .27 Using ctl . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .27 Using Database Window . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .28 General Macro Input Format. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .29 Parameter Use for One-Node, Multiple-Node, All-Node, and Group Macros . . . . . . . . .31 Using One-Node Macros . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .31 Using ByGroup Macros . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .31 Saving and Analyzing Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .32 Executing Macros . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .32 Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .32 EXECUTE MACRO Syntax . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .32 Using ENABLE and DISABLE LOGON Commands. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .36 Purging Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .36 Chapter 4: Resource Usage Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .37 Physical Table Naming Conventions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .37 Relational Primary Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .38 Inserting Rows into Resource Usage Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .38 Occasional Event Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .38 Types of Resource Usage Table Columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .38 About the Invalid Platform Column. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .41 About the Type of Data Column . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .42 Column Names Ending In Sum . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .42 Summary Mode in Resource Usage Tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .42 Chapter 5: ResUsageScpu Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .45 Summary Mode. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .48 Spare Columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .48 10 Resource Usage Macros and Tables Table of Contents Chapter 6: ResUsageSpma Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51 Spare Columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 71 Chapter 7: ResUsageSawt Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 73 Summary Mode. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 77 Spare Columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 78 Chapter 8: ResUsageShst Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79 Summary Mode. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 83 Spare Columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 84 Chapter 9: ResUsageSldv Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 85 Summary Mode. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 88 Spare Columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 89 Chapter 10: ResUsageSpdsk Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 91 Summary Mode. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 97 Spare Columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 97 Chapter 11: ResUsageSps Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 99 Spare Columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 116 Chapter 12: ResUsageSvdsk Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 119 Summary Mode. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 124 Spare Columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 124 Resource Usage Macros and Tables 11 Table of Contents Chapter 13: ResUsageSvpr Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .125 Summary Mode. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .142 Spare Columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .143 Chapter 14: Resource Usage Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .147 ResGeneralInfoView . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .148 ResCPUUsageByAMPView . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .152 ResCPUUsageByPEView . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .154 ResSawtView . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .155 ResShstGroupView . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .157 ResSldvGroupView . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .158 ResSpsView . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .159 ResSvprView . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .166 Chapter 15: Resource Usage Macros . . . . . . . . . . . . . . . . . . . . . . . . . . . . .175 Macro Output Format . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .175 ResAWT Macros . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .177 ResAWT Sample Output . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .180 ResAWTByAMP Sample Output . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .180 ResAWTByNode Sample Output . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .181 ResCPUByAMP Macros . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .182 ResCPUByAMP Sample Output. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .183 ResCPUByAMPOneNode Sample Output . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .184 ResAmpCpuByGroup Sample Output. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .184 Normalized Viewing of CPU Usage by AMP . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .184 ResCPUByPE Macros . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .186 ResCPUByPE Sample Output. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .187 ResCPUByPEOneNode Sample Output . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .188 ResPeCpuByGroup Sample Output . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .188 Normalized Viewing of CPU Usage by PE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .188 ResCPUByNode Macros. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .189 ResCPUByNode Sample Output . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .190 ResCPUOneNode Sample Output . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .190 ResCPUByGroup Sample Output . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .191 ResHostByLink Macros . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .192 12 Resource Usage Macros and Tables Table of Contents ResHostByLink Sample Output . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 194 ResHostOneNode Sample Output . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 195 ResHostByGroup Sample Output . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 195 ResLdvByNode Macros . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ResLdvByNode Sample Output . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ResLdvOneNode Sample Output . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ResLdvByGroup Sample Output . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 196 197 197 198 ResPdskByNode Macros: Pdisk Device Traffic . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ResPdskByNode Sample Output . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ResPdskOneNode Sample Output . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ResPdskByGroup Sample Output . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 199 200 201 201 ResMemMgmtByNode Macros . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ResMemMgmtByNode Sample Output. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ResMemMgmtOneNode Sample Output . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ResMemByGroup Sample Output . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 202 204 204 204 ResNetByNode Macros. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ResNetByNode Sample Output . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ResNetOneNode Sample Output . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ResNetByGroup Sample Output . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 205 206 206 207 ResNode Macros . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ResNode Sample Output . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ResOneNode Sample Output . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ResNodeByNode Sample Output . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ResNodeByGroup Sample Output . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 208 211 211 212 212 ResPs Macros. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ResPsByNode Macro Sample Output . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ResPsByGroup Macro Sample Output . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ResPsByNodeWDJoin Macro Sample Output . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 213 215 216 216 ResVdskByNode Macros . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ResVdskByNode Sample Output . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ResVdskOneNode Sample Output . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ResVdskByGroup Sample Output . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 218 219 219 219 Appendix A: How to Read Syntax Diagrams . . . . . . . . . . . . . . . . . . . 221 Syntax Diagram Conventions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 221 Resource Usage Macros and Tables 13 Table of Contents Appendix B: ResUsageIpma Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .227 Spare Columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .236 Appendix C: ResUsageIvpr Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .237 Summary Mode. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .248 Spare Columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .249 Appendix D: Partition Assignments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .251 Table Conventions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .252 Partition Assignment Listing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .252 Glossary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .255 Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .257 14 Resource Usage Macros and Tables CHAPTER 1 Introduction Resource usage, or ResUsage, is the collection and reporting of statistical information about the operation of your operating system and Teradata Database. Benefits of Using Resource Usage Data Resource usage data is useful for the following purposes: • Measuring system performance • Measuring component performance • Assisting with on-site job scheduling • Identifying potential performance impacts • Planning installation, upgrade, and migration • Analyzing performance degradation and improvement • Identifying problems such as bottlenecks, parallel inefficiencies, down components, and congestion Accessing Resource Usage Data Resource usage data is stored in system tables and views in the DBC database. Macros installed with Teradata Database generate reports that display the data. As with other database data, you can access resource usage data using SQL if you have the proper privileges. You can also write your own queries or macros on resource usage data. Setting Up and Maintaining Resource Usage Data You need to decide what resource usage data you want to collect and the level of detail you want it to cover. This manual documents the resource usage data and settings for a variety of installation configurations and environments in Chapter 2: “Planning Your Resource Usage Data.” To implement the settings you decide on, see Chapter 3: “Resource Usage Procedures.” The only maintenance required is to purge old data regularly. See “Purging Data” on page 36. Resource Usage Macros and Tables 15 Chapter 1: Introduction Overview of Resource Usage Data Related Topics For additional information on performance analysis and system tuning, see the following books: • Workload Management API: PM/API and Open API • Performance Management Overview of Resource Usage Data The following table lists topics covered by resource usage data. Resource usage data covers … Which includes … BYNET traffic on a node point-to-point messaging, broadcast messaging, and merge activities. client-to-server traffic data for each communication link. CPU utilization overhead, user service, and time of session execution. data tracking positions of sessions in locking queues. storage device traffic the number of reads/writes and amount of data transferred as seen from the storage driver. pdisk device traffic pdisk I/O, cylinder allocation, and migration statistics. vdisk device traffic all the cylinders allocated by an AMP (which can come from any pdisks in the clique). Priority Scheduler information data by Performance Group (PG) from the Priority Scheduler and the ability to report resource usage data by Teradata Active System Management (ASM) workload definitions (WDs). AMP Worker Task (AWT) information AWT statistics. memory management activity memory allocation. summary information all data collected for a node or vproc. Gathering Resource Usage Data Resource usage data gathering is a two-phase process as follows: 16 • Data gathering • Data reporting Resource Usage Macros and Tables Chapter 1: Introduction Gathering Resource Usage Data Data Gathering During the data gathering phase the RSS gathers information from the operating system, Parallel Database Extensions (PDE), and Teradata Database. Data gathering periods may not be uniformly spaced and are based on the Teradata Dynamic Workload Management, Collect and Logging rates. The number of gather periods that occurred in any specific reporting period is indicated by the CollectInterval data field. Data Reporting The reporting periods occur at the end of one or more gather intervals. Each of the Teradata Dynamic Workload Management, Collect and Logging rates are independent. For the reporting period the respective reporting buffer is updated at the end of the respective reporting period and made accessible via the rssretrieve interface. The resource usage data is written to the database using the data from the Log buffer. Data Collection Macros and Routines Collect Buffer Gather Buffer Log Buffer Summary Log Teradata Dynamic Workload Management Buffer ResUsage Write Queue ResUsage Tables ResUsage Reports 1099A001 Resource Usage Macros and Tables 17 Chapter 1: Introduction Using Resource Usage Macros Using Resource Usage Macros Resource usage macros produce reports from data collected in the resource usage tables. You can use the reports to analyze key operational statistics and evaluate the performance of your system. Like other macros, resource usage macros consist of one or more Teradata SQL statements stored in Teradata Database and executed by a single EXECUTE statement. In addition to the name of the macro, the EXECUTE statement for resource usage macros can include parameters to specify the following: • A specific single-node • A group of nodes • Starting and ending dates and times • Starting and ending nodes of a range of nodes Refer to Chapter 3: “Resource Usage Procedures” for more information on the resource usage macros, and SQL Quick Reference for details about how to use the EXECUTE statement. Application Programming Interfaces and Resource Usage Data The resource usage data are not used by just the resource usage macros. Resource usage data can also be used by the System Performance Monitor and Production Control Application Programming Interfaces (PMPC APIs). For more information on these APIs, see Workload Management API: PM/API and Open API. 18 Resource Usage Macros and Tables CHAPTER 2 Planning Your Resource Usage Data This chapter describes how to: • Enable resource usage tables • Set the logging rate • Use Summary Mode and Active Row Filter Mode • Optimize resource usage logging Enabling Resource Usage Tables The default resource usage settings provide a good starting point for system monitoring.The default results in the ResUsageSpma (SPMA) table being logged every 10 minutes (600 seconds). The ResUsageSpma table provides a high level summary of how the system is operating and contains summarized or key elements from most of the other tables. If you want to record detailed statistics covered by any of the resource usage tables, then you should enable them for logging, along with specifying the largest logging period that will meet your needs. You should not log data that you do not have a planned need for since this does incur additional database system overhead and uses up additional database space. Naturally, the more tables you enable for logging and the shorter the logging period used, the more overhead the system will use. Tables Based on Needed Reports If you plan on using the report macros provided in Chapter 15: “Resource Usage Macros,” then you need to enable the associated table. The following table lists more information. Resource Usage Macros and Tables 19 Chapter 2: Planning Your Resource Usage Data Enabling Resource Usage Tables For... See... instructions on setting resource usage tables “Enabling RSS Logging” on page 27. instructions on using macros “General Macro Input Format” on page 29 and “Executing Macros” on page 32. descriptions and examples of the macros Chapter 15: “Resource Usage Macros.” Resource Usage Tables The following table describes the tables and provides guidance about which ones to enable. Table Name Covers When You Should Enable ResUsageScpu Statistics on the CPUs within the nodes. When the performance analysis suggests that the overall performance is limited or to check if a program is spinning in an infinite loop on an individual processor. For example, saturation of a particular CPU on each node or on a particular node while others are idle could indicate a task always uses that CPU. Also, you should enable when the system is first brought online to verify the following: • That all CPUs are functioning on all nodes • There is a good load balance among the CPUs ResUsageSpma System-wide node information provides a summary of overall system utilization incorporating the essential information from most of the other tables. To provide an overall history of the system operation. Use the columns in ResUsageSpma to view BYNET utilization. Note: The BYNET can transmit and receive at the same time, resulting in 100% transmitting and 100% receiving values simultaneously. Another method of determining BYNET utilization and traffic is to use the blmstat tool. ResUsageIpma System-wide node information, intended primarily for Teradata engineers. Generally, this table is not used at customer sites. ResUsageSawt Data specific to the AWTs. When you want to monitor the utilization of the AWT and determine if work is backing up because the AWTs are all being used. ResUsageShst Statistics on the host channels and LANs that communicate with Teradata Database. To determine details about the traffic over the IBM Host channels to determine if there is a bottleneck. 20 Resource Usage Macros and Tables Chapter 2: Planning Your Resource Usage Data Setting the Logging Rate Table Name Covers When You Should Enable ResUsageSldv System-wide, logical device statistics collected from the storage driver. To observe the balance of disk usage. The storage device statistics are often difficult to interpret with disk arrays attached due to multi-path access to disks. Note: Use the ResUsageSvdsk table first to observe general system disk utilization unless specifically debugging at a low level. ResUsageSpdsk Statistics collected from the pdisk device. To obtain detailed usage information about pdisks. ResUsageSps Data by PG ID from the Priority Scheduler. When you need to track utilization by the query WD level. ResUsageSvpr Data specific to each virtual processor and its file system. To view details about the resources being used by each vproc on the system. This table is useful for looking for hot AMPS or PEs that may be CPU bound or throttled on other resources. ResUsageIvpr System-wide virtual processor information, intended primarily for Teradata engineers. Generally, this table is not used at customer sites. Setting the Logging Rate The default for the Node Logging Rate is 600. When you have decided what rate to set, see Chapter 3: “Resource Usage Procedures” for details on how to set the logging rate. Logging Rate Logging rate controls the frequency (number of seconds) at which resource usage data is logged to the resource usage tables. Resource usage logging means the writing of resource data as rows to one or more of the resource usage database tables. The tables are named DBC.ResUsagexxxx, where xxxx is the name of the resource usage table (for example, Spma, Ipma, and so forth) as listed in “Resource Usage Tables” on page 20. The shorter the logging period, the more frequently data is logged, and the more disk space is used. When the system is so busy that the resource usage table logging gets backed up, RSS will automatically double the logging period which effectively summarizes the data by providing values for a time period twice that provided by the previous logging period. If you see the resource usage logging rates change without user intervention, this means that the database is busy. When no longer busy, the system resumes logging as before. Note: Events in the event logs related to this doubling of the logging period do not represent fatal errors but are informational to indicate that the automatic operations of the RSS are attempting to maintain data logging. Resource Usage Macros and Tables 21 Chapter 2: Planning Your Resource Usage Data Using Summary Mode Determining the Logging Value The system imposes the following rule on the logging rate: Intervals must evenly divide into 3600 (the number of seconds in an hour). The following table shows the valid logging rate. • The white area of the table shows rates recommended only for short-term use for debugging a specific issue. • The highlighted area of the table shows rates recommended for production processing. 1 2 3 4 5 6 8 9 10 12 15 16 18 20 24 25 30 36 40 45 48 50 60 72 75 80 90 100 120 144 150 180 200 225 240 300 360 400 450 600 720 900 1200 1800 3600 A practical log interval minimum during production processing is 60 seconds. Intermediate log intervals, such as 120 seconds or 300 seconds can also be used. The default rate is 600 seconds. If the system becomes very busy, it will automatically double the logging period. This effectively summarizes the data by providing values for a time period twice that of the previous logging period. The system automatically returns to logging back to the rate you set when it is no longer busy. Rates and enabled tables may be changed at any time and the changes take effect immediately. Using Summary Mode You can use Summary Mode to reduce the system overhead from logging tables that produce multiple rows per logging period. Summary Mode helps reduce overhead by combining data from multiple rows into one or more summary rows based on specific criteria for each table. For example, if you want to log information provided in the ResUsageSvpr table but do not need data for each individual vproc, then use summary mode to produce one row per vproc type instead of one row per vproc. The ResUsageSpma table, in comparison, provides node level summary of key fields from most of the other ResUsage tables. When more details are required than the ResUsageSpma table provides then the next level of information is provided by using summary mode logging for the table of interest. This helps minimize the cost of the data logging. You can select summary mode for each table individually. See the description for each table for details on how summary mode affects that particular table. 22 Resource Usage Macros and Tables Chapter 2: Planning Your Resource Usage Data Using Active Row Filter Mode For example, for the ResUsageSvpr table in summary mode, all the individual vproc rows of the same vproc type are combined into a single row. Since the data values are added together, you need to divide the summary row data value by the number of rows that made up the summary mode row to get the average per vproc. For example, divide the AMP summary row data value by the number of AMPs on that node to determine the average value per AMP. A similar computation needs to be done to derive the average value per PE from the summary row data value. (To determine the number of AMP, PEs, and all other vproc types on your system, you can use the ResUsageSpma table or use the Vproc Manager utility.) Fields that represent a maximum statistic are not summed together. Instead the maximum value from the rows is used. For example, the ResUsageSvpr table MsgWorkQLenMax field in the summary mode row for the AMPs will contain the maximum value from all the AMP rows that would have been logged in non-summary mode. The fields that represent a minimum statistic are summarized by storing the minimum value from all the constituent rows. Summary mode has either no effect on the values of the Housekeeping Columns or it is specifically detailed in the description of each affected field. To enable Summary Mode, see “Enabling RSS Logging” on page 27. For more information on Summary Mode, see “Summary Mode in Resource Usage Tables” on page 42. Using Active Row Filter Mode Active Row Filter Mode reduces the overhead of logging for some of the resource usage table by limiting the data rows that are logged to the database. When active row filter is enabled, it may appear that rows are missing when looking at the query results. This is because the index values of the inactive rows varies over time so that a row with one index may be logged one period but not in another. To determine if rows are not being logged to the database, you should look in the event logs for messages indicating that rows have been lost. Note: Active Row Filtering should not be disabled for the ResUsageSps table. Optimizing Resource Usage Logging The Cost of Logging Logging resource usage data to database tables incurs costs: • Writing to the database adds to the system I/O load. On a heavily loaded system, this could affect the production workload throughput. • The rows written to the database take up space. If this space is never reclaimed, it will eventually grow to consume all available space in user DBC. Resource Usage Macros and Tables 23 Chapter 2: Planning Your Resource Usage Data Optimizing Resource Usage Logging • In an extremely loaded system, it is possible that the RSS can fall behind in writing data to the database. Although it caches such data and will eventually catch up if given a chance, the RSS will be forced to start discarding rows if the system load persists and its cache capacity has been exceeded. Logging Cost Contributors Logging costs are difficult to quantify. They depend on a number of interrelated factors: • How busy is the system • Which resource usage tables are enabled • What resource usage logging rates are in effect • The system configuration (vproc, CPU, host driver, logical devices or device controllers) Operational Methods Use the following methods to optimize performance and reduce the cost of resource usage logging on your system: 1 Use Summary Mode to reduce the number of rows inserted into the resource usage tables if Summary Mode data provides sufficient information for your needs. Note: If resource usage logging terminates due to a lack of table space: a Delete rows from the appropriate table or make more space for it in USER DBC. b Restart resource usage logging by entering the appropriate SET RESOURCE command. 2 For tables with a large number of rows (for example, ResUsageSps), use Active Row Filter Mode to limit the number of rows written to the database each logging period and to minimize the amount of system resources used. 3 Avoid unnecessarily using or exhausting available disk space by doing the following: • Never enable logging on tables that you do not intend to use. For example, logging only to the ResUsageSpma table provides a lot of useful information with a minimal operational load on the system. • Use the largest rates that provide enough detail information for your purposes. Generally, you should use a logging rate no smaller than 60. The default rate is 600. These values can be adjusted any time, regardless of whether the database system is busy. New values take effect as soon as the adjustment command is issued. (For example, with ctl, when you issue the WRITE command.) 4 24 Purge old data from the ResUsage tables periodically. Resource Usage Macros and Tables Chapter 2: Planning Your Resource Usage Data Optimizing Resource Usage Logging Related Topics For instructions on... See... enabling resource usage tables, setting the logging rates, and summarizing or filtering rows “Enabling RSS Logging” on page 27. purging old data from resource usage tables “Purging Data” on page 36. Resource Usage Macros and Tables 25 Chapter 2: Planning Your Resource Usage Data Optimizing Resource Usage Logging 26 Resource Usage Macros and Tables CHAPTER 3 Resource Usage Procedures This chapter describes how to: • Enable RSS logging • Execute different types of macros • Enable logons • Purge old data Enabling RSS Logging By using one of the following interfaces you can enable tables, set the logging rate, and optionally summarize or filter rows. If you are running... You can enable logging by running... For instructions, see... Windows or Linux the ctl utility from the Teradata Command Prompt. “Using ctl” on page 27. Windows or Linux Database Window (DBW). “Using Database Window” on page 28. Before you set the ResUsage tables, determine which tables and controlling rates apply to the resource usage macros you want to run. For more information, see the following topics: • “Enabling Resource Usage Tables” on page 19. • “Setting the Logging Rate” on page 21. Using ctl The Control GDO Editor utility (ctl) is used to set various Teradata Database configuration settings. The RSS-related settings are presented on the RSS screen. For detailed information on starting ctl and modifying the settings, see ctl in Utilities. Resource Usage Macros and Tables 27 Chapter 3: Resource Usage Procedures Enabling RSS Logging Using Database Window Use the database commands below to enable resource usage tables and set the logging rate from DBW on Windows or Linux. For instructions on starting DBW, see "Database Window (xdbw)" in Utilities. To enable RSS logging from DBW 1 Open the Supvr window. 2 Set the Node Logging Rate using the database command below. number LOGGING SET RESOURCE NODE LOG 1099C002 where number is the number of seconds. Note: A rate of zero disables the logging function. 3 Specify the table you want to enable logging to using the database command below. SET LOGTABLE tablename ON ALL OFF FE0CA030 After the table is enabled for logging, you can log rows in Summary Mode. For more information, see “Using Summary Mode” on page 22. Note: To log rows in Summary Mode, you must enable the table specified in both the RSS Table Logging Enable group and in the RSS Summary Mode Enable group. 4 (Optional) Enable Summary Mode on the table specified using the command below. SET SUMLOGTABLE tablename ON OFF 1095A010 Example The following example shows you how to enable table logging and set the Logging rate using the database commands in DBW. Suppose you want to enable the ResUsageShst table and set the logging rate for 10 minutes (600 seconds). You would enter the following: set logtable shst on set resource node log 600 28 Resource Usage Macros and Tables Chapter 3: Resource Usage Procedures General Macro Input Format Related Topics For more information on... See... ctl Utilities. DBW "Database Window (xdbw)" in Utilities. General Macro Input Format As shown in the table below, there are four kinds of macros: • Multiple-node • One-node • All-node • ByGroup For any given line in the following table, the macros on that line report the same statistics for either multiple nodes, one node, all nodes, or group nodes as indicated. Resource Usage Macros and Tables 29 30 Description Multinode Macro AWTs in use by node ResAWTByAMP ResAWTByNode CPU usage by AMP Vprocs ResCPUByAMP ResCPUByAMPOneNode ResAmpCpuByGroup CPU usage by PE Vprocs ResCPUByPE ResCPUByPEOneNode ResPeCpuByGroup CPU usage by nodes ResCPUByNode ResCPUOneNode ResCpuByGroup Host statistics One-Node Macro All-Node Macro ByGroup Macro ResAWT ResHostOneNode ResHostByLink ResHostByGroup Ldv disk statistics ResLdvByNode ResLdvOneNode ResLdvByGroup Memory management ResMemMgmtByNode ResMemMgmtOneNode ResMemByGroup General network statistics ResNetByNode ResNetOneNode ResNetByGroup General node-level statistics ResNodeByNode ResOneNode Priority Scheduler and Teradata ASM Workload statistics ResPsByNode pdisk level I/O statistics ResPdskByNode ResPdskOneNode ResPdskByGroup AMP level I/O statistics ResVdskByNode ResVdskOneNode ResVdskByGroup ResNode ResNodeByGroup ResPsByGroup Resource Usage Macros and Tables Chapter 3: Resource Usage Procedures General Macro Input Format Parameter Use for One-Node, Multiple-Node, All-Node, and Group Macros The following table explains parameter use for one-node, multiple-node, all-node, and group macros. Macro Type Number of Parameters Node Parameters Used Multiple node Six (except ResHostByLink) FromNode, ToNode One node Five Node All node Four None; this macro reports system-wide statistics. Group Four None; this macro reports statistics for all nodes in the group. For instructions on using these macros, see “Executing Macros” on page 32. Using One-Node Macros One-node macro versions are primarily used on single-node systems. Alternatively, you can use the corresponding multiple-node macro to report on just one node by supplying equal FromNode and ToNode parameters. One-node versions are recommended, however, because they eliminate redundant report columns on a single-node system. Examples of redundant columns are the NodeId column and columns that focus on cross-node load balancing. OneNode macros have the same general input format as the other macros. The only differences are that the single-node version of each macro has both of the following: • OneNode qualifier in the macro name. • A single node specification, instead of the FromNode and ToNode parameters to specify a range of nodes. The default is ‘001-01’. Using ByGroup Macros ByGroup macro versions are used on systems with co-existing nodes. In Teradata Database, co-existing nodes are nodes of different model types in the same configurations. Because of the differences, the nodes may become bottlenecks in the throughput of the system as a whole. Therefore, ByGroup macros were developed to provide the system user with a summary of the performance data based on node groupings. Note: The Database Administrator must identify the groupings of nodes when the system is first configured. ByGroup macros are similar to the other macros. The only difference is that they use the GroupId column of the views to report system usage for a specific set of nodes grouped by a GroupId. The input format of the ByGroup macros is the same as the other macros except ByGroup appears as the qualifier in the macro name. Resource Usage Macros and Tables 31 Chapter 3: Resource Usage Procedures Executing Macros Saving and Analyzing Data If you expect an ongoing need to retain and analyze data from different Teradata Database releases, ask your System Administrator to retain two sets of view and macro Data Definition Language (DDL) files in separate places. Rename the views and macros so that you can use either. You could, for example, use ResNodeRxx, where xx represents the Teradata Database release number, as the name of the resource usage macro and use it when you want to analyze the data from that release. Executing Macros Function Macro execution is illustrated in the following diagram. For details about each macro and its resulting report, see Chapter 15: “Resource Usage Macros.” EXECUTE MACRO Syntax The execution of each resource usage macro has the following form. For information on interpreting the syntax diagrams, see Appendix A: “How to Read Syntax Diagrams.” MacroNameMultiNode EXECUTE , ( FromDate EXEC MacroNameAllNode , ( FromDate MacroNameOneNode , ( FromDate FromTime ToTime B , C , D ToDate , , , A , ToDate , ( A ToDate FromDate MacroNameByGroup , ToDate FromNode ); ToNode , B FromTime ToTime , C FromTime , , D FromTime Node ToTime , ToTime GX02B001 where: 32 Resource Usage Macros and Tables Chapter 3: Resource Usage Procedures Executing Macros Syntax element Description MacroNameMultiNode Name of a multinode resource usage macro: • • • • • • MacroNameAllNode ResAwtByNode ResCPUByAMP ResCPUByPE ResCPUByNode ResLdvByNode ResMemMgmtByNode • • • • • ResNetByNode ResNodeByNode ResPdskByNode ResPsByNode ResVdskByNode Name of an all-node resource usage macro: • ResNode • ResHostByLink The ResHostByLink and ResNode macros do not use the FromNode and ToNode parameters. MacroNameByGroup Name of a ByGroup resource usage macro: • • • • • • FromDate ResAmpCpuByGroup ResCPUByGroup ResHostByGroup ResLdvByGroup ResMemByGroup ResNetByGroup • • • • • ResNodeByGroup ResPeCpuByGroup ResPdskByGroup ResPsByGroup ResVdskByGroup Start date to report resource usage data. The date may be entered either as a character string (for example, character format for May 31, 2007 would appear as '2007-05-31') or as a numeric value (for the same date in numeric format, 1070531). The character string is the recommended format. The default is the current system date. See "String Date Validations" in SQL Data Manipulation Language for more detailed information on using numeric dates with macros. Note: The character string date format has been changed from yymmdd to 'yyyy-mm-dd' to accommodate dates in the 21st century. ToDate End date to report resource usage data. See the FromDate syntax element column for a further explanation of date formats. The character string is the recommended format. FromTime Start time to report resource usage data. The format is hhmmss. The default is 000000. ToTime End time to report resource usage data. The format is hhmmss. The default is 999999. Resource Usage Macros and Tables 33 Chapter 3: Resource Usage Procedures Executing Macros Syntax element Description FromNode Starting range of nodes to report resource usage data. The format is 'nnn-nn'. A hyphen must be included in the fourth character position. The default is '000-00'. Note: To identify the node ID numbers for your system, type get config in the DBW Supervisor Window (Supvr). ToNode Ending range of nodes to report resource usage data. The format is 'nnnnn'. A hyphen must be included in the fourth character position. The default is '999-99'. Note: To identify the node ID numbers for your system, type get config in the DBW Supvr window. Node Single-node ID to report resource usage data. The format is 'nnn-nn', and hyphen must be included in the forth character position. For example, 1-01 should be typed out as '001-01'. The default is '001-01'. Example 1: Executing the ResCPUByAMP Macro The following statement executes the ResCPUByAMP macro, producing a report for the period beginning 8:00 a.m. on December 25, 2006 and ending 12:00 midnight, on December 31, 2006. It includes data for nodes 123-02 through 125-04. EXECUTE ResCPUByAmp('2006-12-25','2006-12-31', 080000, 240000, '123-02','125-04'); where: Statement Element Description ResCPUByAMP Name of the resource usage macro '2006-12-25' Starting date of December 25, 2006 '2006-12-31' Ending date of December 31, 2006 080000 Starting time of 8:00 a.m. 240000 Ending time of 12:00 midnight '123-02' Starting node of a range of nodes '125-04' Ending node of a range of nodes See SQL Data Types and Literals for information on using numeric values for dates. 34 Resource Usage Macros and Tables Chapter 3: Resource Usage Procedures Executing Macros Example 2: Executing the ResCPUByAMPOneNode Macro The following statement executes the OneNode version of the ResCPUByAMP macro shown in Example 1. It uses the same starting and ending dates and times (using character string format), except the report is for a single-node, node 123-02. EXECUTE ResCpuByAmpOneNode ('2006-12-25','2006-12-31',080000, 240000,'123-02'); where: Statement Element Description ResCPUByAMPOneNode Name of the resource usage macro '2006-12-25' Starting date of December 25, 2006 '2006-12-31' Ending date of December 31, 2006 080000 Starting time of 8:00 a.m. 240000 Ending time of 12:00 midnight '123-02' Node See SQL Data Types and Literals for information on using numeric values for dates. Example 3: Executing the ResAMPCpuByGroup Macro The following statement executes the ByGroup version of the ResCPUByAmp macro shown in Example 1. It uses the same starting and ending dates and times (using character string format), except the report is for a node grouping. EXECUTE ResAMPCpuByGroup ('2006-12-25','2006-12-31',080000, 240000); where: Statement Element Description ResCPUByAMPByGroup Name of the resource usage macro '2006-12-25' Starting date of December 25, 2006 '2006-12-31' Ending date of December 31, 2006 080000 Starting time of 8:00 a.m. 240000 Ending time of 12:00 midnight See SQL Data Types and Literals for information on using numeric values for dates. Resource Usage Macros and Tables 35 Chapter 3: Resource Usage Procedures Using ENABLE and DISABLE LOGON Commands Using ENABLE and DISABLE LOGON Commands The DISABLE LOGONS command prevents new sessions from logging on. When logons are disabled, resource usage data stops logging to the tables even if there are still active sessions logged on. (DISABLE ALL LOGONS prevents all users, including user DBC, from logging on and also stops logging to the tables.) To enable logons from: • Database Window, run ENABLE LOGONS or ENABLE ALL LOGONS. • Teradata command prompt, use the Start With Logons field of the Screen Debug menu of ctl. See "Control GDO Editor (ctl)" in Utilities. For more information on enabling and disabling logons, see "Changing Logon States and Restarting the System" in Database Administration. Purging Data The RSS does not automatically delete data from the resource usage tables. You need to purge data you no longer need on a regular basis. You can directly remove old resource usage data by submitting SQL statements. For example, use the following SQL statement to remove data more than five days old from the ResUsageSpma table: DELETE FROM ResUsageSpma WHERE TheDate < CURRENT_DATE - 7; For more information about the DELETE syntax, see "SQL Data Manipulation Language Statement Syntax" in SQL Data Manipulation Language. 36 Resource Usage Macros and Tables CHAPTER 4 Resource Usage Tables This chapter describes: • How to name the physical table and insert rows into resource usage tables • Types of resource usage table columns and data • Summary Mode in resource usage tables Physical Table Naming Conventions Each physical table name follows this general naming convention: ResUsage Information_type Table_name where: Element Is one of the following... Information_type Code Description S System-wide information I Internal Teradata Database information Code Description pma Node information vpr vproc information cpu CPU-specific information ldv Logical device statistics pdsk pdisk device statistics vdsk vdisk device statistics awt AWT statistics sps WD resolution statistics hst Channel and LAN host information Table_name Resource Usage Macros and Tables 37 Chapter 4: Resource Usage Tables Relational Primary Index Relational Primary Index All resource usage tables have the same nonunique primary index: • The nonunique primary index consists of TheDate, TheTime, and NodeID columns. • The primary index is nonunique because of duplicate rows that will appear with the same timestamp during daylight savings time. Rows that have duplicate timestamps can be distinguished by the GmtTime column. • Because the primary index is nonunique, all resource usage tables are created as MULTISET tables. This prevents the system from checking for duplicate rows. For more information on MULTISET tables, see "CREATE TABLE (Table Kind Clause)" in SQL Data Definition Language or "Duplicate Rows in Tables" in SQL Fundamentals. Inserting Rows into Resource Usage Tables For information on how rows will be inserted into these tables based on the current resource usage control settings, see Chapter 2: “Planning Your Resource Usage Data.” For information on the number of rows inserted in a resource usage table for each applicable log period, refer to “Using Summary Mode” on page 22. Occasional Event Data Occasional event data is considered outside the scope of resource usage and is, therefore, logged in the ERRORLOG and the DBCINFO tables rather than in the resource usage tables. Types of Resource Usage Table Columns This manual describes what each of the resource usage table columns report (that is, what each DBC.ResUsageXxxx.ColumnName reports) in a table format. Note: The actual table definitions are obtainable by executing the SHOW TABLE statement. See SQL Data Definition Language for more information about SHOW TABLE. All columns described in the following chapters and appendixes are type FLOAT unless otherwise specified in the description of that column. All nonexistent values are stored as NULL. For each resource usage table column, this manual describes the: 38 • Column Name • Type of Data Resource Usage Macros and Tables Chapter 4: Resource Usage Tables Types of Resource Usage Table Columns • Description • Data Type • Invalid Platform The columns are grouped into either housekeeping columns or statistics columns. Statistic columns are further grouped by category and subcategory as shown below. Column Name Type of Data Description Data Type Invalid Platform HOUSEKEEPING OR STATISTICS COLUMNS CATEGORYS Subcategory Each table has: • Housekeeping columns which contain statistics on timestamp, current logging characteristics, gather elements and its general characteristics. • Statistics columns which can be further categorized into subcategories. Categories and subcategories may vary from table to table. The following table shows the types of statistics subdivided into their respective subcategories. Category Subcategories Description File System • Cylinder Management • Cylinder Management Overhead Events • Data Block Prefetches • Data Segment Lock Requests • Segments Acquired • Segments Released • Synchronized Full File Scans • Write Ahead Logging (WAL) Some of the file system columns can be viewed as a subset of memory columns by expanding on the operations performed on disk memory segments. Operations counted are logical memory and physical disk reads and writes (including aging) and locking control activities. Other columns identify the purpose of operations being performed on disk segments such as cylinder migration or data updates; or identify the requests being made by database software on the file system. The WAL columns identify the log-based file system recovery scheme in which modifications to permanent data are written to a log file, the WAL log. General Concurrency Control Database Locks Identification of concurrency control activities is provided and subdivided into control done for user level processing, system overhead processing, and database locks. It does not include control specific to disk, memory or net concurrency control, which are included in the disk, memory or net columns. Resource Usage Macros and Tables 39 Chapter 4: Resource Usage Tables Types of Resource Usage Table Columns Category Subcategories Description Host Controller (SHST) • • • • • Channel Traffic Channel Management Controller Overhead User Commands User Command Arrival and Departure These columns identify traffic on the host-to-node channels and LANs. Some also give overhead and management information on the host channel and LAN. Memory • • • • • • • Memory Allocations Memory Availability Management Memory Pages Resident Memory Resident Paging Swapping Task Context Segment Usage Memory related events, subdivided into memory types, are collected for memory allocation and deallocation, logical memory and physical disk reads and writes (including paging and swapping), access, deaccess and memory control. Memory management columns are also provided to identify events leading up to paging, swapping and aging activities. Finally, a detailed snapshot of the memory is provided by tracking the current states per memory types. Logical Device • • • • • Concurrent Operations Input and Output Traffic Outstanding Requests Response Time Seek Statistics These columns identify individual logical device activities for external storage components connected through the buses. • • • • • • • • • Broadcast Net Traffic Group Coordination Merge Services Net Controller Status and Miscellaneous Management Net Circuit Management Network Transport Per-Bynet Network Transport Data Point-to-point Net Traffic Work Mailbox Queue Traffic over the BYNET is identified through the number and direction of messages, subdivided into the type of transmission, as well as physical utilization of the BYNET. Logical messages and direction are identified through subdivisions of the message class. Controller overhead, channel utilization, and Teradata net contention are identified as well. • • • • • • • • ChnSignal Status Tracking CPU Utilization Cylinder Read Process Allocation Process Block Counts Process Pending Snapshot Process Pending Wait Time Scheduled CPU Switching These columns provide a CPU-level snapshot of work started, with current characteristics and states. Expanded detail is provided for work started but waiting on resources. This helps identify the ability or inability of the system to effectively utilize resources. Time allotments are tracked by monitoring the time spent waiting for resources or processing code. These columns also track the number of times processing was switched to another process for multitasking purposes or to perform interrupt services. Net Process Scheduling 40 The storage device statistics are calculated only on what can be derived from statistics collected by the operating system, since the disk array controllers do not provide us with any useful data for resource usage. Resource Usage Macros and Tables Chapter 4: Resource Usage Tables About the Invalid Platform Column Category Subcategories Description User Commands • User command • User command Arrival and Departure These columns describe the types of commands given to Teradata Database by the user and the progress of those commands. Secondary Cache Misses None. These columns identify the secondary cache miss rate. Spare None. These columns are for future release or internal manipulation by Teradata developers. Teradata ASM • • • • AMP Worker Task In use and Max Array Data Priority Scheduler Worktype Descriptions These columns collect and report statistics about the AWTs and Priority Scheduler. The columns specific to the ResUsageSawt table also report the number of AWTs currently in use and the maximum number of AWTs for the current vproc on the node. Teradata Virtual Storage (VS) • • • • Allocation I/O Migration Node Agent These columns identify individual pdisk and vdisk device activities. Note: Teradata VS is available for purchase separately from Teradata Database. For information about these columns, see Teradata Virtual Storage. About the Invalid Platform Column The tables in this book that describe the resource usage tables contain an Invalid Platform column. If your platform appears in that column for a field, then resource usage data for that particular field is either not collected by the system or is not valid and should not be used. The following table explains the contents of the Invalid Platform column. In the Invalid Platform column … Means … ALL do not use on any platform. The column is either obsolete or not valid on any of the platforms. Linux column is only valid on Windows. It is not valid on Linux. Windows column is only valid on Linux. It is not valid on Microsoft Windows. column is valid on all platforms. When the Invalid Platform column is blank, the column being described is valid on all platforms. Resource Usage Macros and Tables 41 Chapter 4: Resource Usage Tables About the Type of Data Column About the Type of Data Column There are four possible types of data reported in the Type of Data column: • Count - Count fields tallies the number of times an event occurred, such as the number of disk reads or writes during a period of time. • Max - Max fields have a Max suffix in the field name. An example of a Max field in the ResUsageSvdsk table is the ReadRespMax field. This field reports the maximum value for the logging period. • Min - Min fields have a Min suffix in the field name. An example of a Min field in the ResUsageSvpr table is the ReadResponseHotMin field. This field reports the minimum value during each logging period. • Track - Track fields gauge the current value of a countable item such as a queue length during a period of time. The track field reports the value at the end of the logging period. Column Names Ending In Sum Column values ending in Sum, which are the Count Type of Data, are useful for calculating the average value for a gather period. Each sum column accumulates the values measured by the column at the end of every gather period. Divide the resulting logged value by the value CollectIntervals to get the average value. The CollectIntervals value is the number of gather periods per reporting period. Summary Mode in Resource Usage Tables Summary mode combines data from the multiple data rows normally generated into one or more rows. When multiple rows are condensed into a single row, the data is combined using the rules in the following table. 42 Resource Usage Macros and Tables Chapter 4: Resource Usage Tables Summary Mode in Resource Usage Tables For the following Type of Data… Summary fields are combined by… Count summing the values from all the contributing rows. Count fields are also added together when there are multiple gather periods in the reporting period. Depending upon the usage of the field, the value may or may not need to be adjusted by the number of rows that were combined by summary mode or the number of data sampling periods as indicated by the CollectIntervals column. For example, the ResUsageSvpr.FlowCtlCnt field provides the total number of times that the system entered flow control state from a non-flow control state. • In normal mode, the values are reported per AMP and no division by CollectIntervals is necessary since the total over the entire reporting period is desired. • In summary mode, the value needs to be divided by the number of AMPs if the user wishes to determine the average number per AMP rather than the total. On the other hand, for the WorkTypeInuse00 field of the SAWT or SPS table, the value reported is the sum of the current number of AWTs in use from each CollectInterval. In this case, the field should always be divided by the number of CollectIntervals, which will provide the sampled average number of AWTs in use over the reporting period. Note: To obtain the average number of AWTs in use during the reporting period per AMP, divide the summary mode reported value by the CollectIntervals value as well as the number of AMPs. Max taking the maximum value from all the contributing rows. In summary mode, the reported value for a Max field such as ResUsageSpdsk.ConcurrentWriteMax is the maximum value from all the rows that are combined into a single summary row. Min taking the minimum value from all the contributing rows. In summary mode, the reported value for a Min field such as ResUsageSvpr.ReadResponseHotMin is the minimum value from all the rows that are combined into a single summary row. Track summing the values from all the contributing rows. In Summary mode, the Track values from each row to be combined are summed together. For example, ResUsageSvpr.FlowControlled is a track field so that in summary mode, all the AMP vproc rows are combined into a single row and the FlowControlled field will report the summed value from each of the AMP vproc rows. Note: The Track values are not combined across multiple gathering intervals (as represented by the CollectIntervals column). For the ResUsageSvpr.FlowControlled field, this means that if there were 10 gather periods (the CollectIntervals column equals 10) in the reporting period, then the value reported will be the FlowControlled state at the end of the last gather period. This is the same as the value at the end of the reporting period. If summary mode is enabled, the values from each of the non-summary mode rows are added together to produce the summary mode row value. The following table describes Summary Mode for the resource usage tables. Summary Mode is applicable to all tables except ResUsageSpma and ResUsageIpma. If the information for a row of a table is in Summary Mode, the SummaryFlag value is set to ‘S’. If the row is being logged normally, the SummaryFlag value is set to ‘N’. Resource Usage Macros and Tables 43 Chapter 4: Resource Usage Tables Summary Mode in Resource Usage Tables The Table… contains ResUsage data… and the following information when Summary Mode is active… ResUsageIpma for available system-wide, node information Summary Mode not applicable to this table. ResUsageSpma for available system-wide, node information Summary Mode not applicable to this table. ResUsageScpu specific to the CPUs within the nodes. one row is written to the database for each node in the system, summarizing the CPUs on that node, for each log interval. For details, see Chapter 5: “ResUsageScpu Table.” ResUsageSawt specific to the AWTs. one row is written to the database for each node in the system, summarizing all AWTs per node, for each log interval. For details, see Chapter 7: “ResUsageSawt Table.” ResUsageShst specific to the host channels and LANs communicating with Teradata Database. one row is written to the database for each type of host (network or channel-connected) on each node in the system, summarizing the hosts of that type on that node, for each log interval. For details, see Chapter 8: “ResUsageShst Table.” ResUsageSldv specific to each logical storage device. two rows written to the database: one summarizing the system logical devices and one summarizing the Teradata Database logical devices. For details, see Chapter 9: “ResUsageSldv Table.” ResUsageSpdsk specific to the pdisk device. one row for each pdisk type per node inserted each logging period. For example, for large configurations, the ResUsageSpdsk table may contain thousands of rows logged during each logging period, enabling Summary Mode minimizes the amount of system resources used. For details, see Chapter 10: “ResUsageSpdsk Table.” ResUsageSps one row written to the database for each triplet of PGid, VprType, and PPid fields for each log interval. Summary Mode not applicable to this table. For details, see Chapter 11: “ResUsageSps Table.” ResUsageSvdsk specific to the vdisk device. one row written to the database for each node in the system, summarizing all AMP vdisk data in each node, for each log interval. For details, see Chapter 12: “ResUsageSvdsk Table.” ResUsageSvpr specific to each virtual processor and its file system. one row written to the database for each type of vproc on each node in the system, summarizing the vprocs of that type on that node, for each log interval. For details, see Chapter 13: “ResUsageSvpr Table.” 44 Resource Usage Macros and Tables CHAPTER 5 ResUsageScpu Table This resource usage table contains resource usage information specific to the CPUs within the nodes. Table ResUsageScpu includes resource usage data for available system-wide, CPU information. Note: This table is created as a MULTISET table. For more information see “Relational Primary Index” on page 38. The Invalid Platform column is a little counterintuitive. If your platform appears in that column, then resource usage data for that particular column is either not collected or not valid and should not be used. (For more information, see “About the Invalid Platform Column” on page 41.) The following table describes the ResUsageScpu table columns. Column Name Type of Data Description Data Type Invalid Platform HOUSEKEEPING COLUMNS RELATIONAL PRIMARY INDEX COLUMNS These columns taken together form the nonunique primary index. TheDate n/a Date of the log entry. DATE TheTime n/a Nominal time of the log entry. FLOAT Note: Under conditions of heavy system load, entries may be logged late (typically, by no more than one or two seconds), but this field will still contain the time value when the entry should have been logged. See the Secs and NominalSecs columns. NodeId n/a Identifies the Node. The Node ID is formatted as CCC-MM, where CCC denotes the three-digit cabinet number and MM denotes the two-digit chassis number of the node. For example, a node in chassis 9 of cabinet 3 has a node ID of ‘003-09’. INTEGER Note: Symmetric Multi-Processing (SMP) nodes have a chassis and cabinet number of 1. For example, the node ID of an SMP node is ‘001-01’. Resource Usage Macros and Tables 45 Chapter 5: ResUsageScpu Table Column Name Type of Data Description Data Type Invalid Platform MISCELLANEOUS HOUSEKEEPING COLUMNS GmtTime n/a Greenwich Mean Time is not affected by the Daylight Savings Time adjustments that occur twice a year. FLOAT NodeType n/a Type of node, representing the per node system family type. For example, 5600C or 5555H. CHAR(8) CPUId n/a Identifies the CPU within this node. The values are 0 through NCPUs-1. SMALLINT In Summary Mode, the value is zero. Secs n/a Actual number of seconds in the log period represented by this row. Normally the same as NominalSecs, but can be different in three cases: SMALLINT • The first interval after a log rate change • A sample logged late because of load on the system • System clock adjustments affect reported Secs Useful for normalizing the count statistics contained in this row, for example, to a per-second measurement. CentiSecs n/a Number of centiseconds in the logging period. This field is useful when performing data calculations with small elapsed times where the difference between centisecond-based data and whole seconds results in a percentage error. INTEGER NominalSecs n/a A specified or nominal number of seconds in the logging period. SMALLINT SummaryFlag n/a Identifies the summarization status of this row. Possible values are ‘N’ if the row is a non-summary row, and ‘S if the row is a summary row. CHAR Active count Controls whether or not the rows will be logged to the ResUsage tables if Active Row Filter Mode is enabled. FLOAT If Active is set to: • a non-zero value, then the row contains modified data columns. • a zero value, then none of the data columns in the row have been updated during the logging period. For example, if Active Row Filter Mode is enabled, then the rows that have a zero Active field value will not be logged to the ResUsage tables. 46 Resource Usage Macros and Tables Chapter 5: ResUsageScpu Table Column Name Type of Data Description Data Type CollectIntervals n/a The number of gather periods per reporting period. SMALLINT Invalid Platform In the Collect Buffer and Log Buffer, the value is the number of Gather operations that have been performed during the period. This number can vary from one period to the next. STATISTICS COLUMNS PROCESS SCHEDULING COLUMNS CPU Utilization Columns Count all CPU activities, including activities performed for virtual processors, subdivided into the following columns: 1 CPUIdle - Idle time 2 CPUIoWait - Idle and waiting for I/O completion 3 CPUUServ - User service 4 CPUUExec - User execution These statistics are aggregates representing all CPUs on the node. CPU utilization by user code is further subdivided by the vproc tables. Note: • CPU idle time = CPUIdle + CPUIoWait • CPU busy time = CPUUServ + CPUUExec Theoretically, the values of these four columns, for any given interval, account for total CPU time on the node. That is, they should total to 100 * Secs * number of CPUs on the node, since each CPU is always in exactly one of these four states. In practice, there is occasionally a very small plus or minus difference from this theoretical total. CPUIdle count Time in centiseconds the CPU is idle and not waiting for I/O. FLOAT CPUIoWait count Time in centiseconds CPU is waiting for I/O completion. FLOAT Windows Note: This represents another variety of Idle, since the CPU is only recorded as being in this state if there are no processes eligible for execution. This is because if there were any such process, the CPU would be immediately dispatched for that process. CPUUServ count Time in centiseconds CPU is busy executing user service code, that is, privileged work performing system services on behalf of user execution processes which do not have root access. FLOAT CPUUExec count Time in centiseconds CPU is busy executing user execution code, that is, time spent in a user state on behalf of a process. FLOAT Resource Usage Macros and Tables 47 Chapter 5: ResUsageScpu Table Summary Mode Type of Data Column Name Description Data Type Invalid Platform Scheduled CPU Switching Columns Identify the number of times the CPU was switched by the scheduler from doing one type of work to another type of work. CPUProcSwitches count Number of times the scheduler switched the CPUs currently active process to a new process. FLOAT ALL CPUProcSameSwitches count Number of CPUProcSwitches where a process replaced itself, that is, the new process was the same as the old process. FLOAT ALL Summary Mode When Summary Mode is active for tables in this group, one row is written to the database for each node, summarizing all CPUs per node, for each log interval. You can determine if a row is in Summary Mode by checking the SummaryFlag column for that row. IF the SummaryFlag column value is… THEN the data for that row is being logged… ‘S’ in Summary Mode. ‘N’ normally. Spare Columns The ResUsageScpu table has six spare columns (one of which is being used) as shown in the table below. Column Name Type of Data Description SpareCount[00-01] count Spare counted statistic. SpareTrack[00-01] track Spare tracked statistic. SpareTmon00 n/a The SpareTmon00 field contains the Capacity on Demand (COD) value. The value represents the COD value in one tenths of a percent, so a displayed value of 500 represents a COD value of 50.0%. Note: This value is valid only on SUSE Linux Enterprise Server 10 systems and is a single value for the entire system. 48 Resource Usage Macros and Tables Chapter 5: ResUsageScpu Table Spare Columns Column Name Type of Data Description SpareTmon01 count Spare tmonitored statistic. The spare column fields expand to values 00 - 01, so that column names would be SpareCount00 or SpareTrack01, and so on. Resource Usage Macros and Tables 49 Chapter 5: ResUsageScpu Table Spare Columns 50 Resource Usage Macros and Tables CHAPTER 6 ResUsageSpma Table The ResUsageSpma table includes resource usage data for available system-wide, node information. The ResUsageSpma table is similar to the ResUsageIpma table. For information on this table, see Appendix B: “ResUsageIpma Table.” Note: Summary Mode is not applicable to this table. This table is created as a MULTISET table. For more information see “Relational Primary Index” on page 38. The Invalid Platform column is somewhat counterintuitive. If your platform appears in that column, then resource usage data for that particular column is either not collected or not valid and should not be used. The following table describes the ResUsageSpma table columns. However, always use the views provided in Chapter 14: “Resource Usage Views” to access the data rather than accessing the ResUsage table directly. Column Name Type of Data Description Data Type Invalid Platform HOUSEKEEPING COLUMNS RELATIONAL PRIMARY INDEX COLUMNS These columns taken together form the nonunique primary index. TheDate n/a Date of the log entry. DATE TheTime n/a Nominal time of the log entry. FLOAT Note: Under conditions of heavy system load, entries may be logged late (typically, by no more than one or two seconds), but this field will still contain the time when the entry should have been logged. See the Secs and NominalSecs columns. NodeId n/a Identifies the Node. The Node ID is formatted as CCC-MM, where CCC denotes the three-digit cabinet number and MM denotes the two-digit chassis number of the node. For example, a node in chassis 9 of cabinet 3 has a node ID of ‘003-09’. INTEGER Note: SMP nodes have a chassis and cabinet number of 1. For example, the node ID of an SMP node is ‘001-01’. Resource Usage Macros and Tables 51 Chapter 6: ResUsageSpma Table Column Name Type of Data Description Data Type Invalid Platform MISCELLANEOUS HOUSEKEEPING COLUMNS These columns provide a generalized picture of the vprocs running on this node, shown as Type n virtual processors where n = 1 to 7. Under the current implementation, only Type 1 (AMP), Type 2 (PE), Type 3 (GTW), Type 4 (RSG), and Type 5 (VSS) vprocs exist; vproc types 6 through 7 are not currently used. GmtTime n/a Greenwich Mean Time is not affected by the Daylight Savings Time adjustments that occur twice a year. FLOAT NodeType n/a Type of node, representing the per node system family type. For example, 5600C or 5555H. CHAR(8) NCPUs n/a Number of CPUs on this node. SMALLINT This field is useful for normalizing the CPU utilization field values for the number of CPUs on the node. This is especially important in coexistence systems where the number of CPUs can vary across system nodes. Vproc1 n/a Current count of type 1 (AMP) virtual processors running on the node. SMALLINT VprocType1 n/a Type of virtual processor for Vproc1. When the vproc is present on the node, the value is AMP. CHAR(4) Vproc2 n/a Current count of type 2 (PE) virtual processors running under the node. SMALLINT VprocType2 n/a Type of virtual processor for Vproc2. When the vproc is present on the node, the value is PE. CHAR(4) Vproc3 n/a Current count of type 3 (GTW) virtual processors running under the node. SMALLINT VprocType3 n/a Type of virtual processor for Vproc3. When the vproc is present on the node, the value is GTW. CHAR(4) Vproc4 n/a Current count of type 4 (RSG) virtual processors running under the node. SMALLINT VprocType4 n/a Type of virtual processor for Vproc4. When the vproc is present on the node, the value is RSG. CHAR(4) Vproc5 n/a Current count of type 5 (VSS) virtual processors running under the node. SMALLINT VprocType5 n/a Type of virtual processor for Vproc5. When the vproc is present on the node, the value is VSS. CHAR(4) Vproc6 n/a Current count of type 6 virtual processors running under the node. SMALLINT This column reports zeros and " " (blanks). VprocType6 52 n/a Type of virtual processor for Vproc6. CHAR(4) ALL Resource Usage Macros and Tables Chapter 6: ResUsageSpma Table Column Name Type of Data Vproc7 n/a Description Data Type Current count of type 7 virtual processors running under the node. SMALLINT Invalid Platform This column reports zeros and " " (blanks). VprocType7 n/a Type of virtual processor for Vproc7. CHAR(4) MemSize n/a Amount of memory on this node in megabytes. Useful for performing memory usage calculations. INTEGER NodeNormFactor n/a A per node normalization factor that is used to normalize the reported CPU values of the ResUsageSpma table. INTEGER ALL This value is scaled by a factor of 100. For example, if the actual factor is 5.25, then the value of the NodeNormFactor will be 525. Note: This value is constant for the node and scaled up by a factor of 100 to preserve the two digit decimal resolution while using an integer field. Secs n/a Actual number of seconds in the log period represented by this row. Normally the same as NominalSecs, but can be different in three cases: SMALLINT • The first interval after a log rate change • A sample logged late because of load on the system • System clock adjustments affect reported Secs Useful for normalizing the count statistics contained in this row, for example, to a persecond measurement. CentiSecs n/a Actual number of centiseconds in the logging period. INTEGER This field is useful when performing data calculations with small elapsed times where the difference between centisecond-based data and whole seconds results in a percentage error. NominalSecs n/a A specified or nominal number of seconds in the logging period. SMALLINT CollectIntervals n/a The number of gather periods per reporting period. SMALLINT In the Collect Buffer and Log Buffer, the value is the number of Gather operations that have been performed during the period. This number can vary from one period to the next. Resource Usage Macros and Tables 53 Chapter 6: ResUsageSpma Table Column Name Type of Data Active NetSamples Description Data Type n/a Gets set to a non-zero value whenever one of the other data columns in the row is set. FLOAT count Sample count for sampled statistics for a Bynet. FLOAT Invalid Platform Note: NetSamples is used to normalize all net time monitored statistics to a percent-of-time basis. For example, dividing (NetTxIdle/ NetSamples) yields the transmitter-idle time ratio for the net statistics. STATISTICS COLUMNS Teradata VS Columns These columns identify pdisk I/O statistics that are reported by Teradata VS. Note: Teradata VS is available for purchase separately from Teradata Database. For details about these columns, see Teradata Virtual Storage. Process Allocation Columns These columns represent all currently allocated processes, subdivided into the possible process states of running, ready, blocked or suspended. ProcReadySum count Number of runnable or ready tasks able to execute on CPUs when a CPU becomes available. FLOAT Note: A task is a thread. Also, to calculate the average number of runnable or ready tasks, divide this value by the CollectIntervals value. The CollectIntervals value is the number of gather periods per reporting period. For more information, see the CollectIntervals column. ProcBlockedSum count The total number of threads blocked waiting for I/O. FLOAT Windows FLOAT ALL Note: To calculate the average number of processes blocked, divide this value by the CollectIntervals value. The CollectIntervals value is the number of gather periods per reporting period. For more information, see the CollectIntervals column. ProcSuspendedSum count Total number of process suspended from execution, awaiting another process to resume them (during a log interval). Note: To calculate the average number of processes suspended, divide this value by the CollectIntervals value. The CollectIntervals value is the number of gather periods per reporting period. For more information, see the CollectIntervals column. 54 Resource Usage Macros and Tables Chapter 6: ResUsageSpma Table Column Name Type of Data ProcRunningSum count Description Data Type Invalid Platform Total number of processes running (executing) on CPUs during each log interval. FLOAT ALL Note: To calculate the average number of processes running, divide this value by the CollectIntervals value. The CollectIntervals value is the number of gather periods per reporting period. For more information, see the CollectIntervals column. ProcReadyMax max Maximum number of runnable or ready tasks able to execute on CPUs when a CPU becomes available. FLOAT Note: A task is a thread. Process Pending Snapshot Columns Identify how many processes are blocked for each possible reason. These columns total (minus ProcPendDBLock) approximately ProcBlockedSum, since we can only be blocked on one blocking type at a time. Note: In analyzing resource usage, a distinction should be made between the following two kinds of process blocks: • Block involves a process that is logically idle, waiting to receive work on its primary mailbox, or for a timer to elapse. This block does not affect throughput. • Block involves a process that has work to do but is being prevented from proceeding by some circumstance like a segment lock or flow control. This kind of block does affect throughput. The first kind of block is represented by column ProcPendNetRead; the second kind is represented by the remaining columns described here. Note on Averages: To calculate the average number of processes pending, divide the value by the CollectIntervals value. The CollectIntervals value is the number of gather periods per reporting period. In the Collect Buffer and Log Buffer, the value is the number of Gather operations that have been performed during the period. This number can vary from one period to the next. ProcPendMemAlloc count Number of processes blocked pending memory allocations. FLOAT Note: Always divide this value by CollectIntervals. See "Note on Averages" above. ProcPendFsgRead count Number of processes blocked pending a File Segment (FSG) read from disk. FLOAT Note: Always divide this value by CollectIntervals. See "Note on Averages" above. ProcPendFsgWrite count Number of processes blocked pending an FSG write to disk. FLOAT Note: Always divide this value by CollectIntervals. See "Note on Averages" above. ProcPendNetThrottle count Number of processes blocked pending delivery of outstanding outgoing messages. FLOAT Note: Always divide this value by CollectIntervals. See "Note on Averages" above. Resource Usage Macros and Tables 55 Chapter 6: ResUsageSpma Table Column Name Type of Data ProcPendNetRead count Description Data Type Number of processes blocked pending non-step work, that is, the number of processes blocked on any mailbox other than the work mailbox. FLOAT Invalid Platform Always divide this value by CollectIntervals. See "Note on Averages" above. Note: Non-step work is anticipated work the process spawned off and is now waiting for some type of response from the spawned process or processes. Non-step work is not unanticipated work such as a new work request sent when a user initiates a request from the host. ProcPendMonitor count Number of processes blocked pending a user monitor. FLOAT Note: Always divide this value by CollectIntervals. See "Note on Averages" above. ProcPendMonResume count Number of processes blocked pending a user monitor resume from a yield. FLOAT Note: Always divide this value by CollectIntervals. See "Note on Averages" above. ProcPendDBLock count Number of processes blocked pending database locks. FLOAT Note: Always divide this value by CollectIntervals. See "Note on Averages" above. ProcPendSegLock count Number of processes blocked pending a segment lock. FLOAT Note: Always divide this value by CollectIntervals. See "Note on Averages" above. ProcPendFsgLock count Number of processes blocked pending an FSG lock. FLOAT Note: Always divide this value by CollectIntervals. See "Note on Averages" above. ProcPendMisc count Number of processes blocked pending miscellaneous events. FLOAT Note: Always divide this value by CollectIntervals. See "Note on Averages" above. ProcPendQnl count Number of processes blocked pending a TSKQNL lock. FLOAT Note: Always divide this value by CollectIntervals. See "Note on Averages" above. 56 Resource Usage Macros and Tables Chapter 6: ResUsageSpma Table Column Name Type of Data Description Data Type Invalid Platform Process Block Counts Columns Identify how many times a process became blocked on which blocking type. Average time blocked can be approximated by dividing corresponding ProcWaitXxx by ProcBlksXxx. ProcBlksMemAlloc count Number of process blocks for memory allocations. FLOAT ProcBlksQnl count Number of process blocks for a TSKQNL lock. FLOAT ProcBlksFsgRead count Number of process blocks for an FSG read from disk. FLOAT ProcBlksFsgWrite count Number of process blocks for an FSG write to disk. FLOAT ProcBlksNetThrottle count Number of process blocks for delivery of outstanding outgoing messages. FLOAT ProcBlksMsgRead count Number of process blocks for non-step work. FLOAT ProcBlksMonitor count Number of process blocks for a user monitor. FLOAT ProcBlksMonResume count Number of process blocks for a user monitor resume from a yield. FLOAT ProcBlksDBLock count Number of process blocks for database locks. The AWT can do other work while the lock is blocked. FLOAT ProcBlksSegLock count Number of process blocks for a disk or task context (scratch, stack, and so on) segment lock. FLOAT ProcBlksFsgLock count Number of process blocks for an FSG lock. FLOAT ProcBlksTime count Number of process blocks waiting only for timer expiration. FLOAT ProcBlksMisc count Number of process blocks for miscellaneous events. FLOAT Process Pending Wait Time Columns Identify how much time in centiseconds processes were in the blocked state for each possible reason. Note: Since this time is only accounted for when a blocked process leaves the blocked state, it is possible for this statistic to be much larger than the amount of time available to all processes in a single log period. ProcWaitMemAlloc count Total time processes were blocked pending memory allocations. FLOAT ProcWaitPageRead count Total time processes were blocked pending a page read from disk. FLOAT ProcWaitFsgRead count Total time processes were blocked pending an FSG read from disk. FLOAT Resource Usage Macros and Tables 57 Chapter 6: ResUsageSpma Table Column Name Type of Data ProcWaitFsgWrite Description Data Type count Total time processes were blocked pending an FSG write to disk. FLOAT ProcWaitNetThrottle count Total time processes were blocked pending delivery of outstanding outgoing messages. FLOAT ProcWaitMsgRead count Total time processes were blocked pending nonstep work. FLOAT ProcWaitMonitor count Total time processes were blocked pending a user monitor. FLOAT ProcWaitMonResume count Total time processes were blocked pending a user monitor resume from a yield. FLOAT ProcWaitDBLock count Total time processes were blocked pending database locks. FLOAT ProcWaitSegLock count Total time processes were blocked pending a disk or task context (scratch, stack, and so on) segment lock. FLOAT ProcWaitFsgLock count Total time processes were blocked pending an FSG lock. FLOAT ProcWaitTime count Total time processes were blocked pending some amount of elapsed time only. FLOAT ProcWaitQnl count Total time processes were blocked pending a TSKQNL lock. FLOAT ProcWaitMisc count Total time processes were blocked pending miscellaneous events. FLOAT Invalid Platform CPU Utilization Columns Count all CPU activities, including activities performed for virtual processors, subdivided into the following columns: 1 CPUIdle - Idle time 2 CPUIoWait - Idle and waiting for I/O completion 3 CPUUServ - User service 4 CPUUExec - User execution 5 CPUIdleNorm - Normalized idle time 6 CPUIOWaitNorm - Normalized idle and waiting for I/O completion 7 CPUUServNorm - Normalized user service 8 CPUUExecNorm - Normalized user execution These columns represent the sum of all CPUs on the node. To obtain the average node CPU value for each column, CPU(Idle, IOWait, Userv, Uexec), divide the column data by the number of CPUs per node (the value in the NCPUs column) and the number of centiseconds (CentiSecs column) in the logging interval. 58 Resource Usage Macros and Tables Chapter 6: ResUsageSpma Table Column Name Type of Data Description Data Type Invalid Platform Note: • • • • • • CPU idle time = CPUIdle + CPUIoWait CPU busy time = CPUUServ + CPUUExec CPUIdleNorm = (CPUIdle * NodeNormFactor)/100 CPUIOWaitNorm = (CPUIoWait * NodeNormFactor)/100 CPUUServNorm = (CPUUServ * NodeNormFactor)/ 100 CPUUExecNorm = (CPUUExec * NodeNormFactor)/100 where the NodeNormFactor is the per node normalization factor. This is related to the NodeType value reported in this resource usage table. The normalization factor modifies the reported CPU times to the equivalent time of a specified virtual processor. This does not add up to the reported CPU time. You can calculate the CPU time by using the formula below. CPUIdleNorm + CPUIOWaitNorm + CPUUServNorm + CPUUExecNorm = CentiSecs * NCPUs * NodeNormFactor To calculate the non-normalized total CPU time, use the following formula: 100 x Secs x NCPUs ≈ CentiSecs x NCPUs = CPU(Idle, IoWait, UServ, UExec) The CPU time returned in centiseconds is more accurate than those returned in seconds. CPUIdle count Time in centiseconds CPUs are idle and not waiting for I/O. FLOAT CPUIoWait count Time in centiseconds CPUs are idle and waiting for I/O completion. FLOAT Windows On Windows, the value is always 0. Note: This time represents another variety of Idle, since a CPU is only in this state if there are no processes eligible for execution. If there was a process available, the CPU would be immediately dispatched for that process. CPUUServ count Time in centiseconds CPUs are busy executing user service code, that is, privileged work performing system services on behalf of user execution processes which do not have root access. FLOAT CPUUExec count Time in centiseconds CPUs are busy executing user execution code, that is, time spent in a user state on behalf of a process. FLOAT CPUIdleNorm count Time in centiseconds CPUs are idle and not waiting on I/O. FLOAT CPUIOWaitNorm count Time in centiseconds CPUs are idle and waiting for I/O completion. FLOAT Windows On Windows, the value is always 0. Resource Usage Macros and Tables 59 Chapter 6: ResUsageSpma Table Column Name Type of Data CPUUServNorm CPUUExecNorm Description Data Type count Time in centiseconds CPUs are busy executing user service code, that is, privileged work performing system services on behalf of user execution processes which do not have root access. FLOAT count Time in centiseconds CPUs are busy executing user execution code; that is, time spent in a user state on behalf of a process. FLOAT Invalid Platform MEMORY COLUMNS Memory Allocation Columns Identify the number and amount of memory allocations, subdivided into (the only applicable) generic node memory type and a summarization of vproc memory types. MemTextAllocs count Number of successful memory allocations and size-increasing memory alters for non-system overhead text (code). Amount allocated can be derived by multiplying the number of allocations by the fixed page size. FLOAT ALL MemVprAllocs count Number of successful memory allocations and size-increasing memory alters for all vproc memory types, that is, disk segments and task context types. FLOAT ALL MemVprAllocKB count The value represents the change in memory. It represents a delta from the previous reporting period. Thus, it will report negative values as less memory is used. FLOAT Windows Note: The original meaning of this column was the total KBs attributed to allocations and sizeincreasing alters for vproc memory types. Memory Pages Resident Columns Identify the amount, in number of pages or KBs, of memory resident subdivided into memory types. Disk segment memory types are described by the single entries below. Each of these expands into six columns, where [seg] is as follows: • • • • • • PDb = Permanent data block disk segments PCi =Permanent cylinder index disk segments SDb =Regular or restartable spool data block disk segments SCi = Regular or restartable spool cylinder index disk segments TJt = Transient journal table or WAL data block or WAL cylinder index APt = Append table or permanent journal table data block or cylinder index disk segments MemTSysOhRes 60 track Number of pages resident in memory for system overhead text. System Overhead Text is wired into memory upon startup and will not change. FLOAT ALL Resource Usage Macros and Tables Chapter 6: ResUsageSpma Table Column Name Type of Data Description Data Type Invalid Platform MemDSysOhRes track Number of pages resident in memory for system overhead data. System Overhead Data is wired into memory upon startup. FLOAT ALL MemTextRes track Number of pages resident in memory for text. FLOAT ALL MemCtxtRes track Number of pages resident in memory for task context segments. FLOAT ALL Mem[seg]KBRes track Current KBs resident in memory for (nonbackup) disk segments. FLOAT ALL MemFreeKB track KBs of free memory. This value should be equal to the size of memory minus the total amount resident derived from adding all of the above memory resident columns and frozen disk segment resident column from ResUsageSvpr. FLOAT On Linux, the value reported is the approximate amount of memory that is available for use. The Linux operating system uses most free memory for buffers and caching to improve performance, but the operating system can reclaim that memory if it is needed by programs. The following formula is used by the RSS to calculate the MemFreeKB value. MemFreeKB = MemFree + Buffers + Cached + SwapCached - fsgavailpgs*kbperpage (active_slabs*pgsperslab*kbperpage) where the values: • MemFree, Buffers, Cached, and SwapCached come from /proc/meminfo. • fsgavailpgs come from the PDE FSG code. • active_slabs and pgsperslab come from /proc/ slabinfo. Memory Availability Management Columns Identify overhead to managing memory when memory availability is a problem. MemFails count Number of failures performing memory allocations and size-increasing memory alters for vproc memory types as well as node memory types. FLOAT ALL MemAgings count Number of times memory was aged. FLOAT ALL MemTextPageDrops count Number of non-system overhead text pages dropped from memory to make more physical memory available. FLOAT ALL Resource Usage Macros and Tables 61 Chapter 6: ResUsageSpma Table Column Name Type of Data MemTextPageReads count Description Data Type Number of non-system overhead text pages required to be read from disk when it was previously paged out. FLOAT Invalid Platform On Linux, the number of 4KB pages paged minus the pages swapped in. MemProcSwapped track Current count of processes whose stack has been written to disk to make available more physical memory. This value is less than, or equal to, total processes allocated. FLOAT MemCtxtPageWrites count Number of task context (scratch, stack, and so on) pages that were paged out. FLOAT ALL On Linux, the number of 4KB pages swapped out. MemCtxtPageReads count Number of task context (scratch, stack, and so on) pages that were paged in. FLOAT On Linux, the number of 4KB pages swapped in. MemSwapDrops count Number of disk segments that were dropped from memory because all its ancestor processes were swapped out. FLOAT ALL MemSwapDropKB count KBs dropped from memory by MemSwapDrops. FLOAT ALL MemSwapReads count Number of disk segments that were re-read when they were previously dropped from memory because all its ancestor processes were swapped out. FLOAT ALL MemSwapReadKB count KBs re-read from memory by MemSwapReads. FLOAT ALL NET COLUMNS Point-to-Point Net Traffic Columns Identify the number (Reads, Writes) and amount (ReadKB, WriteKB) of input and output messages passing through the Teradata Database nets through point-to-point (1:1) methods (PtP). It excludes TCP/IP traffic. MsgPtPReads count Number of net point-to-point messages input to processes on the node via the message subsystem. FLOAT MsgPtPWrites count Number of net point-to-point messages output from processes on the node via the message subsystem. FLOAT MsgPtPReadKB count Total KBs of net point-to-point messages input to processes on the node via the message subsystem. FLOAT 62 Resource Usage Macros and Tables Chapter 6: ResUsageSpma Table Column Name Type of Data MsgPtPWriteKB count Description Data Type Total KBs of net point-to-point messages output from processes on the node via the message subsystem. FLOAT Invalid Platform Broadcast Net Traffic Columns Identify the number (Reads, Writes) and amount (ReadKB, WriteKB) of input and output messages passing through the Teradata Database nets through broadcast (1:many) methods (Brd). Note: If a single broadcast message is delivered to multiple processes in this node, the NetBrdReads and NetBrdReadKB are only incremented once. MsgBrdReads count Number of net broadcast messages input to processes on the node via the message subsystem. FLOAT MsgBrdWrites count Number of net broadcast messages output from processes on the node via the message subsystem. FLOAT MsgBrdReadKB count Total KBs of net broadcast messages input to processes on the node via the message subsystem. FLOAT MsgBrdWriteKB count Total KBs of net broadcast messages output from processes on the node via the message subsystem. FLOAT Network Transport Data Columns Identify the number (Reads, Writes) and amount of input and output (PDE messages routed by the message subsystem) passing through the Teradata Database nets. These statistics are nonspecific, that is, they do not take into consideration which Bynet performed the transport. NetMsgPtpWriteKB count Amount of point-to-point message data in KBs transmitted by both Bynets. FLOAT NetMsgBrdWriteKB count Amount of broadcast message data in KBs transmitted by both Bynets. FLOAT NetMsgPtpReadKB count Amount of point-to-point message data in KBs received by both Bynets. FLOAT NetMsgBrdReadKB count Amount of broadcast message data in KBs received by both Bynets. FLOAT NetMsgPtpWrites count The number of point-to-point messages transmitted by both Bynets. FLOAT NetMsgBrdWrites count The number of broadcast messages transmitted by both Bynets FLOAT NetMsgPtpReads count The number of point-to-point messages received by both Bynets. FLOAT NetMsgBrdReads count The number of broadcast messages received by both Bynets. FLOAT Resource Usage Macros and Tables 63 Chapter 6: ResUsageSpma Table Column Name Type of Data Description Data Type Invalid Platform Per-Bynet Network Transport Data Columns Identify the amount of input and output passing through the Teradata Database nets. These statistics are net-specific, that is, they relate to each specific Bynet. On a single-node (virtual network [vnet]) system, net-specific statistics are not meaningful and are always zero. NetTxKBPtP count Total point-to-point KBs transmitted over all Bynets. FLOAT NetRxKBPtP count Total point-to-point KBs received over all Bynets. FLOAT NetTxKBBrd count Total broadcast KBs transmitted over all Bynets. FLOAT NetRxKBBrd count Total broadcast KBs received over all Bynets. FLOAT Net Controller Status and Miscellaneous Management Provide utilization and other status information about the Teradata Database net controllers. These statistics are not net-specific since all the Bynet statistics are reported in the net columns. On a single-node (vnet) system, net-specific statistics are not meaningful and are always zero. NetTxRouting count Number of samples showing the transmitter routing on a Bynet. FLOAT NetTxConnected count Number of samples showing the transmitter connected on a Bynet. FLOAT NetRxConnected count Number of samples showing the receiver connected on a Bynet. FLOAT NetTxIdle count Number of samples showing the transmitter idle on a Bynet. FLOAT NetRxIdle count Number of samples showing the receiver idle on a Bynet. FLOAT Net Circuit Management Columns Identify the management of Teradata Database net circuits (Circ). Additional detail is found in Appendix B: “ResUsageIpma Table.” Note: Circuit attempts for one or both Bynets can be computed as the sum of the applicable NetTxCircPtp and NetTxCircBrd columns. All of these columns except for NetCircBackoffs are net-specific. On a single-node system, netspecific statistics are not meaningful and are always zero. NetTxCircHPBrd count Number of high priority broadcast circuits transmitted on all Bynets. FLOAT NetRxCircPtp count Total number (both normal and high priority) of point-to-point circuits received on all Bynets. FLOAT NetTxCircHPPtP count Number of high priority point-to-point circuits transmitted on all Bynets. FLOAT 64 Resource Usage Macros and Tables Chapter 6: ResUsageSpma Table Column Name Type of Data NetRxCircBrd Description Data Type count Total number (both normal and high priority) of broadcast circuits received on all Bynets. FLOAT NetTxCircBrd count Total number (both normal and high priority) of broadcast circuits transmitted on all Bynets. FLOAT NetCircBackoffs count Software backoffs, defined as BNS service blocked occurrences, without regard for which net was involved. FLOAT NetHWBackoffs count Hardware backoffs reported by the BLM for all Bynets. FLOAT NetTxCircPtp count Total number (both normal and high priority) of point-to-point circuits transmitted on all Bynets. FLOAT Invalid Platform Group Coordination Messages Columns Identify messages that are communicated through the Teradata Database net for coordination of a process among a group of vprocs. Coordination is handled either through semaphores, groups, or channels. MsgChnLastDone count Number of last done events that occurred on this node. FLOAT Note: The last AMP to finish an operation may send a last done broadcast message indicating the work is done for this step. This is used in tracking down the slowest node or AMP in the system. A node or AMP that has more last done messages than the others could be a bottleneck in the system performance. NetSemInUseSum count Total number of semaphores in use during each log interval. FLOAT NetSemInUseMax max Maximum number of semaphores in use during each log interval. FLOAT NetChanInUseSum count Total number of channels in use during each log interval. FLOAT NetChanInUseMax max Maximum number of channels in use. FLOAT NetGroupInUseSum count Total number of groups in use during each log interval. This number should be same across all nodes. FLOAT NetGroupInUseMax max Maximum number of groups in use during each log interval. FLOAT Merge Services Columns Identify activity occurring through merge (many:1) methods (Mrg) on Teradata Database net. NetMrgTxKB count Resource Usage Macros and Tables Number of KBs transmitted, without regard to which net, by merge transmission services for currently active merge operations. FLOAT 65 Chapter 6: ResUsageSpma Table Column Name Type of Data NetMrgRxKB Description Data Type count Number of KBs received, without regard to which net, by merge receive services for currently active merge operations. FLOAT NetMrgTxRows count Number of data rows transmitted, without regard to which net, by merge transmission services for currently active merge operations. FLOAT NetMrgRxRows count Number of data rows received, without regard to which net, by merge receive services for currently active merge operations. FLOAT Invalid Platform HOST CONTROLLER COLUMNS Channel Traffic Columns Identify the traffic between the host and the node in three levels of granularity: blocks, messages, and KBs. Blocks are made up of some amount of variable sized messages. ReadKB and WriteKB identify the KBs involved in the traffic. HostBlockReads count Number of blocks read in from the host. FLOAT HostBlockWrites count Number of blocks written out to the host. FLOAT HostMessageReads count Number of messages read in from the host. FLOAT HostMessageWrites count Number of messages written out to the host. FLOAT HostReadKB count KBs transferred in from the host. FLOAT HostWriteKB count KBs transferred out to the host. FLOAT GENERAL CONCURRENCY CONTROL COLUMNS Database Locks Columns Identify database locking occurrences. DBLockBlocks count Number of times a database lock was blocked. FLOAT DBLockDeadlocks count Number of times a database lock was deadlocked. FLOAT FILE SYSTEM COLUMNS Segments Acquired Columns Summarize logical and physical segments acquired by the file system. These columns identify the total disk memory segments acquired by the file system during the log period. Logical acquires (Acqs) and the logical amount acquired (AcqKB) are identified. Acquires causing physical reads (AcqReads) and the amount read (AcqReadKB) are identified as a subset of logical acquires. For more detail, see “Segment Acquires Columns” on page 136 in the ResUsageSvpr Table chapter. FileAcqs 66 count Total number of logical disk segments acquired. FLOAT Resource Usage Macros and Tables Chapter 6: ResUsageSpma Table Column Name Type of Data Description Data Type FileAcqKB count Total KBs logically acquired by FileAcqs. FLOAT Invalid Platform Note: Use the views provided in Chapter 14 instead of accessing the data for this field directly from this table. FileAcqReads count Total number of disk segment acquires that caused a physical read. FLOAT FileAcqReadKB count Total KBs physically read by FileAcqReads. FLOAT Segments Released Columns Summarize logical and physical segments released by the file system. For more detail, see “Segments Released Columns” on page 138 in the ResUsageSvpr Table chapter. FileRels count Total number of logical disk segments released by tasks. FLOAT FileRelKB count Total KBs logically released by FileRels. FLOAT Note: Use the views provided in Chapter 14 instead of accessing the data for this field directly from this table. FileWrites count Total number of disk segment immediate or delayed physical writes. FLOAT FileWriteKB count Total KBs physically written by FileWrites. FLOAT Data Block Prefetches Columns Summarize the effects of prefetching data blocks on the file system. For more detail, see “Data Block Prefetches Columns” on page 137 in the ResUsageSvpr Table chapter. Note: A prefetch is either a cylinder read operation or individual block reads operation. Either of these operations are generically called a prefetch. When all cylinder slots are in use, the cylinder reads revert back to the original algorithm of a block-at-a-time read ahead. So the column FilePreKB is the sum of the size of data blocks logically read by either cylinder reads or data block pre-reads. This also applies to the physical pre-reads. FilePreReadKB includes both physical cylinder reads and single block pre-reads. The number of data blocks that are pre-read at a time is controlled by the DBS Control performance parameter ReadAhead Count. The default is 1 block at a time pre-read. If you enable cylinder reads, there will be extra sectors read in on cylinder reads. An accurate calculation of the wasted kilobytes read by cylinder read is not possible since there are legitimate logical pre-reads that do not incur physical prereads. For more information on cylinder read, see Performance Management. FilePres count Resource Usage Macros and Tables Total number of times a logical data prefetch was performed (either as a cylinder read or individual block reads). FLOAT 67 Chapter 6: ResUsageSpma Table Column Name Type of Data FilePreKB count Description Data Type Sum of the sizes of data blocks logically loaded with data prefetches (either cylinder reads or individual block reads). FLOAT Invalid Platform For cylinder reads, this field does not include the disk sectors in between the loaded data blocks. Note: Use the views provided in Chapter 14 instead of accessing the data for this field directly from this table. FilePreReads count Number of times a data prefetch was physically performed either as a cylinder read or individual blocks read. FLOAT FilePreReadKB count The size of the data prefetch (cylinder section or individual blocks being read) that is physically loaded from disk. FLOAT For cylinder reads, this field includes the disk sectors in between the loaded data blocks. Data Segment Lock Requests Columns Summarize the number of lock requests, blocks, and deadlocks on a disk segment. For more detail, see “Data Segment Lock Requests Columns” on page 139 in the ResUsageSvpr Table chapter. FileLockBlocks count Number of lock requests that were blocked. FLOAT FileLockDeadlocks count Number of deadlocks detected on lock requests. FLOAT FileLockEnters count Number of times a lock was requested. FLOAT Depot Columns Summarize the physical writes to the Depot used to protect in-place modifications. FileSmallDepotWrites 68 count Number of small writes to the depot performed to protect in-place modifications. Each small Depot write protects a single in-place write of either a WAL data block or a database data block. The small Depot is typically used when the inplace writes are initiated by a foreground task. Small Depot writes are also counted against FileWrites; therefore, FileWrites still indicates the total writes regardless of whether it was a Depot write or a database write. FLOAT Resource Usage Macros and Tables Chapter 6: ResUsageSpma Table Column Name Type of Data FileLargeDepotWrites FileLargeDepotBlocks Description Data Type count Number of large writes to the depot performed to protect in-place modifications. Each large Depot write protects multiple in-place writes of either WAL data blocks or database data blocks. The large Depot is typically used when blocks age out of memory in the background. Large Depot writes are also counted against FileWrites; therefore, FileWrites still indicates the total writes regardless of whether it was a Depot write or a database write. FLOAT count Total number of blocks (either WAL or database) that have been protected by large Depot writes. FLOAT Invalid Platform Since a large Depot write protects multiple blocks, the following calculation results in the average number of blocks protected by each large Depot write: FileLargeDepotBlocks / FileLargeDepotWrites USER COMMANDS COLUMNS User Command Columns Summarize the type of statements given to Teradata Database by the user. For more detail, see Chapter 8: “ResUsageShst Table.” CmdDDLStmts count Number of alter, modify, drop, create, replace, grant or revoke commands. FLOAT CmdDeleteStmts count Number of delete commands. FLOAT CmdInsertStmts count Number of insert commands. FLOAT CmdSelectStmts count Number of select commands. FLOAT CmdUpdateStmts count Number of update commands. FLOAT CmdUtilityStmts count Number of utility commands. FLOAT CmdOtherStmts count Number of other commands. FLOAT User Command Arrival and Departure Columns Summarize the arrival and departure of user statements. For more detail, see Chapter 8: “ResUsageShst Table.” CmdStmtsInProgCur count Current count of statements in progress. FLOAT CmdStmtSuccesses count Number of statements that departed normally. FLOAT CmdStmtFailures count Number of statements that departed in failure or were aborted. FLOAT CmdStmtErrors count Number of statements that departed in error. FLOAT Resource Usage Macros and Tables ALL 69 Chapter 6: ResUsageSpma Table Column Name Type of Data CmdStmtTime count Description Data Type Invalid Platform The sums of the resident time of each statement in progress during the log period, including the successes and failures. FLOAT ALL TERADATA ASM COLUMNS AMP Worker Task Columns Collect and report statistics about the AWTs. For more information about the ResUsageSawt table and columns, see Chapter 7: “ResUsageSawt Table.” AwtFlowControlled count Number of AMPs currently in flow control on the work input mailbox. FLOAT AwtFlowCtlCnt count Number of times this log period that the node entered the flow control state from a non-flow controlled state. FLOAT AwtInuse max Number of AWTs currently in use for this node. Divide the value for AwtInUse by the CollectIntervals value to obtain an average. FLOAT The AwtInuse value of a log period can be larger than the AwtInuseMax value of the log period if a log period consists of multiple gather periods. The AwtInuse value in a log period is the summation of the values of the gather periods comprising the log period. This is why the AwtInuse value needs to be divided by the CollectIntervals value. The CollectIntervals value is the number of gather periods per reporting period. For more information, see the CollectIntervals column. AwtInuseMax max Peak number of AWTs (Max) on this node. This is not the Peak or the Max value stored in the Priority Scheduler (sch) data structure and reported by the puma utility. The sch peak value is the Max value since startup is never set and Max is the maximum allowed value. FLOAT Note: This reported Max value is the maximum reached during each log period. For example, if there are 4 gather periods in one log period, and the max value of each period is 10, 32, 7, and 15, the max of the log period would be the max of the individual gather periods, which would be 32. 70 Resource Usage Macros and Tables Chapter 6: ResUsageSpma Table Spare Columns Column Name Type of Data Description Data Type Invalid Platform Priority Scheduler Columns Provides data specific to the Priority Scheduler. For more information about the ResUsageSps table and columns, see Chapter 11: “ResUsageSps Table.” PSNumRequests count Number of work requests received for all Performance Groups on this node. FLOAT PSQWaitTime count Time in centiseconds that work requests waited on an input queue before being serviced. FLOAT To get an approximate average QWaitTime per request during this period, divide QWaitTime by NumRequests. PSServiceTime count Time in centiseconds that work requests required for service. FLOAT To get an approximate average ServiceTime per request during this period, divide ServiceTime by NumRequests. Spare Columns The ResUsageSpma table has 12 spare columns (one of which is being used) as shown in the table below. Column Name Type of Data Description SpareCount[00-03] count Spare counted statistic. SpareTrack[00-03] track Spare tracked statistic. SpareTmon00 n/a The SpareTmon00 field contains the COD value. The value represents the COD value in one tenths of a percent, so a displayed value of 500 represents a COD value of 50.0%. Note: This value is valid only on SUSE Linux Enterprise Server 10 systems and is a single value for the entire system. SpareTmon[01-03] count Spare time monitored statistic. The spare column fields expand to values 00 - 03, so that column names would be SpareCount01 or SpareTrack02, and so on. Resource Usage Macros and Tables 71 Chapter 6: ResUsageSpma Table Spare Columns 72 Resource Usage Macros and Tables CHAPTER 7 ResUsageSawt Table The ResUsageSawt table collects and reports statistics about the AWTs. If table logging is enabled, then data is written to the database once for each log period. To consolidate and summarize the total number of rows written to the database, you can enable Summary Mode. For details, see “Summary Mode” on page 77. Note: This table is created as a MULTISET table. The following table describes the ResUsageSawt table columns. Column Name Type of Data Description Data Type Invalid Platform HOUSEKEEPING COLUMNS RELATIONAL PRIMARY INDEX COLUMNS These columns taken together form the nonunique primary index. TheDate n/a Date of the log entry. DATE TheTime n/a Nominal time of the log entry. FLOAT Note: Under conditions of heavy system load, entries may be logged late (typically, by no more than one or two seconds), but this field will still contain the time value when the entry should have been logged. See the Secs and NominalSecs columns. NodeId n/a Identifies the Node upon which the vproc resides. The Node ID is formatted as CCC-MM, where CCC denotes the three-digit cabinet number and MM denotes the two-digit chassis number of the node. For example, a node in chassis 9 of cabinet 3 has a node ID of ‘003-09’. INTEGER Note: SMP nodes have a chassis and cabinet number of 1. For example, the node ID of an SMP node is ‘001-01’. MISCELLANEOUS HOUSEKEEPING COLUMNS GmtTime n/a Greenwich Mean Time is not affected by the Daylight Savings Time adjustments that occur twice a year. FLOAT NodeType n/a Type of node, representing the per node system family type. For example, 5600C or 5555H. CHAR(8) Resource Usage Macros and Tables 73 Chapter 7: ResUsageSawt Table Column Name Type of Data VprId Secs Description Data Type n/a Identifies the vproc number. All Vprocs in this table are AMPS so there is no VprType field provided. In Summary Mode, this field is zero. INTEGER n/a Actual number of seconds in the log period represented by this row. Normally the same as NominalSecs, but can be different in three cases: SMALLINT Invalid Platform • The first interval after a log rate change • A sample logged late because of load on the system • System clock adjustments affect reported Secs Useful for normalizing the count statistics contained in this row, for example, to a per-second measurement. CentiSecs n/a Number of centiseconds in the logging period. This field is useful when performing data calculations with small elapsed times where the difference between centisecond-based data and whole seconds results in a percentage error. INTEGER NominalSecs n/a A specified or nominal number of seconds in the logging period. SMALLINT SummaryFlag n/a Identifies the summarization status of this row. If the value is ‘N,’ the row is a non-summary row. If the value is ‘S,’ the row is a summary row. For details, see “Summary Mode” on page 77. CHAR Active n/a Controls whether or not the rows will be logged to the ResUsage tables if Active Row Filter Mode is enabled. FLOAT If Active is set to: • a non-zero value, then the row contains modified data columns. • a zero value, then none of the data columns in the row have been updated during the logging period. For example, if Active Row Filter Mode is enabled, then the rows that have a zero Active field value will not be logged to the ResUsage tables. CollectIntervals n/a SMALLINT The number of gather periods per reporting period. In the Collect Buffer and Log Buffer, the value is the number of Gather operations that have been performed during the period. This number can vary from one period to the next. 74 Resource Usage Macros and Tables Chapter 7: ResUsageSawt Table Column Name Type of Data Description Data Type Invalid Platform STATISTICS COLUMNS TERADATA ASM COLUMNS AMP Worker Task Columns Collect and report statistics about the AWTs. MailBoxDepth count The average depth of the AMP work mailbox. FLOAT Note: This value reports the SUM of the values reported in each gather period when there are multiple gather intervals in each log period. It should be divided by the CollectIntervals column to get the average value. FlowControlled track Specifies if an AMP is in flow control. If the value is non-zero, then the AMP is in flow control. FLOAT FlowCtlCnt count Number of times during the log period that the system entered the flow control state from a nonflow controlled state. FLOAT FlowCtlTime count The total time in milliseconds that an AMP is in flow control. FLOAT InuseMax max Maximum number of AWTs in use at any one time during the log period. FLOAT WorkTypeInuse00 WorkTypeInuse15 count Current number of AWTs in use during the log period for each work type for the VprId vproc. FLOAT Note: This value reports the SUM of the values reported in each gather period when there are multiple gather intervals in each log period. It should be divided by the CollectIntervals column to get the average value. WorkTypeMax00 WorkTypeMax15 max Maximum number of AWTs in use at one time during the log period for each work type for the VprId vproc. FLOAT In Summary Mode, the WorkTypeMax field values are the Max of the values for all the AMPS. WORK TYPE DESCRIPTIONS The WorkTypeInuse and WorkTypeMax array data columns above each contain 16 Work Type entries that are described here. For example, WorktypeInuse00 contains the number of in use AWTs that are of Work Type MSGWORKNEW, and WorktypeInuse01 contains the values for MSGWORKONE. These columns allow the user to monitor the usage of the AWTs of each work type. This can be used to determine if the usage is close to the maximum values defined and what type of work they are doing. Also, this can be used to determine characteristics of the system during skew conditions or when there are AWT shortages. Use the tdntune utility to determine the settings for Flow Control. For information on Expedited Allocation Groups, see "Priority Scheduler (schmon)" chapter of Utilities. Resource Usage Macros and Tables 75 Chapter 7: ResUsageSawt Table Column Name Type of Data MSGWORKNEW n/a Description Data Type Used for new work requests. This work type has the lowest number, which means it is queued last. It also has the effect of honoring secondary requests needed to complete existing work items before any new ones are started. n/a Invalid Platform A zero value is used for new work items. MSGWORKONE n/a First level secondary work items. Numbered work types are used for secondary work items. For example, work type one (MSGWORKONE) is used for secondary work requests spawned by new work items; work type two (MSGWORKTWO) requests are spawned from work type one requests and queued for delivery before work type one requests; and so on. Each numbered work type is queued for delivery just before the one from which it is spawned. n/a MSGWORKTWO n/a Second level secondary work items. n/a MSGWORKTHREE n/a Special types of database work. n/a MSGWORKFOUR n/a Start System Recover. n/a MSGWORKFIVE n/a This field is not normally used and MSGWORKNEW, MSGWORKONE, and MSGWORKTWO report work requests for utilities. However, if utilities are configured to use a separate pool of work types, this field reports new work for utilities such as FastLoad, MultiLoad, and FastExport. n/a MSGWORKSIX n/a First level secondary work spawned work for utilities such as FastLoad, MultiLoad, and FastExport. If the utilities are not configured to use a separate pool of work types, they use MSGWORKNEW, MSGWORKONE, and MSGWORKTWO. n/a MSGWORKSEVEN n/a Second level secondary work for utilities such as FastLoad, MultiLoad, and FastExport. If the utilities are not configured to use a separate pool of work types, they use MSGWORKNEW, MSGWORKONE, and MSGWORKTWO. n/a MSGWORKEIGHT n/a New work for Expedited Allocation Groups. n/a MSGWORKNINE n/a First level spawned work for Expedited Allocation Groups. n/a MSGWORKTEN n/a Second level spawned work for Expedited Allocation Groups. n/a MSGWORKELEVEN n/a Not used. n/a 76 Resource Usage Macros and Tables Chapter 7: ResUsageSawt Table Summary Mode Column Name Type of Data MSGWORKABORT n/a Description Data Type Used for transaction abort requests. This work type has a higher value than the numbered work types so that abort requests are honored before beginning any additional work item for the transactions being aborted. n/a Invalid Platform The array number for MSGWORKABORT is 12. MSGWORKSPAWN n/a Used for spawned abort requests and is delivered before normal aborts. n/a The array number for MSGWORKSPAWN is 13. MSGWORKNORMAL n/a Used for messages that do not fall within the standard work type hierarchy. This work type is delivered before any of the work items described above. n/a The array number for MSGWORKNORMAL is 14. MSGWORKCONTROL n/a Used for system control messages. These are delivered before any other kind of message. n/a The array number for MSGWORKCONTROL is 15. Summary Mode When Summary Mode is active for the ResUsageSawt table, one row is written to the database for each node in the system for each log interval. The AWT data will be combined for all the AMP vprocs on the node. You can determine if a row is in Summary Mode by checking the SummaryFlag column for that row. IF the SummaryFlag column value is… THEN the data for that row is being logged… ‘S’ in Summary Mode. ‘N’ normally. Resource Usage Macros and Tables 77 Chapter 7: ResUsageSawt Table Spare Columns Spare Columns The ResUsageSawt table has 30 spare columns (one of which is being used) as shown in the table below. Column Name Type of Data Description SpareCount[00-01, 04-09] count Spare counted statistic. SpareCount[02-03] count The following SpareCount columns will be converted to the specified column names in Teradata Database 14.0. • SpareCount02=Available. Available is the number of unreserved AWTs from the pool that are not being used at the end of the interval. For example, if 12 of the normally 62 unreserved AWTs are removed from the pool by reserving them for expedited work, there could at most be 50 unreserved AWTs available. If in this log period, 10 unreserved AWTs are taken from the pool to service 10 queries that are still executing, than there would be only 40 available at the end of the log period. • SpareCount03=AvailableMin. AvailableMin is the minimum number of unreserved AWTs available in the pool for each AMP for the logged period. For example, a value of 0 for SpareCount03 means there were no unreserved AWTs available in the pool at some point during the reporting period. SpareTrack[00-09] track Spare tracked statistic. SpareTmon00 n/a The SpareTmon00 field contains the COD value. The value represents the COD value in one tenths of a percent, so a displayed value of 500 represents a COD value of 50.0%. Note: This value is valid only on SUSE Linux Enterprise Server 10 systems and is a single value for the entire system. SpareTmon[01-09] count Spare time monitored statistic. The spare column fields expand to values 00–09, so that column names would be SpareCount00, SpareTrack03, SpareTmon08, and so on. 78 Resource Usage Macros and Tables CHAPTER 8 ResUsageShst Table The ResUsageShst table: • Contains resource usage information specific to the host channels and LANs communicating with Teradata Database. • Includes resource usage data for system-wide, host information. Note: This table is created as a MULTISET table. The following table describes the ResUsageShst table columns. Column Name Type of Data Description Data Type Invalid Platform HOUSEKEEPING COLUMNS RELATIONAL PRIMARY INDEX COLUMNS These columns taken together form the nonunique primary index. TheDate n/a Date of the log entry. DATE TheTime n/a Nominal time of the log entry. FLOAT Note: Under conditions of heavy system load, entries may be logged late (typically, by no more than one or two seconds), but this field will still contain the time value when the entry should have been logged. See the Secs and NominalSecs columns. NodeId n/a Identifies the Node upon which the vproc resides. The Node ID is formatted as CCC-MM, where CCC denotes the three-digit cabinet number and MM denotes the two-digit chassis number of the node. For example, a node in chassis 9 of cabinet 3 has a node ID of ‘003-09’. INTEGER Note: SMP nodes have a chassis and cabinet number of 1. For example, the node ID of an SMP node is ‘001-01’. MISCELLANEOUS HOUSEKEEPING COLUMNS GmtTime n/a Greenwich Mean Time is not affected by the Daylight Savings Time adjustments that occur twice a year. FLOAT NodeType n/a Type of node, representing the per node system family type. For example, 5600C or 5555H. CHAR(8) Resource Usage Macros and Tables 79 Chapter 8: ResUsageShst Table Column Name Type of Data VprId n/a Description Data Type Identifies the vproc number. In Summary Mode, VprId is -1. INTEGER Invalid Platform For LAN-connected hosts, VprId is the Gateway vproc ID. For channel-connected hosts, VprId should be the vproc id of the owning PE. If there are multiple PEs on this node connecting to this channel, then VprId will be 65534. If, for some reason, no PE on this node connects to this channel, VprId will be 65535. HstId n/a Identifies the host. Value is BBMMPPHHH (BB = Bus, MM = Module Number (or chassis number), PP = Port, HHH = three digit Host Group ID) with each field getting two or three decimal digits of the resulting 9 digit value. The chassis number is always 0 for network-connected hosts. In Summary Mode, HstId is always 0. INTEGER HstType n/a Type of host. Possible values are “NETWORK” (LAN-connected host) and “IBMMUX” (channelconnected host). CHAR(8) Secs n/a Actual number of seconds in the log period represented by this row. This value is useful for normalizing the statistics contained in this row, for example, to a per-second measurement. SMALLINT CentiSecs n/a Number of centiseconds in the logging period. This field is useful when performing data calculations with small elapsed times where the difference between centisecond-based data and whole seconds results in a percentage error. INTEGER NominalSecs n/a A specified or nominal number of seconds in the logging period. SummaryFlag n/a Identifies the summarization status of this row. If the value is ‘N,’ the row is a non-summary row. If the value is ‘S,’ the row is a summary row. SMALLINT CHAR In Summary Mode, the rows are summarized into a single row. For details, see “Summary Mode” on page 83. 80 Resource Usage Macros and Tables Chapter 8: ResUsageShst Table Column Name Type of Data Active count Description Data Type Controls whether or not the rows will be logged to the ResUsage tables if Active Row Filter Mode is enabled. FLOAT Invalid Platform If Active is set to: • a non-zero value, then the row contains modified data columns. • a zero value, then none of the data columns in the row have been updated during the logging period. For example, if Active Row Filter Mode is enabled, then the rows that have a zero Active field value will not be logged to the ResUsage tables. CollectIntervals n/a The number of gather periods per reporting period. SMALLINT In the Collect Buffer and Log Buffer, the value is the number of Gather operations that have been performed during the period. This number can vary from one period to the next. STATISTICS COLUMNS HOST CONTROLLER COLUMNS Channel Traffic Columns Identify the traffic between the host and the node in three levels of granularity: blocks, messages and KBs. Blocks are made up of some amount of variable sized messages. ReadKB and WriteKB identify the KBs involved in the traffic. HostBlockReads count Number of blocks read in from the host. FLOAT HostBlockWrites count Number of blocks written out to the host. FLOAT HostMessageReads count Number of messages read in from the host. FLOAT HostMessageWrites count Number of messages written out to the host. FLOAT HostReadKB count KBs transferred in from the host. FLOAT HostWriteKB count KBs transferred out to the host. FLOAT Resource Usage Macros and Tables 81 Chapter 8: ResUsageShst Table Column Name Type of Data Description Data Type Invalid Platform FLOAT ALL ALL Channel Management Columns Identify overhead of channel management. HostQLenSum count Total number of messages queued for output to the host during each log interval. Note: To calculate the average HostQLen divide the HostQLenSum by the CollectIntervals value to get the HostQLen average value during the logging period. This average is an average of the values recorded at each of the gather periods that occur during the logging period. HostQLenMax max Maximum number of messages queued in each log interval. FLOAT HostReadFails count Number of failures transmitting from the host. FLOAT Note: This is for Teradata Channel software (TCHN) only. HostWriteFails count Number of failures transmitting to the host. FLOAT Note: This is for TCHN only. User Commands Columns Identify the type of commands given to Teradata Database by the user. Three levels of granularity are given: transaction, request, and statement. Transactions consist of one or more requests. Requests consist of one or more statements. Statements are subdivided into the various statement types. CmdTransactions count Number of transaction commands. FLOAT CmdRequests count Number of request commands. FLOAT CmdAlterStmts count Number of alter, modify, or drop statement commands. FLOAT CmdCreateStmts count Number of create or replace statement commands. FLOAT CmdDeleteStmts count Number of delete commands. FLOAT CmdGrantStmts count Number of grant or revoke commands. FLOAT CmdInsertStmts count Number of insert commands. FLOAT CmdSelectStmts count Number of select commands. FLOAT CmdUpdateStmts count Number of update commands. FLOAT CmdArchUtilityStmts count Number of archival utility commands (for example, dump, restore, archive and recovery). FLOAT CmdLoadUtilityStmts count Number of FastLoad and MultiLoad utility commands. (Tpump commands cannot be distinguished, and are therefore counted by the INSERT, UPDATE and DELETE statements). FLOAT 82 Resource Usage Macros and Tables Chapter 8: ResUsageShst Table Summary Mode Column Name Type of Data Description Data Type Invalid Platform CmdMiscUtilityStmts count Number of miscellaneous utility commands. FLOAT ALL CmdOtherStmts count Number of other commands. FLOAT User Command Arrival and Departure Columns Identify the arrival and departure times and status of user commands. CmdStmtsInProgMax max Maximum number of statements in progress during each log interval. FLOAT ALL CmdStmtsInProgSum count Total count of statements in progress during each log interval. FLOAT ALL Note: To calculate the average number of statements in progress, divide this value by the CollectIntervals value. The CollectIntervals value is the number of gather periods per reporting period. For more information, see the CollectIntervals column. CmdStmtSuccesses count Number of statements that departed normally. FLOAT CmdStmtFailures count Number of statements that departed in failure or abortion. FLOAT CmdStmtErrors count Number of statements that departed in error. FLOAT CmdStmtTime count The sums of the resident time of each statement in progress during the log period, including the successes and failures. FLOAT ALL Summary Mode When Summary Mode is active for the ResUsageShst table, one row is written to the database for each type of host (network or channel-connected) on each node in the system, summarizing the hosts of that type on that node, for each log interval as follows: You can determine if a row is in Summary Mode by checking the SummaryFlag column for that row. IF the SummaryFlag column value is… THEN the data for that row is being logged… ‘S’ in Summary Mode. ‘N’ normally. Resource Usage Macros and Tables 83 Chapter 8: ResUsageShst Table Spare Columns Spare Columns The ResUsageShst table has 30 spare columns (one of which is being used) as shown in the table below. Column Name Type of Data Description SpareCount[00-09] count Spare counted statistic. SpareTrack[00-09] track Spare tracked statistic. SpareTmon00 n/a The SpareTmon00 field contains the Capacity on Demand (COD) value. The value represents the COD value in one tenths of a percent, so a displayed value of 500 represents a COD value of 50.0%. Note: This value is valid only on SUSE Linux Enterprise Server 10 systems and is a single value for the entire system. SpareTmon[01-09] count Spare time monitored statistic. The spare column fields expand to values 00-09, so that column names would be SpareCount00, SpareTrack04, SpareTmon01, and so on. 84 Resource Usage Macros and Tables CHAPTER 9 ResUsageSldv Table The ResUsageSldv table contains resource usage information for system-wide, logical device information. Statistics from this table are collected from the storage devices. Note: This table is created as a MULTISET table. For more information see “Relational Primary Index” on page 38. The following table describes the ResUsageSldv table columns. Column Name Type of Data Description Data Type Invalid Platform HOUSEKEEPING COLUMNS RELATIONAL PRIMARY INDEX COLUMNS These columns taken together form the nonunique primary index. TheDate n/a Date of the log entry. DATE TheTime n/a Nominal time of the log entry. FLOAT Note: Under conditions of heavy system load, entries may be logged late (typically, by no more than one or two seconds), but this field will still contain the time value when the entry should have been logged. See the Secs and NominalSecs columns. NodeId n/a Identifies the Node upon which the vproc resides. The Node ID is formatted as CCC-MM, where CCC denotes the three-digit cabinet number and MM denotes the two-digit chassis number of the node. For example, a node in chassis 9 of cabinet 3 has a node ID of ‘003-09’. INTEGER Note: SMP nodes have a chassis and cabinet number of 1. For example, the node ID of an SMP node is ‘001-01’. MISCELLANEOUS HOUSEKEEPING COLUMNS GmtTime n/a Greenwich Mean Time is not affected by the Daylight Savings Time adjustments that occur twice a year. FLOAT NodeType n/a Type of node, representing the per node system family type. For example, 5600C or 5555H. CHAR(8) Resource Usage Macros and Tables 85 Chapter 9: ResUsageSldv Table Column Name Type of Data VprId CtlId Description Data Type n/a Note: This column is obsolete in normal mode and the value is set to 65535. INTEGER n/a Represents the controller number. INTEGER Invalid Platform The value is the decimal equivalent of the three digit controller ID in the LdvId. The maximum controller ID is 255 decimal. This allows the storage devices to be grouped by CtlId for controller based summarization. If the controller information is not available, its value is set to 255. In summary mode, the CtlId is set to 255. LdvId n/a Represents the storage device in the Bus System where it resides. The value in LdvId is -1. BYTE(4) Note: For Linux, the LdvId is derived from the Host, Channel, Id, and Lun information of the device. For Windows, the LdvId is derived from the Port number, path Id, target Id, and Lun information of the device. If the device address information is not available, this field contains the device major and minor number. LdvType n/a Type of logical device. The value is either DISK for database disk or SDSK for system disk. CHAR(4) Secs n/a Actual number of seconds in the log period represented by this row. Normally the same as NominalSecs, but can be different in three cases: SMALLINT • The first interval after a log rate change • A sample logged late because of load on the system • System clock adjustments affect reported Secs Useful for normalizing the count statistics contained in this row, for example, to a per-second measurement. CentiSecs n/a Number of centiseconds in the logging period. This field is useful when performing data calculations with small elapsed times where the difference between centisecond-based data and whole seconds results in a percentage error. INTEGER NominalSecs n/a A specified or nominal number of seconds in the logging period. SMALLINT 86 Resource Usage Macros and Tables Chapter 9: ResUsageSldv Table Column Name Type of Data SummaryFlag n/a Description Data Type Identifies the summarization status of this row. If the value is ‘N,’ the row is a non-summary row. If the value is ‘S,’ the row is a summary row. CHAR Invalid Platform In Summary Mode, the rows are summarized into a single row. For details, see “Summary Mode” on page 88. Active count Controls whether or not the rows will be logged to the ResUsage tables if Active Row Filter Mode is enabled. FLOAT If Active is set to: • A non-zero value, then the row contains modified data columns. • A zero value, then none of the data columns in the row have been updated during the logging period. For example, if Active Row Filter Mode is enabled, then the rows that have a zero Active field value will not be logged to the ResUsage tables. CollectIntervals n/a Number of gather periods per reporting period. SMALLINT In the Collect Buffer and Log Buffer, the value is the number of Gather operations that have been performed during the period. This number can vary from one period to the next. STATISTICS COLUMNS LOGICAL DEVICE COLUMNS Input and Output Traffic Columns The following columns represent the number and amount, in KBs, of data read and written to the logical device. LdvReads count Number of reads issued. FLOAT LdvWrites count Number of writes issued. FLOAT LdvReadKB count The number of KBs (1024) read from the logical device. FLOAT LdvWriteKB count The number of KBs (1024) written to the logical device. FLOAT LdvReadRespMax max Contains the maximum of the total read response time in centiseconds. (Note that this is not the maximum of individual read I/O response times.) FLOAT ALL LdvWriteRespMax max Contains the maximum of the total write response times in centiseconds (Note that this is not the maximum of individual write I/O response times.) FLOAT ALL Resource Usage Macros and Tables 87 Chapter 9: ResUsageSldv Table Summary Mode Column Name Type of Data Description Data Type Invalid Platform FLOAT Windows FLOAT Windows Response Time Columns The following columns represent the response time to requests given to the logical device. LdvReadRespTot count Linux: Total of individual read response times in centiseconds. Windows: 0. LdvWriteRespTot count Linux: Total of individual write response times in centiseconds. Windows: 0. ReadActiveTotal count Total of read I/O active time in centiseconds. FLOAT ALL WriteActiveTotal count Total of write I/O active time in centiseconds. FLOAT ALL Concurrent Operations Columns The following columns represent the number of concurrent operations performed on the logical device at a time. LdvConcurrentMax max Maximum number of concurrent requests during the log period. Default value is always 0. FLOAT ALL Note: Do not use this field for any platform. Outstanding Requests Columns The following columns represent the number of outstanding operation requests and the amount of time with outstanding requests for the logical device. QReadLength count Number of read operations in queue. FLOAT ALL QWriteLength count Number of write operations in queue. FLOAT ALL LdvOutReqSum count Sum of the average of queued requests at each gather period. FLOAT To estimate an average value over the report period, divide LdvOutReqSum by the CollectIntervals column. LdvOutReqAvg = LdvOutReqSum / CollectIntervals. LdvOutReqMax max Maximum value of the LdvOutReqSum field. FLOAT LdvOutReqTime count Total time in centiseconds with (any) outstanding requests. The values in this field should be less than or equal to the reported logging period. FLOAT ALL Summary Mode When Summary Mode is active for the ResUsageSldv table, the following rows are written to the database for each node in the system for each log interval: 88 Resource Usage Macros and Tables Chapter 9: ResUsageSldv Table Spare Columns • One row summarizes the system logical devices • One row summarizes Teradata Database logical devices Also, you can determine if a row is in Summary Mode by checking the SummaryFlag column for that row. IF the SummaryFlag column value is… THEN the data for that row is being logged… ‘S’ in Summary Mode. ‘N’ normally. Spare Columns The ResUsageSldv table has nine spare columns (one of which is being used) as shown in the table below. Column Name Type of Data Description SpareCount[00-02] count Spare counted statistic. SpareTrack[00-02] track Spare tracked statistic. SpareTmon00 n/a The SpareTmon00 field contains the COD value. The value represents the COD value in one tenths of a percent, so a displayed value of 500 represents a COD value of 50.0%. Note: This value is valid only on SUSE Linux Enterprise Server 10 systems and is a single value for the entire system. SpareTmon[01-02] count Spare time monitored statistic. The spare column fields expand to values 00-02, so that column names would be SpareCount00, SpareCount01, SpareCount02, SpareTrack00, and so on. Resource Usage Macros and Tables 89 Chapter 9: ResUsageSldv Table Spare Columns 90 Resource Usage Macros and Tables CHAPTER 10 ResUsageSpdsk Table The ResUsageSpdsk table: • Provides pdisk level statistics. • Includes resource usage logs on cylinder I/O, allocation, and migration. Note: This table is created as a MULTISET table. For more information see “Relational Primary Index” on page 38. The following table describes the ResUsageSpdsk table columns. Column Name Type of Data Description Data Type Invalid Platform HOUSEKEEPING COLUMNS RELATIONAL PRIMARY INDEX COLUMNS These columns taken together form the nonunique primary index. TheDate n/a Date of the log entry. DATE TheTime n/a Nominal time of the log entry. FLOAT Note: Under conditions of heavy system load, entries may be logged late (typically, by no more than one or two seconds), but this field will still contain the time value when the entry should have been logged. See the Secs and NominalSecs columns. NodeId n/a Identifies the Node upon which the pdisk is connected. The Node ID is formatted as CCC-MM, where CCC denotes the threedigit cabinet number and MM denotes the two-digit chassis number of the node. For example, a node in chassis 9 of cabinet 3 has a node ID of ‘003-09’. INTEGER Note: SMP nodes have a chassis and cabinet number of 1. For example, the node ID of an SMP node is ‘001-01’. MISCELLANEOUS HOUSEKEEPING COLUMNS GmtTime Resource Usage Macros and Tables n/a Greenwich Mean Time is not affected by the Daylight Savings Time adjustments that occur twice a year. FLOAT 91 Chapter 10: ResUsageSpdsk Table Column Name Type of Data PdiskGlobalId n/a Description Data Type Identifies the pdisk in the system. Each pdisk in the system has a global ID which uniquely identifies the pdisk in the system. If a pdisk is connected to the nodes in a clique, all the nodes in that clique see the same pdisk global ID associated with that pdisk. INTEGER Invalid Platform In Summary Mode, the pdisk global ID is -1. PdiskType n/a Type of pdisk. The pdisk can be one of the following: CHAR(4) • DISK: This type of pdisk is a storage device. • FILE: This type of pdisk is a file. PdiskDeviceId n/a Identifies the local pdisk device. BYTE(4) For DISK pdisk, the pdisk device ID can be one of the following: • Linux: This is the pdisk major/minor number. The major number bit positions are 20-31 and the minor number is in bits 0-19. The format is similar to the one shown below. (MMMM MMMM MMMM mmmm mmmm mmmm mmmm mmmm) • Windows: This is the pdisk physical disk/ partition number. The physical disk number is in the lower 16 bits and the partition number is in the upper 16 bits. The format is similar to the one shown below. (0000 0000 0000 PPPP DDDD DDDD DDDD DDDD) For FILE pdisk, the pdisk device ID is -1. In Summary Mode, the pdisk device ID is -1. NodeType 92 n/a Type of node, representing the per node system family type. For example, 5600C or 5555H. CHAR(8) Resource Usage Macros and Tables Chapter 10: ResUsageSpdsk Table Column Name Type of Data Secs n/a Description Data Type Actual number of seconds in the log period represented by this row. Normally the same as NominalSecs, but can be different in three cases: SMALLINT Invalid Platform • The first interval after a log rate change • A sample logged late because of load on the system • System clock adjustments affect reported Secs Useful for normalizing the count statistics contained in this row, for example, to a persecond measurement. CentiSecs n/a Number of centiseconds in the logging period. This field is useful when performing data calculations with small elapsed times where the difference between centisecondbased data and whole seconds results in a percentage error. INTEGER NominalSecs n/a A specified or nominal number of seconds in the logging period. SMALLINT SummaryFlag n/a Identifies the summarization status of this row. If the value is ‘N,’ the row is a nonsummary row. If the value is ‘S,’ the row is a summary row. CHAR In Summary Mode, the rows are summarized into a single row per pdisk type per node. For details, see “Summary Mode” on page 97. Active count Controls whether or not the rows will be logged to the ResUsage tables if Active Row Filter Mode is enabled. FLOAT If Active is set to: • a non-zero value, then the row contains modified data columns. • a zero value, then none of the data columns in the row have been updated during the logging period. For example, if Active Row Filter Mode is enabled, then the rows that have a zero Active field value will not be logged to the ResUsage tables. Resource Usage Macros and Tables 93 Chapter 10: ResUsageSpdsk Table Column Name Type of Data CollectIntervals n/a Description Data Type The number of gather periods per reporting period. SMALLINT Invalid Platform In the Collect Buffer and Log Buffer, the value is the number of Gather operations that have been performed during the period. This number can vary from one period to the next. STATISTICS COLUMNS I/O Statistics Columns These columns identify the I/O statistics reported by the Extent Driver. ReadCnt count Number of logical device reads. FLOAT WriteCnt count Number of logical device writes. FLOAT ReadKB count Number of KBs (1024 bytes) read from the logical device. FLOAT WriteKB count Number of KBs (1024 bytes) written to the logical device. FLOAT ReadRespTot count Total of individual read response time in centiseconds. FLOAT WriteRespTot count Total of individual write response time in centiseconds. FLOAT ReadRespMax max Maximum number of individual read response time in centiseconds. FLOAT WriteRespMax max Maximum number of individual write response time in centiseconds. FLOAT ReadRespSq count Total of squares of the individual read response time in centiseconds. FLOAT WriteRespSq count Total of squares of the individual write response time in centiseconds. FLOAT ConcurrentReadMax max Maximum number of concurrent read I/O requests. FLOAT ConcurrentWriteMax max Maximum number of concurrent write I/O requests. FLOAT ConcurrentMax count Maximum number of concurrent I/O requests. FLOAT OutReqTime count Time with outstanding requests (busy time), in centiseconds. FLOAT 94 ALL Resource Usage Macros and Tables Chapter 10: ResUsageSpdsk Table Column Name Type of Data MigrationBlockedIos count Description Data Type Number of inputs and outputs that are blocked due to migration request. FLOAT Invalid Platform Allocation Columns These columns identify the allocation statistics reported by the Allocator process of the VSS vproc. Note: These columns are populated and used by Teradata VS, an option sold separately from Teradata Database. For detail s about these columns, see Teradata Virtual Storage. Migration Columns The following columns identify the number of cylinders that migrated to a different location on a device as well as the time, in centiseconds, of all migration I/Os used, incurred, or saved during the log period. Note: Each allocation is for a cylinder size worth of data, also known internally in the allocator as an extent. Thus the column names begin with Ext for extent. ExtMigrateFaster count Number of cylinders migrated to a faster location on a device. This count is for cylinders that were allocated on this device and migrated to a different location within the same device or migrated to a completely different device. FLOAT The following formula calculates a ExtMigrateSlower value, which is the number of cylinders migrated to slower locations: Migrate Slower = ExMigrateTotal ExMigrateFaster. ExtMigrateTotal count Total number of cylinders migrated to a different physical location. For more information, see the ExtMigrateFaster field. FLOAT ExtMigrateReadRespTot count Migration read I/O response time. FLOAT ExtMigrateWriteRespTot count Migration write I/O response time. FLOAT ExtMigrateIOTimeCost count Estimates the total cost (in centiseconds) incurred by migration I/Os completing during the log period, where cost is the extra time waited by all non-migration I/Os as a result of the migration I/O. The Migrator estimates migration costs. FLOAT Note: This field is for internal use only. Do not use this field unless directed by Teradata Support Center. Resource Usage Macros and Tables 95 Chapter 10: ResUsageSpdsk Table Column Name Type of Data ExtMigrateIOTimeBenefit count Description Data Type Estimates the total I/O time savings achieved by migrations completing in the log period. The I/O time savings include the improvement in response time caused by the new data arrangement up to the time horizon. ExtMigrateIOTimeBenefit does not include the cost of the migration I/Os and is a gross benefit, not a net benefit. The Migrator estimates the migration benefit. FLOAT Invalid Platform Note: This field is for internal use only. Do not use this field unless directed by Teradata Support Center. ExtMigrateIOTimeImprove count Estimates the percent improvement in average I/O response time due to migrations completing in the log interval. In theory, this percentage improvement is permanent. For example, if, right before a particular log interval, the average I/O response time was 10 milliseconds (ms), then the Migration logs an ExtMigrateIOTimeImprove value of 10% in this interval. The average IO response time after the log interval should be (100%10%)*10ms = 9ms. Migration then logs an ExtMigrateIOTimeImprove of 1% in the next interval. The average I/O response time in the new log interval is (100%-1%)*9ms = 8.91ms. FLOAT ExtMigrateIOTimeImprove is only an estimate. Its permanent improvement remains in effect as long as the workload does not change and newer migrations do not significantly alter the data arrangement. When the workload changes or new migrations affect data arrangement, response time changes in an un-quantified way. Despite this, ExtMigrateIOTimeImprove is useful because it predicts actual system performance at least for short periods of time and can be used to understand why the migration algorithm is doing what it is doing. Note: This field is for internal use only. Do not use this field unless directed by Teradata Support Center. 96 Resource Usage Macros and Tables Chapter 10: ResUsageSpdsk Table Summary Mode Summary Mode When Summary Mode is active for the ResUsageSpdsk table, rows are summarized into a single row for each pdisk type (for example, DISK or FILE) for each node in the system per log interval. You can determine if a row is in Summary Mode by checking the SummaryFlag column for that row. IF the SummaryFlag column value is… THEN the data for that row is being logged… ‘S’ in Summary Mode. ‘N’ normally. Spare Columns The ResUsageSpdsk table has 30 spare columns (one of which is being used) as shown in the table below. Column Name Type of Data Description SpareCount[00-09] count Spare counted statistic. SpareTrack[00-09] track Spare tracked statistic. SpareTmon00 n/a The SpareTmon00 field contains the COD value. The value represents the COD value in one tenths of a percent, so a displayed value of 500 represents a COD value of 50.0%. Note: This value is valid only on SUSE Linux Enterprise Server 10 systems and is a single value for the entire system. SpareTmon[01-09] count Spare time monitored statistic. The spare column fields expand to values 00–09, so that column names would be SpareCount00, SpareTrack02, SpareTmon05 and so on. Resource Usage Macros and Tables 97 Chapter 10: ResUsageSpdsk Table Spare Columns 98 Resource Usage Macros and Tables CHAPTER 11 ResUsageSps Table The ResUsageSps table contains data by Performance Group from the Priority Scheduler. It allows you to see accumulated CPU, number of active processes, and other detail by Priority Scheduler Allocation Group. The ResUsageSps table carries information that is similar to what is displayed in Priority Scheduler monitor output. Information carried in the table is organized by: • Collection date/time • Node • Vproc Type • Performance Group • Performance Period/Allocation Group For those using Teradata ASM, each Workload Definition is the equivalent of one Performance Group in ResUsageSps. For a complete description of the Priority Scheduler and its components, see "Priority Scheduler (schmon)" chapter in Utilities. If table logging is enabled on ResUsageSps, a row is written to the database once for every triplet of Vproc Type, Performance Group ID, and Performance Period ID (VprType, PGId, PPId) in the system for each log interval. Note: This table is created as a MULTISET table. The following table describes the ResUsageSps table columns. Column Name Type of Data Description Data Type Invalid Platform HOUSEKEEPING COLUMNS RELATIONAL PRIMARY INDEX COLUMNS These columns taken together form the nonunique primary index. TheDate n/a Date of the log entry. DATE TheTime n/a Nominal time of the log entry. FLOAT Note: Under conditions of heavy system load, entries may be logged late (typically, by no more than one or two seconds), but this column will still contain the time value when the entry should have been logged. See the Secs and NominalSecs columns. Resource Usage Macros and Tables 99 Chapter 11: ResUsageSps Table Column Name Type of Data NodeId n/a Description Data Type Identifies the Node upon which the vproc resides. The Node ID is formatted as CCCMM, where CCC denotes the three-digit cabinet number and MM denotes the twodigit chassis number of the node. For example, a node in chassis 9 of cabinet 3 has a node ID of ‘003-09’. INTEGER Invalid Platform Note: SMP nodes have a chassis and cabinet number of 1. For example, the node ID of an SMP node is ‘001-01’. MISCELLANEOUS HOUSEKEEPING COLUMNS GmtTime n/a Greenwich Mean Time is not affected by the Daylight Savings Time adjustments that occur twice a year. FLOAT NodeType n/a Type of node, representing the per node system family type. For example, 5600C or 5555H. CHAR(8) VprId n/a Note: This column is obsolete. INTEGER Identifies the vproc number. Multiple Vprocs contribute to each Performance Group task. The VprId value is -1. PPId n/a Identifies the performance period. The PPId is a mapping of the internal performance period value (ranges 0 to 7) to a RSS value (ranges 0 to 1). A PPId of 0 maps to the value 0, and the PPId of 1 maps to the values 1 through 7. BYTEINT The PPId column allows RSS to log two rows for each node, VprType, and PGId set when a PGId uses more than one AGId during a logging period. See the AGId column for more information. VprType 100 n/a Type of vproc (for example, AMP, PE, and MISC). CHAR(4) Resource Usage Macros and Tables Chapter 11: ResUsageSps Table Column Name Type of Data PGId n/a Description Data Type Identifies the Performance Group. There is a one to one mapping between a Performance Group ID and a Workload Definition ID at any point in time. The Performance Group ID value ranges from 0 to 250, while the value of a Workload Definition ID is not in a specific range (that is, the value is incremented and not reused). SMALLINT Invalid Platform The mapping between Performance Group ID and Workload Definition ID can be determined by looking at the Teradata Viewpoint Workload Designer portlet or the TDWM.WlcPerfGroupMappings table. WDId track Workload Definition ID number. FLOAT 0 indicates there is no WDId associated with the PG. Use this column to obtain the WD name from the WLcdefs table of the Teradata Dynamic Workload Management database by joining ResUsageSps.WDId with WLcdefs.WlcId. The resulting join table outputs the WD name from WLcdefs.WlcName field. Secs n/a Actual number of seconds in the log period represented by this row. Normally the same as NominalSecs, but can be different in three cases: SMALLINT • The first interval after a log rate change • A sample logged late because of load on the system • System clock adjustments affect reported Secs Useful for normalizing the count statistics contained in this row, for example, to a persecond measurement. CentiSecs n/a Number of centiseconds in the logging period. This column is useful when performing data calculations with small elapsed times where the difference between centisecond-based data and whole seconds results in a percentage error. INTEGER NominalSecs n/a A specified or nominal number of seconds in the logging period. SMALLINT Resource Usage Macros and Tables 101 Chapter 11: ResUsageSps Table Column Name Type of Data Description Data Type NCPUs n/a Number of CPUs on this node. SMALLINT Invalid Platform This column is useful for normalizing the CPU utilization column values for the number of CPUs on the node. This is especially important in coexistence systems where the number of CPUs can vary across system nodes. SummaryFlag n/a Identifies the summarization status of this row. If the value is ‘N,’ the row is a nonsummary row. If the value is ‘S,’ the row is a summary row. CHAR Active count Controls whether or not the Performance Group ID rows will be logged to the ResUsage tables when Active Row Filter Mode is enabled. FLOAT If Active is set to: • A non-zero value, then the Performance Group ID row contains modified data columns. • A zero value, then none of the data columns in the Performance Group ID row have been updated during the logging period. CollectIntervals n/a The number of gather periods per reporting period. SMALLINT In the Collect Buffer and Log Buffer, the value is the number of Gather operations that have been performed during the period. This number can vary from one period to the next. 102 Resource Usage Macros and Tables Chapter 11: ResUsageSps Table Column Name Type of Data Description Data Type Invalid Platform STATISTICS COLUMNS TERADATA ASM COLUMNS Priority Scheduler Columns The following columns provide a summary of the Priority Scheduler resource usage statistics. AGId track Identifies the current Allocation Group for the Performance Group ID that is being reported. This value can be any number from 0 to 200. FLOAT Note: A value of 200 is the system Allocation Group. This value cannot be assigned for user work. For more information on the Allocation Group (AG), see "Priority Scheduler (schmon)" chapter in Utilities. RelWgt track Weight of the Allocation Group relative to the active Allocation Groups of the Resource Partition and the active Resource Partitions. FLOAT Note: Allocation Groups with higher relative weights will have quicker access to system resources. For more information on allocation group weights, see the "Priority Scheduler (schmon)" chapter in Utilities. CPUTime count Milliseconds of CPU time consumed by all tasks that have the same VprType, PGId, and PPId values for a reporting period. FLOAT IOBlks count Number of logical data blocks read or written by Performance Group, or both. FLOAT NumProcs track Number of tasks assigned to the Performance Group at the end of the gather period. FLOAT NumSets track Number of Scheduling Sets per PG/PPid combination. There is one Scheduling Set per session. FLOAT NumRequests count Number of AWT messages/requests that got assigned AWTs to them on the node. FLOAT Resource Usage Macros and Tables 103 Chapter 11: ResUsageSps Table Column Name Type of Data QWaitTime count Description Data Type Total time for all messages delivered in the period (if not delivered then not counted). FLOAT Invalid Platform Divide by NumRequests to obtain the average QwaitTime per Request. This column is reported in DBC.ResSpsView as QWaitTimeRequestAvg. QWaitTimeMax max Maximum time in milliseconds that work requests waited on an input queue before being serviced. FLOAT QLength count Sum of the average number of work requests waiting on the input queue for service. FLOAT This value is derived each gather period from QWaitTime by dividing by the sample period and rounding the value. The values from each gather period are then summed together. To use this column, always divide QLength by the AMPcount to get the desired average Qlength per AMP. The Average number of work requests waiting on the input queue for service = QLength /(CollectIntervals * SpareTrack00). QLengthMax max Maximum number of work requests waiting on the input queue for service. FLOAT This value is derived by dividing the number of AMPs to display the maximum per AMP average number of work requests waiting on the input queue for service = QLengthMax / SpareTrack00. ServiceTime count FLOAT Time in milliseconds that work requests required for service. To calculate an approximate average ServiceTime for each request during this period, divide ServiceTime by NumRequests. The service time is the elapsed time from the time the message was received to the time the AWT was released. This is the amount of time the AWT was held through sleeps, CPU, I/O, and so on until it is released. ServiceTimeMax 104 max Maximum time in milliseconds that work requests required for service. FLOAT Resource Usage Macros and Tables Chapter 11: ResUsageSps Table Column Name Type of Data Description Data Type Invalid Platform PROCESS SCHEDULING COLUMNS CPU Utilization Columns The following columns represent CPU activities on the node associated with the AWT, Dispatcher, Parser, or miscellaneous things. CPUUServAWT count Time in milliseconds CPUs are busy in the AWT executing user service code. This is the system level time spent on a process. FLOAT CPUUServDisp count Time in milliseconds CPUs are busy in the Dispatcher or Parser executing user service code. This is the system level time spent on a process. FLOAT CPUUServPars count Time in milliseconds CPUs are busy in the Parser executing user service code. This is the system level time spent on a process. FLOAT CPUUServMisc count Time in milliseconds CPUs are busy executing miscellaneous activities for user service code. This is the system level time spent on a process. FLOAT CPUUExecAWT count Time in milliseconds CPUs are busy in the AWT executing user execution code. This is the user level time spent on a process. FLOAT CPUUExecDisp count Time in milliseconds CPUs are busy in the Dispatcher or Parser executing user execution code. This is the user level time spent on a process. FLOAT CPUUExecPars count Time in milliseconds CPUs are busy in the Parser executing user execution code. This is the user level time spent on a process. FLOAT CPUUExecMisc count Time in milliseconds CPUs are busy executing miscellaneous activities for user execution code. This is the user level time spent on a process. FLOAT ALL ALL FILE SYSTEM COLUMNS Cylinder Read Columns The following columns represent file system resource usage statistics. The Cylinder Read feature uses these statistics for tracking performance and utilization. Resource Usage Macros and Tables 105 Chapter 11: ResUsageSps Table Column Name Type of Data FileFcrRequests count Description Data Type Invalid Platform Total number of requests for the File System to use Cylinder Read. FLOAT ALL FLOAT ALL FLOAT ALL This column is tracked and recorded by the File System. It records the number of attempts to use Cylinder Read independent of whether the request will be issued to FSG or not. A request can be denied due to insufficient data blocks or because there is insufficient space in the FSG cache. Requests can also be denied at both the user and kernel level. Each of these items is counted in other FileFcr ResUsage columns. A number of calculations can be performed using this column: • Requests issued to FSG = FileFcrRequests - FileFcrDeniedUser • Successful Cylinder Reads = FileFcrRequests - FileFcrDeniedUser FileFcrDeniedKern FileFcrRequestsAdaptive count Number of adaptive requests from File System. This column is tracked and recorded by the File System. It records the number of requests for adaptive-style Cylinder Reads. FileFcrBlocksRead count Number of data blocks read in using Cylinder Read. This column is tracked and recorded by the FSG subsystem. It records the total number of data blocks read in by successful Cylinder Read operations. The average number of data blocks in a successful Cylinder read can be calculated as: Average data blocks/ Cylinder Read = FileFcrBlocksRead / (FileFcrRequests FileFcrDeniedUser - FileFcrDeniedKern) 106 Resource Usage Macros and Tables Chapter 11: ResUsageSps Table Column Name Type of Data FileFcrBlocksDeniedUser count Description Data Type Invalid Platform Number of data blocks in the Cylinder Read requests denied by the File System. FLOAT ALL FLOAT ALL FLOAT ALL This column is tracked and recorded by the File System. It records the number of Cylinder Read attempts that have been denied by the File System. A request can be denied by the File System due to insufficient number of data blocks being requested (for example, the FileFcrDeniedThreshUser column). For information, see the FileFcrDeniedThreshUser column. FileFcrBlocksDeniedKern count Number of data blocks in the Cylinder Read requests denied by the FSG subsystem. This column is tracked and recorded by the FSG subsystem. It records the number of Cylinder Read requests issued to the FSG subsystem which, for any reason, have been denied. A request can be denied due to insufficient data blocks (for example, the FileFcrDeniedThreshKern column) or because there is insufficient space in the FSG cache (for example, the FileFcrDeniedCache column). The FSG subsystem can reject a request containing insufficient data blocks that the File System thought had enough blocks because the FSG subsystem reduces the count by the number of data blocks that are already resident in the cache. FileFcrBlocksDeniedCache count Number of data blocks in the Cylinder Read requests rejected by the FSG subsystem due to insufficient cache. This column is tracked and recorded by the FSG subsystem. It records the number of data blocks that were part of attempts to use Cylinder read that were denied by the FSG subsystem due to insufficient cache space; therefore, also incremented the FileFcrDeniedCache column. Resource Usage Macros and Tables 107 Chapter 11: ResUsageSps Table Column Name Type of Data FileFcrBlocksDeniedThreshUser count Description Data Type Invalid Platform Number of data blocks in the Cylinder Read requests denied by the File System due to insufficient data blocks. FLOAT ALL FLOAT ALL FLOAT ALL This column is tracked and recorded by the File System. It records the number of Cylinder Read requests which have been denied due to the data block threshold criteria. There is a minimum threshold of data blocks for an individual Cylinder Read request. If the number of data blocks is below this threshold, the overhead of the Cylinder Read operation is considered too large and issuing individual data block reads is considered more efficient. Therefore, the Cylinder Read request is denied. FileFcrDeniedUser count Number of Cylinder Read requests denied by the File System. This column is tracked and recorded by the File System. It records the number of Cylinder Read attempts that have been denied by the File System. A request can be denied by the File System due to insufficient number of data blocks being requested (for example, the FileFcrDeniedThreshUser column). For information, see the FileFcrDeniedThreshUser column description. FileFcrDeniedKern count Number of Cylinder Read requests denied by the FSG subsystem. This column is tracked and recorded by the FSG subsystem. It records the number of Cylinder Read requests issued to the FSG subsystem which, for any reason, have been denied. A request can be denied due to insufficient data blocks (for example, the FileFcrDeniedThreshKern column) or because there is insufficient space in the FSG cache (for example, the FileFcrDeniedCache column). The FSG subsystem can reject a request containing insufficient data blocks that the File System thought had enough blocks because the FSG subsystem reduces the count by the number of data blocks that are already resident in the cache. 108 Resource Usage Macros and Tables Chapter 11: ResUsageSps Table Column Name Type of Data FileFcrDeniedCache count Description Data Type Invalid Platform Number of Cylinder Read requests denied by FSG due to insufficient cache. FLOAT ALL FLOAT ALL This column is tracked and recorded by FSG. It records the number of Cylinder Read requests which have been denied due to insufficient FSG cache space for a cylinders worth of data. FileFcrDeniedThreshUser count Number of Cylinder Read requests denied by the File System due to insufficient data blocks. This column is tracked and recorded by the File System. It records the number of Cylinder Read requests which have been denied due to the data block threshold criteria. There is a minimum threshold of data blocks for an individual Cylinder Read request. If the number of data blocks is below this threshold, the overhead of the Cylinder Read operation is considered too large and issuing individual data block reads is considered more efficient. Therefore, the Cylinder Read request is denied. Segment Acquires Columns The following columns identify the total disk memory segments acquired by the file system during the log period. Logical acquires (Acqs) and the logical amount acquired (AcqKB) are described by single entries below, each of which expands into six actual columns, where [seg] is replaced as follows: PDb = Permanent data block disk segments PCi = Permanent cylinder index disk segments SDb = Regular or restartable spool data block disk segments SCi = Regular or restartable spool index disk segments Tjt = Transient journal table APt = Append table or permanent journal table data block or cylinder index disk segments File[seg]Acqs count Total number of disk segments acquired. FLOAT File[seg]AcqKB count Total KBs acquired by File[seg]Acqs. FLOAT File[seg]AcqReads count Number of disk segment acquires that caused a physical read. FLOAT File[seg]AcqReadKB count KBs physically read by File[seg]AcqReads. FLOAT Resource Usage Macros and Tables 109 Chapter 11: ResUsageSps Table Column Name Type of Data Description Data Type Invalid Platform Data Block Prefetches Columns The following columns identify File Segment Prefetch activities. File segments prefetches are described by single entries below, each of which expands into six actual columns, where [seg] is replaced as follows: PDb = Permanent data block disk segments PCi = Permanent cylinder index disk segments SDb = Regular or restartable spool data block disk segments SCi = Regular or restartable spool index disk segments Tjt = Transient journal table APt = Append table or permanent journal table data block or cylinder index disk segments File[seg]Pres count Total number of disk segments prefetched. FLOAT File[seg]PresKB count Total number of KBs prefetched by File[seg]Pres. FLOAT File[seg]PreReads count Total number of disk segment prefetches that caused a physical read. FLOAT File[seg]PreReadKB count Total number of KBs physically read by File[seg]PreReads. FLOAT Segments Released Columns The following columns identify the total disk memory segments released by the file system, as well as those segments that are dropped from memory during the log period. When a segment is release, the segment is either: Force out of memory (F) Remains resident in memory (R) Aged out of memory (A), from segments that remain resident Both the number of segments (Rels, Writes, Drps) and the size of the segments (RelKB, WriteKB, DrpKB) are counted. When a segment leaves memory, it must be written to disk only if the segment is dirty, that is, modified (Dy). Otherwise, the clean or unmodified (Cn) segment is simply dropped. (Most spool blocks are simply dropped from a task and put on the age queue. This may happen multiple times. Each of these will be counted as a resident release. If the system is low on memory and the age queue must be processed, this may also result in an age write or age drop. Forced writes are always also counted as either clean resident releases or forced drops, depending on whether age normal or age out now was specified.) Disk segment memory types are described by single entries below, each of which expands into six actual columns, where [seg] is replaced as follows: • • • • • • PDb = Permanent data block disk segments PCi = Permanent cylinder index disk segments SDb = Regular or restartable spool data block disk segments SCi = Regular or restartable spool cylinder index disk segments TJt = Transient journal table or WAL data block or WAL cylinder index APt = Append table or permanent journal table data block or cylinder index disk segments File[seg]DyRRels 110 count Number of dirty disk segment resident releases. FLOAT Resource Usage Macros and Tables Chapter 11: ResUsageSps Table Column Name Type of Data Description Data Type File[seg]DyRRelKB count KBs released by File[seg]DyRRels. FLOAT File[seg]FWrites count Number of disk segment forced releases or specific I/O requests causing an immediate physical write. Includes spool data that is aged out immediately and permanent data that is written immediately. FLOAT File[seg]FWriteKB count KBs written by File[seg]FWrites. FLOAT Invalid Platform Cylinder Management Overhead Events Columns The following columns identify the number of times the file system software performed a cylinder management event. The table ResUsageIvpr further breaks down the I/Os associated with these events. See Appendix C: “ResUsageIvpr Table.” FileCylMigrs count Number of cylinder migrations. FLOAT ALL FileCylAllocs count Number of new cylinders allocated. FLOAT ALL Note: A new cylinder allocation event implies one logical cylinder index read and one logical cylinder index write. Synchronized Full File Scans Columns The following columns contain statistics relating to synchronized full-file scans. FileSyncScans count Number of attempts to synchronize a full file scan. FLOAT ALL FileSyncSubtables track Number of subtables scanned by one or more full file scanners who are willing to synchronize scans. FLOAT ALL FileSyncScanners track Number of tasks involved in full file scans who are willing to synchronize with other scanners. FLOAT ALL FileSyncGroups track Number of groups of scanners involved in full file scans. A group consists of scanners who are able to use the same read I/O to obtain data from disk. FLOAT ALL ChnSignal Status Tracking columns The following columns track the chnsignal last done status (or track slowest vproc on the system for processing AMP steps). MsgChnLastDone count The number of last done events that occurred for this vproc. FLOAT ALL Note: The last AMP to finish an operation may send a last done broadcast message indicating the work is done for this step. This is used in tracking down the slowest AMP in the system. An AMP that has more last done messages than the others could be a bottleneck in the system performance. Resource Usage Macros and Tables 111 Chapter 11: ResUsageSps Table Column Name Type of Data Description Data Type Invalid Platform Memory Allocation Columns The following columns represent the number and amount of memory allocations, subdivided into (the only applicable) generic node memory type. MemAllocs count Number of successful SEG memory allocations. FLOAT MemAllocKB count Total KBs attributed to SEG memory allocations. FLOAT MemCtxtAllocs count Number of successful SWAP memory allocations. FLOAT ALL FLOAT ALL Note: Only scratch pages are allocated. MemKBRes count The amount of memory resident that is specific to virtual processor activities. Amp Worker Task Columns The following columns collect and report statistics about the AWTs. For more information about the ResUsageSawt table and columns, see Chapter 7: “ResUsageSawt Table.” Note: The system writes the data to the database once for every triplet of Vproc Type, Performance Group ID, and Performance Period ID (VprType, PGId, PPId). The data is reporting the contribution of the respective WD to the column and the values are not the same as the values reported in the ResUsageSawt table. The ResUsageSps table values should add up to the ResUsageSawt table for columns like WorkTypeInuse. The Max columns will not be able to be correlated to the ResUsageSawt table Max values in such a direct way since the ResUsageSps Max columns report the Max value of the ResUsageSps table InUse column for the WD and not the Max value of the ResUsageSawt table for all the WDs combined. FlowControlled count Number of times this log period that system entered the flow control state from a nonflow controlled state. FLOAT ALL FlowCtlCnt count Number of AWTs currently in flow control on the work input mailbox. FLOAT ALL WorkTypeInuse00 WorkTypeInuse15 count Current number of AWTs in use during the log period for each work type for the WD (PGid/VprType/PPid triplet). FLOAT 112 Resource Usage Macros and Tables Chapter 11: ResUsageSps Table Column Name WorkTypeMax00 WorkTypeMax15 Type of Data max Description Data Type The value reported is the maximum of the WorkTypeInuse values seen at the end of each gather period during the reporting period. If only a single gather period occurs during the reporting period, the WorkTypeMax and WorkTypeInUse columns would report the same value. When multiple gather periods occur during the reporting period the value is the maximum of the sampled values. FLOAT Invalid Platform This is therefore a maximum sampled value. The true maximum number of inuse AWTs of a WorkType may occur at a different time during the reporting period and not be seen at the end of the gather period and therefore not be reported. Maximum number of AWTs in use at one time during the log period for each work type for the WD (PGid/VprType/PPid triplet). NET COLUMNS Point-to-Point Net Traffic Columns The following columns identify the number (Reads, Writes) and amount (ReadKB, WriteKB) of input and output messages passing through either Teradata Database net through point-to-point (1:1) methods (PtP). Note: The system writes the data to the database once for every triplet of Vproc Type, Performance Group ID, and Performance Period ID (VprType, PGId, PPId). NetPtPReads count Number of point-to-point messages input to the vproc on behalf of the WD. FLOAT NetPtPWrites count Number of point-to-point messages output from the vproc on behalf of the WD. FLOAT NetPtPReadKB count Total KBs of point-to-point messages input to the vproc on behalf of the WD. FLOAT NetPtPWriteKB count Total KBs of point-to-point messages output to the vproc on behalf of the WD. FLOAT Broadcast Net Traffic Columns The following columns identify the number (Reads, Writes) and amount (ReadKB, WriteKB) of input and output messages passing through the Teradata Database nets through broadcast (1:many) methods (Brd) per net. NetBrdReads count Number of broadcast messages input to the vproc. FLOAT NetBrdWrites count Number of broadcast messages output from the vproc. FLOAT Resource Usage Macros and Tables 113 Chapter 11: ResUsageSps Table Column Name Type of Data Description Data Type Invalid Platform Allocator Columns The following columns identify the number of requests or I/Os from the Allocator. AllocatorExtentAllocReqs count Number of cylinder allocation requests received by the allocator. FLOAT ALL AllocatorExtentFreeReqs count Number of cylinder free requests received by the allocator. FLOAT ALL AllocatorMapIOsStarted count Number of map I/Os initiated by the allocator. FLOAT ALL AllocatorMapIOsDone count Number of map I/Os completed by the allocator. FLOAT ALL Node Agent Columns The following columns identify the migration and buffer processing statistics reported by the Node Agent. NodeAgentMigrationsStarted count Number of migration requests started by the Node Agent. FLOAT ALL NodeAgentMigrationsDone count Number of migration requests completed by the Node Agent. FLOAT ALL NodeAgentStatProcessed count Number of statistics buffers processed by the Node Agent. FLOAT ALL I/O Columns These columns identify the I/O statistics reported from the extent driver. Note: These columns are populated and used by Teradata VS, an option sold separately from Teradata Database. Associated configuration settings appear in the ctl utility if you purchased Teradata Virtual Storage. For details about these columns, see Teradata Virtual Storage. Write Ahead Logging Columns The following columns identify the log-based file system recovery scheme in which modifications to permanent data are written to a log file, the WAL log. FileWCylAllocs count Number of new WAL cylinders allocated. FLOAT ALL FileWCylFrees count Number of times the file system logically frees a cylinder. FLOAT ALL 114 Resource Usage Macros and Tables Chapter 11: ResUsageSps Table Column Name Type of Data Description Data Type Invalid Platform Process Blocking and Waiting Columns The following columns count of blocks and wait time in milliseconds where [reason] is replaced with one of the following: • • • • • • • • • • • SegNoVirtual FsgNIOs SegMDL MonResume NetThrottle Qnl FsgRead FsgWrite DBLock Monitor SegLock • FsgLock • Time • FlowControla • CpuLimitb • Misc For example, columns can appear as ProcBlksSegNoVirtual, ProcBlksMisc, ProcWaitSegNoVirtual, and so on. The following column definition descriptions can be found in the ResUsageSpma table descriptions of the same names except where noted. ProcBlks[reason] count Number of times processes were blocked. FLOAT Note: ProcBlksDBLock is invalid on all platforms. ProcWait[reason] count Total time processes were blocked pending. FLOAT Note: ProcWaitDBLock is invalid on all platforms. a. FlowControl refers to the delays caused by the flow control conditions. b. CpuLimit refers to delays due to the Priority Scheduler CPU Limits (System Limit, AG Limit, or RP Limit). Resource Usage Macros and Tables 115 Chapter 11: ResUsageSps Table Spare Columns Spare Columns The ResUsageSps table has 30 spare columns (several of which are being used) as shown in the table below. Column Name Type of Data Description SpareCount[00-05] count The following SpareCount columns will be converted to the specified column names in Teradata Database 14.0: • SpareCount00 = WorkMsgSendDelay. This column reports the total time in milliseconds for all messages delivered in a period (if the messages are not sent yet, then they are not counted). • SpareCount01 = WorkMsgSendDelayMax. This column reports the longest time in milliseconds seen or still waiting at sample time (if the messages are not sent yet, then they are not counted). • SpareCount02 = WorkMsgReceiveDelay. This column reports the time for all messages not yet delivered at the end of each gather period. This column is related to the QWaitTime column and represents a running total of delays attributed to the tasks that still have not been assigned an AWT within this interval. When the task does receive an AWT in a later interval, the time attributed here will be counted again within QWaitTime of the interval where it was assigned an AWT. • SpareCount03 = WorkMsgReceiveDelayMax. This column reports the maximum delay time in milliseconds for messages that are still in the work box. • SpareCount04 = WorkTimeInuse. This column reports service time consumed by a WD during the current reporting interval. It can be used to calculate the average usage of AWTs during the reporting period, for example: Average AWTs used = WorkTimeInuse/ (Centisecs*10) This value is available in the ResSpsView as AwtUsedAvg. Note: WorkTimeInUse is not the running sum of a WD that exists over multiple intervals. • SpareCount05 = WorkTimeInuseMax. This column reports the maximum service time of a single task in a WD that is still running or has finished in the current reporting interval. This includes time used during previous intervals for that task. If you use the spare columns above, see “ResSpsView” on page 159. 116 Resource Usage Macros and Tables Chapter 11: ResUsageSps Table Spare Columns Column Name Type of Data Description SpareCount[06 and 09] count Spare counted statistic. SpareCount[07-08] count The following SpareCount columns will be converted to the specified column names in Teradata Database 14.0: • SpareCount07 = WorkMsgSendDelayCnt. This column reports the number of messages that are delivered to the work box. • SpareCount08 = WorkMsgReceiveDelayCnt. This column reports the number of messages that are still waiting for AWTs at the end of each gather period. SpareTrack00 track SpareCount00 will be converted to the column name AMPcount in Teradata Database 14.0. AMPcount is the number of AMPs on the Node. AMPcount is used to divide columns that are reporting data from all the AMPs. This allows the ResSpsView view to report the data columns on a per AMP basis. See “ResSpsView” on page 159 for an example of the view. SpareTrack[01-09] track Spare tracked statistic. SpareTmon00 n/a The SpareTmon00 column contains the COD value. The value represents the COD value in one tenths of a percent, so a displayed value of 500 represents a COD value of 50.0%. Note: This value is valid only on SUSE Linux Enterprise Server 10 systems and is a single value for the entire system. SpareTmon[01-09] count Spare time monitored statistic. The spare column fields expand to values 00–09, so that column names would be SpareCount00, SpareTrack09, SpareTmon03, and so on. Resource Usage Macros and Tables 117 Chapter 11: ResUsageSps Table Spare Columns 118 Resource Usage Macros and Tables CHAPTER 12 ResUsageSvdsk Table The ResUsageSvdsk table: • Provides AMP-level storage statistics. • Includes resource usage logs on cylinder allocation, migration, and I/O statistics. If table logging is enabled on ResUsageSvdsk, a row is written to the database once for every AMP vproc in the system for each log interval. To consolidate and summarize the total number of rows written to the database, you can enable Summary Mode. For details, see “Summary Mode” on page 124. Note: This table is created as a MULTISET table. The following table describes the ResUsageSvdsk table columns. Column Name Type of Data Description Data Type Invalid Platform HOUSEKEEPING COLUMNS RELATIONAL PRIMARY INDEX COLUMNS These columns taken together form the nonunique primary index. TheDate n/a Date of the log entry. DATE TheTime n/a Nominal time of the log entry. FLOAT Note: Under conditions of heavy system load, entries may be logged late (typically, by no more than one or two seconds), but this column will still contain the time value when the entry should have been logged. See the Secs and NominalSecs columns. NodeId n/a Identifies the Node upon which the vproc resides. The Node ID is formatted as CCCMM, where CCC denotes the three-digit cabinet number and MM denotes the two-digit chassis number of the node. For example, a node in chassis 9 of cabinet 3 has a node ID of ‘003-09’. INTEGER Note: SMP nodes have a chassis and cabinet number of 1. For example, the node ID of an SMP node is ‘001-01’. Resource Usage Macros and Tables 119 Chapter 12: ResUsageSvdsk Table Column Name Type of Data Description Data Type Invalid Platform MISCELLANEOUS HOUSEKEEPING COLUMNS GmtTime n/a Greenwich Mean Time is not affected by the Daylight Savings Time adjustments that occur twice a year. FLOAT VprId n/a Identifies the AMP vproc. The AMP vproc ID is numbered upward from 0. The maximum value is 8191. INTEGER In Summary Mode, the value of the AMP vproc ID is -1. NodeType n/a Type of node, representing the per node system family type. For example, 5600C or 5555H. CHAR(8) Secs n/a Actual number of seconds in the log period represented by this row. Normally the same as NominalSecs, but can be different in three cases: SMALLINT • The first interval after a log rate change • A sample logged late because of load on the system • System clock adjustments affect reported Secs Useful for normalizing the count statistics contained in this row, for example, to a persecond measurement. CentiSecs n/a Number of centiseconds in the logging period. This column is useful when performing data calculations with small elapsed times where the difference between centisecond-based data and whole seconds results in a percentage error. INTEGER NominalSecs n/a A specified or nominal number of seconds in the logging period. SMALLINT SummaryFlag n/a Identifies the summarization status of this row. If the value is ‘N,’ the row is a non-summary row. If the value is ‘S,’ the row is a summary row. CHAR In Summary Mode, the rows are summarized into a single row. For details, see “Summary Mode” on page 124. 120 Resource Usage Macros and Tables Chapter 12: ResUsageSvdsk Table Column Name Type of Data Description Data Type Active count Controls whether or not the rows will be logged to the ResUsage tables if Active Row Filter Mode is enabled. FLOAT Invalid Platform If Active is set to: • a non-zero value, then the row contains modified data columns. • a zero value, then none of the data columns in the row have been updated during the logging period. For example, if Active Row Filter Mode is enabled, then the rows that have a zero Active column value will not be logged to the ResUsage tables. CollectIntervals n/a The number of gather periods per reporting period. SMALLINT In the Collect Buffer and Log Buffer, the value is the number of Gather operations that have been performed during the period. This number can vary from one period to the next. STATISTICS COLUMNS I/O Statistics Columns The following columns identify the I/O statistics reported by FSG for each AMP. ReadCnt count Number of logical device reads. FLOAT WriteCnt count Number of logical device writes. FLOAT ReadKB count Number of KBs (1024 bytes) read from the logical device. FLOAT WriteKB count Number of KBs (1024 bytes) written to the logical device. FLOAT ReadRespTot count Total of individual read response time in centiseconds. FLOAT WriteRespTot count Total of individual write response time in centiseconds. FLOAT ReadRespMax max Maximum number of individual read response time in centiseconds. FLOAT WriteRespMax max Maximum number of individual write response time in centiseconds. FLOAT ReadRespSq count Total of squares of the individual read response time in centiseconds. FLOAT WriteRespSq count Total of squares of the individual write response time in centiseconds. FLOAT Resource Usage Macros and Tables 121 Chapter 12: ResUsageSvdsk Table Column Name Type of Data Description Data Type ConcurrentReadMax max Maximum number of concurrent read I/O requests. FLOAT ConcurrentWriteMax max Maximum number of concurrent write I/O requests. FLOAT ConcurrentMax max Maximum number of concurrent I/O requests. FLOAT OutReqTime count Time with outstanding requests (busy time), in centiseconds. FLOAT Invalid Platform Allocation Columns These columns identify the allocation statistics reported by the Allocator process of the VSS vproc. Note: These columns are populated and used by Teradata VS, an option sold separately from Teradata Database. For details about these columns, see Teradata Virtual Storage. Migration Columns The following columns identify the number of cylinders that migrated to a different location on a device as well as the time, in centiseconds, of all migration I/Os used, incurred, or saved during the log period. Note: Each allocation is for a cylinder size worth of data, also known internally in the allocator as an extent. Thus the column names begin with Ext for extent. ExtMigrateFaster count FLOAT Number of cylinders migrated to faster locations (that is, migrations whose gross benefits are positive) for the associated AMP. The following formula calculates a Slower Migration value, which is the number of cylinders migrated to slower locations: Slower Migration = ExtMigrateTotal ExtMigrateFaster Cylinders are migrated to slower locations to make room for hotter cylinders to replace them. ExtMigrateTotal count Total number of cylinders migrated to a different physical location. For more information, see the ExtMigrateFaster column. FLOAT ExtMigrateReadRespTot count Migration read I/O response time. FLOAT ExtMigrateWriteRespTot count Migration write I/O response time. FLOAT ExtMigrateIOTimeCost count Estimates the total cost (in centiseconds) incurred by migration I/Os completing during the log period, where cost is the extra time waited by all non-migration I/Os as a result of the migration I/O. The Migrator estimates migration costs. FLOAT Note: This column is for internal use only. Do not use this column unless directed by Teradata Support. 122 Resource Usage Macros and Tables Chapter 12: ResUsageSvdsk Table Column Name Type of Data Description Data Type ExtMigrateIOTimeBenefit count Estimates the total I/O time savings achieved by migrations completing in the log period. The I/O time savings include the improvement in response time caused by the new data arrangement up to the time horizon. FLOAT Invalid Platform This value does not include the cost of the migration I/Os and is a gross benefit, not a net benefit. The Migrator estimates the migration benefit. Note: This column is for internal use only. Do not use this column unless directed by Teradata Support. ExtMigrateIOTimeImprove count Estimates the percent improvement in average I/O response time due to migrations completing in the log interval. In theory, this percentage improvement is permanent. For example, if, right before a particular log interval, the average I/O response time was 10 milliseconds (ms), then the Migration logs an ExtMigrateIOTimeImprove value of 10% in this interval. The average I/O response time after the log interval should be (100%10%)*10ms = 9ms. Migration then logs an ExtMigrateIOTimeImprove of 1% in the next interval. The average I/O response time in the new log interval is (100%-1%)*9ms = 8.91ms. FLOAT ExtMigrateIOTimeImprove is only an estimate. Its permanent improvement remains in effect as long as the workload does not change and newer migrations do not significantly alter the data arrangement. When the workload changes or new migrations affect data arrangement, response time changes in an unquantifiable way. Despite this, ExtMigrateIOTimeImprove is useful because it predicts actual system performance at least for short periods of time and can be used to understand why the migration algorithm is doing what it is doing. Note: This column is for internal use only. Do not use this column unless directed by Teradata Support. Resource Usage Macros and Tables 123 Chapter 12: ResUsageSvdsk Table Summary Mode Summary Mode When Summary Mode is active for the ResUsageSvdsk table, one row is written to the database for each node in the system. This row summarizes all AMP data in each node per log interval. You can determine if a row is in Summary Mode by checking the SummaryFlag column for that row. IF the SummaryFlag column value is… THEN the data for that row is being logged… ‘S’ in Summary Mode. ‘N’ normally. Spare Columns The ResUsageSvdsk table has 30 spare columns (one of which is being used) as shown in the table below. Column Name Type of Data Description SpareCount[00-09] count Spare counted statistic. SpareTrack[00-09] track Spare tracked statistic. SpareTmon00 n/a The SpareTmon00 column contains the COD value. The value represents the COD value in one tenths of a percent, so a displayed value of 500 represents a COD value of 50.0%. Note: This value is valid only on SUSE Linux Enterprise Server 10 systems and is a single value for the entire system. SpareTmon[01-09] count Spare time monitored statistic. The spare column fields expand to values 00–09, so that column names would be SpareCount00, SpareTrack02, or SpareTmon06, and so on. 124 Resource Usage Macros and Tables CHAPTER 13 ResUsageSvpr Table ResUsageSvpr logical table includes resource usage data for available system-wide, virtual processor information. Note: This table is created as a MULTISET table. For more information see “Relational Primary Index” on page 38. The following table describes the ResUsageSvpr table columns. However, always use the view “ResSvprView” on page 166 to access the data rather than accessing the ResUsageSvpr table directly. Column Name Type of Data Description Data Type Invalid Platform HOUSEKEEPING COLUMNS RELATIONAL PRIMARY INDEX COLUMNS These columns taken together form the nonunique primary index. TheDate n/a Date of the log entry. DATE TheTime n/a Nominal time of the log entry. FLOAT Note: Under conditions of heavy system load, entries might be logged late (typically, by no more than one or two seconds), but this column will still contain the time value when the entry should have been logged. See the Secs and NominalSecs columns. NodeId n/a Identifies the Node upon which the vproc resides. The Node ID is formatted as CCCMM, where CCC denotes the three-digit cabinet number and MM denotes the two-digit chassis number of the node. For example, a node in chassis 9 of cabinet 3 has a node ID of ‘003-09’. INTEGER Note: SMP nodes have a chassis and cabinet number of 1. For example, the node ID of an SMP node is ‘001-01’. MISCELLANEOUS HOUSEKEEPING COLUMNS GmtTime Resource Usage Macros and Tables n/a Greenwich Mean Time is not affected by the Daylight Savings Time adjustments that occur twice a year. FLOAT 125 Chapter 13: ResUsageSvpr Table Column Name Type of Data NodeType VprId Description Data Type n/a Type of node, representing the per node system family type. For example, 5600C or 5555H. CHAR(8) n/a Identifies the vproc number (non-Summary Mode) or the vproc type (Summary Mode; 0 = NODE, 1 = AMP, 2 = PE, 3=GTW, 4=RSG, 5=VSS). INTEGER Invalid Platform The VprId can be any of the following depending on the type: • AMP vprocs: numbered upward from 0. • PE vprocs: numbered downward from 16383. • NODE vprocs: numbered upward from 16384. • GTW vprocs: numbered upward from 8192. • RSG vprocs: numbered downward from 9215. • VSS vprocs: numbered downward from 10238. The vproc numbers within each type range are contiguous. Each existing vproc type range should not overlap into the range of another existing vproc type on the system. VprType n/a Type of vproc. The values can be NODE, AMP, PE, GTW, RSG, or TVS (see Teradata Virtual Storage). CHAR(4) Secs n/a Actual number of seconds in the log period represented by this row. Normally the same as NominalSecs, but can be different in three cases: SMALLINT • The first interval after a log rate change • A sample logged late because of load on the system • System clock adjustments affect reported Secs Useful for normalizing the count statistics contained in this row, for example, to a persecond measurement. CentiSecs n/a Number of centiseconds in the logging period. This column is useful when performing data calculations with small elapsed times where the difference between centisecond-based data and whole seconds results in a percentage error. INTEGER NominalSecs n/a A specified or nominal number of seconds in the logging period. SMALLINT 126 Resource Usage Macros and Tables Chapter 13: ResUsageSvpr Table Column Name Type of Data Description Data Type NCPUs n/a Number of CPUs on this node. SMALLINT Invalid Platform This column is useful for normalizing the CPU utilization column values for the number of CPUs on the node. This is especially important in coexistence systems where the number of CPUs can vary across system nodes. SummaryFlag n/a Identifies the summarization status of this row. Possible values are ‘N’ if the row is a nonsummary row and ‘S’ if it is a summary row. CHAR Active count Controls whether or not the rows will be logged to the ResUsage tables if Active Row Filter Mode is enabled. FLOAT If Active is set to: • a non-zero value, then the row contains modified data columns. • a zero value, then none of the data columns in the row have been updated during the logging period. For example, if Active Row Filter Mode is enabled, then the rows that have a zero Active column value will not be logged to the ResUsage tables. CollectIntervals n/a The number of gather periods per reporting period. SMALLINT In the Collect Buffer and Log Buffer, the value is the number of Gather operations that have been performed during the period. This number can vary from one period to the next. STATISTICS COLUMNS PROCESS SCHEDULING COLUMNS CPU Utilization Columns These columns represent CPU activities associated with this virtual processor, subdivided into 48 partitions. Partition 0 is reserved for use by PDE processes. See Appendix D: “Partition Assignments” for more information on the other partitions. Each entry below represents 48 columns, where [i] expands to the values 00 - 47, for example, CPUUservPart31. For definitions of user service and user execution, see "Process Scheduling Columns" in the Chapter 5: “ResUsageScpu Table.” CPUUServPart[i] Resource Usage Macros and Tables count Time in centiseconds CPUs are busy in partition i doing user service. This is the system level time spent on a process. FLOAT 127 Chapter 13: ResUsageSvpr Table Column Name Type of Data CPUUExecPart[i] count Description Data Type Time in centiseconds CPUs are busy in partition i doing user execution. This is the user level time spent on a process. FLOAT Invalid Platform Cylinder Read Columns These columns represent file system resource usage statistics. The Cylinder Read feature uses these statistics for tracking performance and utilization. FileFcrRequests count Total number of requests for the File System to use Cylinder Read. FLOAT This column is tracked and recorded by the File System. It records the number of attempts to use Cylinder Read independent of whether the request will be issued to FSG or not. A request can be denied due to insufficient data blocks or because there is insufficient space in the FSG cache. Requests can also be denied at both the user and kernel level. Each of these items is counted in other FileFcr ResUsage columns. A number of calculations can be performed using this column: • Requests issued to FSG = FileFcrRequests - FileFcrDeniedUser • Successful Cylinder Reads = FileFcrRequests - FileFcrDeniedUser FileFcrDeniedKern FileFcrRequestsAdaptive count Number of adaptive requests from File System. FLOAT ALL This column is tracked and recorded by the File System. It records the number of requests for adaptive-style Cylinder Reads. Note: This column is not currently used. FileFcrBlocksRead count Number of data blocks read in using Cylinder Read. FLOAT This column is tracked and recorded by the FSG subsystem. It records the total number of data blocks read in by successful Cylinder Read operations. The average number of data blocks in a successful Cylinder read can be calculated as: Average data blocks/ Cylinder Read = FileFcrBlocksRead / (FileFcrRequests FileFcrDeniedUser - FileFcrDeniedKern) 128 Resource Usage Macros and Tables Chapter 13: ResUsageSvpr Table Column Name Type of Data FileFcrDeniedUser count Description Data Type Number of Cylinder Read requests denied by the File System. FLOAT Invalid Platform This column is tracked and recorded by the File System. It records the number of Cylinder Read attempts that have been denied by the File System. A request can be denied by the File System due to insufficient number of data blocks being requested (for example, the FileFcrDeniedThreshUser column). For information, see the FileFcrDeniedThreshUser column. FileFcrBlocksDeniedUser count Number of data blocks contained in the rejected requests for Cylinder Read. FLOAT This column is tracked and recorded by the File System. It records the number of data blocks that were part of attempts to use Cylinder Read that were denied by the File System; therefore, also incremented the FileFcrDeniedUser column. FileFcrDeniedKern count Number of Cylinder Read requests denied by the FSG subsystem. FLOAT This column is tracked and recorded by the FSG subsystem. It records the number of Cylinder Read requests issued to the FSG subsystem which, for any reason, have been denied. A request can be denied due to insufficient data blocks (for example, the FileFcrDeniedThreshKern column) or because there is insufficient space in the FSG cache (for example, the FileFcrDeniedCache column). The FSG subsystem can reject a request containing insufficient data blocks that the File System thought had enough blocks because the FSG subsystem reduces the count by the number of data blocks that are already resident in the cache. FileFcrBlocksDeniedKern count Number of data blocks contained in the rejected requests for Cylinder Read. FLOAT This column is tracked and recorded by the FSG subsystem. It records the number of data blocks that were part of attempts to use Cylinder Read that were denied by the FSG subsystem; therefore, also incremented the FileFcrDeniedKern column. Resource Usage Macros and Tables 129 Chapter 13: ResUsageSvpr Table Column Name Type of Data FileFcrDeniedCache count Description Data Type Number of Cylinder Read requests denied by FSG due to insufficient cache. FLOAT Invalid Platform This column is tracked and recorded by FSG. It records the number of Cylinder Read requests which have been denied due to insufficient FSG cache space for a cylinders worth of data. FileFcrBlocksDeniedCache count Number of data blocks contained in Cylinder Read requests rejected by the FSG subsystem due to insufficient cache. FLOAT This column is tracked and recorded by the FSG subsystem. It records the number of data blocks that were part of attempts to use Cylinder read that were denied by the FSG subsystem due to insufficient cache space; therefore, also incremented the FileFcrDeniedCache column. FileFcrDeniedThreshUser count Number of Cylinder Read requests denied by the File System due to insufficient data blocks. FLOAT This column is tracked and recorded by the File System. It records the number of Cylinder Read requests which have been denied due to the data block threshold criteria. There is a minimum threshold of data blocks for an individual Cylinder Read request. If the number of data blocks is below this threshold, the overhead of the Cylinder Read operation is considered too large and issuing individual data block reads is considered more efficient. Therefore, the Cylinder Read request is denied. FileFcrBlocksDeniedThreshUser count Number of data blocks contained in Cylinder Read requests rejected for threshold by the File System. FLOAT This column is tracked and recorded by the File System. It records the number of data blocks that were part of attempts to use Cylinder Read that were denied by the File System due to the number of blocks being below the threshold; therefore, also incremented the FileFcrDeniedThreshUser column. 130 Resource Usage Macros and Tables Chapter 13: ResUsageSvpr Table Column Name Type of Data FileFcrDeniedThreshKern count Description Data Type Number of Cylinder Read requests denied by the FSG subsystem due to insufficient data blocks. FLOAT Invalid Platform This column is tracked and recorded by the FSG subsystem. It records the number of Cylinder Read requests which have been denied due to the data block threshold criteria. There is a minimum threshold of data blocks for an individual Cylinder Read request. If the number of data blocks is below this threshold, the overhead of the Cylinder Read operation is considered too large and issuing individual data block reads is considered more efficient. Therefore, the Cylinder Read request is denied. FSG must reevaluate the threshold for a request that the File System considered valid since FSG eliminates any data blocks from the request list that are already resident in the cache. This could reduce the count that the File System thought was above the threshold to one that is now below. FileFcrBlocksDeniedThreshKern count Number of data blocks contained in Cylinder Read requests rejected for threshold by the FSG subsystem. FLOAT This column is tracked and recorded by the FSG subsystem. It records the number of data blocks that were part of attempts to use Cylinder read that were denied by the FSG subsystem due to the number of blocks being below the threshold; therefore, also incremented the FileFcrDeniedThreshKern column. ChnSignal Status Tracking Columns These columns track the chnsignal last done status (or track slowest vproc on the system for processing AMP steps). MsgChnLastDone count The number of last done events that occurred for this vproc. FLOAT Note: The last AMP to finish an operation may send a last done broadcast message indicating the work is done for this step. This is used in tracking down the slowest AMP in the system. An AMP that has more last done messages than the others could be a bottleneck in the system performance. Resource Usage Macros and Tables 131 Chapter 13: ResUsageSvpr Table Column Name Type of Data Description Data Type Invalid Platform Process Pending Counts and Wait Time Columns These columns identify the number of processes blocked on database locks, and how long they were blocked. ProcPendDBLock count Number of processes blocked pending database locks. FLOAT ProcBlksDBLock count Number of times processes blocked for database locks. FLOAT ProcWaitDBLock count Total time processes were blocked pending database locks. FLOAT ALL MEMORY COLUMNS Memory Allocations Columns These columns represent the number and amount of memory allocations specific to virtual processor activities, subdivided into segment types. The columns do not include any memory allocations specific to the node the vproc is running under. Teradata Database context amounts are not included since they can be calculated by multiplying the fixed page size by the number of page allocations. Disk segment memory types are described by single entries below, each of which expands into six actual columns, where [seg] is replaced as follows: • PDb = Permanent data block disk segments • PCi = Permanent cylinder index disk segments • SDb = Regular or restartable spool data block disk segments • SCi = Regular or restartable spool cylinder index disk segments • TJt = Transient journal table or WAL data block or WAL cylinder index • APt = Append table or permanent journal table data block or cylinder index disk segments For example MemPDbAlloc, MemPCiAlloc, MemSDb, and so on. Mem[seg]Allocs count Number of successful memory allocations and size-increasing memory alters on disk segments. FLOAT ALL Mem[seg]AllocKB count Total KBs attributed to allocations and sizeincreasing memory alters for disk segments. FLOAT ALL MemCtxtAllocs count Number of successful memory allocations and size-increasing memory alters on task context pages. FLOAT Note: Only scratch pages will get allocated. All other task context pages will appear resident at some point soon after component restart. 132 Resource Usage Macros and Tables Chapter 13: ResUsageSvpr Table Column Name Type of Data Description Data Type Invalid Platform Memory Resident Columns These columns represent the amount of memory resident specific to virtual processor activities, subdivided into memory types. The columns do not include any memory allocations specific to the node the vproc is running under. Disk memory segments can be in one of four states: • Clean (unmodified) and Unaccessed by any process (CU) • Dirty (modified) and Unaccessed (DU) • Clean and Accessed (CA) • Dirty and Accessed (DA). Permanent segments for an entire table can be user-locked-in to memory. These are called frozen segments (Frz), and no state subdivision is necessary because they cannot be aged or forced out of memory. ‘Regular’ disk segment memory types are described by single entries below, each of which expands into six actual columns, where [seg] is replaced as follows: • • • • • • PDb = Permanent data block disk segments PCi = Permanent cylinder index disk segments SDb = Regular or restartable spool data block disk segments SCi = Regular or restartable spool cylinder index disk segments TJt =Transient journal table or WAL data block or WAL cylinder index APt = Append table or permanent journal table data block or cylinder index disk segments MemCtxtRes track Current pages resident in memory for task context segments. FLOAT ALL MemPKBResFrz track Current KBs resident in memory for frozen segments. FLOAT ALL Mem[seg]KBResCU track Current KBs resident in memory for regular disk segments that are currently clean and not accessed. FLOAT ALL Note: MemBaseKBResCU tracks of the sum of the data block sizes in the FSG cache (for both general purpose preloads and cylinder read preloads). Mem[seg]KBResDU track Current KBs resident in memory for regular disk segments that are currently dirty and unaccessed. FLOAT ALL Mem[seg]KBResCA track Current KBs resident in memory for regular disk segments that are currently clean and accessed. FLOAT ALL Mem[seg]KBResDA track Current KBs resident in memory for regular disk segments that are currently dirty and accessed. FLOAT ALL Resource Usage Macros and Tables 133 Chapter 13: ResUsageSvpr Table Column Name Type of Data Description Data Type Invalid Platform Paging Columns These columns identify paging activities on pages containing Teradata Database context pages only. MemCtxtPageReads count Number of task context pages that were paged in. FLOAT ALL MemCtxtPageWrites count Number of task context pages that were paged out. FLOAT ALL Swapping Columns These columns identify the effects on disk segments when all processes accessing them get swapped out. MemSwapDrops count Number of disk segments that were dropped from memory because all accessor processes were swapped out. FLOAT ALL MemSwapDropKB count KBs dropped from memory by MemSwapDrops. FLOAT ALL MemSwapReads count Number of disk segments that were re-read when they were previously dropped from memory because all accessor processes were swapped out. FLOAT ALL MemSwapReadKB count KBs re-read from memory by MemSwapReads. FLOAT ALL Task Context Segment Usage Columns These columns identify the usage of task context segments and how they leave memory. MemCtxtAccesses count Number of segments accessed. FLOAT MemCtxtAccessKB count KBs of segments accessed. FLOAT MemCtxtDeaccesses count Number of segments deaccessed. (Deaccessed segments remain in memory until paged out through aging.) FLOAT MemCtxtDeaccessKB count KBs of segments deaccessed. FLOAT MemCtxtDestroys count Number of segments destroyed. (Destroyed segments are immediately dropped from memory.) FLOAT MemCtxtDestroyKB count KBs of segments destroyed. FLOAT NET COLUMNS Point-to-Point Net Traffic Columns These columns identify the number (Reads, Writes) and amount (ReadKB, WriteKB) of input and output messages passing through either Teradata Database net through point-to-point (1:1) methods (PtP) NetPtPReads 134 count Number of point-to-point messages input to the vproc. FLOAT Resource Usage Macros and Tables Chapter 13: ResUsageSvpr Table Column Name Type of Data NetPtPWrites Description Data Type count Number of point-to-point messages output from the vproc. FLOAT NetPtPReadKB count Total KBs of point-to-point messages input to the vproc. FLOAT NetPtPWriteKB count Total KBs of point-to-point messages output to the vproc. FLOAT Invalid Platform Broadcast Net Traffic Columns These columns identify the number (Reads, Writes) and amount (ReadKB, WriteKB) of input and output messages passing through the Teradata Database nets through broadcast (1:many) methods (Brd) per net. NetBrdReads count Number of broadcast messages input to the vproc. FLOAT NetBrdWrites count Number of broadcast messages output from the vproc. FLOAT NetBrdReadKB count Total KBs of broadcast messages input to the vproc. FLOAT NetBrdWriteKB count Total KBs of broadcast messages output from the vproc. FLOAT Work Mailbox Queue Columns These columns identify the virtual processor work mailbox queue length where requested work awaits the allocation of a process to perform the work. MsgWorkQLenSum count Total number of work requests waiting during each log interval. FLOAT The Sum count tracks the current count at the end of each gather period and sums the counts over the log period. Note: To calculate the average work requests waiting, divide this value by the CollectIntervals value. The CollectIntervals value is the number of gather periods per reporting period. For more information, see the CollectIntervals column. MsgWorkQLenMax max Maximum number of work requests waiting during each log interval. FLOAT The Max count, unlike the Sum count, tracks the maximum count over the log period. Therefore the Sum count can be 0 even though the Max count can be non-zero over the log period. Resource Usage Macros and Tables 135 Chapter 13: ResUsageSvpr Table Column Name Type of Data Description Data Type Invalid Platform GENERAL CONCURRENCY CONTROL COLUMNS Database Locks Columns These columns identify database locking activities. DBLockEnters count Number of times a database lock was entered for holding. FLOAT DBLockBlocks count Number of times a database lock was blocked. FLOAT DBLockDeadlocks count Number of times a database lock was deadlocked. FLOAT DBLockBlocksSum count Total number of requests blocked on database locks during each log interval. FLOAT ALL To calculate the average number of requests blocked, divide this value by the CollectIntervals value. The CollectIntervals value is the number of gather periods per reporting period. For more information, see the CollectIntervals column. DBLockBlocksMax max Maximum number of requests blocked on database locks during each log interval. FLOAT ALL DBLocksHeldSum count Total number of database locks held during each log interval. FLOAT ALL FLOAT ALL To calculate the average number of database locks held, divide this value by the CollectIntervals value. The CollectIntervals value is the number of gather periods per reporting period. For more information, see the CollectIntervals column. DBLocksHeldMax max Maximum number of database locks held during each log interval. FILE SYSTEM COLUMNS Segment Acquires Columns These columns identify the total disk memory segments acquired by the file system during the log period. Logical acquires (Acqs) and the logical amount acquired (AcqKB) are identified. Acquires causing physical reads (AcqReads) and the amount read (AcqReadKB) are identified as a subset of logical acquires. Disk segment memory types are described by single entries below, each of which expands into six actual columns, where [seg] is replaced as follows: • • • • • • 136 PDb = Permanent data block disk segments PCi = Permanent cylinder index disk segments SDb = Regular or restartable spool data block disk segments SCi = Regular or restartable spool cylinder index disk segments TJt = Transient journal table or WAL data block or WAL cylinder index APt = Append table or permanent journal table data block or cylinder index disk segments Resource Usage Macros and Tables Chapter 13: ResUsageSvpr Table Column Name Type of Data Description Data Type File[seg]Acqs count Total number of disk segments acquired. FLOAT File[seg]AcqKB count Total KBs acquired by File[seg]Acqs. FLOAT File[seg]AcqReads count Number of disk segment acquires that caused a physical read. FLOAT File[seg]AcqReadKB count KBs physically read by File[seg]AcqReads. FLOAT Invalid Platform Data Block Prefetches Columns These columns identify File Segment Prefetch activities. File segment prefetches are described by single entries below, each of which expands into six actual columns, where [seg] is replaced as follows: • PDb = Permanent data block disk segments • PCi = Permanent cylinder index disk segments • SDb = Regular or restartable spool data block disk segments • SCi = Regular or restartable spool cylinder index disk segments • TJt = Transient journal table or WAL data block or WAL cylinder index • APt = Append table or permanent journal table data block or cylinder index disk segments Note: A prefetch is either a cylinder read operation or an individual block read operation. Either of these operations are generically called a prefetch. File[seg]Pres count Total number of times a logical data prefetch was performed (either a cylinder read or an individual block read). FLOAT File[seg]PresKB count Total number of KBs logically prefetched (either a cylinder read or an individual block read) by File[seg]Pres. FLOAT For cylinder reads, this column does not include the disk sectors in between the loaded data blocks. File[seg]PreReads count Total number of disk segment prefetches (either a cylinder read or an individual block read) that caused a logical read. FLOAT File[seg]PreReadKB count Total number of KBs physically read by File[seg]PreReads. FLOAT For cylinder reads, this column includes the disk sectors in between the loaded data blocks. Resource Usage Macros and Tables 137 Chapter 13: ResUsageSvpr Table Column Name Type of Data Description Data Type Invalid Platform Segments Released Columns These columns identify the total disk memory segments released by the file system, as well as those segments that are dropped from memory during the log period. When a segment is released, the segment is either: • Forced (F) • Remains resident in memory (R) • Aged out of memory (A), from segments that are currently resident Both the number of segments (Rels, Writes, Drps) and the size of the segments (RelKB,WriteKB, DrpKB) are counted. When a segment leaves memory, it must be written to disk only if the segment is dirty (Dy), that is, modified. Otherwise, the clean (Cn), that is, unmodified segment is simply dropped. Most spool blocks for a small table remain resident when they are created and age there. Each of these will be counted as a dirty resident release (DyRRel fields). If a block survives in the cache, it would be reacquired (whenever the system creates spool data, a subsequent step will read it) and released again. The release will still be counted as a dirty resident release, since the block survived in a modified state. On the other hand, if there is contention for room in the FSG cache, the segment might be removed from memory. Because it is a modified segment, it must be written out first. This is counted as a dirty age write (DyAWrite fields). When it is reacquired it will no longer be modified, so the subsequent release will be counted as a clean resident release (CnRRel fields). Full table modification operations make one pass on the table and modify each block only once. Since these operations do not access a block multiple times, there is no point keeping them in the cache. If a block that was examined did not contain any rows that qualify for the modification, when the block is released it will be dropped from memory immediately (FDrp fields). However if the block was modified, when it is released the system issues the write as part of the release so it is counted as a forced write (FWrite fields). Since the system also drops the block from memory as soon as the write is complete, this release is also counted as a forced drop (FDrp fields). Disk segment memory types are described by single entries below, each of which expands into six actual columns, where [seg] is replaced as follows: • • • • • • PDb = Permanent data block disk segments PCi = Permanent cylinder index disk segments SDb = Regular or restartable spool data block disk segments SCi = Regular or restartable spool cylinder index disk segments TJt = Transient journal table or WAL data block or WAL cylinder index APt = Append table or permanent journal table data block or cylinder index disk segments File[seg]DyRRels count Number of dirty disk segment resident releases. FLOAT File[seg]DyRRelKB count KBs released by File[seg]DyRRels. FLOAT File[seg]FWrites count Number of disk segment forced releases or specific I/O requests causing an immediate physical write. Includes spool data that is aged out immediately and permanent data that is written immediately. FLOAT File[seg]FWriteKB count KBs written by File[seg]FWrites. FLOAT File[seg]DyAWrites count Number of dirty disk segments aged out of memory causing a delayed physical write. FLOAT If the segment is unmodified, only CnADrps is incremented. If the segment is modified, both DyAWrites and CnADrps are incremented. 138 Resource Usage Macros and Tables Chapter 13: ResUsageSvpr Table Column Name Type of Data Description Data Type File[seg]DyAWriteKB count KBs written by File[seg]DyAWrites. FLOAT Invalid Platform If the segment is unmodified, only CnADrpKB is incremented. If the segment is modified, both DyAWriteKB and CnADrpKB are incremented. File[seg]CnRRels count Number of clean disk segment resident releases. FLOAT File[seg]CnRRelKB count KBs released by File[seg]CnRRels. FLOAT File[seg]FDrps count Number of disk segment forced releases causing an immediate memory drop. Segments that are never to be part of the memory cache (the age queue) are counted as forced drops. FLOAT File[seg]FDrpKB count KBs dropped by File[seg]FDrps. FLOAT File[seg]CnADrps count Number of clean disk segments aged out of memory. If the segment is unmodified, only CnADrps is incremented. If the segment is modified, both DyAWrites and CnADrps are incremented. FLOAT Note: CnADrps counts includes the DyAWrites counts. To calculate the clean segments that aged out of memory, subtract the DyAWrites value from the CnADrps value. File[seg]CnADrpKB count KBs dropped by File[seg]CnADrps. FLOAT If the segment is unmodified, only CnADrpKB is incremented. If the segment is modified, both DyAWriteKB and CnADrpKB are incremented. Note: CnADrpKB counts includes the DyAWriteKB counts. To calculate the KBs of clean segments that aged out of memory, subtract the DyAWriteKB value from the CnADrpKB value. Data Segment Lock Requests Columns These columns identify the number of lock requests, blocks, and deadlocks on a disk segment, including those implied for segment acquires. FileLockEnters count Number of lock requests on disk segments. FLOAT FileLockBlocks count Number of lock requests that were blocked. (Total locks - locks blocked = locks with immediate grants.) FLOAT FileLockDeadlocks count Number of deadlocks detected on lock requests. FLOAT Resource Usage Macros and Tables 139 Chapter 13: ResUsageSvpr Table Column Name Type of Data Description Data Type Invalid Platform Cylinder Management Overhead Events Columns These columns identify the number of times the file system software performed a cylinder management event. The table ResUsageIvpr further breaks down the I/Os associated with these events. See Appendix C: “ResUsageIvpr Table.” FileCylAllocs count Number of new cylinders allocated. FLOAT FileCylFrees count Number of logical or physical cylinders freed. FLOAT FileCylMigrs count Number of cylinder migrations. FLOAT FileMCylPacks count Number of MiniCylPacks performed. FLOAT FileCylDefrags count Number of cylinder defragments performed. FLOAT Synchronized Full Table Scans Columns These columns contain statistics relating to synchronized full table scans. Note: The following columns have been moved from ResUsageIvpr to ResUsageSvpr to avoid costly joins. FileSyncScans count Number of attempts to synchronize a full table scan. FLOAT FileSyncSubtables track Number of subtables scanned by one or more full table scanners who are willing to synchronize scans. FLOAT FileSyncScanners track Number of tasks involved in full table scans who are willing to synchronize with other scanners. FLOAT FileSyncGroups track Number of groups of scanners involved in full table scans. A group consists of scanners who are able to use the same read I/O to obtain data from disk. FLOAT Write Ahead Logging Columns These columns identify the number of times the file system software performed a cylinder management event associated with the WAL log. FileWCylAllocs count Number of new WAL cylinders allocated. FLOAT FileWCylFrees count Number of times the file system logically frees a cylinder. FLOAT Allocation Columns These columns identify the allocation statistics reported by the Allocator. AllocatorExtentAllocReqs count Number of cylinder allocation requests received by the allocator. FLOAT AllocatorExtentFreeReqs count Number of cylinder free requests received by the allocator. FLOAT AllocatorMapIOsStarted count Number of map I/Os initiated by the allocator. FLOAT 140 Resource Usage Macros and Tables Chapter 13: ResUsageSvpr Table Column Name Type of Data AllocatorMapIOsDone count Description Data Type Number of map I/Os completed by the allocator. FLOAT Invalid Platform Node Agent Columns These columns identify the migration and buffer processing statistics reported by the Node Agent. NodeAgentMigrationsStarted count Number of migration requests started by the Node Agent. FLOAT NodeAgentMigrationsDone count Number of migration requests completed by the Node Agent. FLOAT NodeAgentStatProcessed count Number of statistics buffers processed by the Node Agent. FLOAT Extent Driver I/O Columns These columns identify the I/O statistics reported from the extent driver. Note: These columns are populated and used by Teradata VS, an option sold separately from Teradata Database. Associated configuration settings appear in the ctl utility if you have purchased Teradata VS. For details about these columns, see Teradata Virtual Storage. FSG I/O Columns These columns identify the I/O statistics reported from the FSG. IoRespMax max Maximum I/O response time in milliseconds on an AMP. FLOAT IoGapMax max Maximum time gap between I/O completions in milliseconds on an AMP. FLOAT ALL Master Index Columns MIWriteLocks count Number of write locks acquired on a MI. FLOAT MIWriteLockTime count MI write lock hold time in milliseconds. FLOAT MIWriteLockTimeMax max Maximum MI write lock hold time in milliseconds. FLOAT MIWrites count Number of writes while holding a MI no modification (nomod) write lock. FLOAT MIWriteTime count Total write time while holding MI no modification (nomod) write lock in milliseconds. FLOAT MIWriteTimeMax max Maximum write time while holding a MI no modification (nomod) write lock in milliseconds. FLOAT MISleeps count Number of times spent waiting to get a MI lock. FLOAT Resource Usage Macros and Tables 141 Chapter 13: ResUsageSvpr Table Summary Mode Column Name Type of Data MISleepTime MISleepTimeMax Description Data Type count Total amount of time spent waiting to get a MI lock in milliseconds. FLOAT max Maximum amount of time spent waiting to get a MI lock. FLOAT Invalid Platform Summary Mode When Summary Mode is active for the ResUsageSvpr table, one row is written to the database for each type of vproc on each node in the system, summarizing the vprocs of that type on that node, for each log interval. You can determine if a row is in Summary Mode by checking the SummaryFlag column for that row. 142 IF the SummaryFlag column value is… THEN the data for that row is being logged… ‘S’ in Summary Mode. ‘N’ normally. Resource Usage Macros and Tables Chapter 13: ResUsageSvpr Table Spare Columns Spare Columns The ResUsageSvpr table has 30 spare columns as shown in the table below. Column Name Type of Data Description SpareCount[00-07] count The following SpareCount columns will be converted to the specified column names in Teradata Database 14.0: • SpareCount00 = DBMergeTried. The number of times the data block being modified was attempted to be merged with some number of adjacent data blocks as part of the modification. • SpareCount01 = DBMergeDone. The number of times the data block being modified has successfully merged with some number of adjacent data blocks as part of the modification. Subtracting DBMergeDone from DBMergeTried will result in the number of data block merge operations that were tried and failed. • SpareCount02 = DBMergeElim. The number of data blocks eliminated due to data block merges. If n data blocks are merged into 1 large block (where n is the number of data blocks), this number is incremented by n-1. • SpareCount03 = DBMergeExtrIO. The number of additional physical I/Os performed in the data block merge process over and above those that would have been done if no data block merges were attempted. This includes any extra physical I/Os that were performed regardless of whether a particular merge attempt succeeded or not. • SpareCount04 = FileACPCylsSkipped. The number of cylinders AutoCylPack scanned at the MI level and decided nothing needed to be done. • SpareCount05 = FileACPCylsMigr. The number of successful migrations performed by AutoCylPack. Resource Usage Macros and Tables 143 Chapter 13: ResUsageSvpr Table Spare Columns Column Name Type of Data Description SpareCount[00-07] count • SpareCount06 = FileACPCylsUnFSEOnly. The number of cylinders the background task, Automatic Cylinder Packing (ACP), selected for performing a migration, but could not because of a CI that is marked down, a locking conflict, or a recently modified CI. Instead (except for the down CI case), ACP cleaned-upa the unfree sector entries (UNFSEs) on these cylinders. For more information on ACP, see Utilities or Performance Management. • SpareCount07 = FileACPCylsPostponed. The number of cylinders AutoCylPack selected for performing a migration, but could not be performed at the current time. This can happen due to conflicts with foreground tasks modifying the cylinder at around the same time as AutoCylPack. AutoCylPack, therefore, postpones the work until the next time it scans the MI from the beginning. When AutoCylPack sees this cylinder again, if the cylinder still qualifies, it is selected again for processing. SpareCount[08-09] count Spare counted statistic. SpareTrack[00-09] track Spare tracked statistic. SpareTmon00 n/a The SpareTmon00 column contains the COD value. The value represents the COD value in one tenths of a percent, so a displayed value of 500 represents a COD value of 50.0%. (continued) Note: This value is valid only on SUSE Linux Enterprise Server 10 systems and is a single value for the entire system. 144 Resource Usage Macros and Tables Chapter 13: ResUsageSvpr Table Spare Columns Column Name Type of Data Description SpareTmon[01-03] count The following SpareCount columns will be converted to the specified column names in Teradata Database 14.0: • SpareTmon01= FSGCacheWaits. The number of times the file system waits for memory to become available in the file segment cache when trying to read data from disk. • SpareTmon02 = FSGCacheWaitTime. The total amount of time the file system waits for memory to become available in the file segment cache when trying to read data from disk. • SpareTmon03 = MAX. The maximum amount of time the file system waits for memory to become available in the file system cache when trying to read data from disk. SpareTmon[04-09] count Spare time monitored statistic. a. Unfree sectors are those that have no current data block, but cannot yet be used for a new data block. The file system cleans up the unfree sectors, which entails deleting the entries in the CI that say the sectors are unfree and creating new entries that say these sectors are free. The spare column fields expand to values 00-09, so that column names would be SpareCount08, SpareTrack04, SpareTmon01, and so on. Resource Usage Macros and Tables 145 Chapter 13: ResUsageSvpr Table Spare Columns 146 Resource Usage Macros and Tables CHAPTER 14 Resource Usage Views This chapter provides the definitions of the resource usage views. Note: Always use the views to access the data when it is available through the views rather than accessing the ResUsage table directly. To see the view definitions, execute SHOW VIEW viewname, where viewname is the name of the view whose most recent SQL create text is to be reported. For details on using the SHOW VIEW statement, see SQL Data Definition Language. The following views report the table column, GroupId. A homogenous system requires no changes to use this macro because all the nodes will be assigned to group A. For a coexisting system, however, the values need to be set up when the system is installed or reconfigured so that each type of node is assigned to a specific group ID. For the easiest setup, let the group with the fewest nodes be assigned under the WHEN clause and the group with most nodes be assigned via the ELSE clause in the case statement. Caution: Do not change or delete columns in these views. If the columns are modified, the resource usage macros that use these views may not work properly. You can, however, safely add columns. Resource Usage Macros and Tables 147 148 ResGeneralInfoView ResGeneralInfoView provides an overview of system operation. Note: The data columns in this view will change as the columns in the ResUsageSpma table change. REPLACE VIEW DBC.ResGeneralInfoView AS SELECT /* housekeeping fields */ TheDate, TheTime, NodeId (FORMAT '999-99') AS NodeId, CASE WHEN NodeId IN ('001-01', '001-03', '001-05') THEN 'A' WHEN NodeId IN ('001-02', '001-04', '001-06') THEN 'A' ELSE 'A' END AS GroupId, GmtTime, NodeType, NodeNormFactor, NCPUs, Vproc1, VprocType1, Vproc2, VprocType2, Vproc3, VprocType3, Vproc4, VprocType4, Vproc5, VprocType5, Vproc6, VprocType6, Vproc7, VprocType7, MemSize, Secs, CentiSecs, NominalSecs, CollectIntervals, Reserved, Resource Usage Macros and Tables /* transformed fields */ ( (CPUUServ + CPUUExec) / NULLIFZERO(NCPUs) ) AS CPUBusy, ( CPUUServ / NULLIFZERO(NCPUs) ) AS CPUOpSys, ( CPUUExec / NULLIFZERO(NCPUs) ) AS CPUUser, ( CPUIoWait / NULLIFZERO(NCPUs) ) AS CPUWaitIO, ( (CPUUServ + CPUUExec) * (NodeNormFactor / 100)/ NULLIFZERO(NCPUs) ) AS CPUBusyNorm, ( CPUUServ * (NodeNormFactor / 100) / NULLIFZERO(NCPUs) ) AS CPUOpSysNorm, ( CPUUExec * (NodeNormFactor / 100) / NULLIFZERO(NCPUs) ) AS CPUUserNorm, ( CPUIoWait* (NodeNormFactor / 100) / NULLIFZERO(NCPUs) ) AS CPUWaitIONorm, ( FileAcqReads + FilePreReads + FileWrites ) AS DiskSegmentIO, ( FileAcqReads + FilePreReads + FileWrites + MemTextPageReads + MemCtxtPageReads + MemCtxtPageWrites + MemSwapReads ) AS LogicalDeviceIO, ( FileAcqReads + FilePreReads + MemTextPageReads + MemCtxtPageReads + MemSwapReads ) AS LogicalDeviceReads, ( FileWrites + MemCtxtPageWrites ) AS LogicalDeviceWrites, ( FileAcqReadKB + FilePreReadKB + Resource Usage Macros and Tables /* paging or swapping count times pagesize (= 4K) */ (MemTextPageReads + MemCtxtPageReads + MemSwapReads) * 4 ) AS LogicalDeviceReadKB, ( FileWriteKB + /* paging or swapping count times pagesize (= 4K) */ MemCtxtPageWrites * 4 ) AS LogicalDeviceWriteKB, ( NetTxCircPtP + NetTxCircBrd ) (FORMAT '------9') AS NetAttempts, ( NetCircBackoffs ) (FORMAT '------9') AS NetBackoffs, 0 AS NetChannelSR, ( NetMsgBrdReads + NetMsgBrdWrites ) AS NetMultiIO, ( NetMsgPtPReads + NetMsgPtPWrites ) AS NetPtoPIO, ( NetMsgBrdReadKB + NetMsgPtPReadKB ) AS NetReadKB, ( NetMsgBrdReads + NetMsgPtPReads ) AS NetReads, ( NetMsgBrdWriteKB + NetMsgPtPWriteKB ) AS NetWriteKB, ( NetMsgBrdWrites + NetMsgPtPWrites ) AS NetWrites, ( MemTextPageReads + MemCtxtPageReads + MemCtxtPageWrites + MemSwapReads ) AS PageOrSwapIO, ( ProcBlockedSum + ProcReadySum + ProcRunningSum )/NULLIFZERO(CollectIntervals) AS ProcActiveAvg, ProcBlockedSum/NULLIFZERO(CollectIntervals) (FORMAT '------9') AS ProcBlockedAvg, ( ProcBlksDBLock + ProcBlksMemAlloc + ProcBlksMisc + ProcBlksMonitor + ProcBlksMonResume + ProcBlksNetThrottle + ProcBlksSegLock + ProcBlksFsgLock + ProcBlksFsgRead + ProcBlksFsgWrite ) (FORMAT '------9') AS ProcBlocks, ( ProcWaitDBLock + ProcWaitMemAlloc + ProcWaitMisc + ProcWaitMonitor + ProcWaitMonResume + ProcWaitNetThrottle + ProcWaitPageRead + ProcWaitSegLock + ProcWaitFsgLock + ProcWaitFsgRead + ProcWaitFsgWrite ) (FORMAT '------9') AS ProcWaits, ( CmdDDLStmts + CmdDeleteStmts + CmdInsertStmts + CmdSelectStmts + CmdUpdateStmts + CmdUtilityStmts + CmdOtherStmts ) AS UserStmtsArriving, CmdStmtsInProgCur AS UserStmtsInProgress, /* TVS Teradata Virtual Storage fields will be renamed in the table in the 14.0 release */ VssReadCnt AS TvsReadCnt, VssWriteCnt AS TvsWriteCnt, VssReadRespTot AS TvsReadRespTot, VssWriteRespTot AS TvsWriteRespTot, /* Spare Field Usage */ SpareCount00 AS TvsReadMax, SpareCount01 AS TvsWriteMax, SpareTmon00 AS COD, /* 14.0 hdr field Capacity On Demand factor */ 149 /* SPMA table fields */ Active, ProcReadySum, ProcBlockedSum, ProcSuspendedSum, ProcRunningSum, NetSemInUseSum, NetChanInUseSum, NetGroupInUseSum, ProcReadyMax, ProcPendMemAlloc, ProcPendFsgRead, ProcPendFsgWrite, ProcPendNetThrottle, ProcPendNetRead, ProcPendMonitor, ProcPendMonResume, ProcPendDBLock, 150 Resource Usage Macros and Tables ProcPendSegLock, ProcPendMisc, ProcPendFsgLock, ProcPendQnl, ProcBlksMemAlloc, ProcBlksFsgRead, ProcBlksFsgWrite, ProcBlksNetThrottle, ProcBlksMsgRead, ProcBlksMonitor, ProcBlksMonResume, ProcBlksDBLock, ProcBlksSegLock, ProcBlksTime, ProcBlksMisc, ProcBlksFsgLock, ProcBlksQnl, ProcWaitMemAlloc, ProcWaitPageRead, ProcWaitFsgRead, ProcWaitFsgWrite, ProcWaitNetThrottle, ProcWaitMsgRead, ProcWaitMonitor, ProcWaitMonResume, ProcWaitDBLock, ProcWaitSegLock, ProcWaitTime, ProcWaitMisc, ProcWaitFsgLock, ProcWaitQnl, CPUIdle, CPUIoWait, CPUUServ, CPUUExec, CPUIdleNorm, CPUIoWaitNorm, CPUUServNorm, CPUUExecNorm, MemTextAllocs, MemVprAllocs, MemVprAllocKB, MemTSysOhRes, MemDSysOhRes, MemTextRes, MemCtxtRes, MemPDbKBRes, MemPCiKBRes, MemSDbKBRes, MemSCiKBRes, MemTJtKBRes, MemAPtKBRes, MemFreeKB, MemFails, MemAgings, MemTextPageDrops, MemTextPageReads, MemProcSwapped, MemCtxtPageWrites, MemCtxtPageReads, MemSwapDrops, MemSwapDropKB, MemSwapReads, Resource Usage Macros and Tables 151 MemSwapReadKB, MsgPtPReads, MsgPtPWrites, MsgPtPReadKB, MsgPtPWriteKB, MsgBrdReads, MsgBrdWrites, MsgBrdReadKB, MsgBrdWriteKB, NetTxRouting, NetTxConnected, NetRxConnected, NetTxIdle, NetRxIdle, NetSamples, NetMsgPtPWriteKB, NetMsgBrdWriteKB, NetMsgPtPReadKB, NetMsgBrdReadKB, NetMsgPtPWrites, NetMsgBrdWrites, NetMsgPtPReads, NetMsgBrdReads, NetTxCircHPBrd, NetRxCircPtP, NetTxCircHPPtP, NetRxKBPtP, NetTxKBPtP, NetRxCircBrd, NetTxCircBrd, NetRxKBBrd, NetTxKBBrd, NetCircAttempts, NetCircBackoffs, NetSemInUseMax, NetChanInUseMax, NetGroupInUseMax, NetHWBackoffs, NetMrgTxKB, NetMrgRxKB, NetMrgTxRows, NetTxCircPtP, NetMrgRxRows, HostBlockReads, HostBlockWrites, HostMessageReads, HostMessageWrites, HostReadKB, HostWriteKB, DBLockBlocks, DBLockDeadlocks, FileAcqs, FileAcqKB, FileAcqReads, FileAcqReadKB, FileRels, FileRelKB, FileWrites, FileWriteKB, FilePres, FilePreKB, FilePreReads, FilePreReadKB, 152 FileLockBlocks, FileLockDeadlocks, FileLockEnters, FileSmallDepotWrites, FileLargeDepotWrites, FileLargeDepotBlocks, MsgChnLastDone, CmdDDLStmts, CmdDeleteStmts, CmdInsertStmts, CmdSelectStmts, CmdUpdateStmts, CmdUtilityStmts, CmdOtherStmts, CmdStmtsInProgCur, CmdStmtSuccesses, CmdStmtFailures, CmdStmtErrors, CmdStmtTime, AwtFlowControlled, AwtFlowCtlCnt, AwtInuse, AwtInuseMax, PSNumRequests, PSQWaitTime, PSServiceTime, SpareCount00, SpareCount01, SpareCount02, SpareCount03, SpareTrack00, SpareTrack01, SpareTrack02, SpareTrack03, SpareTmon00, SpareTmon01, SpareTmon02, SpareTmon03 FROM ResUsageSpma WITH CHECK OPTION; COMMENT ON VIEW DBC.ResGeneralInfoView AS 'View of general system information'; Resource Usage Macros and Tables ResCPUUsageByAMPView ResCPUUsageByAMPView describes CPU usage per AMP. REPLACE VIEW DBC.ResCPUUsageByAMPView ( TheDate, TheTime, Vproc, NodeId, Secs, NCPUs, GroupId, AMPWorkTaskExec, AMPWorkTaskServ, AMPMiscUserExec, AMPMiscUserServ, AMPTotalUserExec, AMPTotalUserServ ) AS SELECT TheDate, TheTime, VprId, NodeID, Secs, NCPUs, /* GroupId */ Resource Usage Macros and Tables CASE WHEN NodeId IN ('001-01', '001-03', '001-05') THEN 'A ' WHEN NodeId IN ('001-02', '001-04', '001-06') THEN 'A' ELSE 'A' END, /* AMPWorkTaskExec */ CPUUExecPart11, /* AMPWorkTaskServ */ CPUUServPart11, /* AMPMiscUserExec*/ ( CPUUExecPart01+CPUUExecPart02+CPUUExecPart03 + CPUUExecPart04+CPUUExecPart05+CPUUExecPart06+CPUUExecPart07 + CPUUExecPart08+CPUUExecPart09+CPUUExecPart10 + CPUUExecPart12+CPUUExecPart13+CPUUExecPart14+CPUUExecPart15 + CPUUExecPart16+CPUUExecPart17+CPUUExecPart18+CPUUExecPart19 + CPUUExecPart20+CPUUExecPart21+CPUUExecPart22+CPUUExecPart23 + CPUUExecPart24+CPUUExecPart25+CPUUExecPart26+CPUUExecPart27 + CPUUExecPart28+CPUUExecPart29+CPUUExecPart30+CPUUExecPart31 + CPUUExecPart32+CPUUExecPart33+CPUUExecPart34+CPUUExecPart35 + CPUUExecPart36+CPUUExecPart37+CPUUExecPart38+CPUUExecPart39 + CPUUExecPart40+CPUUExecPart41+CPUUExecPart42+CPUUExecPart43 + CPUUExecPart44+CPUUExecPart45+CPUUExecPart46+CPUUExecPart47), /* AMPMiscUserServ */ ( CPUUServPart01+CPUUServPart02+CPUUServPart03 + CPUUServPart04+CPUUServPart05+CPUUServPart06+CPUUServPart07 + CPUUServPart08+CPUUServPart09+CPUUServPart10 + CPUUServPart12+CPUUServPart13+CPUUServPart14+CPUUServPart15 + CPUUServPart16+CPUUServPart17+CPUUServPart18+CPUUServPart19 + CPUUServPart20+CPUUServPart21+CPUUServPart22+CPUUServPart23 + CPUUServPart24+CPUUServPart25+CPUUServPart26+CPUUServPart27 + CPUUServPart28+CPUUServPart29+CPUUServPart30+CPUUServPart31 + CPUUServPart32+CPUUServPart33+CPUUServPart34+CPUUServPart35 + CPUUServPart36+CPUUServPart37+CPUUServPart38+CPUUServPart39 + CPUUServPart40+CPUUServPart41+CPUUServPart42+CPUUServPart43 + CPUUServPart44+CPUUServPart45+CPUUServPart46+CPUUServPart47), /* AMPTotalUserExec */ (CPUUExecPart00+CPUUExecPart01+CPUUExecPart02+CPUUExecPart03 + CPUUExecPart04+CPUUExecPart05+CPUUExecPart06+CPUUExecPart07 + CPUUExecPart08+CPUUExecPart09+CPUUExecPart10+CPUUExecPart11 + CPUUExecPart12+CPUUExecPart13+CPUUExecPart14+CPUUExecPart15 + CPUUExecPart16+CPUUExecPart17+CPUUExecPart18+CPUUExecPart19 + CPUUExecPart20+CPUUExecPart21+CPUUExecPart22+CPUUExecPart23 + CPUUExecPart24+CPUUExecPart25+CPUUExecPart26+CPUUExecPart27 + CPUUExecPart28+CPUUExecPart29+CPUUExecPart30+CPUUExecPart31 + CPUUExecPart32+CPUUExecPart33+CPUUExecPart34+CPUUExecPart35 + CPUUExecPart36+CPUUExecPart37+CPUUExecPart38+CPUUExecPart39 + CPUUExecPart40+CPUUExecPart41+CPUUExecPart42+CPUUExecPart43 + CPUUExecPart44+CPUUExecPart45+CPUUExecPart46+CPUUExecPart47), /* AMPTotalUserServ */ (CPUUServPart00+CPUUServPart01+CPUUServPart02+CPUUServPart03 + CPUUServPart04+CPUUServPart05+CPUUServPart06+CPUUServPart07 + CPUUServPart08+CPUUServPart09+CPUUServPart10+CPUUServPart11 + CPUUServPart12+CPUUServPart13+CPUUServPart14+CPUUServPart15 + CPUUServPart16+CPUUServPart17+CPUUServPart18+CPUUServPart19 + CPUUServPart20+CPUUServPart21+CPUUServPart22+CPUUServPart23 + CPUUServPart24+CPUUServPart25+CPUUServPart26+CPUUServPart27 + CPUUServPart28+CPUUServPart29+CPUUServPart30+CPUUServPart31 + CPUUServPart32+CPUUServPart33+CPUUServPart34+CPUUServPart35 + CPUUServPart36+CPUUServPart37+CPUUServPart38+CPUUServPart39 + CPUUServPart40+CPUUServPart41+CPUUServPart42+CPUUServPart43 + CPUUServPart44+CPUUServPart45+CPUUServPart46+CPUUServPart47) 153 FROM ResUsageSvpr WHERE VprType like 'AMP%' WITH CHECK OPTION; 154 ResCPUUsageByPEView ResCPUUsageByPEView describes CPU usage by each PE. REPLACE VIEW DBC.ResCPUUsageByPEView ( TheDate, TheTime, Vproc, NodeId, Secs, NCPUs, GroupId, PEDispExec, PEDispServ, PEParsExec, PEParsServ, PESessExec, PESessServ, PEMiscUserExec, PEMiscUserServ, PETotalUserExec, PETotalUserServ ) Resource Usage Macros and Tables AS SELECT TheDate, TheTime, VprId, NodeID(FORMAT'999-99'), Secs, NCPUs, /* GroupId */ CASE WHEN NodeId IN ('001-01', '001-03', '001-05') THEN 'A ' WHEN NodeId IN ('001-02', '001-04', '001-06') THEN 'A' ELSE 'A' END, /* PEDispExec */ CPUUExecPart13, /* PEDispServ */ CPUUServPart13, /* PEParsExec */ CPUUExecPart14, /* PEParsServ */ CPUUServPart14, /* PESessExec */ CPUUExecPart12, /* PESessServ */ CPUUServPart12, /* PEMiscUserExec */ ( CPUUExecPart01+CPUUExecPart02+CPUUExecPart03 + CPUUExecPart04+CPUUExecPart05+CPUUExecPart06+CPUUExecPart07 + CPUUExecPart08+CPUUExecPart09+CPUUExecPart10+CPUUExecPart11 + CPUUExecPart15 + CPUUExecPart16+CPUUExecPart17+CPUUExecPart18+CPUUExecPart19 + CPUUExecPart20+CPUUExecPart21+CPUUExecPart22+CPUUExecPart23 + CPUUExecPart24+CPUUExecPart25+CPUUExecPart26+CPUUExecPart27 + CPUUExecPart28+CPUUExecPart29+CPUUExecPart30+CPUUExecPart31 + CPUUExecPart32+CPUUExecPart33+CPUUExecPart34+CPUUExecPart35 + CPUUExecPart36+CPUUExecPart37+CPUUExecPart38+CPUUExecPart39 + CPUUExecPart40+CPUUExecPart41+CPUUExecPart42+CPUUExecPart43 + CPUUExecPart44+CPUUExecPart45+CPUUExecPart46+CPUUExecPart47), /* PEMiscUserServ */ ( CPUUServPart01+CPUUServPart02+CPUUServPart03 + CPUUServPart04+CPUUServPart05+CPUUServPart06+CPUUServPart07 + CPUUServPart08+CPUUServPart09+CPUUServPart10+CPUUServPart11 + CPUUServPart15 + CPUUServPart16+CPUUServPart17+CPUUServPart18+CPUUServPart19 + CPUUServPart20+CPUUServPart21+CPUUServPart22+CPUUServPart23 + CPUUServPart24+CPUUServPart25+CPUUServPart26+CPUUServPart27 + CPUUServPart28+CPUUServPart29+CPUUServPart30+CPUUServPart31 + CPUUServPart32+CPUUServPart33+CPUUServPart34+CPUUServPart35 + CPUUServPart36+CPUUServPart37+CPUUServPart38+CPUUServPart39 + CPUUServPart40+CPUUServPart41+CPUUServPart42+CPUUServPart43 + CPUUServPart44+CPUUServPart45+CPUUServPart46+CPUUServPart47), /* PETotalUserExec */ (CPUUExecPart00+CPUUExecPart01+CPUUExecPart02+CPUUExecPart03 + CPUUExecPart04+CPUUExecPart05+CPUUExecPart06+CPUUExecPart07 + CPUUExecPart08+CPUUExecPart09+CPUUExecPart10+CPUUExecPart11 + CPUUExecPart12+CPUUExecPart13+CPUUExecPart14+CPUUExecPart15 + Resource Usage Macros and Tables CPUUExecPart16+CPUUExecPart17+CPUUExecPart18+CPUUExecPart19 + CPUUExecPart20+CPUUExecPart21+CPUUExecPart22+CPUUExecPart23 + CPUUExecPart24+CPUUExecPart25+CPUUExecPart26+CPUUExecPart27 + CPUUExecPart28+CPUUExecPart29+CPUUExecPart30+CPUUExecPart31 + CPUUExecPart32+CPUUExecPart33+CPUUExecPart34+CPUUExecPart35 + CPUUExecPart36+CPUUExecPart37+CPUUExecPart38+CPUUExecPart39 + CPUUExecPart40+CPUUExecPart41+CPUUExecPart42+CPUUExecPart43 + CPUUExecPart44+CPUUExecPart45+CPUUExecPart46+CPUUExecPart47), /* PETotalUserServ */ (CPUUServPart00+CPUUServPart01+CPUUServPart02+CPUUServPart03 + CPUUServPart04+CPUUServPart05+CPUUServPart06+CPUUServPart07 + CPUUServPart08+CPUUServPart09+CPUUServPart10+CPUUServPart11 + CPUUServPart12+CPUUServPart13+CPUUServPart14+CPUUServPart15 + CPUUServPart16+CPUUServPart17+CPUUServPart18+CPUUServPart19 + CPUUServPart20+CPUUServPart21+CPUUServPart22+CPUUServPart23 + CPUUServPart24+CPUUServPart25+CPUUServPart26+CPUUServPart27 + CPUUServPart28+CPUUServPart29+CPUUServPart30+CPUUServPart31 + CPUUServPart32+CPUUServPart33+CPUUServPart34+CPUUServPart35 + CPUUServPart36+CPUUServPart37+CPUUServPart38+CPUUServPart39 + CPUUServPart40+CPUUServPart41+CPUUServPart42+CPUUServPart43 + CPUUServPart44+CPUUServPart45+CPUUServPart46+CPUUServPart47) FROM ResUsageSvpr WHERE VprType like 'PE%' WITH CHECK OPTION; ResSawtView ResSawtView is based on the ResUsageSawt table. REPLACE VIEW DBC.ResSawtView AS SELECT /* housekeeping fields */ TheDate, TheTime, NodeId (FORMAT '999-99') AS NodeId, CASE /* Coexistence reporting support */ WHEN NodeId IN ('001-01', '001-03', '001-05') THEN 'A' WHEN NodeId IN ('001-02', '001-04', '001-06') THEN 'A' ELSE 'A' END AS GroupId, NodeType, GmtTime, Secs, CentiSecs, NominalSecs, CollectIntervals, VprId, SummaryFlag, Reserved, /* Spare Field usage */ SpareCount02 AS SpareCount03 AS SpareTmon00 AS Available, AvailableMin, COD, /* Capacity On Demand */ 155 /* transformed fields */ MailBoxDepth/CollectIntervals AS MailBoxDepthAvg, 156 ( WorkTypeInuse00 WorkTypeInuse04 WorkTypeInuse08 WorkTypeInuse12 + + + + WorkTypeInuse01 WorkTypeInuse05 WorkTypeInuse09 WorkTypeInuse13 AS WorkTypeInuse00/CollectIntervals WorkTypeInuse01/CollectIntervals WorkTypeInuse02/CollectIntervals WorkTypeInuse03/CollectIntervals WorkTypeInuse04/CollectIntervals WorkTypeInuse05/CollectIntervals WorkTypeInuse06/CollectIntervals WorkTypeInuse07/CollectIntervals WorkTypeInuse08/CollectIntervals WorkTypeInuse09/CollectIntervals WorkTypeInuse10/CollectIntervals WorkTypeInuse11/CollectIntervals WorkTypeInuse12/CollectIntervals WorkTypeInuse13/CollectIntervals WorkTypeInuse14/CollectIntervals WorkTypeInuse15/CollectIntervals Resource Usage Macros and Tables /* Remaining table fields */ Active, MailBoxDepth, FlowControlled, FlowCtlCnt, FlowCtlTime, InuseMax, WorkTypeInuse00, WorkTypeInuse01, WorkTypeInuse02, WorkTypeInuse03, WorkTypeInuse04, WorkTypeInuse05, WorkTypeInuse06, WorkTypeInuse07, WorkTypeInuse08, WorkTypeInuse09, WorkTypeInuse10, WorkTypeInuse11, WorkTypeInuse12, WorkTypeInuse13, WorkTypeInuse14, WorkTypeInuse15, WorkTypeMax00, WorkTypeMax01, WorkTypeMax02, WorkTypeMax03, WorkTypeMax04, WorkTypeMax05, WorkTypeMax06, WorkTypeMax07, WorkTypeMax08, WorkTypeMax09, WorkTypeMax10, WorkTypeMax11, WorkTypeMax12, WorkTypeMax13, WorkTypeMax14, WorkTypeMax15, AS AS AS AS AS AS AS AS AS AS AS AS AS AS AS AS + WorkTypeInuse02 + WorkTypeInuse06 + WorkTypeInuse10 + WorkTypeInuse14 WorkTypeInuseAvg, + + + + WorkTypeInuse00Avg, WorkTypeInuse01Avg, WorkTypeInuse02Avg, WorkTypeInuse03Avg, WorkTypeInuse04Avg, WorkTypeInuse05Avg, WorkTypeInuse06Avg, WorkTypeInuse07Avg, WorkTypeInuse08Avg, WorkTypeInuse09Avg, WorkTypeInuse10Avg, WorkTypeInuse11Avg, WorkTypeInuse12Avg, WorkTypeInuse13Avg, WorkTypeInuse14Avg, WorkTypeInuse15Avg, WorkTypeInuse03 WorkTypeInuse07 WorkTypeInuse11 WorkTypeInuse15 + + + ) / CollectIntervals Resource Usage Macros and Tables SpareCount00, SpareCount01, SpareCount02, SpareCount03, SpareCount04, SpareCount05, SpareCount06, SpareCount07, SpareCount08, SpareCount09, SpareTrack00, SpareTrack01, SpareTrack02, SpareTrack03, SpareTrack04, SpareTrack05, SpareTrack06, SpareTrack07, SpareTrack08, SpareTrack09, SpareTmon00, SpareTmon01, SpareTmon02, SpareTmon03, SpareTmon04, SpareTmon05, SpareTmon06, SpareTmon07, SpareTmon08, SpareTmon09 FROM ResUsageSawt WITH CHECK OPTION; COMMENT ON VIEW DBC.ResSawtView AS 'View of Sawt table data'; ResShstGroupView ResShstGroupView is based on the ResUsageShst table. REPLACE VIEW DBC.ResShstGroupView ( TheDate, TheTime, NodeId, VprId, HstId, HstType, Secs, NominalSecs, GroupId, CollectIntervals, HostBlockReads, HostBlockWrites, HostMessageReads, HostMessageWrites, HostReadKB, HostWriteKB, HostQLenSum, HostQLenMax, HostReadFails, HostWriteFails ) 157 AS SELECT TheDate, TheTime, NodeId, VprId, HstId, HstType, Secs, /* GroupId */ CASE WHEN NodeId IN ('001-01', '001-03', '001-05') THEN 'A ' WHEN NodeId IN ('001-02', '001-04', '001-06') THEN 'A' ELSE 'A' END, NominalSecs, 158 CollectIntervals, HostBlockReads, HostBlockWrites, HostMessageReads, HostMessageWrites, HostReadKB, HostWriteKB, HostQLenSum, HostQLenMax, HostReadFails, HostWriteFails FROM ResUsageShst WITH CHECK OPTION; ResSldvGroupView ResSldvGroupView is based on the ResUsageSldv table. REPLACE VIEW DBC.ResSldvGroupView ( TheDate, TheTime, NodeId, VprId, LdvId, LdvType, Secs, NominalSecs, GroupId, CollectIntervals, LdvOutReqSum, LdvReads, LdvWrites, LdvReadKB, LdvWriteKB, LdvReadRespTot, LdvWriteRespTot, LdvReadRespMax, LdvWriteRespMax, LdvConcurrentMax, LdvOutReqMax, LdvOutReqTime ) Resource Usage Macros and Tables AS SELECT TheDate, TheTime, NodeId, VprId, LdvId, LdvType, Secs, NominalSecs, /* GroupId */ CASE WHEN NodeId IN ('001-01', '001-03', '001-05') THEN 'A' WHEN NodeId IN ('001-02', '001-04', '001-06') THEN 'A' ELSE 'A' END, CollectIntervals, LdvOutReqSum, LdvReads, LdvWrites, LdvReadKB, LdvWriteKB, LdvReadRespTot, LdvWriteRespTot, LdvReadRespMax, LdvWriteRespMax, LdvConcurrentMax, LdvOutReqMax, LdvOutReqTime FROM ResUsageSldv WITH CHECK OPTION; Resource Usage Macros and Tables ResSpsView ResSpsView is based on the ResUsageSps table. show view ResSpsView; REPLACE VIEW DBC.ResSpsView AS SELECT /* housekeeping fields */ TheDate, TheTime, NodeId (FORMAT '999-99') AS NodeId, CASE /* Coexistence reporting support */ WHEN NodeId IN ('001-01', '001-03', '001-05') THEN 'A' WHEN NodeId IN ('001-02', '001-04', '001-06') THEN 'A' ELSE 'A' END AS GroupId, NodeType, NCPUs, GmtTime, Secs, CentiSecs, NominalSecs, CollectIntervals, PGid, PPid, VprId, VprType, SummaryFlag, 159 /* Spare Field usage */ /* * WorkTimeInuse reports the service time consummed by a WD during the current * reporting interval. This is not the running sum of a WD that * exists over multiple intervals. * Average AWTs used = WorkTimeInuse/(Centisecs*10) * WorkTimeInuseMax reports the maximum service time of a single task in a WD * that is still running or has finished in the current reporting * interval. This includes time used during previous intervals for * that task. This value may be much larger than the reporting period. * ServiceTime reports the time it took a WD to be serviced and is * reported when the AWT is released (task is done). * ServiceTimeMax reports the maximum time it took a single task in a WD * to be serviced that has completed in this reporting interval. * AwtReleases reports the number of AWTs released (completed requests) * while NumRequests reports number that arrived. */ /* Total delay time of messages successfully sent to the workbox */ SpareCount00 AS WorkMsgSendDelay, /* Longest delay of messages successfully sent to the workbox */ SpareCount01 AS WorkMsgSendDelayMax, /* be the total delay time of messages still waiting for AWTs.*/ /* So that would be SpareCount02 */ SpareCount02 AS WorkMsgReceiveDelay, SpareCount03 AS WorkMsgReceiveDelayMax, SpareCount04 AS WorkTimeInuse, /* Average number of AWTs used based on WorkTimeInuse */ SpareCount04/(Centisecs*10) AS AwtUsedAvg, SpareCount05 AS WorkTimeInuseMax, 160 /* 13.1 AwtReleases */ SpareCount06 AS AwtReleases, /* number of messages accumulating the send side delay time */ SpareCount07 AS WorkMsgSendDelayCnt, /* number of messages accumulating the receive side delay time */ SpareCount08 AS WorkMsgReceiveDelayCnt, SpareTrack00 AS AMPcount, /* housekeeping field in 14.0 */ /* Capacity On Demand factor. housekeeping field in 14.0 */ SpareTmon00 AS COD, /* transformed fields */ (CpuTime * 10)/(CentiSecs * NCPUs) AS CpuPct, /* report average wait time per request for messages */ /* already delivered to AWTs */ QWaitTime / NULLIFZERO(NumRequests) AS QWaitTimeRequestAvg, QLength / ( SpareTrack00 * CollectIntervals ) AS QLengthAmpAvg, /* Avg delay for msgs delivered in period per Request */ WorkMsgSendDelay / NULLIFZERO(WorkMsgSendDelayCnt) AS WorkMsgSendDelayRequestAvg, /* requested by Anita Richards to match WorkMsgSendDelayRequestAvg */ /* avg delay per request on receive side for messages not */ /* yet delivered to AWTs */ WorkMsgReceiveDelay / NULLIFZERO(WorkMsgReceiveDelayCnt) AS WorkMsgReceiveDelayRequestAvg, /* report average service time per request */ ServiceTime / NULLIFZERO(AwtReleases) AS ServiceTimeRequestAvg, (CPUUServAWT + CPUUServDisp + CPUUServMisc) AS CPUUServ, (CPUUExecAWT + CPUUExecDisp + CPUUExecMisc) AS CPUUExec, /* All WorkTypes in use, averaged per AMP */ (WorkTypeInuse00 + WorkTypeInuse01 + WorkTypeInuse02 + WorkTypeInuse03 + WorkTypeInuse04 + WorkTypeInuse05 + WorkTypeInuse06 + WorkTypeInuse07 + WorkTypeInuse08 + WorkTypeInuse09 + WorkTypeInuse10 + WorkTypeInuse11 + WorkTypeInuse12 + WorkTypeInuse13 + WorkTypeInuse14 + WorkTypeInuse15) / ( SpareTrack00 * CollectIntervals ) AS WorkTypeInuseAmp, /* Max of (WorkTypesInuse for all AMPs (NOT per AMP) */ /* Can NOT divide by AMPs. MAX(SUM(AWT[AMP])) */ (WorkTypeMax00 + WorkTypeMax01 + WorkTypeMax02 + WorkTypeMax03 + WorkTypeMax04 + WorkTypeMax05 + WorkTypeMax06 + WorkTypeMax07 + WorkTypeMax08 + WorkTypeMax09 + WorkTypeMax10 + WorkTypeMax11 + WorkTypeMax12 + WorkTypeMax13 + WorkTypeMax14 + WorkTypeMax15) AS WorkTypeInuseMax, Resource Usage Macros and Tables (ProcBlksFsgRead + ProcBlksFsgWrite + ProcBlksFsgNIOs) AS IODelay, (ProcWaitFsgRead + ProcWaitFsgWrite + ProcWaitFsgNIOs) AS IODelayTime, (FilePDbAcqs + FilePDbPres) AS LogicalReadPerm, FilePDbDyRRels AS LogicalWritePerm, (FilePDbAcqReads + FilePDbPreReads) AS PhysicalReadPerm, FilePDbFWrites AS PhysicalWritePerm, FilePDbFWriteKB AS PhysicalWritePermKB, (NetPtPReads + NetBrdReads) AS NetReads, (NetPtPWrites + NetBrdWrites) AS NetWrites, (FilePCiAcqs + FileSDbAcqs + FileSCiAcqs + FileTJtAcqs + FileAPtAcqs + FilePCiPres + FileSDbPres + FileSCiPres + FileTJtPres + FileAPtPres) AS LogicalReadOther, (FilePCiAcqReads + FileSDbAcqReads + FileSCiAcqReads + FileTJtAcqReads + FileAPtAcqReads + FilePCiPreReads + FileSDbPreReads + FileSCiPreReads + FileTJtPreReads + FileAPtPreReads) AS PhysicalReadOther, (FilePCiDyRRels + FileSDbDyRRels + FileSCiDyRRels + FileTJtDyRRels + FileAPtDyRRels) Resource Usage Macros and Tables AS LogicalWriteOther, + FileSCiFWrites + FileTJtFWrites + FileAPtFWrites) AS PhysicalWriteOther, (FilePCiFWriteKB + FileSDbFWriteKB + FileSCiFWriteKB + FileTJtFWriteKB + FileAPtFWriteKB) AS PhysicalWriteOtherKB, (FilePCiFWrites ( FilePDbAcqKB ( FileSDbAcqKB FilePCiAcqKB FilePCiPresKB + FileSDbFWrites + + + + FilePDbPresKB FileSDbPresKB FileSCiAcqKB FileSCiPresKB ) AS LogicalReadPermKB, + + FileTJtAcqKB + FileAPtAcqKB + + FileTJtPresKB + FileAPtPresKB) AS LogicalReadOtherKB, ( FilePDbPreReadKB + FilePDbAcqReadKB ) AS PhysicalReadPermKB, ( FilePCiAcqReadKB + FileSDbAcqReadKB + FileSCiAcqReadKB + FileTJtAcqReadKB + FileAPtAcqReadKB + FilePCiPreReadKB + FileSDbPreReadKB + FileSCiPreReadKB + FileTJtPreReadKB + FileAPtPreReadKB) AS PhysicalReadOtherKB, FilePDbDyRRelKB AS LogicalWritePermKB, ( FileSDbDyRRelKB + FilePCiDyRRelKB + FileSCiDyRRelKB + FileTJtDyRRelKB + FileAPtDyRRelKB) AS LogicalWriteOtherKB, (ProcBlksSegNoVirtual + ProcBlksSegMDL + ProcBlksSegLock) AS ProcBlksSeg, (ProcWaitSegNoVirtual + ProcWaitSegMDL + ProcWaitSegLock) AS ProcWaitSeg, (ProcBlksMisc + ProcBlksNetThrottle + ProcBlksQnl +ProcBlksTime + ProcBlksFlowControl) AS ProcBlksMisc, /* ProcBlksMonResume = covered by DBLocks */ (ProcWaitMisc + ProcWaitMonResume + ProcWaitNetThrottle +ProcWaitQnl + ProcWaitTime + ProcWaitFlowControl) AS ProcWaitMisc, 161 /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * */ obsolete Sps table fields removed in 14.0 CPUUServPars, CPUUExecPars, FileFcrRequests, FileFcrRequestsAdaptive, FileFcrBlocksRead, FileFcrBlocksDeniedUser, FileFcrBlocksDeniedKern, FileFcrBlocksDeniedCache, FileFcrBlocksDeniedThreshUser, FileFcrDeniedUser, FileFcrDeniedKern, FileFcrDeniedCache, FileFcrDeniedThreshUser, FileCylMigrs, FileCylAllocs, FileSyncScans, FileSyncSubtables, FileSyncScanners, FileSyncGroups, FileWCylAllocs, FileWCylFrees, MsgChnLastDone, MemCtxtAllocs, MemKBRes, FlowControlled, FlowCtlCnt, AllocatorExtentAllocReqs, AllocatorExtentFreeReqs, AllocatorMapIOsStarted, AllocatorMapIOsDone, NodeAgentMigrationsStarted, NodeAgentMigrationsDone, NodeAgentStatProcessed, 162 /* Remaining table fields */ Active, WDid, AGid, RelWgt, CpuTime, IOBlks, NumProcs, NumSets, NumRequests, QWaitTime, QWaitTimeMax, QLength, QLengthMax, ServiceTime, ServiceTimeMax, Resource Usage Macros and Tables SpareCount00, SpareCount01, SpareCount02, SpareCount03, SpareCount04, SpareCount05, SpareCount06, SpareCount07, SpareCount08, SpareCount09, SpareTrack00, SpareTrack01, SpareTrack02, SpareTrack03, SpareTrack04, SpareTrack05, SpareTrack06, SpareTrack07, SpareTrack08, SpareTrack09, SpareTmon00, SpareTmon01, SpareTmon02, SpareTmon03, SpareTmon04, SpareTmon05, SpareTmon06, SpareTmon07, SpareTmon08, SpareTmon09, CPUUServAWT, CPUUServDisp, CPUUServMisc, CPUUExecAWT, CPUUExecDisp, CPUUExecMisc, MemAllocs, MemAllocKB, /* QWaitTime = wait time of messages delivered. */ /* WorkMsgReceiveDelay in SpareCount02 is wait time of /* messages still waiting for AWTs. */ /* /* /* /* /* /* /* /* /* 13.1 13.1 13.1 13.1 13.1 13.1 13.1 13.1 13.1 WorkMsgSendDelay WorkMsgSendDelayMax WorkMsgReceiveDelay WorkMsgReceiveDelayMax WorkTimeInuse WorkTimeInuseMax AwtReleases WorkMsgSendDelayCnt WorkMsgReceiveDelayCnt /* 13.1 AMPcount /* 14.0:hdr COD: CodFactor */ */ */ */ */ */ */ */ */ */ */ */ Resource Usage Macros and Tables /* AWT fields */ WorkTypeInuse00, WorkTypeInuse01, WorkTypeInuse02, WorkTypeInuse03, WorkTypeInuse04, WorkTypeInuse05, WorkTypeInuse06, WorkTypeInuse07, WorkTypeInuse08, WorkTypeInuse09, WorkTypeInuse10, WorkTypeInuse11, WorkTypeInuse12, WorkTypeInuse13, WorkTypeInuse14, WorkTypeInuse15, WorkTypeMax00, WorkTypeMax01, WorkTypeMax02, WorkTypeMax03, WorkTypeMax04, WorkTypeMax05, WorkTypeMax06, WorkTypeMax07, WorkTypeMax08, WorkTypeMax09, WorkTypeMax10, WorkTypeMax11, WorkTypeMax12, WorkTypeMax13, WorkTypeMax14, WorkTypeMax15, FilePDbAcqs, FilePCiAcqs, FileSDbAcqs, FileSCiAcqs, FileTJtAcqs, FileAPtAcqs, FilePDbAcqKB, FilePCiAcqKB, FileSDbAcqKB, FileSCiAcqKB, FileTJtAcqKB, FileAPtAcqKB, FilePDbAcqReads, FilePCiAcqReads, FileSDbAcqReads, FileSCiAcqReads, FileTJtAcqReads, FileAPtAcqReads, 163 FilePDbAcqReadKB, FilePCiAcqReadKB, FileSDbAcqReadKB, FileSCiAcqReadKB, FileTJtAcqReadKB, FileAPtAcqReadKB, 164 FilePDbPres, FilePCiPres, FileSDbPres, FileSCiPres, FileTJtPres, FileAPtPres, FilePDbPresKB, FilePCiPresKB, FileSDbPresKB, FileSCiPresKB, FileTJtPresKB, FileAPtPresKB, FilePDbPreReads, FilePCiPreReads, FileSDbPreReads, FileSCiPreReads, FileTJtPreReads, FileAPtPreReads, FilePDbPreReadKB, FilePCiPreReadKB, FileSDbPreReadKB, FileSCiPreReadKB, FileTJtPreReadKB, FileAPtPreReadKB, FilePDbDyRRels, FilePCiDyRRels, FileSDbDyRRels, FileSCiDyRRels, FileTJtDyRRels, FileAPtDyRRels, FilePDbDyRRelKB, FilePCiDyRRelKB, FileSDbDyRRelKB, FileSCiDyRRelKB, FileTJtDyRRelKB, FileAPtDyRRelKB, Resource Usage Macros and Tables FilePDbFWrites, FilePCiFWrites, FileSDbFWrites, FileSCiFWrites, FileTJtFWrites, FileAPtFWrites, FilePDbFWriteKB, FilePCiFWriteKB, FileSDbFWriteKB, FileSCiFWriteKB, FileTJtFWriteKB, FileAPtFWriteKB, NetPtPReads, NetPtPWrites, NetPtPReadKB, NetPtPWriteKB, NetBrdReads, NetBrdWrites, Resource Usage Macros and Tables /* TVSA fields */ ReadsHot, ReadsWarm, ReadsCold, WritesHot, WritesWarm, WritesCold, /* Process blocking reasons: ProcBlks count of blocks */ ProcBlksSegNoVirtual, ProcBlksFsgNIOs, ProcBlksSegMDL, ProcBlksMonResume, ProcBlksNetThrottle, ProcBlksQnl, ProcBlksFsgRead, ProcBlksFsgWrite, ProcBlksDBLock, ProcBlksMonitor, ProcBlksSegLock, ProcBlksFsgLock, ProcBlksTime, ProcBlksFlowControl, ProcBlksCpuLimit, /* Process blocking reasons: ProcWait time in milliseconds */ ProcWaitSegNoVirtual, ProcWaitFsgNIOs, ProcWaitSegMDL, ProcWaitMonResume, ProcWaitNetThrottle, ProcWaitQnl, ProcWaitFsgRead, ProcWaitFsgWrite, ProcWaitDBLock, ProcWaitMonitor, ProcWaitSegLock, ProcWaitFsgLock, ProcWaitTime, ProcWaitFlowControl, ProcWaitCpuLimit FROM ResUsageSps WITH CHECK OPTION; 165 166 ResSvprView Use the ResSvprView to access the ResUsageSvpr table data. This view allows data to be properly presented and reports all the columns available from the ResUsageSvpr table. Note: The data columns in this view will change as the columns in the ResUsageSvpr table change. REPLACE VIEW DBC.ResSvprView AS SELECT /* housekeeping fields */ NodeID, thedate, thetime, GmtTime, vprid, VprType, NodeType, NCPUs, Secs, CentiSecs, NominalSecs, CollectIntervals, SummaryFlag, Reserved, /* transformed fields */ /* user defined co-existence system node groupings */ CASE WHEN NodeId IN ('001-01', '001-03', '001-05') THEN 'A' WHEN NodeId IN ('001-02', '001-04', '001-06') THEN 'A' ELSE 'A' END AS GroupId, Resource Usage Macros and Tables /* Spare field usage */ SpareCount00 AS DBMergeTried, SpareCount01 AS DBMergeDone, SpareCount02 AS DBMergeElim, SpareCount03 AS DBMergeExtrIO, SpareCount04 AS FileACPCylsSkipped, SpareCount05 AS FileACPCylsMigr, SpareCount06 AS FileACPCylsUnFSEOnly, SpareCount07 AS FileACPCylsPostponed, SpareTmon00 SpareTmon01 SpareTmon02 SpareTmon03 AS AS AS AS COD, FSGCacheWaits, FSGCacheWaitTime, FSGCacheWaitTimeMax, /* SVpr table fields (remaining) */ Active, ProcPendDBLock, ProcBlksDBLock, ProcWaitDBLock, MemPDbAllocs, MemPCiAllocs, MemSDbAllocs, MemSCiAllocs, Resource Usage Macros and Tables MemTJtAllocs, MemAPtAllocs, MemPDbAllocKB, MemPCiAllocKB, MemSDbAllocKB, MemSCiAllocKB, MemTJtAllocKB, MemAPtAllocKB, MemCtxtAllocs, MemCtxtRes, MemPKBResFrz, MemPDbKBResCU, MemPCiKBResCU, MemSDbKBResCU, MemSCiKBResCU, MemTJtKBResCU, MemAPtKBResCU, MemPDbKBResDU, MemPCiKBResDU, MemSDbKBResDU, MemSCiKBResDU, MemTJtKBResDU, MemAPtKBResDU, MemPDbKBResCA, MemPCiKBResCA, MemSDbKBResCA, MemSCiKBResCA, MemTJtKBResCA, MemAPtKBResCA, MemPDbKBResDA, MemPCiKBResDA, MemSDbKBResDA, MemSCiKBResDA, MemTJtKBResDA, MemAPtKBResDA, MemCtxtPageReads, MemCtxtPageWrites, MemSwapDrops, MemSwapDropKB, MemSwapReads, MemSwapReadKB, MemCtxtAccesses, MemCtxtAccessKB, MemCtxtDeaccesses, MemCtxtDeaccessKB, MemCtxtDestroys, MemCtxtDestroyKB, NetPtPReads, NetPtPWrites, NetPtPReadKB, NetPtPWriteKB, NetBrdReads, NetBrdWrites, NetBrdReadKB, NetBrdWriteKB, MsgWorkQLenMax, DBLockEnters, DBLockBlocks, DBLockDeadlocks, DBLockBlocksMax, DBLocksHeldMax, 167 FilePDbAcqs, 168 Resource Usage Macros and Tables FilePCiAcqs, FileSDbAcqs, FileSCiAcqs, FileTJtAcqs, FileAPtAcqs, FilePDbAcqKB, FileSDbAcqKB, FilePCiAcqKB, FileSCiAcqKB, FileTJtAcqKB, FileAPtAcqKB, FilePDbAcqReads, FilePCiAcqReads, FileSDbAcqReads, FileSCiAcqReads, FileTJtAcqReads, FileAPtAcqReads, FilePDbAcqReadKB, FilePCiAcqReadKB, FileSDbAcqReadKB, FileSCiAcqReadKB, FileTJtAcqReadKB, FileAPtAcqReadKB, FilePDbPres, FilePCiPres, FileSDbPres, FileSCiPres, FileTJtPres, FileAPtPres, FilePDbPresKB, FileSDbPresKB, FilePCiPresKB, FileSCiPresKB, FileTJtPresKB, FileAPtPresKB, FilePDbPreReads, FilePCiPreReads, FileSDbPreReads, FileSCiPreReads, FileTJtPreReads, FileAPtPreReads, FilePDbPreReadKB, FileSDbPreReadKB, FilePCiPreReadKB, FileSCiPreReadKB, FileTJtPreReadKB, FileAPtPreReadKB, FilePDbDyRRels, FilePCiDyRRels, FileSDbDyRRels, FileSCiDyRRels, FileTJtDyRRels, FileAPtDyRRels, FilePDbDyRRelKB, FileSDbDyRRelKB, FilePCiDyRRelKB, FileSCiDyRRelKB, FileTJtDyRRelKB, FileAPtDyRRelKB, FilePDbFWrites, FilePCiFWrites, FileSDbFWrites, FileSCiFWrites, Resource Usage Macros and Tables 169 FileTJtFWrites, FileAPtFWrites, FilePDbFWriteKB, FilePCiFWriteKB, FileSDbFWriteKB, FileSCiFWriteKB, FileTJtFWriteKB, FileAPtFWriteKB, FilePDbDyAWrites, FilePCiDyAWrites, FileSDbDyAWrites, FileSCiDyAWrites, FileTJtDyAWrites, FileAPtDyAWrites, FilePDbDyAWriteKB, FilePCiDyAWriteKB, FileSDbDyAWriteKB, FileSCiDyAWriteKB, FileTJtDyAWriteKB, FileAPtDyAWriteKB, FilePDbCnRRels, FilePCiCnRRels, FileSDbCnRRels, FileSCiCnRRels, FileTJtCnRRels, FileAPtCnRRels, FilePDbCnRRelKB, FileSDbCnRRelKB, FilePCiCnRRelKB, FileSCiCnRRelKB, FileTJtCnRRelKB, FileAPtCnRRelKB, FilePDbFDrps, FilePCiFDrps, FileSDbFDrps, FileSCiFDrps, FileTJtFDrps, FileAPtFDrps, FilePDbFDrpKB, FileSDbFDrpKB, FilePCiFDrpKB, FileSCiFDrpKB, FileTJtFDrpKB, FileAPtFDrpKB, FilePDbCnADrps, FilePCiCnADrps, FileSDbCnADrps, FileSCiCnADrps, FileTJtCnADrps, FileAPtCnADrps, FilePDbCnADrpKB, FilePCiCnADrpKB, FileSDbCnADrpKB, FileSCiCnADrpKB, FileTJtCnADrpKB, FileAPtCnADrpKB, FileLockEnters, FileLockBlocks, FileLockDeadlocks, FileCylMigrs, FileCylAllocs, FileCylFrees, FileMCylPacks, 170 Resource Usage Macros and Tables FileCylDefrags, FileWCylAllocs, FileWCylFrees, FileFcrRequests, FileFcrRequestsAdaptive, FileFcrDeniedUser, FileFcrDeniedCache, FileFcrDeniedThreshUser, FileFcrBlocksRead, FileFcrBlocksDeniedUser, FileFcrBlocksDeniedCache, FileFcrBlocksDeniedThreshUser, FileFcrDeniedKern, FileFcrDeniedThreshKern, FileFcrBlocksDeniedKern, FileFcrBlocksDeniedThreshKern, FileSyncScans, FileSyncSubtables, FileSyncScanners, FileSyncGroups, MsgChnLastDone, MsgWorkQLenSum, DBLockBlocksSum, DBLocksHeldSum, CPUUServPart00, CPUUServPart01, CPUUServPart02, CPUUServPart03, CPUUServPart04, CPUUServPart05, CPUUServPart06, CPUUServPart07, CPUUServPart08, CPUUServPart09, CPUUServPart10, CPUUServPart11, CPUUServPart12, CPUUServPart13, CPUUServPart14, CPUUServPart15, CPUUServPart16, CPUUServPart17, CPUUServPart18, CPUUServPart19, CPUUServPart20, CPUUServPart21, CPUUServPart22, CPUUServPart23, CPUUServPart24, CPUUServPart25, CPUUServPart26, CPUUServPart27, CPUUServPart28, CPUUServPart29, CPUUServPart30, CPUUServPart31, CPUUServPart32, CPUUServPart33, CPUUServPart34, CPUUServPart35, CPUUServPart36, CPUUServPart37, CPUUServPart38, Resource Usage Macros and Tables 171 CPUUServPart39, CPUUServPart40, CPUUServPart41, CPUUServPart42, CPUUServPart43, CPUUServPart44, CPUUServPart45, CPUUServPart46, CPUUServPart47, CPUUExecPart00, CPUUExecPart01, CPUUExecPart02, CPUUExecPart03, CPUUExecPart04, CPUUExecPart05, CPUUExecPart06, CPUUExecPart07, CPUUExecPart08, CPUUExecPart09, CPUUExecPart10, CPUUExecPart11, CPUUExecPart12, CPUUExecPart13, CPUUExecPart14, CPUUExecPart15, CPUUExecPart16, CPUUExecPart17, CPUUExecPart18, CPUUExecPart19, CPUUExecPart20, CPUUExecPart21, CPUUExecPart22, CPUUExecPart23, CPUUExecPart24, CPUUExecPart25, CPUUExecPart26, CPUUExecPart27, CPUUExecPart28, CPUUExecPart29, CPUUExecPart30, CPUUExecPart31, CPUUExecPart32, CPUUExecPart33, CPUUExecPart34, CPUUExecPart35, CPUUExecPart36, CPUUExecPart37, CPUUExecPart38, CPUUExecPart39, CPUUExecPart40, CPUUExecPart41, CPUUExecPart42, CPUUExecPart43, CPUUExecPart44, CPUUExecPart45, CPUUExecPart46, CPUUExecPart47, AllocatorExtentAllocReqs, AllocatorExtentFreeReqs, AllocatorMapIOsStarted, AllocatorMapIOsDone, NodeAgentMigrationsStarted, NodeAgentMigrationsDone, 172 Resource Usage Macros and Tables NodeAgentStatProcessed, ReadResponseHotTotal, ReadResponseWarmTotal, ReadResponseColdTotal, WriteResponseHotTotal, WriteResponseWarmTotal, WriteResponseColdTotal, ReadsHot, ReadsWarm, ReadsCold, WritesHot, WritesWarm, WritesCold, ReadResponseHotMax, ReadResponseWarmMax, ReadResponseColdMax, ReadResponseHotMin, ReadResponseWarmMin, ReadResponseColdMin, WriteResponseHotMax, WriteResponseWarmMax, WriteResponseColdMax, WriteResponseHotMin, WriteResponseWarmMin, WriteResponseColdMin, IoRespMax, IoGapMax, MIWrites, MIWriteTime, MIWriteTimeMax, MIWriteLocks, MIWriteLockTime, MIWriteLockTimeMax, MISleeps, MISleepTime, MISleepTimeMax, SpareCount00, SpareCount01, SpareCount02, SpareCount03, SpareCount04, SpareCount05, SpareCount06, SpareCount07, SpareCount08, SpareCount09, SpareTrack00, SpareTrack01, SpareTrack02, SpareTrack03, SpareTrack04, SpareTrack05, SpareTrack06, SpareTrack07, SpareTrack08, SpareTrack09, SpareTmon00, SpareTmon01, SpareTmon02, SpareTmon03, SpareTmon04, SpareTmon05, SpareTmon06, Resource Usage Macros and Tables SpareTmon07, SpareTmon08, SpareTmon09 FROM ResUsageSvpr WITH CHECK OPTION; COMMENT ON VIEW DBC.ResSvprView AS 'View of ResUsageSvpr (per Vproc) table data'; 173 Chapter 14: Resource Usage Views ResSvprView 174 Resource Usage Macros and Tables Chapter 15: Resource Usage Macros Macro Output Format CHAPTER 15 Resource Usage Macros This chapter describes the output format of the resource usage macros and each macro. Macro Output Format Resource usage macros provide output in the following general format.