本文為英文版的機器翻譯版本，如內容有任何歧義或不一致之處，概以英文版為準。
# aurora\$1stat\$1dml\$1activity
報告 Aurora PostgreSQL 叢集中資料庫上每種資料處理語言 (DML) 作業類型的累積活動。
## 語法
```
aurora_stat_dml_activity(database_oid)
```
## 引數
*database\$1oid*
Aurora PostgreSQL 叢集中資料庫的物件 ID (OID)。
## 傳回類型
SETOF 記錄
## 使用須知
`aurora_stat_dml_activity` 函數僅適用於與 PostgreSQL 引擎 11.6 及更新版本相容的 Aurora PostgreSQL 3.1 版。
在具有大量資料庫的 Aurora PostgreSQL 叢集上使用此函數，來識別哪些資料庫具有較多或較慢的 DML 活動，或兩者兼具。
`aurora_stat_dml_activity` 函數傳回的作業執行的次數，及 SELECT、INSERT、UPDATE 和 DELETE 作業的累積延遲 (以微秒為單位)。該報告僅包含成功的 DML 作業。
您可使用 PostgreSQL 統計數字存取函數 `pg_stat_reset` 來重置此統計數字。您可使用 `pg_stat_get_db_stat_reset_time` 函數，檢查上次重設此統計數字的時間。如需有關 PostgreSQL 統計數字存取函數的詳細資訊，請參閱 PostgreSQL 文件中的[統計數字收集器](https://www.postgresql.org/docs/9.1/monitoring-stats.html)。
## 範例
下列範例顯示如何報告已連線資料庫的 DML 活動統計數字。
```
––Define the oid variable from connected database by using \gset
=> SELECT oid,
datname
FROM pg_database
WHERE datname=(select current_database()) \gset
=> SELECT *
FROM aurora_stat_dml_activity(:oid);
select_count | select_latency_microsecs | insert_count | insert_latency_microsecs | update_count | update_latency_microsecs | delete_count | delete_latency_microsecs
--------------+--------------------------+--------------+--------------------------+--------------+--------------------------+--------------+--------------------------
178957 | 66684115 | 171065 | 28876649 | 519538 | 1454579206167 | 1 | 53027
–– Showing the same results with expanded display on
=> SELECT *
FROM aurora_stat_dml_activity(:oid);
-[ RECORD 1 ]------------+--------------
select_count | 178957
select_latency_microsecs | 66684115
insert_count | 171065
insert_latency_microsecs | 28876649
update_count | 519538
update_latency_microsecs | 1454579206167
delete_count | 1
delete_latency_microsecs | 53027
```
下列範例顯示 Aurora PostgreSQL 叢集中所有資料庫的 DML 活動統計數字。此叢集有兩個資料庫：`postgres` 和 `mydb`。以逗號分隔的清單會與 `select_count`、`select_latency_microsecs`、`insert_count`、`insert_latency_microsecs`、`update_count`、`update_latency_microsecs`、`delete_count` 和 `delete_latency_microsecs` 欄位相對應。
Aurora PostgreSQL 建立並使用一個名為 `rdsadmin` 的系統資料庫來支援管理作業，例如備份、還原、運作狀態檢查、複寫等等。這些 DML 作業對您的 Aurora PostgreSQL 叢集並無任何影響。
```
=> SELECT oid,
datname,
aurora_stat_dml_activity(oid)
FROM pg_database;
oid | datname | aurora_stat_dml_activity
-------+----------------+-----------------------------------------------------------------
14006 | template0 | (,,,,,,,)
16384 | rdsadmin | (2346623,1211703821,4297518,817184554,0,0,0,0)
1 | template1 | (,,,,,,,)
14007 | postgres | (178961,66716329,171065,28876649,519538,1454579206167,1,53027)
16401 | mydb | (200246,64302436,200036,107101855,600000,83659417514,0,0)
```
下列範例顯示所有資料庫的 DML 活動統計數字以資料欄進行編排，提高可讀性。
```
SELECT db.datname,
BTRIM(SPLIT_PART(db.asdmla::TEXT, ',', 1), '()') AS select_count,
BTRIM(SPLIT_PART(db.asdmla::TEXT, ',', 2), '()') AS select_latency_microsecs,
BTRIM(SPLIT_PART(db.asdmla::TEXT, ',', 3), '()') AS insert_count,
BTRIM(SPLIT_PART(db.asdmla::TEXT, ',', 4), '()') AS insert_latency_microsecs,
BTRIM(SPLIT_PART(db.asdmla::TEXT, ',', 5), '()') AS update_count,
BTRIM(SPLIT_PART(db.asdmla::TEXT, ',', 6), '()') AS update_latency_microsecs,
BTRIM(SPLIT_PART(db.asdmla::TEXT, ',', 7), '()') AS delete_count,
BTRIM(SPLIT_PART(db.asdmla::TEXT, ',', 8), '()') AS delete_latency_microsecs
FROM (SELECT datname,
aurora_stat_dml_activity(oid) AS asdmla
FROM pg_database
) AS db;
datname | select_count | select_latency_microsecs | insert_count | insert_latency_microsecs | update_count | update_latency_microsecs | delete_count | delete_latency_microsecs
----------------+--------------+--------------------------+--------------+--------------------------+--------------+--------------------------+--------------+--------------------------
template0 | | | | | | | |
rdsadmin | 4206523 | 2478812333 | 7009414 | 1338482258 | 0 | 0 | 0 | 0
template1 | | | | | | | |
fault_test | 66 | 452099 | 0 | 0 | 0 | 0 | 0 | 0
db_access_test | 1 | 5982 | 0 | 0 | 0 | 0 | 0 | 0
postgres | 42035 | 95179203 | 5752 | 2678832898 | 21157 | 441883182488 | 2 | 1520
mydb | 71 | 453514 | 0 | 0 | 1 | 190 | 1 | 152
```
下列範例顯示了資料庫 (具 OID `16401`) 之每個 DML 作業的平均累積延遲 (累積延遲除以計數)。
```
=> SELECT select_count,
select_latency_microsecs,
select_latency_microsecs/NULLIF(select_count,0) select_latency_per_exec,
insert_count,
insert_latency_microsecs,
insert_latency_microsecs/NULLIF(insert_count,0) insert_latency_per_exec,
update_count,
update_latency_microsecs,
update_latency_microsecs/NULLIF(update_count,0) update_latency_per_exec,
delete_count,
delete_latency_microsecs,
delete_latency_microsecs/NULLIF(delete_count,0) delete_latency_per_exec
FROM aurora_stat_dml_activity(16401);
-[ RECORD 1 ]------------+-------------
select_count | 451312
select_latency_microsecs | 80205857
select_latency_per_exec | 177
insert_count | 451001
insert_latency_microsecs | 123667646
insert_latency_per_exec | 274
update_count | 1353067
update_latency_microsecs | 200900695615
update_latency_per_exec | 148478
delete_count | 12
delete_latency_microsecs | 448
delete_latency_per_exec | 37
```