背景信息
在SQL 语句执行性能不符合预期时，
可以查看SQL 语句执行信息，
便于事后分析SQL
语句执行时的行为，从而诊断SQL 语句执行出现的相关问题。
前提条件
数据库实例运行正常。
查询SQL 语句信息，需要正确设置GUC 参数track_stmt_stat_level。
只能用系统管理员和监控管理员权限进行操作。
应用场景
查看数据库实例中慢SQL 语句执行信息，语法格式：
select * from dbe_perf.get_global_slow_sql_by_timestamp(start_timestamp,
end_timestamp);
查看数据库实例中SQL 语句执行信息，语法格式：
select * from dbe_perf.get_global_full_sql_by_timestamp(start_timestamp,
end_timestamp);
GBase 8c 管理员指南
南大通用数据技术股份有限公司
50
例如：
postgres=# select * from DBE_PERF.get_global_full_sql_by_timestamp('2020-12-01
09:25:22', '2020-12-31 23:54:41');
-[ RECORD
1 ]--------+-----------------------------------------------------------------
----------------------------------------------
---------------------------------------------------------------
node_name
| dn_6001_6002_6003
db_name
| postgres
schema_name
| "$user",public
origin_node
| 1938253334
user_name
| user_dj
application_name
| gsql
client_addr
|
client_port
| -1
unique_query_id
| 3671179229
debug_query_id
| 72339069014839210
query
| select name, setting from pg_settings where name in (?)
start_time
| 2020-12-19 16:19:51.216818+08
finish_time
| 2020-12-19 16:19:51.224513+08
slow_sql_threshold
| 1800000000
transaction_id
| 0
thread_id
| 139884662093568
session_id
| 139884662093568
n_soft_parse
| 0
n_hard_parse
| 1
query_plan
| Datanode Name: dn_6001_6002_6003
| Function Scan on pg_show_all_settings a
(cost=0.00..12.50 rows=5 width=64)
|
Filter: (name = '***'::text)
...
查看当前主节点SQL 语句执行信息，语法格式：
select * from statement_history;
例如：
postgres=# select * from statement_history;
-[ RECORD
1 ]--------+-----------------------------------------------------------------
----------------------------------------------
GBase 8c 管理员指南
南大通用数据技术股份有限公司
51
---------------------------------------------------------------
db_name
| postgres
schema_name
| "$user",public
origin_node
| 1938253334
user_name
| user_dj
application_name
| gsql
client_addr
|
client_port
| -1
unique_query_id
| 3671179229
debug_query_id
| 72339069014839210
query
| select name, setting from pg_settings where name in (?)
start_time
| 2020-12-19 16:19:51.216818+08
finish_time
| 2020-12-19 16:19:51.224513+08
slow_sql_threshold
| 1800000000
transaction_id
| 0
thread_id
| 139884662093568
session_id
| 139884662093568
n_soft_parse
| 0
n_hard_parse
| 1
query_plan
| Datanode Name: dn_6001_6002_6003
| Function Scan on pg_show_all_settings a
(cost=0.00..12.50 rows=5 width=64)
|
Filter: (name = '***'::text)
...
查看当前备节点SQL 语句执行信息
select * from dbe_perf.standby_statement_history(is_only_slow, start_timestamp,
end_timestamp);
例如：
postgres=# select * from dbe_perf.standby_statement_history(true, '2022-08-01
09:25:22', '2022-08-31 23:54:41');
db_name
| postgres
schema_name
| "$user",public
origin_node
| 0
user_name
| user_dj
application_name
| gsql
client_addr
|
client_port
| -1
unique_query_id
| 1660376009
debug_query_id
| 281474976710740
GBase 8c 管理员指南
南大通用数据技术股份有限公司
52
query
| select name, setting from pg_settings where name in (?)
start_time
| 2022-08-19 16:19:51.216818+08
finish_time
| 2022-08-19 16:19:51.224513+08
slow_sql_threshold
| 1800000000
transaction_id
| 0
thread_id
| 140058747205376
session_id
| 140058747205376
n_soft_parse
| 0
n_hard_parse
| 1
query_plan
| Datanode Name: sgnode
| Function Scan on pg_show_all_settings a
(cost=0.00..12.50 rows=5 width=64)
|
Filter: (name = '***'::text)
...
GBase 8c 管理员指南
南大通用数据技术股份有限公司
53
5
备份与恢复