AWR Report in Greenplum Database

Automatic Workload Repository (AWR) in Oracle Database is very common for Oracle DBA. We are working on creating similar report in Greenplum.

Script name: gpdb_awr_rpt.sh

SQL Scripts: gpdb_awr_rpt.sql

Email us at gpdba@greenplumdba.com with subject "Add to AWR" if you have a SQL scripts that you want to add in this. I have around 50 SQL scripts that is used in this SQL scripts. A sample (Partial) report output is given below/ 

There are different sections in the report to look at. Report is fully customizable.

Note: Some of the information is hardcoded so show the clearity. Sample was ran against 2 node greenplum database on my Linux Server 

Report output:

[gpadmin@sachi scripts]$ ./gpdb_awr_report.sh

SCRIPT VARIABLES LIST /home/gpadmin/maintenance/scripts/db_param EXISTS

######################################################################################################################

DCA_ALIAS = DEV_DCA

SCRIPTS_DIR = /home/gpadmin/maintenance/scripts

SCRIPTS_LOG = /home/gpadmin/maintenance/logs

MAIL_CONTACTS = $SCRIPTS_DIR/mail_contacts

*****************************************************************************************************

2014-10-17 23:25:05 EDT Generating AWR Report.

****************************************************************************************************

Report is being generated. Please wait.... It may take few minutes...

**************************************************************************************************************************************

2014-10-17 23:25:07 EDT END AWR Report Generated. Report is available at /home/gpadmin/maintenance/reports

Filename:/home/gpadmin/maintenance/reports/sachi_dca_stat_rpt_ouput_2014-10-17_23:25.txt

Time spent to generate this report, 00:00:02

**************************************************************************************************************************************

[gpadmin@sachi scripts]$ view /home/gpadmin/maintenance/reports/sachi_dca_stat_rpt_ouput_2014-10-17_23:25.txt

[gpadmin@sachi scripts]$ wc /home/gpadmin/maintenance/reports/sachi_dca_stat_rpt_ouput_2014-10-17_23:25.txt

2266 41834 504385 /home/gpadmin/maintenance/reports/sachi_dca_stat_rpt_ouput_2014-10-17_23:25.txt

[gpadmin@sachi scripts]$

#######################################################################################################################

Greenplum AWR Report

Date created: 2014-10-17_18:09

DCA Hostname: sachi

For bug fixes and enhancements, contact Sachchida (Sachi) N Ojha at gpdba@greenplumdba.com

########################################################################################################################

SYSTEM INFORMATION

DCA VERSION:DCA V1.2.1.1

Hostname:sachi

OS Version:Red Hat Enterprise Linux Server release 6.5 (Santiago)

Greenplum database version:gpstate version 4.2.2.4 build 1 Community Edition

----------------------------------------------------------------------------------------

-- Section 1 - DCA Segments Health Check

----------------------------------------------------------------------------------------

connection_details

-----------------------------------------------------------

You are now connected to database gpadmin as user gpadmin

(1 row)

-- check total no of segments in the DCA and working

total_segments_in_the_database

--------------------------------

3

(1 row)

--check_GP_server_segment status

dbid | content | role | preferred_role | mode | status | port | hostname | address

------+---------+------+----------------+------+--------+-------+----------+---------

1 | -1 | p | p | s | u | 5432 | sachi | sachi

2 | 0 | p | p | s | u | 40000 | sachi | sachi

3 | 1 | p | p | s | u | 40001 | sachi | sachi

(3 rows)

--check storage utilization per segment server

You are now connected to database "gpperfmon" as user "gpadmin".

ctime | hostname | filesystem | total_bytes | used_bytes | avail_bytes | pct_used

---------------------+----------+------------+-------------+------------+-------------+----------

2014-06-20 11:55:00 | sachi | /home | 177 GB | 136 GB | 41 GB | 76.87%

2014-06-20 11:55:00 | sachi | / | 49 GB | 12 GB | 38 GB | 23.76%

2014-06-20 11:55:00 | sachi | /boot | 484 MB | 80 MB | 404 MB | 16.51%

