做外单什么网站好,福州模板建站哪家好,营销软件免费版,WordPress主题VR插件#x1f4ab;《博主介绍》#xff1a;✨又是一天没白过#xff0c;我是奈斯#xff0c;DBA一名✨ #x1f4ab;《擅长领域》#xff1a;✌️擅长Oracle、MySQL、SQLserver、阿里云AnalyticDB for MySQL(分布式数据仓库)、Linux#xff0c;也在扩展大数据方向的知识面✌️… 《博主介绍》✨又是一天没白过我是奈斯DBA一名✨ 《擅长领域》✌️擅长Oracle、MySQL、SQLserver、阿里云AnalyticDB for MySQL(分布式数据仓库)、Linux也在扩展大数据方向的知识面✌️ 大佬们都喜欢静静的看文章并且也会默默的点赞收藏加关注 兄弟们沉寂了一段时间后今天我回来了最近博主在赶项目进度有几天午饭也没有顾得上吃不过好在项目也算是顺利上线终于可以休息一下了养精蓄锐恢复一下身体。我也想借此机会与大家分享一个感悟在快节奏的生活中大家都在追求着事业的成功与梦想的实现但请别忘了在追求的同时也要学会适时地停下脚步去品味生活的美好。 希望大家能在工作与生活的天平上找到那个完美的平衡点毕竟身体健康才是革命的本钱既能在职场上挥洒汗水高效完成每一项任务也能在闲暇之余静心享受一杯咖啡的香醇一本好书的静谧或是与家人朋友共度的温馨时光 。 如标题所说今天给大家介绍一下和SQL优化相关的系列——统计信息、SQL执行计划、优化器。众所周知Oracle优化器在SQL执行过程中扮演着至关重要的角色它依赖于统计信息来为每个SQL语句制定最优的执行计划。而这些统计信息对于优化器的决策具有决定性的影响。因此了解和掌握统计信息、执行计划、优化器对于数据库的性能调优至关重要。接下来我们将探讨统计信息、执行计划、优化器的相关知识帮助大家更好地理解并优化自己的数据库性能。 还是老规矩为了让大家更容易消化和逐个理解我将分成六篇文章来进行介绍以便大家劳逸结合而不至于感觉到阅读枯燥六篇的内容分别如下
第一篇统计信息和动态采样的深度剖析当前篇第二篇搞懂优化器和SQL语句的解析步骤(含执行计划分析)第三篇SQL执行计划之访问路径第四篇SQL执行计划之多表连接第五篇了解SQL Tuning Advisor工具的使用并优化SQL语句第六篇SQL性能优化实战(从15秒优化到0.08秒) 目录
一、统计信息
1、Oracle收集统计信息的原理 2、Oracle统计信息的收集分两种自动收集和手动收集
2.1 自动收集
2.1.1 Oracle 10g时的自动收集自动收集维护任务周一至周五晚上2200-600周末全天。
2.1.2 11g/12c时的自动收集自动收集维护任务周一至周五晚上2200开始持续4个小时周末早上600开始持续20个小时
案例111g/12c修改自动收集统计信息的时间避开高峰期。周一至周五凌晨2开始持续4个小时周六凌晨2点开始持续20个小时周七凌晨2点开始持续10个小时
2.2 手动收集为什么要手动收集统计信息
3、统计信息收集的级别
4、统计信息收集的内容
5、动态采样和统计信息的区别
6、收集统计信息的方法和工具
6.1 analyze收集方式8i、9i目前兼容
6.2 dbms_stats包收集方式10g之后主推的方式
6.2.1 dbms_stats包之GATHER_*
案例1dbms_stats.gather_database_stats收集数据库统计信息。多用于升级或迁移后
案例2dbms_stats.gather_schema_stats收集用户统计信息。
案例3dbms_stats.gather_table_stats收集表统计信息。对新上线的表手动收集统计信息。Oracle统计信息会晚上自动统计为了给出真实的执行计划手动收集统计信息便于下午分析
6.2.2 dbms_stats包之DELETE_*
案例1删除数据库统计信息只做了解生产环境不要操作
案例2删除用户统计信息只做了解生产环境不要操作
案例3删除表统计信息只做了解生产环境不要操作
案例4删除索引统计信息只做了解生产环境不要操作
6.2.3 dbms_stats包之LOCK_*/UNLOCK_*
案例1锁定并解锁表的统计信息
6.2.4 dbms_stats包之包的重建
案例一重建dbms_stats包。如果dbms_stats包误删或者出问题那么通过执行oracle内部4个脚本重建即可
7、数据库默认收集的统计信息
8、统计信息的保留时间以及发布验证功能
案例一查看旧的统计信息默认保留天数并修改对于大数据的库频繁收集统计可能导致sysaux表空间增速过快适当减少旧的统计信息的保留时间。31days—7days
二、动态采样当对象还没有统计信息时那么先通过动态采样技术来选择执行计划动态采样用于弥补统计信息缺失时使用
案例1删除表的统计信息默认使用动态采样2级别通过优化器给出执行计划
案例2删除表的统计信息也不使用动态采样 好了言归正传让我们开始今天的内容show time................ 一、统计信息 统计信息statistic主要是描述数据库中表、索引的大小、规模、数据分布状况等的一类信息。例如表的行数块数平均每行的大小索引的leaf blocks索引字段的行数不同值的大小等都属于统计信息。CBO正是根据这些统计信息数据计算出不同访问路径下不同join方式下各种计划的成本最后选择出成本最小的计划。optimizer优化器根据统计信息对每个sql语句执行最优的执行计划执行计划受统计信息影响。 统计信息是存放在数据字典表中的如tab$一般可通过察看某些视图来获取统计信息状况如DBA_TABLES、DBA_INDEXES、DBA_TAB_COL_STATISTICS、DBA_TAB_HISTOGRAMS等。在这些视图中包含表示统计信息的一些字段这些字段只有搜集过统计信息之后才有值否则是空的。例如last_analyzed 字段表示上次统计信息搜集的时间可以根据这个字段快速的了解最近一次统计信息搜集的时间。 1、Oracle收集统计信息的原理 统计信息对于Oracle数据库来说至关重要尤其是在使用CBO基于成本的优化器模式的时候统计信息包括表的使用块数、空闲块数、平均行长度、统计信息收集时间等。在Oracle 9i数据库中两种优化器模式RBO和CBO并存在默认情况下optimizer_mode参数的值是choosechoose不是优化器模式它表示在分析数据库中的语句时如果在对象上有统计信息就是用CBO方式生成执行计划如果对象上没有统计信息则使用RBO模式。 从总体上来说CBO的准确度高于RBO但是它要求要有统计信息并且统计信息必须准确否则Oracle可能会做出错误的判断。所以在Oracle 9i数据库中我们会自己来规划在什么样的时间采用什么样的策略来收集统计信息。也就是说Oracle 9i的统计信息收集工作必须通过手工方式来实现。 到了Oracle 10g默认情况下optimizer_modeall_rows也就是采用了CBO的方式为了保证执行计划的准确在周一到周五晚22:00-次日6:00通过一个jobgather_stat_job自动收集对象的统计信息。这种自动收集统计信息的方式并不是收集所有对象的统计信息而是收集没有统计信息的对象和统计信息过旧的对象。 Automatic Statistics Gathering是由Scheduler调度GATHER_STATS_JOB作业来完成的在GATHER_STATS_JOB作业中则调用DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC存储过程。GATHER_DATABASE_STATS_JOB_PROC是一个内部的存储过程基本上跟DBMS_STATS.GATHER_DATABASE_STATS的功能一样但在其内部有优先顺序的考虑更新量变化量越多的表将会越优先收集统计信息。为对象收集统计信息的条件是之前从来没有收集过的或者是更新的包括insert,update,delete,truncate记录数超过当前总记录数10%的表在Oracle11g中则提供了SET_TABLE_PREFS函数修改10%这个阈值。记录数的更改量由Oracle数据库自动监控在初始化参数statistics_level设置为TYPICAL或者ALL时自动监控即会生效。 2、Oracle统计信息的收集分两种自动收集和手动收集
2.1 自动收集 Oracle的Automatic Statistics Gathering是通过Scheduler来实现收集和维护的。Job名称是GATHER_STATS_JOB, 该Job收集数据库所有对象的2种统计信息 1Missing statistics统计信息缺失 2Stale statistics统计信息陈旧 该JOB是在数据库创建的时候自动创建并由Scheduler来管理。Scheduler在maintenance windows open时运行gather job。默认情况下job 会在每天晚上10到早上6点和周末全天开启10g的收集时间。该过程首先检测统计信息缺失和陈旧的对象。然后确定优先级再开始进行统计信息。 Scheduler Job的stop_on_window_close 属性控制GATHER_STATS_JOB 是否继续。该属性默认值为True. 如果该值设置为False那么GATHER_STATS_JOB 会中断而没有收集完的对象将在下次启动时继续收集。 Gather_stats_job调用dbms_stats.gather_database_stats_job_proc过程来收集statistics 的信息。该过程收集对象statistics的条件如下 1对象的统计信息之前没有收集过。 2当对象有超过10%的rows 被修改此时对象的统计信息也称为stale statistics。 但是对于高度变化的表在白天的活动期间被TRUNCATE/DROP并重建或者块加载超过本身总大小10%的对象我们可以将这些表上的统计设置为NULL。 为了决定是否对对象进行监控Oracle 提供了一个参数STATISTICS_LEVEL。通过设置初始化参数STATISTIC_LEVEL 为TYPICAL 或ALL就可以自动收集统计信息(默认值为TYPICAL因此可以随即启用自动收集统计信息的功能)。STATISTIC_LEVEL 参数的值可以激活GATHER_STATS_JOB。 在10g中表监控默认是激活的如果STATISTICS_LEVEL设置为basic不仅不能监控表而且将禁掉如下一些10g的新功能 1ASH(Active Session History) 2ASSM(Automatic Shared Memory Management) 3AWR(Automatic Workload Repository) 4ADDM(Automatic Database Diagnostic Monitor) 当启动对象的监控后从上次统计信息收集之后的的信息如insertsupdatesdeletes 等这些改变的信息会记录到user_tab_modifications 视图。 当对象的数据发生改变之后经过几分钟的延时这些信息写入到user_tab_modifications视图然后dbms_stats.flush_database_monitoring_info过程就会发现这些信息并讲这些信息保存在内存中。 当监控的对象被修改的部分超过10%时gather_database_stats 或者gather_schema_stats 过程就会去收集这些stale statistics。 2.1.1 Oracle 10g时的自动收集自动收集维护任务周一至周五晚上2200-600周末全天。 自动收集维护任务周一至周五晚上2200-600周末全天 SQL select * from dba_scheduler_jobs where job_namegather_stats_job; ---查看自动收集统计信息的状态 关闭及开启自动收集统计信息的两种方法 --方法01
SQL
exec dbms_scheduler_disable(sys.gather_stats_job);
exec dbms_scheduler_enable(sys.gather_stats_job); --方法02
SQL
alter system set _optimizer_autostats_jobfalse scopespfile;
alter system set _optimizer_autostats_jobtrue scopespfile; 2.1.2 11g/12c时的自动收集自动收集维护任务周一至周五晚上2200开始持续4个小时周末早上600开始持续20个小时 自动收集维护任务周一至周五晚上2200开始持续4个小时周末早上600开始持续20个小时 from dba_scheduler_windows a, dba_scheduler_wingroup_members b
where a.window_name b.window_name
and b.window_group_name MAINTENANCE_WINDOW_GROUP; --查看自动收集统计信息的计
MONDAY_WINDOW freqdaily;bydayMON;byhour22;byminute0; bysecond0 000 04:00:00星期一 每周一22:00:00开始 执行4小时 关闭及开启自动收集统计信息功能 SQL select * from dba_autotask_client; ---查看11g/12c进行的自动任务默认每天进行三个自动任务。status任务的状态。Enable启动disable关闭---auto optimizer stats collection自动统计信息收集就是每日自动收集统计信息的任务---sql tuning advisorSTA自动优化功能启用自动统计信息任务
begindbms_auto_task_admin.enable(client_name auto optimizer stats collection,operation null,window_name null);
end;
/禁止自动统计信息任务
begindbms_auto_task_admin.DISABLE(client_name auto optimizer stats collection,operation null,window_name null);
end;
/ 案例111g/12c修改自动收集统计信息的时间避开高峰期。周一至周五凌晨2开始持续4个小时周六凌晨2点开始持续20个小时周七凌晨2点开始持续10个小时 1查看自动收集统计信息任务是否打开如果没打开修改了没用 SQL select * from dba_autotask_client
---auto optimizer stats collection自动统计信息收集就是每日自动收集统计信息的任务
---sql tuning advisorSTA自动优化功能 2修改自动收集统计信息的时间需要一天一天修改哦 周一
beginsys.dbms_scheduler.set_attribute(name sys.monday_window,attribute repeat_interval,value freqdaily;bydaymon;byhour2;byminute0;bysecond0);sys.dbms_scheduler.set_attribute(name sys.monday_window,attribute duration,value 0 04:00:00);
end;
/周二
beginsys.dbms_scheduler.set_attribute(name sys.tuesday_window,attribute repeat_interval,value freqdaily;bydaytue;byhour2;byminute0;bysecond0);sys.dbms_scheduler.set_attribute(name sys.tuesday_window,attribute duration,value 0 04:00:00);
end;
/周三
beginsys.dbms_scheduler.set_attribute(name sys.wednesday_window,attribute repeat_interval,value freqdaily;bydaywed;byhour2;byminute0; bysecond0);sys.dbms_scheduler.set_attribute(name sys.wednesday_window,attribute duration,value 0 04:00:00);
end;
/周四
beginsys.dbms_scheduler.set_attribute(name sys.thursday_window,attribute repeat_interval,value freqdaily;bydaythu;byhour2;byminute0;bysecond0);sys.dbms_scheduler.set_attribute(name sys.thursday_window,attribute duration,value 0 04:00:00);
end;
/周五
beginsys.dbms_scheduler.set_attribute(name sys.friday_window,attribute repeat_interval,value freqdaily;bydayfri;byhour2;byminute0;bysecond0);sys.dbms_scheduler.set_attribute(name sys.friday_window,attribute duration,value 0 04:00:00);
end;
/周六
beginsys.dbms_scheduler.set_attribute(name sys.saturday_window,attribute repeat_interval,value freqdaily;bydaysat;byhour2;byminute0;bysecond0);sys.dbms_scheduler.set_attribute(name sys.saturday_window,attribute duration,value 0 20:00:00);
end;
/周七
beginsys.dbms_scheduler.set_attribute(name sys.sunday_window,attribute repeat_interval,value freqdaily;bydaysun;byhour2;byminute0;bysecond0);sys.dbms_scheduler.set_attribute(name sys.sunday_window,attribute duration,value 0 10:00:00);
end;
/ 3查看自动统计信息的收集时间是否修改成功 SQL select a.window_name, a.repeat_interval,a.duration from dba_scheduler_windows a, dba_scheduler_wingroup_members b where a.window_name b.window_name and b.window_group_name MAINTENANCE_WINDOW_GROUP; 2.2 手动收集为什么要手动收集统计信息 Oracle自动收集统计信息都是在晚上那么对新上线的表不能立即更新统计信息可能导致执行计划不是最优的给出错误的执行计划。那么这时候就要手动收集统计信息。 关于手动收集的方式在下面的第六小结就会详细介绍哦。 3、统计信息收集的级别 在CBO(基于代价的优化器模式)条件下SQL语句的执行计划由统计信息来决定若没有统计信息则会采取动态采样的方式决定执行计划可以说统计信息关乎sql的执行计划是否正确属于sql执行的指导思想oracle的初始化参数statistics_level控制收集统计信息的级别。 并且调整此参数也会影响AWR报告的输出内容为TYPICAL时AWR报告将包含一般性能统计信息为ALL时AWR报告将包含更详细的性能统计信息以提供更全面的数据库性能分析。 SQL show parameter statistics_level
STATISTICS_LEVELSTATISTICS_LEVEL指定数据库和操作系统统计信息的收集级别。Oracle数据库收集这些统计数据的目的多种多样包括做出自我管理决策。一共有三个值BASIC收集基本的统计信息TYPICAL收集大部分统计信息足够诊断99%的性能问题默认值ALL 收集全部统计信息包括OS以及sql执行路径方面的一些统计信息。除非遇见严重的性能问题或在一些特殊的性能诊断方面才会用到 相关视图 SQL select * from v$statistics_level; ---收集的统计信息类型总共为24项。为Typical时收集21项为all时收集全部24项 4、统计信息收集的内容 统计信息可以帮助优化器更好地理解数据从而选择更有效的查询路径。例如如果一个表只有很少的数据而一个全表扫描的成本很低那么优化器可能会选择全表扫描而不是使用索引。相反如果表的数据量很大并且使用索引的成本更低那么优化器可能会选择使用索引。 1表的统计信息 表的统计信息用于描述表的详细信息包括记录数num_rows、表块的数量blocks、平均行长度avg_row_len等典型维度。 2索引的统计信息 索引的统计信息描述了索引的详细信息它包含了索引的层级blevel、叶子块数量leaf_blocks、聚簇因子clustering_factor等典型维度。 1层级level层级表示从根节点到叶子块的深度层级被CBO用于计算访问索引叶子块的成本层级越大表示从根节点到叶子块所需要访问的数据块的数量就越多耗费的i/o就会越多索引访问的成本就会越大。在数据库里如果需要降低索引的层级需要rebuild才可以。 2聚簇因子的含义及重要性oracle数据库中聚簇因子是指按照索引键值排序的索引行和存储于对应表中的数据行的存储顺序的相似程度。oracle数据库按照如下算法计算聚簇因子 ①聚簇因子初始值为1. ②oracle首先定位到目标索引处于最左边的叶子块。 ③从最左边叶子块的第一个索引键值所在的索引行开始顺序扫描在顺序扫描的过程中oracle会比较当前索引行的rowid和之前那个索引行的rowid如果这两个rowid并不是指向同一个表块那么oracle就将聚簇因子的当前值递增1如果这两个rowid是指向同一个表块oracle就不改变聚簇因子的值。oracle在比对rowid时并不会回表去访问相应的表块。 ④上述的比对过程会持续下去知道扫描完目标索引的所有索引块的所有索引行。 ⑤上述顺序扫描完成后聚簇因子的当前值就是索引统计信息中的clustering_factor然后存储在数据字典里。 由以上的过程可知聚簇因子高的索引走索引范围扫描时比相同条件下聚簇因子低的索引要耗费更多的物理i/o所以聚簇因子高的索引走索引范围扫描的成本会比相同条件下聚簇因子低的索引走索引范围扫描的成本高。即聚簇因子越小越好。 oracle数据库中能够降低聚簇因子的唯一方法就是对表中数据按照目标索引的索引键值排序后重新存储。 oracle数据库里cbo在计算索引范围扫描index range scan的成本计算公式入下 (*)IRS COSTI/O COSTCPU COST
(*)I/O COSTINDEX ACCESS I/O COSTTABLE ACCESS I/O COST
(*)index access i/o costblevel_celt(#leaf_blocks*ix_sel)
(*)table access i/o costcelt(clustering_factor*ix_sel_with_filters) 从这个公式可以推断出走索引范围扫描的成本可以近似看作是与聚簇因子成正比。因此聚簇因子值得大小实际对CBO判断是否走相关索引起着至关重要的作用。 3列的统计信息: oracle里列的统计信息用于描述oracle数据库里列的详细信息包括列的distinct值num_distinct、列的null值num_nulls得数量、列的最小值low_value、列的最大值high_value等一些典型维度。可以通过数据字典dba_tab_col_statistics、dba_part_col_statistics和dba_subpart_col_statistics分别查看表、分区表的分区、分区表的子分区的列的统计信息。 1列的distinct值上述数据字典中字段num_distinct表示distinct值数量cbo用num_distinct值来计算目标列做等值查询时的可选择率。 2上述字典中的字段num_nulls存储的就是目标列的null值数量cbo用num_null值来评估对目标列施加“is null”或“is not null”条件后的返回结果集cardinality。另外cbo还用num_nulls值来调整对有null值得目标列做等值查询时的可选择率selectivity。对目标列进行等值查询时可选择率计算公式selectivity(1/num_distinct)*((num_rows-num_nulls)/num_rows) 3上述字典的列low_value和high_value值就是目标列的最小值和最大值cbo通过low_value和high_value来计算目标列进行范围查询时可选择率selectivity的值。 5、动态采样和统计信息的区别 统计信息statistic统计信息会收集数据库中对象的详细信息通过优化器给出执行计划 动态采样默认2级别的动态采样采取对象的64个数据块进行分析通过优化器给出执行计划 6、收集统计信息的方法和工具
6.1 analyze收集方式8i、9i目前兼容 三大功能 1、搜集和删除索引、表和簇的统计信息 2、验证表、索引和簇的结构analyze统计信息方式特有dbms_stats不支持 3、鉴定表和簇和行迁移和行链接analyze统计信息方式特有dbms_stats不支持 analyze统计收集对比于dbms_stats的优势 1、收集在freelist上的blocks信息 2、检验存储格式的合法性 3、识别表或cluster的行迁移与行链接 analyze命令 --01.同时收集表表字段索引字段推荐 analyze table TABLE_NAME compute statistics; --02.表收集 analyze table TABLE_NAME compute statistics for table; --03.表字段收集 analyze table TABLE_NAME compute statistics for all columns; --04.索引收集 analyze table TABLE_NAME compute statistics for all indexes; --05.索引字段收集 analyze table TABLE_NAME compute statistics for all indexed columns; --06.同时收集表表字段索引 analyze table TABLE_NAME compute statistics for table for all indexes for all columns; --07.删除统计信息 analyze table TABLE_NAME delete statistics; 补充利用analyze ...validate structure..;语句验证表、索引、聚簇、分区表、临时表的结构。 对于表该语句会扫描所有的数据块和数据行以验证表的结构是否跟数据字典中表的定义一样是否满足定义的各种约束 analyze table TABLE_NAME validate structure; ---分析表的结构 analyze table TABLE_NAME validate structure cascade; ---连带分析索引等结构 analyze table TABLE_NAME validate structure online | offiline; ---当有DML操作访问该表的时候online则支持在线验证offline则不支持。 对于聚簇跟表原理一样验证聚簇的数据分布是否跟聚簇的定义一样 对于临时表验证该会话阶段临时表和索引的结构是否跟定义的一样 对于索引该语句会扫描所有的数据块以验证是否有坏的数据块存在 对于分区表该语句会扫描每一个数据块和数据行以验证行数据是否分配到正确的分区 若验证的结构不一样就会报错。对于该类错误修正方法是删除、重建 6.2 dbms_stats包收集方式10g之后主推的方式 DBMS_STATS包PL/SQL包收集统计信息能良好地估量统计数据尤其是针对较大的分区表并能取得更好统计效果。最终制订出速度更快的SQL执行计划。 可以收集数据库级别、schema级别及表级别的统计信息。还可以对统计信息删除、锁定、导出、导入等。 dbms_stats与analyze的区别 dbms_stats是Oracle 9i及后续版本中用于收集统计信息的包虽然analyze命令也一直可以使用但是现在已经不推荐使用analyze命令来收集统计信息而是使用dbms_stats。两者之间有很大的不同dbms_stats能正确收集分区表的统计信息也就是说能够收集global statistic而analyze只能收集最低层次对象的统计信息然后推导和汇总出高一级对象的统计信息如果分区表只会收集分区统计信息然后再汇总出所有分区的统计信息得到表一级的统计信息。 ps补充global statisticgolbal statistic是指直接从对象本身收集到的统计信息而不是从下一级对象“推导”和“汇总”出来的统计信息golbal statistic对于优化器来说非常重要一个SQL除非其查询条件限制了数据只在分区上否则大多数情况下需要golbal statistic才能得到正确的执行计划。有的统计值可以从下一级对象进行汇总后得到如表的总行数可以通过各分区的行数相加得到。但有的统计值不能通过下一级对象得到比如列上的唯一值数量distinct value以及密度值density。 dbms_stats其实就是一个包里面包含多可执行的存储过程 ①包定义部分 ②包主体部分被隐藏 dbms_stats收集的相关视图 SQL select * from dba_tab_statistics;
SQL select * from dba_tab_col_statistics;
SQL select * from dba_ind_statistics; dbms_stas包不仅能够对表进行分析它还可以对数据库进行分析 1性能数据的收集 2性能数据的设置 3性能数据的删除 4性能数据的备份和恢复 6.2.1 dbms_stats包之GATHER_* DBMS_STATS.GATHER_* 系列过程可以用来收集统计信息以帮助优化器生成高效的执行计划。以下是一些常用的 DBMS_STATS.GATHER_* 过程及其功能 DBMS_STATS.GATHER_DATABASE_STATS收集整个数据库的统计信息包括所有表、索引和其他数据库对象的统计信息。DBMS_STATS.GATHER_SCHEMA_STATS收集指定模式schema中的所有对象的统计信息。DBMS_STATS.GATHER_TABLE_STATS收集指定表的统计信息。DBMS_STATS.GATHER_INDEX_STATS收集指定索引的统计信息。DBMS_STATS.GATHER_SYSTEM_STATS收集硬件的统计信息。记录I/O寻址速度、I/O 传输速度、CPU 处理速度等DBMS_STATS.GATHER_DICTIONARY_STATS收集数据字典对象的统计信息。 案例1dbms_stats.gather_database_stats收集数据库统计信息。多用于升级或迁移后 包相关参数与语法 DBMS_STATS.GATHER_DATABASE_STATS (estimate_percent NUMBER DEFAULT to_estimate_percent_type(get_param(ESTIMATE_PERCENT)),block_sample BOOLEAN DEFAULT FALSE,method_opt VARCHAR2 DEFAULT get_param(METHOD_OPT),degree NUMBER DEFAULT to_degree_type(get_param(DEGREE)),granularity VARCHAR2 DEFAULT GET_PARAM(GRANULARITY),cascade BOOLEAN DEFAULT to_cascade_type(get_param(CASCADE)),stattab VARCHAR2 DEFAULT NULL,Statid VARCHAR2 DEFAULT NULL,Options VARCHAR2 DEFAULT GATHER,Objlist OUT ObjectTab,statown VARCHAR2 DEFAULT NULL,force BOOLEAN DEFAULT FALSE, gather_sys BOOLEAN DEFAULT TRUE,no_invalidate BOOLEAN DEFAULT to_no_invalidate_type (get_param(NO_INVALIDATE)),obj_filter_list ObjectTab DEFAULT NULL); 值描述 estimate_percent 采样行的百分比,取值范围[0.000001,100],null为全部分析,不采样。 常量dbms_stats.auto_sample_size是默认值 , 由oracle绝定最佳取采样值。estimate_percent 参数是一种比照新的设计它答应 Oracle 的dbms_stats 在搜罗统计数据时自动估量要采样的一个segment的最佳百分比estimate_percent dbms_stats.auto_sample_size, 自动统计采样。在使用自动采样时Oracle 会为一个样本尺寸挑选5到20的百分比的统计。记住统计数据品质越好CBO做出的选择越好。 block_sapmple 是否用块采样代替行采样 method_opt 决定 histograms 直方图信息是怎样被统计的. method_opt 的取值如下 for table ---只统计表的 histograms. for all columns ---统计所有列的 histograms. for all indexed columns: ---统计所有 indexed 列的 histograms. for all indexes ---统计所有索引 for all hidden columns ---统计你看不到列的 histograms for columns list size n|repeat|auto|skewonly: 统计指定列的histograms。n 的取值范围[1,254]; repeat 上次统计过的 histograms;auto 由oracle决定n的大小;skewonlyOracle 确定需要收集检查每个索引中每列值的分布。如 method_optfor all columns size skewonly method_optfor all columns size repeat method_optfor all columns size auto Degree 决定并行度.默认值为null. 分析索引速度加快,根据 cpu 数量来设置一般在业务空闲的时候 degree 可设为 cpu 数量1繁忙的时候就再小点。 granularity 分区统计的收集可以通过声明参数GRANULARITY。根据将优化的SQL语句优化器可以选择使用分区统计或全局统计对于大多数系统这两种统计都是很重要的Oracle推荐将GRANULARITY设置为AUTO同时收集全部信息。其中Granularity用于定义采集粒度 ALL采集Global、partition、subpartition等粒度统计信息。 AUTO根据分区类型由Oracle确定统计信息采集粒度。 PARTITION只采集partition粒度统计信息。 SUBPARTITION只采集subpartition粒度统计信息。 Cascade 是收集索引的信息.默认为 falase Stattab 指定要存储统计信息的表 Statid 如果多个表的统计信息存储在同一个 stattab 中用于进行区分。 Options 使用4个预设的法子之一这个选项能把握 Oracle 统计的刷新方法 Gather ——重新分析整个Schema。 gather empty ——只分析目前还没有统计的表。 gather stale ——只重新分析修改量超过 10%的表这些修改包含插入、更新和删除。 gather auto ——重新分析以前没有统计的对象以及统计数据过期变脏的对象。 注意使用 gather auto 相似于组合使用 gather stale 和 gather empty。不论 gather stale仍是gather auto都请求进行监视。假如你施行一个alter table xxx monitoring 命令Oracle会用dba_tab_modifications 视图来跟踪发生变动的表。这样一来你就确实地知道自从上一次剖析统计数据以来发生了多少次插入、更新和删除操作。 objlist: 指定对象列表 Statown 存储统计信息 表的拥有者.以上三个参数若不指定,统计信息会直接更新到数据字典 Force 即使表锁住了也收集统计信息 gather_sys 只收集 sys 的对象 no_invalidate 通过不同的参数配置可以实现对 Oracle 失效共享游标行为的控制TRUEFALSE。 如果取值为 true表示不进行游标失效动作原有的 shared cursor 保持原有状态。 如果取值为 false表示将统计量对象相关的所有 cursor 全部失效。如果设置为auto_invalidate根据官方文档Oracle 自己决定shared cursor失效动作。从10G开始Oracle就将auto_invalidate 作为默认的统计量收集行为select dbms_stats.get_param(pname no_invalidate) from dual; obj_filter_list 对象筛选器列表 1收集数据库层面的统计信息 [oraclelf01 backup]$ vi status.sql begin
dbms_stats.gather_database_stats;
end;
/ [oraclelf01 backup]$ nohup sqlplus / as sysdba status.sql ---因为收集统计信息时间长所以写个sh后台运行 2查询所有表、列、索引的统计信息是否被收集 SQL select table_name,blocks,num_rows,avg_row_len,last_analyzed from dba_tables where owneruser; ---迁移后的生产用户SQL select column_name,num_distinct,num_nulls,histogram,last_analyzed from dba_tab_columns where owneruser; ---迁移后的生产用户SQL select index_name,leaf_blocks,clustering_factor,blevel,num_rows,last_analyzed from dba_indexes where owneruser; ---迁移后的生产用户 案例2dbms_stats.gather_schema_stats收集用户统计信息。 包相关参数与语法 DBMS_STATS.GATHER_SCHEMA_STATS ( ownname VARCHAR2, estimate_percent NUMBER DEFAULT to_estimate_percent_type (get_param(ESTIMATE_PERCENT)), block_sample BOOLEAN DEFAULT FALSE, method_opt VARCHAR2 DEFAULT get_param(METHOD_OPT), degree NUMBER DEFAULT to_degree_type(get_param(DEGREE)), granularity VARCHAR2 DEFAULT GET_PARAM(GRANULARITY),cascade BOOLEAN DEFAULT to_cascade_type(get_param(CASCADE)), stattab VARCHAR2 DEFAULT NULL, statid VARCHAR2 DEFAULT NULL, options VARCHAR2 DEFAULT GATHER, objlist OUT ObjectTab, statown VARCHAR2 DEFAULT NULL, no_invalidate BOOLEAN DEFAULT to_no_invalidate_type ( get_param(NO_INVALIDATE)), force BOOLEAN DEFAULT FALSE, obj_filter_list ObjectTab DEFAULT NULL); 值描述 ownname 要分析的拥有者 其他参数和案例1的dbms_stats.gather_database_stats是相同的可以参考案例1的 1收集用户层面的统计信息 SQL begin
dbms_stats.gather_schema_stats(ownname user, ---收集的用户名options gather auto, --重新分析以前没有统计的对象以及统计数据过期变脏的对象estimate_percent dbms_stats.auto_sample_size, ---自动统计采样method_opt for all indexed columns, ---直方图收集所有索引列degree 2); ---并行度
end;
/ 2查询所有表、列、索引的统计信息是否被收集 SQL select table_name,blocks,num_rows,avg_row_len,last_analyzed from dba_tables where ownerITPUX; ---查看itpux用户表的统计信息SQL select column_name,num_distinct,num_nulls,histogram,last_analyzed from dba_tab_columns where ownerITPUX; ---查看itpux用户表列的统计信息SQL select index_name,leaf_blocks,clustering_factor,blevel,num_rows,last_analyzed from dba_indexes where ownerITPUX; ---查看itpux用户索引的统计信息 案例3dbms_stats.gather_table_stats收集表统计信息。对新上线的表手动收集统计信息。Oracle统计信息会晚上自动统计为了给出真实的执行计划手动收集统计信息便于下午分析 包相关参数与语法 DBMS_STATS.GATHER_TABLE_STATS ( ownname varchar2,tabname varchar2,Partname varchar2 default null,estimate_percent number default DEFAULT_ESTIMATE_PERCENT,block_sample boolean default FALSE,method_opt varchar2 default DEFAULT_METHOD_OPT,degree number default to_degree_type(get_param(DEGREE)),granularity varchar2 default DEFAULT_GRANULARITY,cascade boolean default DEFAULT_CASCADE,stattab varchar2 default null,Statid varchar2 default null,statown varchar2 default null,no_invalidate boolean default to_no_invalidate_type(get_param(NO_INVALIDATE)),stattype varchar2 default DATA,force boolean default FALSE); 值描述 ownname 要分析的拥有者 tabname 要分析的表名 partname 分区的名字只对分区表或分区索引有用 其他参数和案例1的dbms_stats.gather_database_stats是相同的可以参考案例1的 1查看表当前的统计信息情况 SQL select count(*) from table_m11; ---table_m11有1万行数据SQL select * from dba_tables where ownerUSER and table_nameTABLE_M11; Num_rows列记录着表的行数量oracle每晚会自动收集行数据的信息然后把数量维护到列中。因为表刚上线所以统计信息还没有更新 2如果当天下午对表进行查询了那么由于统计信息不全导致执行计划不真实就需要手动收集统计信息 SQL begin
dbms_stats.gather_table_stats(ownname user, ---收集的用户名tabname table_m11, ---要分析的表名estimate_percent dbms_stats.auto_sample_size, ---自动统计采样degree 2); ---并行度end;
/ 3查看表、表的字段、表的索引的统计信息情况 SQL select table_name,blocks,num_rows,avg_row_len,last_analyzed from dba_tables where ownerUSER and table_nameTABLE_M11; SQL select column_name,num_distinct,num_nulls,histogram,last_analyzed from dba_tab_columns where ownerUSER and table_nameTABLE_M11; num_distinct列记录着表下每个列数据不同值对信息统计来说非常重要 SQL select index_name,leaf_blocks,clustering_factor,blevel,num_rows,last_analyzed from dba_indexes where ownerUSER and table_nameTABLE_M11; 6.2.2 dbms_stats包之DELETE_* DBMS_STATS.DELETE_* 过程用于删除数据库中收集的统计信息。你可以使用它来删除特定表、索引或模式的统计信息以便重新收集或清理不需要的信息。这个过程有助于保持数据库统计信息的准确性和相关性。以下是一些常用的 DBMS_STATS.DELETE_*过程及其功能 dbms_stats.delete_database_stats 删除数据库统计信息。 dbms_stats.delete_schema_stats 删除特定模式中所有对象的统计信息。 dbms_stats.delete_table_stats 删除特定表的统计信息。 dbms_stats.delete_index_stats 删除特定索引的统计信息。 dbms_stats.delete_column_stats 删除特定列统计信息。 dbms_stats.delete_dictionary_stats 删除特定数据字典统计信息。 PS小提示删除统计信息后还没有自动收集的时oracle的执行计划默认根据动态采样来选择执行计划动态采样用于弥补统计信息缺失时使用 SQL set autotrace traceonly
SQL select * from table_m11; ---使用动态采样 table_m11有20000行数据优化器根据动态采样做出执行计划时误差到23139行 SQL show parameter optimizer_dynamic_sampling
动态采样2级别。表示没有统计信息时使用动态采样影响执行计划如果设置为0表示没有统计信息时也不使用动态采样那么执行计划误差非常大 案例1删除数据库统计信息只做了解生产环境不要操作
SQL
begindbms_stats.delete_database_stats;
end;
/
案例2删除用户统计信息只做了解生产环境不要操作
SQL
begindbms_stats.delete_schema_stats(ownname USER);
end;
/
案例3删除表统计信息只做了解生产环境不要操作
SQL
begindbms_stats.delete_table_stats(ownname USER,tabname TABLE_NAME);
end;
/
案例4删除索引统计信息只做了解生产环境不要操作
SQL
begindbms_stats.delete_index_stats(ownname USER,indname idx_user_table_field);
end;
/ 6.2.3 dbms_stats包之LOCK_*/UNLOCK_* DBMS_STATS 包提供了用于锁定和解锁统计信息的过程以防止统计信息在收集过程中被意外修改或删除。这对维护数据的一致性和优化查询性能非常重要。以下是一些常用的 DBMS_STATS.LOCK_*/UNLOCK_*过程及其功能 锁定统计信息 dbms_stats.lock_schema_stats 锁定用户统计信息 dbms_stats.lock_table_stats 锁定表统计信息 dbms_stats.lock_partition_stats 锁定分区表统计信息 解锁统计信息 dbms_stats.unlock_schema_stats 解锁用户统计信息 dbms_stats.unlock_table_stats 解锁用户统计信息 dbms_stats.unlock_partition_stats 解锁分区表统计信息 查看哪些表的统计信息被锁定stattype_locked字段为ALL的表示锁定了表的统计信息默认stattype_locked字段为空表示可以收集统计信息 select * from dba_ind_statistics where stattype_lockedALL;
select * from dba_tab_statistics where stattype_lockedALL; ps小提示oracle默认有部分表的统计信息是锁定的不允许收集 案例1锁定并解锁表的统计信息 1锁定分区表的统计信息 SQL
begindbms_stats.lock_table_stats(ownname USER,tabname TABLE_L1); ---分区表
end;
/SQL select * from dba_tab_statistics where ownerUSER and stattype_lockedALL; ---stattype_locked字段为ALL的表示锁定了表的统计信息默认stattype_locked字段为空表示可以收集统计信息 2收集表的统计信息提交被锁 SQL
begindbms_stats.gather_table_stats(ownname USER,tabname TABLE_L1);
end;
/ 3解锁分区表的统计信息 SQL
begindbms_stats.unlock_table_stats(ownname USER,tabname TABLE_L1);
end;
/ 4继续收集表的统计信息 SQL
begindbms_stats.gather_table_stats(ownname USER,tabname TABLE_L1);
end;
/SQL select table_name,blocks,num_rows,avg_row_len,last_analyzed from dba_tables where ownerUSER and table_nameTABLE_L1; 6.2.4 dbms_stats包之包的重建 如果各位不小心误删除dbms_stats包或者dbms_stats包自身出现了一些bug那么Oracle提供重建dbms_stats包的办法 案例一重建dbms_stats包。如果dbms_stats包误删或者出问题那么通过执行oracle内部4个脚本重建即可 1误删除dbms_stats包 SQL drop package dbms_stats; 2执行4个脚本重建dbms_stats包 SQL
?/rdbms/admin/dbmsstat.sql
?/rdbms/admin/prvtstas.plb
?/rdbms/admin/prvtstai.plb
?/rdbms/admin/prvtstat.plb 7、数据库默认收集的统计信息 Table statistics表统计信息 Column statistics列统计信息 Index statistics索引统计信息 system statistics系统统计信息 Table statistics相关视图user_tables、all_tables、dba_tables SQL select table_name,blocks,num_rows,EMPTY_BLOCKS,avg_row_len,last_analyzed from dba_tables where ownerUSER and table_nameTABLE_M11; ---以table_m11表为例收集表的统计信息Blocks表占用的块数可以计算出表多大num_rows表的总行数Avg_row_len行的平均字符长度EMPTY_BLOCKS表中未使用数据块的数量只有当使用DBMS_STATS包收集表上的统计信息时才会填充此列。last_analyzed表的统计信息的最后收集时间sql执行计划受统计信息影响。SQL select Table_Name,Partition_Name,High_Value,Num_Rows,EMPTY_BLOCKS,last_analyzed from dba_tab_partitions where TABLE_NAMETABLE_M10;SQL select Table_Name,Partition_Name,High_Value,Num_Rows,EMPTY_BLOCKS,last_analyzed from dba_tab_subpartitions where TABLE_NAMETABLE_M10; Column statistics相关视图user_tab_columns、all_tab_columns、dba_tab_columns SQL select column_name,num_distinct,num_nulls,histogram,last_analyzed from dba_tab_columns where ownerUSER and table_nameTABLE_M11; ---以table_m11表为例收集列的统计信息num_distinct表下每个列数据的不同值有多少行num_nulls表下每个列中有空值的数量。0表示没有空值都有数据histogram直方图。统计报告图描述数据分布last_analyzed表的统计信息的最后收集时间sql执行计划受统计信息影响。 Index statistics相关视图 : user_indexes、all_indexes、dba_indexes SQL select index_name,leaf_blocks,clustering_factor,blevel,num_rows,last_analyzed from dba_indexes where ownerUSER and table_nameTABLE_M1; ---以table_m1表为例收集索引的统计信息SQL select t2.table_name,t1.index_name,t1.partition_name,t1.last_analyzed,t1.num_rows,t1.leaf_blocks,t1.status from dba_ind_partitions t1, dba_indexes t2 where t1.index_namet2.index_name and t2.table_nameTABLE_M10;leaf_blocks索引中叶子块数量。clustering_factor集群因子。值越高越差小于总行数(表的总num_rows数)接近块数最好表的总blocks数Blevelb-tree索引等级num_rows索引的总行数last_analyzed表的统计信息的最后收集时间sql执行计划受统计信息影响。 system statistics相关视图aux_stats$、X$KCFIO SQL select * from sys.aux_stats$;SQL select * from sys.x$KCFIO; ---两个表记录系统IO/cpu使用率。数据库做系统统计信息后IO/cpu等信息记录到两张表中 8、统计信息的保留时间以及发布验证功能 10G当一个表生成新的统计信息时该表的旧统计信息会保留。如果依赖该新统计信息的查询出现性能问题时可以恢复旧的来解决可能出现的性能问题。10g、11g中对象旧的统计信息默认的保存时间是31天统计信息在sysaux表空间有存储开销。 案例一查看旧的统计信息默认保留天数并修改对于大数据的库频繁收集统计可能导致sysaux表空间增速过快适当减少旧的统计信息的保留时间。31days—7days SQL select dbms_stats.get_stats_history_retention from dual; SQL select dbms_stats.get_stats_history_availability from dual; ---最晚可恢复的统计信息。一般是当前时间减去31天 利用DBMS_STATS.ALTER_STATS_HISTORY_RETENTION包更改对象的统计信息保存时间 SQL begindbms_stats.alter_stats_history_retention(retention 20); ---统计信息保留20天end;/ 呼~统计信息到这里就结束了已经2万字了各位小伙伴可以休息一下眼睛了......... 摸鱼时间到不是逃避是智慧的充电站哦 摸鱼1分钟快速眨眼操给眼睛做个微型瑜伽恢复亮晶晶 摸鱼5分钟站起来转转脖子扭扭腰办公室也能跳出广场舞的节奏 摸鱼10分钟想象自己是条深海鱼在思维的海洋里自由遨游说不定下一个灵感大鱼就上钩了
好啦休息完毕电量满格接下来开始动态采样的学习记得学习路上适当摸鱼效率加倍哦 二、动态采样当对象还没有统计信息时那么先通过动态采样技术来选择执行计划动态采样用于弥补统计信息缺失时使用 在CBO(基于代价的优化器模式)条件下SQL语句的执行计划由统计信息来决定若没有统计信息则会采取动态采样的方式决定执行计划。统计信息关乎sql的执行计划是否正确属于sql执行的指导思想。 动态采样Dynamic Sampling)提出是在9iR2在段表索引分区没有分析的情况下为了使CBO优化器得到足够的信息以保证做出正确的执行计划而发明的一种技术。可以把它看做分析手段的一种补充。当段对象没有统计信息时即没有做分析动态采样技术可以通过直接从需要分析的对象上收集数据块采样来获得CBO需要的统计信息。 有动态采样虽然有少许的误差但是比不采样好的多最起码执行计划不会很离谱。 动态采样和统计信息的区别 统计信息statistic统计信息会收集数据库中对象的详细信息通过优化器给出执行计划 动态采样默认2级别的动态采样采取对象的64个数据块进行分析通过优化器给出执行计划 动态采样的10个级别 SYSorcl show parameter optimizer_dynamic_sampling ---动态采样2级别。表示没有统计信息时使用动态采样影响执行计划如果设置为0表示没有统计信息时也不使用动态采样那么执行计划误差非常大 Level 0不做动态分析 Level 1Oracle对没有分析的表进行动态采样但需要同时满足以下 4 个条件。 1SQL 中至少有一个未分析的表 2未分析的表出现在关联查询或者子查询中 3未分析的表没有索引 4未分析的表占用的数据块要大于动态采样默认的数据块32 个 Level 2对所有的未分析表做分析动态采样的数据块是默认数据块的2倍即 64 个。 Level 3采样的表包含满足 Level 2 定义的所有表同时包括那些谓词有可能潜在地需要动态采样的表这些动态采样的数据块为默认数据块对没有分析的表动态采样的默认块为默认数据块的 2 倍 即 64 个 。 Level 4采样的表包含满足 Level 3 定义的表同时还包括一些表他们包含一个单表的谓词会引用另外的 2 个列或者更多的列采样的块数是动态采样默认数据块数 对没有分析的表动态采样的数据块为默认数据块的 2 倍。 Level 56789采样的表包含满足 Level 4 定义的表同时分别使用动态采样默认数据块的 24832128 倍的数量来做动态分析。 Level 10采样的表包含满足 Level 9 定义的所有表同时对表的所有数据进行动态采样。采样的数据块越多得到的分析数据就越接近与真实但同时伴随着资源消耗的也越大 当表中的统计信息被删除或过时数据库优化器在执行查询时无法依赖现有的统计数据来准确评估数据分布和选择性从而难以直接判断最优的查询执行计划。为了弥补这一不足优化器通常会采用动态采样的方式来获取实时的数据样本以辅助生成执行计划那么让我们通过案例一起了解下
案例1删除表的统计信息默认使用动态采样2级别通过优化器给出执行计划 SQL show parameter optimizer_dynamic_sampling ---动态采样2级别。表示没有统计信息时使用动态采样影响执行计划如果设置为0表示没有统计信息时也不使用动态采样那么执行计划误差非常大 SQL begindbms_stats.delete_table_stats(ownname USER,tabname TABLE_M11);
end;
/SQL set autotrace traceonly
SQL select * from table_m11; ---使用动态采样 table_m11真实有20000行数据优化器根据动态采样采取64个块做出执行计划时误差到23139行 那么还有一种极端情况删除表统计信息的同时也不使用动态采样会发生什么情况呢这么做太不给Oracle面子Oracle你会玩你清高这种只是测试了解哦千万不要在生产瞎搞
案例2删除表的统计信息也不使用动态采样 SQL alter session set optimizer_dynamic_sampling0;
SQL show parameter optimizer_dynamic_sampling ---动态采样0级别。不做动态分析 SQL begindbms_stats.delete_table_stats(ownname USER,tabname TABLE_M11);
end;
/ITPUXorcl set autotrace traceonly
ITPUXorcl select * from table_m11; table_m11真实有20000行数据优化器直接给出执行计划时误差到51949行。误差的原因是因为高水位导致的分配的段、块没有被回收那么就以高水位为基准算出块大小如果采用动态采样那么会扫描到有些块高水位以下部分是空的那么扫描的行就变少了 两眼发昏洋洋洒洒2万7千余字这篇文章到这里就算结束了 构思了1天撰写了5小时关于统计信息这部分涉及到理论所以我的这篇文章可能并不完美但是已经非常用心在整理了真的虚了需要一根士力架做回自己那各位小伙伴们就慢慢消化吧记得一键三连哦。
