STATEMENT字段
https://docs.mogdb.io/zh/mogdb/v2.1/STATEMENT
| 名稱 | 類型 | 描述 |
|---|---|---|
| node_name | name | 數(shù)據(jù)庫(kù)進(jìn)程名稱。 |
| node_id | integer | 節(jié)點(diǎn)的ID。 |
| user_name | name | 用戶名稱。 |
| user_id | oid | 用戶OID。 |
| unique_sql_id | bigint | 歸一化的SQL ID。 |
| query | text | 歸一化的SQL。 |
| n_calls | bigint | 調(diào)用次數(shù)。 |
| min_elapse_time | bigint | SQL在內(nèi)核內(nèi)的最小運(yùn)行時(shí)間（單位: 微秒）。 |
| max_elapse_time | bigint | SQL在內(nèi)核內(nèi)的最大運(yùn)行時(shí)間（單位: 微秒）。 |
| total_elapse_time | bigint | SQL在內(nèi)核內(nèi)的總運(yùn)行時(shí)間（單位: 微秒）。 |
| n_returned_rows | bigint | SELECT返回的結(jié)果集行數(shù)。 |
| n_tuples_fetched | bigint | 隨機(jī)掃描行。 |
| n_tuples_returned | bigint | 順序掃描行。 |
| n_tuples_inserted | bigint | 插入行。 |
| n_tuples_updated | bigint | 更新行。 |
| n_tuples_deleted | bigint | 刪除行。 |
| n_blocks_fetched | bigint | buffer的塊訪問(wèn)次數(shù)。 |
| n_blocks_hit | bigint | buffer的塊命中次數(shù)。 |
| n_soft_parse | bigint | 軟解析次數(shù)，n_soft_parse + n_hard_parse可能大于n_calls，因?yàn)樽硬樵兾从?jì)入n_calls。 |
| n_hard_parse | bigint | 硬解析次數(shù)，n_soft_parse + n_hard_parse可能大于n_calls，因?yàn)樽硬樵兾从?jì)入n_calls。 |
| db_time | bigint | 有效的DB時(shí)間花費(fèi)，多線程將累加（單位: 微秒）。 |
| cpu_time | bigint | CPU時(shí)間（單位: 微秒）。 |
| execution_time | bigint | 執(zhí)行器內(nèi)執(zhí)行時(shí)間（單位: 微秒）。 |
| parse_time | bigint | SQL解析時(shí)間（單位: 微秒）。 |
| plan_time | bigint | SQL生成計(jì)劃時(shí)間（單位: 微秒）。 |
| rewrite_time | bigint | SQL重寫(xiě)時(shí)間（單位: 微秒）。 |
| pl_execution_time | bigint | plpgsql上的執(zhí)行時(shí)間（單位: 微秒）。 |
| pl_compilation_time | bigint | plpgsql上的編譯時(shí)間（單位: 微秒）。 |
| net_send_time | bigint | 網(wǎng)絡(luò)上的時(shí)間花費(fèi)（單位: 微秒）。 |
| data_io_time | bigint | IO上的時(shí)間花費(fèi)（單位: 微秒）。 |
| sort_count | bigint | 排序執(zhí)行的次數(shù)。 |
| sort_time | bigint | 排序執(zhí)行的時(shí)間（單位: 微秒）。 |
| sort_mem_used | bigint | 排序過(guò)程中使用的work memory大?。▎挝? KB）。 |
| sort_spill_count | bigint | 排序過(guò)程中，若發(fā)生落盤(pán)，寫(xiě)文件的次數(shù)。 |
| sort_spill_size | bigint | 排序過(guò)程中，若發(fā)生落盤(pán)，使用的文件大小（單位: KB）。 |
| hash_count | bigint | hash執(zhí)行的次數(shù)。 |
| hash_time | bigint | hash執(zhí)行的時(shí)間（單位: 微秒）。 |
| hash_mem_used | bigint | hash過(guò)程中使用的work memory大?。▎挝? KB）。 |
| hash_spill_count | bigint | hash過(guò)程中，若發(fā)生落盤(pán)，寫(xiě)文件的次數(shù)。 |
| hash_spill_size | bigint | hash過(guò)程中，若發(fā)生落盤(pán)，使用的文件大?。▎挝? KB）。 |
| last_updated | timestamp with time zone | 最后一次更新該語(yǔ)句的時(shí)間。 |
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;
最后修改時(shí)間：2022-09-05 11:43:19
「喜歡這篇文章，您的關(guān)注和贊賞是給作者最好的鼓勵(lì)」
關(guān)注作者
【版權(quán)聲明】本文為墨天輪用戶原創(chuàng)內(nèi)容，轉(zhuǎn)載時(shí)必須標(biāo)注文章的來(lái)源（墨天輪），文章鏈接，文章作者等基本信息，否則作者和墨天輪有權(quán)追究責(zé)任。如果您發(fā)現(xiàn)墨天輪中有涉嫌抄襲或者侵權(quán)的內(nèi)容，歡迎發(fā)送郵件至：contact@modb.pro進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，墨天輪將立刻刪除相關(guān)內(nèi)容。
