Greenplum database ASH (active session history) and AWR report light and advanced version

posted Apr 28, 2017, 10:42 AM by Sachchida Ojha
This report displays the information related to active sessions such as
  1.  No of Active Sessions
  2.  No of Waiting Sessions
  3. Resources (CPU, MEMORY, DISK READ, DISK WRITE, NETWORK READ, NETWORK WRITE, LOAD ON DCA) consumed by active sessions
  4. Most used resource queue 
  5. SWAP used
  6. Query Submit time, Start time and Query Text
  7. Which database user is running most of the queries  and what resource queue is being used by those queries
  8.  Locks
  9.  Idle Sessions
  10. Long running queries
Note: As of today, I have not found this report on Pivotal documents so I thought of creating one for my internal use only. 

Being Oracle DBA for almost 22 years I understand the importance of AWR report for DBA's. I have seen most Oracle DBA's use this report to understand and tune the bottleneck of Oracle performance. Recently Oracle has added more advanced feature in this report . I started to build similar kind of AWR report in Greenplum. Though it is not even 20% of what Oracle provides but it gives me lots of information that I can use to monitor health of my Greenplum DCA. Here are 11 sections of the Greenplum AWR Report.

1. Section 1: General Information about DCA - DCA Version, DCA Software Version, OS version, DCA hostname, DB Version and similar about the env.
2. Section 2: DCA Heath Check: This section contains information about DCA segments, Utilization , DCA available and used space etc
3. Section 3: List Databases, Schemas, Users and Roles
5. Section 4: Schema Objects report 
6. Section 5: Grants and Privilege Report
7. Section 6: Space Monitoring - Database, Schema, Table and Index Sizes including Partitions
8. Section 7: Reports on Locks and Resource Queues
8. Section 8: Database activity - various reports on whats going on into your database
9. Section 9: Database health diagnosis - Includes bloat, missing stats, analyze and high Skew reports
10. Sections 10: Partition details and monitoring
11. Section 11: Miscellaneous Report (customer centric)

Light version does not include Section Section 6, 10 and 11.

In order to enhance this report I need feedback from all like minded people working in Greenplum environment. Any new reports/information that will be useful for Greenplum DBA's and SQL to generate this report (if available) will be highly appreciated.

Send your feedback on with subject line "Greenplum ASH and AWR Report".