如何生成和阅读EnterpriseDB (PPAS(Oracle 兼容版)) AWR诊断报告
背景
PPAS是基于PostgreSQL的高度兼容Oracle的商业产品。
不仅语法和Oracle兼容，功能也和Oracle很类似。
例如它也支持生成类似statspack或者AWR报告。
写在前面
rds用户请使用rds_snap, rds_get_snaps, rds_report, rds_truncsnap代替本文的函数。
PPAS AWR不是全局的
注意，PPAS的AWR不是全局的报告，所以需要分析哪个库，就到哪个库去打快照。
应该分析哪个库
首先我们需要创建pg_stat_statements扩展包，从中了解total_time最多的库，去对应的数据库分析。
edb=# select datname,sum(total_time) from pg_stat_statements t1, pg_database t2 where t1.dbid=t2.oid group by 1 order by 2 desc;
datname | sum
---------------+------------------
a | 70971703.7060002
b | 951986.866
c | 1437.653
d | 49.9
e | 1.06
(5 rows)
显然a库的耗时最多，所以我们应该去分析a库。
连接到a库，继续往下看。
如何创建快照
配置参数timed_statistics=true或者在客户端会话中设置timed_statistics=true.
然后创建一个快照
edb=# SELECT * FROM edbsnap();
edbsnap
----------------------
Statement processed.
(1 row)
你可以周期性的创建快照。
例如在ECS上创建一个crontab, 每一个小时打一次快照，并删除7天前的快照。
从而在出现问题时，可以指定时间段生成AWR报告进行分析。
查看已创建的快照
edb=# SELECT * FROM get_snaps();
get_snaps
------------------------------
1 11-FEB-10 10:41:05.668852
2 11-FEB-10 10:42:27.26154
3 11-FEB-10 10:45:48.999992
4 11-FEB-10 11:01:58.345163
5 11-FEB-10 11:05:14.092683
6 11-FEB-10 11:06:33.151002
7 11-FEB-10 11:11:16.405664
8 11-FEB-10 11:13:29.458405
9 11-FEB-10 11:23:57.595916
10 11-FEB-10 11:29:02.214014
11 11-FEB-10 11:31:44.244038
(11 rows)
如何生成诊断报告
然后选择两个快照，产生这两个快照时间点之间的数据库运行诊断报告。
SELECT * FROM edbreport(beginning_id, ending_id);
返回指定快照范围内，TOP N的系统等待信息
sys_rpt(beginning_id, ending_id, top_n)
edb=# SELECT * FROM sys_rpt(9, 10, 10);
sys_rpt
-----------------------------------------------------------------------------
WAIT NAME COUNT WAIT TIME % WAIT
---------------------------------------------------------------------------
wal write 21250 104.723772 36.31
db file read 121407 72.143274 25.01
wal flush 84185 51.652495 17.91
wal file sync 712 29.482206 10.22
infinitecache write 84178 15.814444 5.48
db file write 84177 14.447718 5.01
infinitecache read 672 0.098691 0.03
db file extend 190 0.040386 0.01
query plan 52 0.024400 0.01
wal insert lock acquire 4 0.000837 0.00
(12 rows)
返回指定快照范围内，TOP N的会话等待信息
sess_rpt(beginning_id, ending_id, top_n)
SELECT * FROM sess_rpt(18, 19, 10);
sess_rpt
-----------------------------------------------------------------------------
ID USER WAIT NAME COUNT TIME(ms) %WAIT SES %WAIT ALL
----------------------------------------------------------------------------
17373 enterprise db file read 30 0.175713 85.24 85.24
17373 enterprise query plan 18 0.014930 7.24 7.24
17373 enterprise wal flush 6 0.004067 1.97 1.97
17373 enterprise wal write 1 0.004063 1.97 1.97
17373 enterprise wal file sync 1 0.003664 1.78 1.78
17373 enterprise infinitecache read 38 0.003076 1.49 1.49
17373 enterprise infinitecache write 5 0.000548 0.27 0.27
17373 enterprise db file extend 190 0.04.386 0.03 0.03
17373 enterprise db file write 5 0.000082 0.04 0.04
(11 rows)
返回指定PID的诊断信息
sessid_rpt(beginning_id, ending_id, backend_id)
SELECT * FROM sessid_rpt(18, 19, 17373);
sessid_rpt
-----------------------------------------------------------------------------
ID USER WAIT NAME COUNT TIME(ms) %WAIT SES %WAIT ALL
----------------------------------------------------------------------------
17373 enterprise db file read 30 0.175713 85.24 85.24
17373 enterprise query plan 18 0.014930 7.24 7.24
17373 enterprise wal flush 6 0.004067 1.97 1.97
17373 enterprise wal write 1 0.004063 1.97 1.97
17373 enterprise wal file sync 1 0.003664 1.78 1.78
17373 enterprise infinitecache read 38 0.003076 1.49 1.49
17373 enterprise infinitecache write 5 0.000548 0.27 0.27
17373 enterprise db file extend 190 0.040386 0.03 0.03
17373 enterprise db file write 5 0.000082 0.04 0.04
(11 rows)
返回指定PID的等待信息
sesshist_rpt(snapshot_id, session_id)
edb=# SELECT * FROM sesshist_rpt (9, 5531);
sesshist_rpt
----------------------------------------------------------------------------
ID USER SEQ WAIT NAME
ELAPSED(ms) File Name # of Blk Sum of Blks
----------------------------------------------------------------------------
5531 enterprise 1 db file read
18546 14309 session_waits_pk 1 1
5531 enterprise 2 infinitecache read
125 14309 session_waits_pk 1 1
5531 enterprise 3 db file read
376 14304 edb$session_waits 0 1
5531 enterprise 4 infinitecache read
166 14304 edb$session_waits 0 1
5531 enterprise 5 db file read
7978 1260 pg_authid 0 1
5531 enterprise 6 infinitecache read
154 1260 pg_authid 0 1
5531 enterprise 7 db file read
628 14302 system_waits_pk 1 1
5531 enterprise 8 infinitecache read
463 14302 system_waits_pk 1 1
5531 enterprise 9 db file read
3446 14297 edb$system_waits 0 1
5531 enterprise 10 infinitecache read
187 14297 edb$system_waits 0 1
5531 enterprise 11 db file read
14750 14295 snap_pk 1 1
5531 enterprise 12 infinitecache read
416 14295 snap_pk 1 1
5531 enterprise 13 db file read
7139 14290 edb$snap 0 1
5531 enterprise 14 infinitecache read
158 14290 edb$snap 0 1
5531 enterprise 15 db file read
27287 14288 snapshot_num_seq 0 1
5531 enterprise 16 infinitecache read
(17 rows)
清除指定范围内的快照
purgesnap(beginning_id, ending_id)
SELECT * FROM purgesnap(6, 9);
purgesnap
------------------------------------
Snapshots in range 6 to 9 deleted.
(1 row)
edb=# SELECT * FROM get_snaps();
get_snaps
------------------------------
1 11-FEB-10 10:41:05.668852
2 11-FEB-10 10:42:27.26154
3 11-FEB-10 10:45:48.999992
4 11-FEB-10 11:01:58.345163
5 11-FEB-10 11:05:14.092683
10 11-FEB-10 11:29:02.214014
11 11-FEB-10 11:31:44.244038
(7 rows)
清除所有快照
truncsnap()
SELECT * FROM truncsnap();
truncsnap
----------------------
Snapshots truncated.
(1 row)
SELECT * FROM get_snaps();
get_snaps
-----------
(0 rows)
生成AWR报告
全面的系统报告
edbreport(beginning_id, ending_id)
数据库报告
stat_db_rpt(beginning_id, ending_id)
指定范围的表级报告
stat_tables_rpt(beginning_id, ending_id, top_n, scope)
scope=ALL, USER, SYS
指定范围的表级IO报告
statio_tables_rpt(beginning_id, ending_id, top_n, scope)
指定范围的索引级报告
stat_indexes_rpt(beginning_id, ending_id, top_n, scope)
指定范围的索引级IO报告
statio_indexes_rpt(beginning_id, ending_id, top_n, scope)
范围
scope determines which tables the function returns statistics about. Specify SYS, USER or ALL:
SYS indicates that the function should return information about system defined tables.
A table is considered a system table if it is stored in one of the following schemas:
pg_catalog, information_schema, sys, or dbo.
USER indicates that the function should return information about user-defined tables.
ALL specifies that the function should return information about all tables.
rds ppas用户注意
rds ppas用户是普通用，如果要使用以上的函数，需要在前面加rds_前缀，如下方法可以查看有哪些rds函数。
找到对应的rds函数就可以执行了。
postgres=# \df rds*
List of functions
Schema | Name | Result data type |
Argument data types
| Type
--------+--------------------------+--------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------+--------
sys | rds_add_policy | void | object_schema text DEFAULT NULL::text, object_name text, policy_name text, function_schema text DEFAULT NULL::text, policy_function text, statement_types text DEFAULT 'inser
t,update,delete,select'::text, update_check boolean DEFAULT false, enable boolean DEFAULT true, static_policy boolean DEFAULT false, policy_type integer DEFAULT NULL::integer, long_predicate boolean DEFAULT false, sec_relevant_cols text
DEFAULT NULL::text, sec_relevant_cols_opt integer DEFAULT NULL::integer | normal
sys | rds_drop_policy | void | object_schema text DEFAULT NULL::text, object_name text, policy_name text
| normal
sys | rds_enable_policy | void | object_schema text DEFAULT NULL::text, object_name text, policy_name text, enable boolean
| normal
sys | rds_get_snaps | SETOF text |
| normal
sys | rds_manage_extension | boolean | operation text, pname text, schema text DEFAULT NULL::text, logging boolean DEFAULT false
| normal
sys | rds_pg_cancel_backend | boolean | upid integer
| normal
sys | rds_pg_stat_activity | SETOF pg_stat_activity |
| normal
sys | rds_pg_stat_statements | SETOF pg_stat_statements |
| normal
sys | rds_pg_terminate_backend | boolean | upid integer
| normal
sys | rds_report | SETOF text | beginsnap bigint, endsnap bigint
| normal
sys | rds_snap | text |
| normal
sys | rds_truncsnap | text |
| normal
(12 rows)
参考
https://www.enterprisedb.com/docs/en/9.5/eeguide/toc.html
https://www.enterprisedb.com/docs/en/9.5/eegui
Postgres_Plus_Enterprise_Edition_Guide.1.141.html