1.如何获取查询运行时和已用时间。
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
例子: Select tstart, tfinish, (tfinish-tstart) as total_time, trim(query_text) from queries_history Where tstart >= '2011-07-07 11:00:00' and tstart < '2011-07-07 13:00:00' and db ='yourdatabasename' Order by tstart;
2。 *查看上次执行的操作
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
gpdb=# SELECT schemaname as schema, objname as table, usename as role, actionname as action, gpdb-# subtype as type, statime as time gpdb-# FROM pg_stat_operations gpdb-# WHERE objname='cust'; schema | table | role | action | type | time --------+-------+------+--------+------+------ (0 rows)
*最后分析或创建表或ETC...
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
Select * from pg_stat_operations where schemaname='SCHEMA NAME ' and actionname in ('ANALYZE','VACUUM') order by statime; gpdb=# Select * from pg_stat_operations gpdb-# where schemaname='SCHEMA NAME ' gpdb-# and actionname in ('ANALYZE','VACUUM') gpdb-# order by statime; classname | objname | objid | schemaname | usestatus | usename | actionname | subty pe | statime -----------+---------+-------+------------+-----------+---------+------------+------ ---+--------- (0 rows)
3.显示已关闭的细分。
select * from gp_segment_configuration where status='d'; 4.如何计算磁盘中的数据库大小?
select pg_size_pretty(pg_database_size('test1')); 5.如何计算磁盘中的表大小?
select pg_size_pretty(pg_relation_size('gpdb' )); 6.对于分区表,您需要使用下面的sql来查找累积表大小
select sum(pg_total_relation_size(tablename)) from pg_tables where tablename like 'table_name%' ; 7。 *授予/撤消模式中对象的权限的功能
*撤销模式对象(表,视图,序列)权限的功能
8. GreenPlum数据库GUI工具
9.您可能喜欢的数据偏斜查询
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
gpdb=# select gp_segment_id, count(*) , abs(count(*) - avg(count(*)) over(order by gp_segment_id rowsbetween unbounded preceding and unbounded following))/count(*) skewfrom test1group by gp_segment_idorder by skew desclimit 10; gp_segment_id | count | skew ---------------+-------+------------------------ 1 | 2 | 0.40000000000000000000 3 | 1 | 0.20000000000000000000 4 | 1 | 0.20000000000000000000 6 | 1 | 0.20000000000000000000 0 | 1 | 0.20000000000000000000 (5 rows)
10。 *segments
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
gpdb=# SELECT count(*)::smallint AS numsegments FROM gp_segment_configurationWHERE gp_segment_configuration.preferred_role = 'p'::"char" ANDgp_segment_configuration.content >= 0; numsegments ------------- 8 (1 row)
*segments的大小
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
gpdb=# SELECT pg_size_pretty(dfspace)FROM gp_toolkit.gp_disk_freeORDER BY dfsegment; pg_size_pretty ---------------- 39 MB 39 MB 39 MB 39 MB 39 MB 39 MB 39 MB 39 MB (8 rows)
11.确定当前的主段配置
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
gpdb=# SELECT dbid, content, address as host_address, port, replication_port, fselocation as datadirFROM gp_segment_configuration, pg_filespace_entryWHERE dbid=fsedbidORDER BY dbid; dbid | content | host_address | port | replication_port | datadir ------+---------+--------------+-------+------------------+------------------------- -------- 1 | -1 | greenplum01 | 5432 | | /greenplum/data/master/g pseg-1 2 | 0 | greenplum02 | 6000 | 34000 | /greenplum/data/primary/ gpseg0 3 | 1 | greenplum02 | 6001 | 34001 | /greenplum/data/primary/ gpseg1 4 | 2 | greenplum02 | 6002 | 34002 | /greenplum/data2/primary /gpseg2 5 | 3 | greenplum02 | 6003 | 34003 | /greenplum/data2/primary /gpseg3 6 | 4 | greenplum03 | 6000 | 34000 | /greenplum/data/primary/ gpseg4 7 | 5 | greenplum03 | 6001 | 34001 | /greenplum/data/primary/ gpseg5 8 | 6 | greenplum03 | 6002 | 34002 | /greenplum/data2/primary /gpseg6 9 | 7 | greenplum03 | 6003 | 34003 | /greenplum/data2/primary /gpseg7 10 | 0 | greenplum03 | 43000 | 44000 | /greenplum/data/mirror/g pseg0 11 | 1 | greenplum03 | 43001 | 44001 | /greenplum/data/mirror/g pseg1 12 | 2 | greenplum03 | 43002 | 44002 | /greenplum/data2/mirror/ gpseg2 13 | 3 | greenplum03 | 43003 | 44003 | /greenplum/data2/mirror/ gpseg3 14 | 4 | greenplum02 | 43000 | 44000 | /greenplum/data/mirror/g pseg4 15 | 5 | greenplum02 | 43001 | 44001 | /greenplum/data/mirror/g pseg5 16 | 6 | greenplum02 | 43002 | 44002 | /greenplum/data2/mirror/
12.时区设置:
gpdb=# BEGIN; gpdb=# SELECT NOW(); gpdb=# SET timezone TO '-8'; gpdb=# SELECT NOW();
13.Re-create GP Toolkit objects:
You only get one shot, do not miss your chance to blow.