慢SQL诊断
- 背景信息
- 前提条件
慢SQL诊断

背景信息

在SQL语句执行性能不符合预期时，可以查看SQL语句执行信息，便于事后分析SQL语句执行时的行为，从而诊断SQL语句执行出现的相关问题。
前提条件

数据库实例运行正常。
查询SQL语句信息，需要正确设置GUC参数track_stmt_stat_level。
只能用系统管理员和监控管理员权限进行操作。
1. 执行命令查看数据库实例中SQL语句执行信息
2. select * from dbe_perf.get_global_full_sql_by_timestamp(start_timestamp, end_timestamp);
3. 例如：
4. select * from DBE_PERF.get_global_full_sql_by_timestamp('2020-12-01 09:25:22', '2020-12-31 23:54:41');
5. -[ RECORD 1 ]--------+---------------------------------------------------------------------------------------------------------------
6. ---------------------------------------------------------------
7. node_name            | dn_6001_6002_6003
8. db_name              | postgres
9. schema_name          | "$user",public
10. origin_node          | 1938253334
11. user_name            | user_dj
12. application_name     | gsql
13. client_addr          |
14. client_port          | -1
15. unique_query_id      | 3671179229
16. debug_query_id       | 72339069014839210
17. query                | select name, setting from pg_settings where name in (?)
18. start_time           | 2020-12-19 16:19:51.216818+08
19. finish_time          | 2020-12-19 16:19:51.224513+08
20. slow_sql_threshold   | 1800000000
21. transaction_id       | 0
22. thread_id            | 139884662093568
23. session_id           | 139884662093568
24. n_soft_parse         | 0
25. n_hard_parse         | 1
26. query_plan           | Datanode Name: dn_6001_6002_6003
27. | Function Scan on pg_show_all_settings a  (cost=0.00..12.50 rows=5 width=64)
28. |   Filter: (name = '***'::text)
29. ...

31. 执行命令查看数据库实例中慢SQL语句执行信息
32. select * from dbe_perf.get_global_slow_sql_by_timestamp(start_timestamp, end_timestamp);
33. 例如：
34. select * from DBE_PERF.get_global_slow_sql_by_timestamp('2020-12-01 09:25:22', '2020-12-31 23:54:41');
35. -[ RECORD 1 ]--------+---------------------------------------------------------------------------------------------------
36. node_name            | dn_6001_6002_6003
37. db_name              | postgres
38. schema_name          | "$user",public
39. origin_node          | 1938253334
40. user_name            | user_dj
41. application_name     | gsql
42. client_addr          |
43. client_port          | -1
44. unique_query_id      | 2165004317
45. debug_query_id       | 72339069014839319
46. query                | select * from DBE_PERF.get_global_slow_sql_by_timestamp(?, ?);
47. start_time           | 2020-12-19 16:23:20.738491+08
48. finish_time          | 2020-12-19 16:23:20.773714+08
49. slow_sql_threshold   | 10000
50. transaction_id       | 0
51. thread_id            | 139884662093568
52. session_id           | 139884662093568
53. n_soft_parse         | 10
54. n_hard_parse         | 8
55. query_plan           | Datanode Name: dn_6001_6002_6003
56. | Result  (cost=1.01..1.02 rows=1 width=0)
57. |   InitPlan 1 (returns $0)
58. |     ->  Seq Scan on pgxc_node  (cost=0.00..1.01 rows=1 width=64)
59. |           Filter: (nodeis_active AND ((node_type = '***'::"char") OR (node_type = '***'::"char")))
60. ...

62. 查看当前主节点SQL语句执行信息
63. select * from statement_history;
64. 例如：
65. select * from statement_history;
66. -[ RECORD 1 ]--------+---------------------------------------------------------------------------------------------------------------
67. ---------------------------------------------------------------
68. db_name              | postgres
69. schema_name          | "$user",public
70. origin_node          | 1938253334
71. user_name            | user_dj
72. application_name     | gsql
73. client_addr          |
74. client_port          | -1
75. unique_query_id      | 3671179229
76. debug_query_id       | 72339069014839210
77. query                | select name, setting from pg_settings where name in (?)
78. start_time           | 2020-12-19 16:19:51.216818+08
79. finish_time          | 2020-12-19 16:19:51.224513+08
80. slow_sql_threshold   | 1800000000
81. transaction_id       | 0
82. thread_id            | 139884662093568
83. session_id           | 139884662093568
84. n_soft_parse         | 0
85. n_hard_parse         | 1
86. query_plan           | Datanode Name: dn_6001_6002_6003
87. | Function Scan on pg_show_all_settings a  (cost=0.00..12.50 rows=5 width=64)
88. |   Filter: (name = '***'::text)
89. ...

91. 查看当前备节点SQL语句执行信息
92. select * from dbe_perf.standby_statement_history(is_only_slow, start_timestamp, end_timestamp);
93. 例如：
94. select * from dbe_perf.standby_statement_history(true, '2022-08-01 09:25:22', '2022-08-31 23:54:41');
95. db_name              | postgres
96. schema_name          | "$user",public
97. origin_node          | 0
98. user_name            | user_dj
99. application_name     | gsql
100. client_addr          |
101. client_port          | -1
102. unique_query_id      | 1660376009
103. debug_query_id       | 281474976710740
104. query                | select name, setting from pg_settings where name in (?)
105. start_time           | 2022-08-19 16:19:51.216818+08
106. finish_time          | 2022-08-19 16:19:51.224513+08
107. slow_sql_threshold   | 1800000000
108. transaction_id       | 0
109. thread_id            | 140058747205376
110. session_id           | 140058747205376
111. n_soft_parse         | 0
112. n_hard_parse         | 1
113. query_plan           | Datanode Name: sgnode
114. | Function Scan on pg_show_all_settings a  (cost=0.00..12.50 rows=5 width=64)
115. |   Filter: (name = '***'::text)
116. ...