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 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 ###################################################################################################################### 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 --------------------------------------------------------------------------------------- New version includes
|
|
|