达梦数据库-统计信息收集-记录

达梦数据库-统计信息收集-记录总结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, --间隔频率默认 1FREQ_SUB_INTERVAL INT, --间隔频率与 FREQ_INTERVAL 配合使用FREQ_MINUTE_INTERVAL INT, --间隔分钟默认为 1440STARTTIME VARCHAR(128), --开始时间默认为 22:00DURING_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);DECLAREi NUMBER : 1;BEGINWHILE i 100000 LOOPinsert 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 ownerTEST--查某模式下的索引select * from dba_objects where object_type INDEX and ownerTEST--查看所有模式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 ownerTEST--查某模式下的索引select * from dba_objects where object_type INDEX and ownerTEST--查表大小和条数selectowner,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_COUNTfrom 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 selectowner,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_PERCENT100,||METHOD_OPTFOR ALL COLUMNS SIZE AUTO,||CASCADETRUE,||DEGREE4);AS gather_sqlfrom dba_TABLES AWHERE A.OWNER in (TEST) and table_used_pages(owner, table_name)*(page()/1024.0)/1024.01000--查询批量执行的命令放入脚本disql SYSDBA/xxxxxxlocalhost:5237 -E select gather_sql from sysdba.TAB_STATS /home/dmdba/TAB_STATS.sql--修改脚本/home/dmdba/TAB_STATS.sql--执行nohup disql SYSDBA/xxxxxxlocalhost:5237 \/home/dmdba/TAB_STATS.sql /home/dmdba/output.log 212大表单独执行--收集指定用户下某表统计信息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 SELECTtable_owner,table_name,last_analyzed,DBMS_STATS.GATHER_TABLE_STATS(||OWNNAME||table_owner||,||TABNAME||table_name||,||ESTIMATE_PERCENT100,||METHOD_OPTFOR ALL COLUMNS SIZE AUTO,||CASCADETRUE,||DEGREE4);AS gather_sqlFROM dba_tab_statisticsWHERE table_owner IN (TEST) AND (last_analyzed IS NULL OR last_analyzed SYSDATE-7) ORDER BY table_owner,table_name;更多达梦数据库运维指南、在线文档、相关资料、社区在线提问以及技术分享访问 https://eco.dameng.com/