GreenPlum数据常用语句

1，GreenPlumn空间查询

磁盘空间查询

  SELECT * FROM gp_toolkit.gp_disk_free ORDER BY dfsegment;

文件空间查询

select a.dbid,

       a.content,

       a.role,

       a.port,

       a.hostname,

       b.fsname,

       c.fselocation

  from gp_segment_configuration a, pg_filespace b, pg_filespace_entry c

 where a.dbid = c.fsedbid

   and b.oid = c.fsefsoid

 order by content;

表空间

select * from pg_tablespace;

查询数据库使用空间大小

SELECT sodddatname,sodddatsize/1024/1024 Size_M FROM gp_toolkit.gp_size_of_database ORDER BY sodddatname;

用户使用大小

select 

  schemaname,

(sum(pg_relation_size(schemaname||'."'||tablename||'"'))/1024/1024)::decimal(18,2) as "size_mb",

(sum(pg_total_relation_size(schemaname||'."'||tablename||'"'))/1024/1024)::decimal(18,2) as "total_size_mb" 

from pg_tables 

where schemaname not like '%temp%' group by schemaname;

表占用大小

select pg_size_pretty(pg_relation_size('mg.log_gpload'))

索引占用大小

select pg_size_pretty(pg_relation_size('mg.ms_act_evt_log_pkey'));

数据倾斜

SELECT gp_segment_id, count(*) FROM mg.ms_gen_table_column GROUP BY gp_segment_id order by 1;

select * from  gp_toolkit.gp_skew_coefficients;

  其中skccoeff 通过存储记录均值计算出的标准差，这个值越低说明数据存放约均匀，反之说明数据存储分布不均匀，要考虑分布键选择是否合理。

select * from gp_toolkit.gp_skew_idle_fractions;  

  通过计算表扫描过程中，系统闲置的百分比，帮助用户快速判断，是否存在分布键选择不合理，导致数据处理倾斜的问题。

  siffraction字段表示表扫描过程中系统闲置的百分比，比如0.1表示10%的倾斜。

数据膨胀

select * from gp_toolkit.gp_bloat_diag;

锁查询 

SELECT l.locktype,

       l.database,

       c.relname,

       l.relation,

       l.transactionid,

       l.transaction,

       l.pid,

       l.mode,

       l.granted--,

       --a.current_query

  FROM pg_locks l, pg_class c, pg_stat_activity a

 WHERE l.relation = c.oid

   AND l.pid = a.procpid

 ORDER BY c.relname;

 会话查询

 select * from pg_stat_activity order by current_query desc limit 2;