2014-06-20 11:55:00 | sachi | /usr | 49 GB | 5508 MB | 44 GB | 10.93%

2014-06-20 11:55:00 | sachi | /opt | 49 GB | 1101 MB | 48 GB | 2.19%

2014-06-20 11:55:00 | sachi | /software | 197 GB | 2481 MB | 194 GB | 1.23%

2014-06-20 11:55:00 | sachi | /disk2 | 197 GB | 1105 MB | 196 GB | 0.55%

2014-06-20 11:55:00 | sachi | /disk1 | 197 GB | 1104 MB | 196 GB | 0.55%

2014-06-20 11:55:00 | sachi | /app | 197 GB | 377 MB | 196 GB | 0.19%

2014-06-20 11:55:00 | sachi | /vm | 98 GB | 188 MB | 98 GB | 0.19%

2014-06-20 11:55:00 | sachi | /disk3 | 197 GB | 271 MB | 197 GB | 0.13%

2014-06-20 11:55:00 | sachi | /disk4 | 197 GB | 187 MB | 197 GB | 0.09%

2014-06-20 11:55:00 | sachi | /tmp | 197 GB | 188 MB | 197 GB | 0.09%

(13 rows)

Disk Space Availability

total_tb | used_tb | available_tb | pct_used | pct_available

----------+---------+--------------+----------+---------------

0.19 | 0.00 | 0.19 | 0.00 | 100.00

(1 row)

You are now connected to database "sachi" as user "gpadmin".

----------------------------------------------------------------------------------------

-- Section 2 - List Databases, Schemas, Users and Roles

----------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------

-- Section 3 - Grants and Privileges Report

----------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------

-- Section 4 - Schema Objects Report

----------------------------------------------------------------------------------------

-- List Schema Objects with type and storage class

---------------------------------------------------------------------------------------------------

-- Section 5 - Space Monitoring - Database, Schema, Table and Index Size

--------------------------------------------------------------------------------------------------

--Database size

datname | size

-----------+---------

gpadmin | 29 MB

postgres | 29 MB

template1 | 28 MB

template0 | 27 MB

himanshu | 28 MB

sachi | 1400 MB

gpperfmon | 284 MB

(7 rows)

-- Schema size

schemaname | table_size_gb | index_size_gb | schema_size_gb

------------+---------------+---------------+----------------

public | 1.30 | 0.02 | 1.32

(1 row)

-- Table and Index Size

schema_name | table_name | table_gb | index_gb | total_gb

-------------+----------------------+----------+----------+----------

public | bloattest | 1.282 | 0.021 | 1.303

public | sales | 0.012 | 0.000 | 0.012

public | cms_qlz | 0.002 | 0.000 | 0.002

public | cms_zlib | 0.002 | 0.000 | 0.002

public | cms_zlib9 | 0.002 | 0.000 | 0.002

public | wwearthquakes_lastwk | 0.001 | 0.000 | 0.001

public | abc | 0.000 | 0.000 | 0.000

public | cms | 0.000 | 0.000 | 0.000

public | cms_part | 0.000 | 0.000 | 0.000

public | err_earthquakes | 0.000 | 0.000 | 0.000

public | foo | 0.000 | 0.000 | 0.000

public | foo1 | 0.000 | 0.000 | 0.000

public | sachi | 0.000 | 0.000 | 0.000

public | sachi3 | 0.000 | 0.000 | 0.000

public | test | 0.000 | 0.000 | 0.000

public | usha | 0.000 | 0.000 | 0.000

public | usha1 | 0.000 | 0.000 | 0.000

public | usha3 | 0.000 | 0.000 | 0.000

(18 rows)

----------------------------------------------------------------------------------------

-- Section 6 - Database Locks and Resource Queues

----------------------------------------------------------------------------------------

--check_waiting_lock

usename | count

---------+-------

(0 rows)

--check_size_of_resource_queue

rsqname | rsqcountlimit | rsqcountvalue | rsqcostlimit | rsqcostvalue | rsqwaiters | rsqholders

------------+---------------+---------------+--------------+--------------+------------+------------

