kingbase常见数据字典

统计数据库相关信息

查看数据库数据读写信息
	select datname,blks_read,blks_hit,tup_returned,tup_fetched,tup_inserted,tup_updated,tup_deleted from sys_stat_database;
	
数据库连接数及事务操作信息
	select datname,numbackends,xact_commit,xact_rollback from sys_stat_database;

统计表相关的信息

查询表的索引扫描的比例
	select relid,schemaname,relname,seq_scan,idx_scan,
	trunc(cast (idx_scan as numeric)/(idx_scan+seq_scan)*100,2) || '%' as idx_scan_pct
	from sys_stat_user_tables where(idx_scan+seq_scan)>0
	order by idx_scan_pct desc limit 5;
	
查看表的HOT方式更新数据的比例
	select relid,schemaname,relname,n_tup_upd,n_tup_hot_upd,
	trunc(cast(n_tup_hot_upd as numeric)) / n_tup_upd*100,2||'%'
	as hot_pct from sys_stat_user_tables where n_tup_upd>0 order
	by hot_pct desc limit 5;
	
查询表的数据增改删的比例
	select relname,trunc(cast (n_tup_ins as numeric) / (n_tup_ins+n_tup_upd+n_tup_del)*100,2) || '%' as ins_pct,
	trunc(cast (n_tup_upd as numeric)/(n_tup_ins+n_tup_upd+n_tup_del)*100,2)||'%' as upd_pct,
	trunc(cast (n_tup_del as numeric)/(n_tup_ins+n_tup_upd+n_tup_del)*100,2)||'%' as del_pct
	from sys_stat_user_tables where relname='td1' order by relname;
	
查询表的数据时缓存命中的比例
	select relname,heap_blks_hit,heap_blks_read ,trunc(cast(heap_blks_hit as numeric)/(heap_blks_hit+heap_blks_read)*100,2)||'%' as hit_pct
	from sys_statio_user_tables where(heap_blks_hit+heap_blks_read)>0 
	order by hit_pct desc limit 5;
	
查询表的索引数据时缓存命中的比例
	select relname,idx_blks_hit,idx_blks_read ,trunc(cast(idx_blks_hit as numeric)/(idx_blks_hit+idx_blks_read)*100,2)||'%' as hit_pct
	from sys_statio_user_tables where(idx_blks_hit+idx_blks_read)>0 
	order by hit_pct desc limit 5;

统计表的空间占用信息
	select nspname,relname,sys_size_pretty(sys_relation_size(c.oid)) tsize
	from sys_class c left join sys_namespace n on c.relnamespace=n.oid 
	where nspname not in ('sys_catalog','information_schema')
	order by sys_relation_size(c.oid) desc limit 10;

统计索引相关信息

每次索引扫描返回的平均数据量
	select indexrelname,idx_scan,idx_tup_read,
	trunc(cast(idx_tup_read as numeric)/idx_scan,2) as avg_rows
	from sys_stat_user_indexes where idx_scan>0;
查询使用少的索引信息
	select schemaname,relname,indexrelname,idx_scan,
	sys_size_pretty(sys_relation_size(i.indexrelid)) as ind_size
	from sys_stat_user_indexes i join SYS_INDEX using(indexrelid)
	where INDISUNIQUE is false order by idx_scan desc,relname limit 5;
索引的数据缓存命中信息
	select indexrelname,idx_blks_hit,idx_blks_read,
	trunc(cast(idx_blks_hit as numeric)/(idx_blks_hit+idx_blks_read)*100,2)||'%' as hit_pct
	from sys_statio_user_indexes where (idx_blks_hit+idx_blks_read)>0
	order by hit_pct desc limit 5;

统计会话相关信息

查看连接数据库的客户端信息
	select datid,datname,pid,usesysid,usename,application_name,state,query from sys_stat_activity where not pid=sys_backend_pid();
查看长时间运行的操作及等待信息
	select pid,wait_event_type,wait_event,current_timestamp-least(query_start,xact_start)run_time,
	substr(query,1,50)sql_text from sys_stat_activity where not pid=sys_backend_pid();