Oracle数据库性能优之统计信息与执行计划.pptxVIP

SQL性能优化_执行计划与统计信息 作者：　谭家俊 编码：　 日期： 2015-07-13 版本：　1.0 概述 统计信息 Oracle数据库优化统计描述的详细信息的数据库及其对象。优化器成本模型依赖于收集有关查询中的对象的统计和数据库和主机在运行查询。统计信息是优化器选择一个SQL语句的最佳执行计划的关键。 执行计划 一个执行计划描述了一段SQL语句建议的执行方法,该计划显示ORACLE数据库的执行sql时步骤的组合。每一步都得到数据库中的物理数据行或准备为他们的用户发布的声明。如下图，优化器生成SQL语句输入的两个可能的执行计划，利用统计方法计算其成本，比较它们的成本，并选择成本最低的执行计划。 2 统计信息 3 项目KPI Table statistics –Number of rows(行数) –Number of blocks(块数) –Average row length(行平均长度) Column statistics –Number of distinct values (NDV) in a column(计算数据密度) –Number of nulls in a column(列中null的数量) –Data distribution (histogram)(数据分布，直方图) –Extended statistics Index statistics –Number of leaf blocks(叶子块的数量) –Number of levels(索引的高度) –Index clustering factor(评估索引的查询成本，聚集因子) System statistics –I/O performance and utilization(I/O性能和利用) –CPU performance and utilization(CPU性能和利用) 统计信息(数据字典) 4 项目KPI 统计信息（收集方法） 5 项目KPI DBMS_STAT包 GATHER_INDEX_STATS : Index statistics GATHER_TABLE_STATS : Table, column, and index statistics GATHER_SCHEMA_STATS : Statistics for all objects in a schema GATHER_DATABASE_STATS : Statistics for all objects in a database GATHER_SYSTEM_STATS: CPU and I/O statistics for the system 这个包使您可以控制如何统计数据收集，包括对统计数据的收集，并行度的抽样方法，在分区表的统计粒度，等等。 注意：不要使用COMPUTE 和 ESTIMATE clauses of the ANALYZE 语句去收集统计信息，这些条款已经过时，要使用DBMS_STATS去替代。 统计信息（其它扩展） 6 项目KPI 控制自动优化统计信息收集 手动收集优化器统计信息 执行大容量加载和不能等待某些类型的维护窗口收集统计因为查询必须立即执行。 在一个不具有代表性的工作加载，自动收集固定表统计信息。 自动统计信息收集不会收集系统(system)统计信息。 白天经常被delete或者truncated之后又rebuild的表(经常变化的表)。 锁定和解锁统计信息 处理统计信息的缺失 在数据库中有一些表缺少统计信息，因为这些表中有些数据并不是在每日夜间维护窗口中加载的。在白天数据库处理其他工作负载时，不能为表收集统计信息。对此，oracle提供了动态采样来弥补统计信息的缺失。当启用动态采样之后，数据库会随机扫描表中的采样数据块。通过设置optimizer_dynamic_sampling初始化参数再数据库中启用/禁用动态采样。 导入和导出的优化器统计信息 还原以前版本的统计信息 验证新的统计信息 执行计划 7 执行计划(生成) 8 项目KPI DBMS_XPLAN包 Oracle提供的PL/SQL包DBMS_XPLAN中包含了大量的功能，可用来获取给定查询的解释计划信息。在DBMS_XPLAN包中很多函数；DISPLAY函数可以用来快速获得一个查询的执行计划，并可以按照特定需求对输出的格式进行定制。 执行计划-例子1 9 Cardinality 是返回的结果集数据量，我们在对sql进行优化的时候最主要关注的是的设法减少Cardinality。 视图Dba_tables保存的是表相关的统计信息 视图dba_tab_cols保存的是列相关的统计信息 上图查询显示