perf_test | 2 | 0 | 1e+08 | 0 | 0 | 0

myque3 | -1 | | 1e+08 | 0 | 0 | 0

cdr_test | 25 | 0 | -1 | | 0 | 0

myque1 | 20 | 0 | -1 | | 0 | 0

highrq | 20 | 0 | -1 | | 0 | 0

myque2 | 20 | 0 | 1e+08 | 0 | 0 | 0

pg_default | 20 | 0 | -1 | | 0 | 0

(7 rows)

-- check resource queue usage by user activity

----------------------------------------------------------------------------------------

-- Section 7 - Current Database Activity

----------------------------------------------------------------------------------------

--check_current_activity

datid | datname | procpid | sess_id | usesysid | usename | waiting | query_start | backend_start | xact_start | application_name | current_query

-------+-----------+---------+---------+----------+---------+---------+-------------------------------+-------------------------------+-------------------------------+------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------

16993 | sachi | 25246 | 147 | 10 | gpadmin | f | 2014-10-17 18:09:38.671451-04 | 2014-10-17 18:09:37.345324-04 | 2014-10-17 18:09:38.671451-04 | psql | SELECT datid, datname, procpid, sess_id, usesysid, usename, waiting, query_start, backend_start, xact_start, application_name, current_query FROM pg_stat_activity ;

16992 | gpadmin | 13812 | 105 | 10 | gpadmin | f | | 2014-10-17 17:27:40.062017-04 | | psql | <IDLE>

33553 | gpperfmon | 14131 | 106 | 10 | gpadmin | f | 2014-10-17 18:05:30.369481-04 | 2014-10-17 17:37:46.340393-04 | | psql | <IDLE>

(3 rows)

--check_user_activity_by_database

datname | usename | count

-----------+---------+-------

gpadmin | gpadmin | 1

gpperfmon | gpadmin | 1

sachi | gpadmin | 1

(3 rows)

-- Check sessins started and idle for more than 60 minutes

datid | datname | procpid | sess_id | usesysid | usename | current_query | waiting | query_start | backend_start | client_addr | client_port | application_name | xact_start

-------+---------+---------+---------+----------+---------+---------------+---------+-------------+---------------+-------------+-------------+------------------+------------

(0 rows)

----------------------------------------------------------------------------------------

-- Section 8 - Active Database Monitoring

----------------------------------------------------------------------------------------

-- check missing stats report

smischema | smitable | smisize | smicols | smirecs

-----------+-----------------------------------------------------------------+---------+---------+---------

public | sachitest | f | 2 | 0

public | gp_log_master_ext | f | 30 | 0

public | usha | f | 1 | 0

public | usha1 | f | 1 | 0

public | sachi3 | f | 1 | 0

public | sachi | f | 3 | 3

public | test | f | 3 | 0

public | err_earthquakes | f | 8 | 0

public | cms_part_1_prt_p1 | f | 11 | 0

public | cms_part_1_prt_p2 | f | 11 | 0

public | cms_part_1_prt_p3 | f | 11 | 0

public | cms_part_1_prt_p4 | f | 11 | 0

public | cms_part_1_prt_p5 | f | 11 | 0

public | cms_part_1_prt_p6 | f | 11 | 0

public | cms_part_1_prt_p7 | f | 11 | 0

public | cms_part_1_prt_p8 | f | 11 | 0

public | cms_part_1_prt_p9 | f | 11 | 0

public | cms_part_1_prt_p10 | f | 11 | 0

public | cms_part_1_prt_p11 | f | 11 | 0

public | cms_part_1_prt_p12 | f | 11 | 0

public | cms_part_1_prt_p13 | f | 11 | 0

public | cms_part_1_prt_p14 | f | 11 | 0

public | cms_part_1_prt_p15 | f | 11 | 0

public | cms_part_1_prt_p16 | f | 11 | 0

public | cms_part_1_prt_other | f | 11 | 0

public | cms_part | f | 11 | 0

public | cms | f | 11 | 0

public | cms_qlz_1_prt_p1 | f | 11 | 0

public | cms_qlz_1_prt_p2 | f | 11 | 0

