kingbase常用SQL总结之统计大小

本文主要是介绍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 kBtest=# select pg_size_pretty(pg_relation_size(oid::regclass)) from pg_class where relname='fb_example'; --常用SQLpg_size_pretty
----------------8192 bytestest=# 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;

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

这篇关于kingbase常用SQL总结之统计大小的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---