# PGXC_STAT_TABLE_DIRTY
PGXC_STAT_TABLE_DIRTY显示当前集群中所有节点（CN和DN）上全部表的统计信息，并展示表在单节点（单CN级或者单DN级）的脏页率。该视图仅8.1.3及以上集群版本支持。
![](https://support.huaweicloud.com/devg-dws/public_sys-resources/note_3.0-zh-cn.png)
该视图的统计信息依赖于ANALYZE，为获取最准确的信息请先对表进行ANALYZE。
表1PGXC_STAT_TABLE_DIRTY字段 
| 名称                | 类型                      | 描述                                                                                                                                                                           |
|:---|:---|:---|
| nodename          | text                    | 节点名。                                                                                                                                                                         |
| schema            | name                    | 表的模式名。                                                                                                                                                                       |
| tablename         | name                    | 表名。                                                                                                                                                                          |
| partname          | name                    | 分区表的分区名。                                                                                                                                                                     |
| last_vacuum       | timestampwith time zone | 最后一次手动vacuum时间。                                                                                                                                                              |
| last_autovacuum   | timestampwith time zone | 最后一次autovacuum时间。                                                                                                                                                            |
| last_analyze      | timestampwith time zone | 最后一次非自动轮询分析的时间。                                                                                                                                                              |
| last_autoanalyze  | timestampwith time zone | 最后一次自动轮询分析的时间。                                                                                                                                                               |
| vacuum_count      | bigint                  | vacuum次数。                                                                                                                                                                    |
| autovacuum_count  | bigint                  | autovacuum次数。                                                                                                                                                                |
| analyze_count     | bigint                  | analyze次数。                                                                                                                                                                   |
| autoanalyze_count | bigint                  | 自动分析的次数。                                                                                                                                                                     |
| n_tup_ins         | bigint                  | 插入的行数。                                                                                                                                                                       |
| n_tup_upd         | bigint                  | 更新的行数。                                                                                                                                                                       |
| n_tup_del         | bigint                  | 删除的行数。                                                                                                                                                                       |
| n_tup_hot_upd     | bigint                  | HOT更新的行数。                                                                                                                                                                    |
| n_tup_change      | bigint                  | analyze之后改变的行数。                                                                                                                                                              |
| n_live_tup        | bigint                  | live行估计数。                                                                                                                                                                    |
| n_dead_tup        | bigint                  | dead行估计数。                                                                                                                                                                    |
| dirty_rate        | bigint                  | 单节点的脏页率（单CN或单DN）。                                                                                                                                                            |
| last_data_changed | timestampwith time zone | 记录表最后一次数据变化的时间。 该字段在8.2.0及以上集群版本中固定为空，建议通过[PG_STAT_ALL_TABLES](https://support.huaweicloud.com/devg-dws/dws_04_0758.html)查询。 |
   
#### 使用建议
- 对于高脏页率的系统表，建议在确认当前没有用户操作该系统表时，再执行VACUUM FULL。
- 建议对脏页率超过80%的非系统表执行VACUUM FULL，用户也可根据业务场景自行选择是否执行VACUUM FULL。
 
#### 使用场景
1. 查询全库所有用户表的整体脏页率：
   ```
   select 
       t1.schema,
       t1.tablename,
       t1.total_ins,
       t1.total_upd,
       t1.total_del,
       t1. total_tup_hot_upd,
       t1.total_change,
       t1.total_live,
       t1.total_dead,
       t1.total_dirty_rate,
       t1.max_dirty,
       t2.max_node,
       t1.min_dirty,
       t2.min_node
   from
       (select 
           a.schema,
           a.tablename,
           sum(a.n_tup_ins) as total_ins,
           sum(a.n_tup_upd) as total_upd,
           sum(a.n_tup_del) as total_del,
           sum(a.n_tup_hot_upd) as total_tup_hot_upd,
           sum(a.n_tup_change) as total_change,
           sum(a.n_live_tup) as total_live,
           sum(a.n_dead_tup) as total_dead,
           Round((total_dead / (total_dead + total_live + 0.0001) * 100),2) AS total_dirty_rate,
           max(a.dirty_rate) as max_dirty,
           min(a.dirty_rate) as min_dirty
       from pg_catalog.pgxc_stat_table_dirty a where a.partname is null and a.schema not in ('pg_toast','cstore','gs_logical_cluster','sys','dbms_om','information_schema','pg_catalog','dbms_output','dbms_random','utl_raw','utl_raw dbms_sql','dbms_lob') group by a.tablename, a.schema
       ) t1,
       (select distinct
       tablename, schema, 
       first_value(nodename) over(partition by tablename, schema order by dirty_rate) as min_node,
       first_value(nodename) over(partition by tablename, schema order by dirty_rate desc) as max_node
       from (select * from pg_catalog.pgxc_stat_table_dirty)) t2
   where t1.tablename = t2.tablename and t1.schema = t2.schema;
   ```
   
2. 查询全库所有表(用户表+系统表)的整体脏页率：
   ```
   select 
       t1.schema,
       t1.tablename,
       t1.total_ins,
       t1.total_upd,
       t1.total_del,
       t1. total_tup_hot_upd,
       t1.total_change,
       t1.total_live,
       t1.total_dead,
       t1.total_dirty_rate,
       t1.max_dirty,
       t2.max_node,
       t1.min_dirty,
       t2.min_node
   from
       (select 
           a.schema,
           a.tablename,
           sum(a.n_tup_ins) as total_ins,
           sum(a.n_tup_upd) as total_upd,
           sum(a.n_tup_del) as total_del,
           sum(a.n_tup_hot_upd) as total_tup_hot_upd, 
           sum(a.n_tup_change) as total_change,
           sum(a.n_live_tup) as total_live,
           sum(a.n_dead_tup) as total_dead,
           Round((total_dead / (total_dead + total_live + 0.0001) * 100),2) AS total_dirty_rate,
           max(a.dirty_rate) as max_dirty,
           min(a.dirty_rate) as min_dirty
       from pg_catalog.pgxc_stat_table_dirty a where a.partname is null group by a.tablename, a.schema
       ) t1,
       (select distinct
       tablename, schema, 
       first_value(nodename) over(partition by tablename, schema order by dirty_rate) as min_node,
       first_value(nodename) over(partition by tablename, schema order by dirty_rate desc) as max_node
       from (select * from pg_catalog.pgxc_stat_table_dirty)) t2
   where t1.tablename = t2.tablename and t1.schema = t2.schema;
   ```
   
3. 查询全库系统表信息：
   ```
   select * from pgxc_stat_table_dirty where schema in ('pg_toast','cstore','gs_logical_cluster','sys','dbms_om','information_schema','pg_catalog','dbms_output','dbms_random','utl_raw','utl_raw dbms_sql','dbms_lob');
   ```