public | cms_qlz_1_prt_p3 | f | 11 | 0

public | cms_qlz_1_prt_p4 | f | 11 | 0

....

public | sales_1_prt_2_2_prt_3 | f | 5 | 0

public | sales_1_prt_2_2_prt_4 | f | 5 | 0

public | sales_1_prt_outlying_years_2_prt_other_months | f | 5 | 0

public | sales_1_prt_2_2_prt_5 | f | 5 | 0

public | sales_1_prt_2_2_prt_6 | f | 5 | 0

public | sales_1_prt_2_2_prt_7 | f | 5 | 0

public | sales_1_prt_2_2_prt_8 | f | 5 | 0

public | sales_1_prt_2_2_prt_9 | f | 5 | 0

public | sales_1_prt_2_2_prt_10 | f | 5 | 0

public | sales_1_prt_2_2_prt_11 | f | 5 | 0

public | sales_1_prt_2_2_prt_12 | f | 5 | 0

public | sales_1_prt_2_2_prt_13 | f | 5 | 0

public | sales_1_prt_outlying_years_2_prt_2 | f | 5 | 0

public | sales_1_prt_outlying_years_2_prt_3 | f | 5 | 0

public | sales_1_prt_outlying_years_2_prt_4 | f | 5 | 0

public | sales_1_prt_outlying_years_2_prt_5 | f | 5 | 0

public | sales_1_prt_outlying_years_2_prt_6 | f | 5 | 0

public | sales_1_prt_outlying_years_2_prt_7 | f | 5 | 0

(212 rows)

-- check bloat diagnosis report

bdirelid | bdinspname | bdirelname | bdirelpages | bdiexppages | bdidiag

----------+------------+---------------+-------------+-------------+------------------------------------

1259 | pg_catalog | pg_class | 36 | 9 | moderate amount of bloat suspected

2606 | pg_catalog | pg_constraint | 44 | 11 | moderate amount of bloat suspected

(2 rows)

-- check tables with random distribution key

table_owner | table_name | distribution_keys

-------------+--------------------------+----------------------

gp_toolkit | __gp_localid | DISTRIBUTED RANDOMLY

gp_toolkit | __gp_log_segment_ext | DISTRIBUTED RANDOMLY

gp_toolkit | gp_disk_free | DISTRIBUTED RANDOMLY

public | err_earthquakes | DISTRIBUTED RANDOMLY

public | ext_wwearthquakes_lastwk | DISTRIBUTED RANDOMLY

public | foo | DISTRIBUTED RANDOMLY

public | foo1 | DISTRIBUTED RANDOMLY

public | sachitest | DISTRIBUTED RANDOMLY

(8 rows)

-- check tables with high skew

-- check table not analyzed in last 3 days

nspname | relname | statime | ?column?

---------+---------+---------+----------

(0 rows)

-- check worst performing SQL

You are now connected to database "gpperfmon" as user "gpadmin".

ctime | query_text | username | db | rsqname | run_time | month | day | weekday | hour | cnt | avg_cpu_used | min_cpu_used | max_cpu_used | avg_load2 | min_load2 | max_load2 | avg_disk_rb_rate | min_disk_rb_rate | max_disk_rb_rate | avg_disk_wb_rate | min_disk_wb_rate | max_disk_wb_rate | avg_net_rb_rate | min_net_rb_rate | max_net_rb_rate | avg_net_wb_rate | min_net_wb_rate | max_net_wb_rate | avg_mem_actual_used_gb | min_mem_actual_used_gb | max_mem_actual_used_gb | avg_swap_used | min_swap_used | max_swap_used

-------+------------+----------+----+---------+----------+-------+-----+---------+------+-----+--------------+--------------+--------------+-----------+-----------+-----------+------------------+------------------+------------------+------------------+------------------+------------------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+------------------------+------------------------+------------------------+---------------+---------------+---------------

(0 rows)

----------------------------------------------------------------------------------------

--- Section 9   - Partition Information and Monitoring

 ---------------------------------------------------------------------------------------

Email us at gpdba@greenplumdba.com to get this script or buy our premium email support