kingbase常用SQL总结之统计大小

概述

数据库运维中，我们需要总结一些常用的SQL语句，无论是日常巡检、故障排查或是死锁分析，都可以随时拿来用，提升工作效率，下面是一些常见的经典SQL或者是笔者自己工作过程中用到的常用的SQL,整理记录以备不时之需。
小建议：还可以将不同的SQL进行分类整理，通过一个shell脚本去调用不同的SQL脚本，拿到想要的信息和数据。这是笔者在某基金项目向他们DBA学到的很好用。

kingbase基于pg那个版本的SQL语句

test=# select name,setting from sys_settings where name='server_version';
-[ RECORD 1 ]-----------
name    | server_version
setting | 12.1

获取统计信息常用SQL

查询表大小SQL

函数解析
pg_relation_size ( relation regclass [, fork text ] ) → bigint,只查询表数据大小
   Computes the disk space used by one “fork” of the specified relation
pg_table_size ( regclass ) → bigint，统计表大小+空闲映射空间文件大小+可见性文件大小
  Computes the disk space used by the specified table, excluding indexes (but including its TOAST table if any, free space map, and visibility map).
pg_total_relation_size ( regclass ) → bigint: pg_table_size +和表相关的索引大小
  Computes the total disk space used by the specified table, including all indexes and TOAST data. The result is equivalent to pg_table_size + pg_indexes_size.
  
test=# select pg_size_pretty(pg_total_relation_size(oid::regclass)) from pg_class where relname='fb_example';
 pg_size_pretty
----------------
 56 kB

test=# select pg_size_pretty(pg_relation_size(oid::regclass)) from pg_class where relname='fb_example'; --常用SQL
 pg_size_pretty
----------------
 8192 bytes

test=# select pg_size_pretty(pg_table_size(oid::regclass)) from pg_class where relname='fb_example';
 pg_size_pretty
----------------
 40 kB

查询模式大小SQL

了解了pg_relation_size、pg_table_size和pg_total_relation_size 就可以计算模式大小了。

SELECT 
    pg_size_pretty(
        sum(pg_relation_size(C.oid))
    ) as schema_size
FROM 
    pg_class C
JOIN 
    pg_namespace N ON (N.oid = C.relnamespace)
WHERE 
    N.nspname ='your schema name';

查询库大小SQL

select pg_size_pretty(pg_database_size(oid::regclass)) from pg_database;

查询top10表大小

    SELECT 
    nspname AS schema_name,
    relname AS table_name,
    pg_size_pretty(pg_relation_size(c.oid::regclass)) AS size
FROM 
    pg_class C
JOIN 
    pg_namespace N ON (N.oid = C.relnamespace)
WHERE 
    N.nspname = 'your schema name'  and c.relkind='r' -- 替换为你要查询的模式名称
ORDER BY 
    pg_relation_size(c.oid::regclass) DESC
LIMIT 10;

查询top10索引大小

    SELECT 
    nspname AS schema_name,
    relname AS table_name,
    pg_size_pretty(pg_relation_size(c.oid::regclass)) AS size
FROM 
    pg_class C
JOIN 
    pg_namespace N ON (N.oid = C.relnamespace)
WHERE 
    N.nspname = 'your schema name' and c.relkind='i' -- 替换为你要查询的模式名称
ORDER BY 
    pg_relation_size(c.oid::regclass) DESC
LIMIT 10;

查询事务号top10的表

select relnamespace::regnamespace,relname,pg_size_pretty(pg_relation_size(oid)),age(relfrozenxid) from pg_class where relkind='r' and relpersistence !='x' and order by age(relfrozenxid) desc,pg_relation_size(oid) desc limit 10;

查询执行次数最频繁的top10 SQL

前置条件，需要开启sys_stat_statement/pg_stat_statement插件配置好相关参数

test=# show shared_preload_libraries ;
                                                                                                                        shared_preload_libraries
---------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------
 liboracle_parser, synonym, plsql, force_view, kdb_flashback,plugin_debugger, plsql_plugin_debugger, plsql_plprofiler, kdb_ora_expr, sepapower, dblink, sys_kwr
, sys_spacequota, sys_stat_statements, backtrace, kdb_utils_function, auto_bmr, sys_squeeze, src_restrict
test=# show sys_stat_statements.track;
 sys_stat_statements.track
---------------------------
 top
test =# show sys_stat_statements.max ;
 sys_stat_statements.max
-------------------------
 10000
--查询执行次数最频繁的top10 SQL
select calls,userid::regrole,total_exec_time,total_exec_time/calls avg_exec_time_one_time,queryid,substr(query,1,1000)
from sys_stat_statements
where userid not in (select oid from pg_roles where rolname ='esrep' or rolname='system') and calls>0
and upper(query) not like 'COPY%' order by total_exec_time/calls desc limit 10;

如有不正确之处请评论指出，感谢感谢。