STATEMENT字段
https://docs.mogdb.io/zh/mogdb/v2.1/STATEMENT
| 名称 | 类型 | 描述 |
|---|---|---|
| node_name | name | 数据库进程名称。 |
| node_id | integer | 节点的ID。 |
| user_name | name | 用户名称。 |
| user_id | oid | 用户OID。 |
| unique_sql_id | bigint | 归一化的SQL ID。 |
| query | text | 归一化的SQL。 |
| n_calls | bigint | 调用次数。 |
| min_elapse_time | bigint | SQL在内核内的最小运行时间（单位: 微秒）。 |
| max_elapse_time | bigint | SQL在内核内的最大运行时间（单位: 微秒）。 |
| total_elapse_time | bigint | SQL在内核内的总运行时间（单位: 微秒）。 |
| n_returned_rows | bigint | SELECT返回的结果集行数。 |
| n_tuples_fetched | bigint | 随机扫描行。 |
| n_tuples_returned | bigint | 顺序扫描行。 |
| n_tuples_inserted | bigint | 插入行。 |
| n_tuples_updated | bigint | 更新行。 |
| n_tuples_deleted | bigint | 删除行。 |
| n_blocks_fetched | bigint | buffer的块访问次数。 |
| n_blocks_hit | bigint | buffer的块命中次数。 |
| n_soft_parse | bigint | 软解析次数，n_soft_parse + n_hard_parse可能大于n_calls，因为子查询未计入n_calls。 |
| n_hard_parse | bigint | 硬解析次数，n_soft_parse + n_hard_parse可能大于n_calls，因为子查询未计入n_calls。 |
| db_time | bigint | 有效的DB时间花费，多线程将累加（单位: 微秒）。 |
| cpu_time | bigint | CPU时间（单位: 微秒）。 |
| execution_time | bigint | 执行器内执行时间（单位: 微秒）。 |
| parse_time | bigint | SQL解析时间（单位: 微秒）。 |
| plan_time | bigint | SQL生成计划时间（单位: 微秒）。 |
| rewrite_time | bigint | SQL重写时间（单位: 微秒）。 |
| pl_execution_time | bigint | plpgsql上的执行时间（单位: 微秒）。 |
| pl_compilation_time | bigint | plpgsql上的编译时间（单位: 微秒）。 |
| net_send_time | bigint | 网络上的时间花费（单位: 微秒）。 |
| data_io_time | bigint | IO上的时间花费（单位: 微秒）。 |
| sort_count | bigint | 排序执行的次数。 |
| sort_time | bigint | 排序执行的时间（单位: 微秒）。 |
| sort_mem_used | bigint | 排序过程中使用的work memory大小（单位: KB）。 |
| sort_spill_count | bigint | 排序过程中，若发生落盘，写文件的次数。 |
| sort_spill_size | bigint | 排序过程中，若发生落盘，使用的文件大小（单位: KB）。 |
| hash_count | bigint | hash执行的次数。 |
| hash_time | bigint | hash执行的时间（单位: 微秒）。 |
| hash_mem_used | bigint | hash过程中使用的work memory大小（单位: KB）。 |
| hash_spill_count | bigint | hash过程中，若发生落盘，写文件的次数。 |
| hash_spill_size | bigint | hash过程中，若发生落盘，使用的文件大小（单位: KB）。 |
| last_updated | timestamp with time zone | 最后一次更新该语句的时间。 |
MogDB=# show search_path MogDB-# ; search_path -------------------------- "$user", publi, dbe_perf (1 row) MogDB=# \d+ statement View "dbe_perf.statement" Column | Type | Modifiers | Storage | Description ----------------------+--------------------------+-----------+----------+------------- node_name | name | | plain | node_id | integer | | plain | user_name | name | | plain | user_id | oid | | plain | unique_sql_id | bigint | | plain | query | text | | extended | n_calls | bigint | | plain | min_elapse_time | bigint | | plain | max_elapse_time | bigint | | plain | total_elapse_time | bigint | | plain | n_returned_rows | bigint | | plain | n_tuples_fetched | bigint | | plain | n_tuples_returned | bigint | | plain | n_tuples_inserted | bigint | | plain | n_tuples_updated | bigint | | plain | n_tuples_deleted | bigint | | plain | n_blocks_fetched | bigint | | plain | n_blocks_hit | bigint | | plain | n_soft_parse | bigint | | plain | n_hard_parse | bigint | | plain | db_time | bigint | | plain | cpu_time | bigint | | plain | execution_time | bigint | | plain | parse_time | bigint | | plain | plan_time | bigint | | plain | rewrite_time | bigint | | plain | pl_execution_time | bigint | | plain | pl_compilation_time | bigint | | plain | data_io_time | bigint | | plain | net_send_info | text | | extended | net_recv_info | text | | extended | net_stream_send_info | text | | extended | net_stream_recv_info | text | | extended | last_updated | timestamp with time zone | | plain | sort_count | bigint | | plain | sort_time | bigint | | plain | sort_mem_used | bigint | | plain | sort_spill_count | bigint | | plain | sort_spill_size | bigint | | plain | hash_count | bigint | | plain | hash_time | bigint | | plain | hash_mem_used | bigint | | plain | hash_spill_count | bigint | | plain | hash_spill_size | bigint | | plain | View definition: SELECT * FROM get_instr_unique_sql() get_instr_unique_sql(node_name, node_id, user_name, user_id, unique_sql_id, query, n_calls, min_elapse_time, max_elapse_time, tot al_elapse_time, n_returned_rows, n_tuples_fetched, n_tuples_returned, n_tuples_inserted, n_tuples_updated, n_tuples_deleted, n_blocks_fetched, n_blocks_hit, n_s oft_parse, n_hard_parse, db_time, cpu_time, execution_time, parse_time, plan_time, rewrite_time, pl_execution_time, pl_compilation_time, data_io_time, net_send_ info, net_recv_info, net_stream_send_info, net_stream_recv_info, last_updated, sort_count, sort_time, sort_mem_used, sort_spill_count, sort_spill_size, hash_cou nt, hash_time, hash_mem_used, hash_spill_count, hash_spill_size);
TOP SQL
############### top by dbtime ##############select * from (select user_name,unique_sql_id, round(total_elapse_time/1000) total_elapse_time, round(db_time/1000) db_time, round(db_time/sum(db_time)over()*100)||'%' pct, round(total_elapse_time/greatest(n_calls,1)/1000) avg_elapse_time, n_calls, round(max_elapse_time/1000) max_elapse_time, round(execution_time/1000) execution_time, round(cpu_time/1000) cpu_time, round(data_io_time/1000) data_io_time, round(plan_time/1000) plan_time, round((n_returned_rows+n_tuples_inserted+n_tuples_updated+n_tuples_deleted)/greatest(n_calls,1)) avg_rows--,query from statement order by db_time desc) where rownum<10;############### top by total_elapse_time ############## select * from (select user_name,unique_sql_id, round(total_elapse_time/1000) ela_ms, round(db_time/1000) db_time, round(total_elapse_time/sum(total_elapse_time)over()*100)||'%' pct, round(total_elapse_time/greatest(n_calls,1)/1000) avg_ela_ms, n_calls, round(max_elapse_time/1000) max_ela_ms, round(execution_time/1000) exec_time, round(cpu_time/1000) cpu_time, round(data_io_time/1000) data_io_time, round(plan_time/1000) plan_time, n_blocks_fetched, round((n_returned_rows+n_tuples_inserted+n_tuples_updated+n_tuples_deleted)/greatest(n_calls,1)) avg_rows--,query from statement order by total_elapse_time desc) where rownum<11;user_name | unique_sql_id | ela_ms | db_time | pct | avg_ela_ms | n_calls | max_ela_ms | exec_time | cpu_time | data_io_time | plan_time | n_blocks_fetched | avg_rows -----------+---------------+--------+---------+-----+------------+---------+------------+-----------+----------+--------------+-----------+------------------+---------- test | 1323875317 | 221346 | 230002 | 17% | 2 | 137846 | 307 | 199128 | 49739 | 6664 | 19 | 696192 | 1 test | 3867546897 | 165542 | 165614 | 13% | 129 | 1280 | 740 | 165057 | 15699 | 4374 | 58 | 1045897 | 1 test | 3396063170 | 156721 | 156885 | 12% | 120 | 1311 | 845 | 156422 | 15862 | 3149 | 10 | 1018126 | 1 test | 855126023 | 139045 | 146933 | 11% | 1 | 137992 | 305 | 120374 | 37342 | 2188 | 4 | 414630 | 1 test | 617922769 | 135427 | 136282 | 10% | 11 | 12748 | 323 | 133321 | 29983 | 1584 | 1 | 4335611 | 10 test | 3878359364 | 70024 | 71288 | 5% | 5 | 13777 | 106 | 63223 | 36326 | 852 | 5 | 2220181 | 10 test | 1151796260 | 38770 | 35176 | 3% | 4 | 9013 | 376 | 33398 | 4313 | 1763 | 6 | 50761 | 2 test | 3287280357 | 32003 | 33118 | 2% | 3 | 12530 | 106 | 30522 | 8194 | 948 | 1 | 532669 | 10 test | 2486663307 | 31624 | 32498 | 2% | 2 | 13777 | 201 | 28985 | 6262 | 1048 | 13 | 83629 | 1 test | 1284732268 | 29955 | 30862 | 2% | 2 | 13224 | 138 | 29032 | 5964 | 58 | 9 | 69954 | 1 (10 rows)select query,last_updated from statement where unique_sql_id=1323875317;############### top by avg slow sql ############## select * from (select user_name,unique_sql_id, round(total_elapse_time/1000) total_elapse_time, round(db_time/1000) db_time, round(total_elapse_time/sum(total_elapse_time)over()*100)||'%' pct, round(total_elapse_time/greatest(n_calls,1)/1000) avg_elapse_time, n_calls, round(max_elapse_time/1000) max_elapse_time, round(execution_time/1000) execution_time, round(cpu_time/1000) cpu_time, round(data_io_time/1000) data_io_time, round(plan_time/1000) plan_time, n_blocks_fetched, round((n_returned_rows+n_tuples_inserted+n_tuples_updated+n_tuples_deleted)/greatest(n_calls,1)) avg_rows--,query from statement order by round(total_elapse_time/greatest(n_calls,1)/1000) desc) where rownum<10;
最后修改时间：2022-09-05 11:43:19
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
