达梦数据库-统计信息收集-记录总结
1统计信息收集
统计信息主要是描述数据库中表和索引的大小及数据分布状况等信息。比如：表的行数、块数、平均每行的大小、索引的高度、叶子节点数以及索引字段的行数等。统计信息对于CBO（基于代价的优化器）生成执行计划具有直接影响。例如在嵌套循环连接中需要选择小表作为驱动表，哪个是小表完全取决于统计信息中记录的数据量信息。此外，访问一个表是否要走索引，关联查询采用关联方式等都是CBO基于统计信息确定的。
1.1手动收集
--收集指定用户下所有表所有列的统计信息：
DBMS_STATS.GATHER_SCHEMA_STATS('username',100,TRUE,'FOR ALL COLUMNS SIZE AUTO');
--收集指定用户下所有索引的统计信息：
DBMS_STATS.GATHER_SCHEMA_STATS('usename',1.0,TRUE,'FOR ALL INDEXED SIZE AUTO');
--收集单个索引统计信息：
DBMS_STATS.GATHER_INDEX_STATS('username','IDX_T2_X');
--收集指定用户下某表统计信息：
DBMS_STATS.GATHER_TABLE_STATS('username','table_name',null,100,TRUE,'FOR ALL COLUMNS SIZE AUTO');
--收集某表某列的统计信息： STAT 100 ON table_name(column_name);
1.2自动收集
当全表数据量变化超过设定阈值后可自动更新统计信息。
--打开表数据量监控开关，参数值为1时监控所有表，2 时仅监控配置表
SP_SET_PARA_VALUE(1,'AUTO_STAT_OBJ',2);
--设置 SYSDBA.T 表数据变化率超过15%时触发自动更新统计信息
DBMS_STATS.SET_TABLE_PREFS('SYSDBA','T','STALE_PERCENT',15);
--配置自动收集统计信息触发时机
SP_CREATE_AUTO_STAT_TRIGGER(1, 1, 1, 1,'14:36', '2020/3/31',60,1);
/* 函数各参数介绍
SP_CREATE_AUTO_STAT_TRIGGER(
TYPE INT, --间隔类型，默认为天
FREQ_INTERVAL INT, --间隔频率，默认 1
FREQ_SUB_INTERVAL INT, --间隔频率，与 FREQ_INTERVAL 配合使用
FREQ_MINUTE_INTERVAL INT, --间隔分钟，默认为 1440
STARTTIME VARCHAR(128), --开始时间，默认为 22:00
DURING_START_DATE VARCHAR(128), --重复执行的起始时间，默认 1900/1/1 MAX_RUN_DURATION INT, --允许的最长执行时间(秒)，默认不限制
ENABLE INT --0 关闭，1 启用，默认为 1 );
*/
--示例
SP_SET_PARA_VALUE(1,'AUTO_STAT_OBJ',1);
SP_SET_PARA_VALUE(1, 'MONITOR_MODIFICATIONS',1);
SP_CREATE_AUTO_STAT_TRIGGER(1, 1, 1, 1439,'3:00', '2023/9/20',10800,1);
1.3查看统计信息
经过 GATHER_TABLE_STATS、GATHER_INDEX_STATS 或 GATHER_SCHEMA_STATS 收集之后展示。 返回两个结果集：一个是索引的统计信息；另一个是直方图的统计信息
dbms_stats.table_stats_show('模式名','表名');
dbms_stats.index_stats_show('模式名','索引名');
dbms_stats.COLUMN_STATS_SHOW('模式名','表名','列名');
1.4删除统计信息
--表
DBMS_STATS.DELETE_TABLE_STATS('模式名','表名','分区名',...);
--模式
DBMS_STATS.DELETE_SCHMA_STATS('模式名','','',...);
--索引
DBMS_STATS.DELETE_INDEX_STATS('模式名','索引名','分区表名',...);
--字段
DBMS_STATS.DELETE_COLUMN_STATS('模式名','表名','列名','分区表名',...);
1.5操作示例
1.5.1示例需求描述
某用户环境，数据库有多个用户模式，每个用户模式下有非常多的表，同时有少部分表特别大，直接通过整个模式方式收集，时间特别长，超过10小时。
处理思路
用户模式下所有表都是小表，按照模式收集。
用户模式下有大表和小表，小表汇总按照模式表，批量执行收集；大表单独执行，或者大表按照列收集。
涉及收集命令
--收集指定用户下所有表所有列的统计信息：
DBMS_STATS.GATHER_SCHEMA_STATS('username',100,TRUE,'FOR ALL COLUMNS SIZE AUTO');
--收集指定用户下某表统计信息：
DBMS_STATS.GATHER_TABLE_STATS('username','table_name',null,100,TRUE,'FOR ALL COLUMNS SIZE AUTO');
--收集某表某列的统计信息：
STAT 100 ON table_name(column_name);
--收集单个索引统计信息示例：
DBMS_STATS.GATHER_INDEX_STATS('username','IDX_T2_X');
1.5.2环境准备，创建表，索引，插入测试数据
drop table test.t1;
drop table test.t2;
create table test.t1(id int,info varchar2(100));
create table test.t2(id int,info varchar2(100));
create index test.t1_index on TEST.t1(info);
create index test.t2_index on TEST.t2(info);
DECLARE
i NUMBER := 1;
BEGIN
WHILE i <= 100000 LOOP
insert into test.t2("ID", "info") VALUES(i, '在这里编写你要执行的SQL语句sdsdsdsdsds'||i);
i := i + 1;
END LOOP;
END;
commit;
1.5.3查询表和索引的统计信息
call DBMS_STATS.COLUMN_STATS_SHOW('TEST', 't1','id');
call DBMS_STATS.COLUMN_STATS_SHOW('TEST', 't1','info');
call DBMS_STATS.TABLE_STATS_SHOW('TEST', 't1');
call DBMS_STATS.TABLE_STATS_SHOW('TEST', 't2');
call DBMS_STATS.INDEX_STATS_SHOW ('TEST', 't1_index');
call DBMS_STATS.INDEX_STATS_SHOW ('TEST', 't2_index');
1.5.4查询数据库所有用户、模式、表大小
--查看所有模式
select * from dba_objects where object_type ='SCH';
select name from sysobjects where type$='SCH';
--模式与用户关系
select a.name as username, b.name as schenma from sysobjects a inner join sysobjects b on a.id = b.pid where b.subtype$ is null order by username desc;
--查某模式下的表
select * from dba_objects where object_type ='TABLE' and owner='TEST'
--查某模式下的索引
select * from dba_objects where object_type ='INDEX' and owner='TEST'
--查看所有模式
select * from dba_objects where object_type ='SCH';
select name from sysobjects where type$='SCH';
--模式与用户关系
select a.name as username, b.name as schenma from sysobjects a inner join sysobjects b on a.id = b.pid where b.subtype$ is null order by username desc;
--查某模式下的表
select * from dba_objects where object_type ='TABLE' and owner='TEST'
--查某模式下的索引
select * from dba_objects where object_type ='INDEX' and owner='TEST'
--查表大小和条数
select
owner,table_name,table_used_pages(owner, table_name)*(page()/1024.0)/1024.0 SIZE_MB,SF_GET_TABLE_COUNT(A.OWNER, A.TABLE_NAME) TAB_COUNT
from dba_TABLES A WHERE A.OWNER in ('模式1','模式2') order by 3 desc ;
1.5.5如果某个模式下都是小表
--收集指定用户下所有表所有列的统计信息：
DBMS_STATS.GATHER_SCHEMA_STATS('username',100,TRUE,'FOR ALL COLUMNS SIZE AUTO');
1.5.6如果某个模式下有超大表
(1)小表和大表分批拼接生成统计信息的语句
create table TAB_STATS as select
owner,table_name,table_used_pages(owner, table_name)*(page()/1024.0)/1024.0 SIZE_MB ，'DBMS_STATS.GATHER_TABLE_STATS('||
'OWNNAME=>'''||owner||''','||
'TABNAME=>'''||table_name||''','||
'ESTIMATE_PERCENT=>100,'||
'METHOD_OPT=>''FOR ALL COLUMNS SIZE AUTO'','||
'CASCADE=>TRUE,'||
'DEGREE=>4);'
AS gather_sql
from dba_TABLES A
WHERE A.OWNER in ('TEST') and table_used_pages(owner, table_name)*(page()/1024.0)/1024.0<1000
--查询批量执行的命令放入脚本
disql SYSDBA/xxxxxx@localhost:5237 -E "select gather_sql from sysdba.TAB_STATS" >> /home/dmdba/TAB_STATS.sql
--修改脚本
/home/dmdba/TAB_STATS.sql
--执行
nohup disql SYSDBA/xxxxxx@localhost:5237 \`/home/dmdba/TAB_STATS.sql >> /home/dmdba/output.log 2>&1
（2）大表单独执行
--收集指定用户下某表统计信息：
DBMS_STATS.GATHER_TABLE_STATS('username','table_name',null,100,TRUE,'FOR ALL COLUMNS SIZE AUTO');
（3）如果按表执行也很慢，大表按需求列单独执行，示例
STAT 100 ON TEST.t1(info);
1.5.7根据最后统计时间生成统计统计信息批量SQL语句，示例
create table TAB_STATS as SELECT
table_owner,table_name,last_analyzed,'DBMS_STATS.GATHER_TABLE_STATS('||
'OWNNAME=>'''||table_owner||''','||
'TABNAME=>'''||table_name||''','||
'ESTIMATE_PERCENT=>100,'||
'METHOD_OPT=>''FOR ALL COLUMNS SIZE AUTO'','||
'CASCADE=>TRUE,'||
'DEGREE=>4);'
AS gather_sql
FROM dba_tab_statistics
WHERE table_owner IN ('TEST') AND (last_analyzed IS NULL OR last_analyzed < SYSDATE-7) ORDER BY table_owner,table_name;
更多达梦数据库运维指南、在线文档、相关资料、社区在线提问以及技术分享
访问 https://eco.dameng.com/
