一）在oracle中使用直方图来表示数据的分布质量。它会按照某一列不同值出现的数量的多少，以及出现频率的高低来绘制数据的分布情况，以便能够指导优化器根据数据的分布做出正
确的选择（是使用索引还是全表扫描）。
二）如果where子句的过滤谓词的列上有一个合理的正确的直方图，它将会对优化器做出正确的选择发挥巨大的作用，使得sql语句执行成本最低从而提升性能。在获得准确的直方图信息
后，基于成本的优化器，就可以决定使用将返回少量行的索引，而避免使用基于限制条件返回许多行的索引。
三）直方图的使用不受索引的限制，可以在表的任何列上构建直方图。构建直方图最主要的原因就是，帮助优化器在表中数据严重倾斜时做出更好的抉择。例如：一到两个值（status=0
和status=1，其中=0有100条数据，=1有1000000条数据，只有这两个值）就构成了表中的大部分数据（数据倾斜），相关查询就可能无法帮助减少满足查询所需的I/O数量（如查询
status=1）。创建直方图可以让基于成本的优化器知道何时使用索引才最合适，或何时根据where子句中的值返回表中的80%的记录。
四）通常在以下场合建议创建和使用直方图：
1）当where子句引用了列值的分布情况存在明显偏差的列时：当这中偏差相当明显时，以至于where子句中的值将会使优化器选择不同的执行计划。这时，应该使用直方图来帮助优化
器来修正执行路径。（注意：如果查询不引用该列，则创建直方图没有意义。这种错误很常见，许多DBA会在偏差列上创建柱状图，即使没有任何查询引用该列）
2）当列值导致不正确的判断时：这种情况通常会发生在多表连接时，例如：假设有个五项的表连接，其结果集只有10行记录。oracle将会以一种第一个连接的结果集（集合基数）尽
可能小的方式将表连接起来。通过在中间结果集中携带更少的负载，查询将会运行的更快。为了使中间结果集最小化，优化器尝试在sql执行的分析阶段评估每个结果集的集合基数。
在偏差的列上拥有直方图将会极大的帮助优化器做出正确的决策。如优化器对中间结果集的大小做出不正确的判断，它可能会选择一种未达到最优化的表连接方法，因此向该列添加直
方图经常会向优化器提供使用最佳连接方法所需的信息。
五）直方图的分类：可分为等频直方图和等高直方图
等频直方图：针对包含很少不同值的数据集，就是数据分布很均匀。
等高直方图：针对包含很多不同值的数据集。 数据分布不均匀 ，由于列中数据很多，这时数据比较密集，不利于分析和评估，这时直方图需要均衡化默认的，如果一个倾斜列上的唯
一值超过了254个，那么oracle会对此列创建等高直方图，否则建立等频直方图。
直方图信息的准确性由两个数值决定，一个是bucket的个数，一个是num_distinct的个数。一般来说，bucket的数量越多，关于列数据分布的信息就越准确，但统计直方图花费的时间
就越多，oracle中bucket的最大为254个，默认是75个。而sql server中默认是200个。通常情况下当BUCTET <表的NUM_DISTINCT值得到的是HEIGHT BALANCED（高度平衡）直方图，而
当BUCTET >表的NUM_DISTINCT值的时候得到的是FREQUENCY（频率）直方图。由于满足BUCTET = 表的NUM_DISTINCT值概率较低，所以在Oracle中生成的直方图大部分是
HEIGHT BALANCED（高度平衡）直方图。在Oracle 10GR2之前如果使用dbms_stats包来创建直方图，那么如果指定需要创建的直方图的桶的数目与表的NUM_DISTINCT值相等，那么几乎
无法创建出一个FREQUENCY（频率）直方图，此时为了得到频率直方图只能使用analyze命令的“for all columns size 表的NUM_DISTINCT值”，这在某种程度上来说是一个退步，但这个
问题在Oracle 10GR2后被修正。但是如果列中有180 - 200个不同值时，还是无法创建FREQUENCY（频率）直方图.此时需要手工建立直方图,并写入数据字典方能完成FREQUENCY（频率）
直方图的创建。对于含有较少的不同值而且数据分布又非常不均匀的数据集来说，创建FREQUENCY（频率）直方图将会更加合适，因为它不存在会将低频出现的记录存入高频桶中的情况
，而HEIGHT BALANCED（高度平衡）直方图在存储桶(bucket)数分配不合理时就可能会出现这种情况。因此一定要在创建直方图前确定使用何种直方图，并且要合理估计存储桶(bucket)
个数。
六）如何创建直方图：
通过使用早先的analyze命令和最新的dbms_stats工具包都可以创建直方图。Oracle推荐使用后者来创建直方图，而且直方图的创建不受任何条件限制，可以在一张表上的任何你想创建
直方图的列上创建直方图。我们这里主要介绍如何通过dbms_stats包来创建直方图。
Oracle 通过指定 dbms_stats 的 method_opt 参数，来创建直方图。在 method_opt 子句中有三个相关选项，即 skewonly、repeat 和 auto。
“skewonly” 选项，它的时间性很强，因为它检查每个索引中每列值的分布。如果 dbms_stats 发现一个索引中具有不均匀分布的列，它将为该索引创建直方图，以帮助基于成本的 SQL
优化器决定是使用索引还是全表扫描访问。示例如下：
begin
dbms_stats. gather_table_stats (
ownname=>'',
tabname=>''，
estimate_percent =>dbms_stats.auto_sample_size,
method_opt=>'for all columns size skewonly',
cascade=>true,
degree=>7)
end
其中degree指定了并行度视主机的CPU个数而定，estimate_percent指定了采样比率，此处使用了auto目的是让oracle来决定采样收集的比率，绘制直方图时会根据采样的数据分析结果
来绘制，当然也可以人为指定采样比率。如：estimate_percent=>20指定采样比率为20%，cascade=>true指定收集相关表的索引的统计信息，该参数默认为false，因此使用dbms_stats
收集统计信息是默认不收集表的索引信息的。
在对表实施监视 (alter table xxx monitoring) 时使用 auto 选项，它基于数据的分布以及应用程序访问列的方式（例如由监视所确定的列上的负载）来创建直方图。示例如下：
begin
dbms_stats.gather_ table _stats(
ownname =>'',
tabname=>''，
estimate_percent =>dbms_stats.auto_sample_size,
method_opt =>'for all columns size auto',
cascade=>true，
degree =>7
)
end
重新分析统计数据时，使用repeat选项，重新分析任务所消耗的资源就会少一些。使用repeat选项时，只会为现有的直方图重新分析索引，不再生成新的直方图。示例如下：
dbms_stats.gather_ table _stats(
ownname =>'',
tabname=>''，
estimate_percent =>dbms_stats.auto_sample_size,
method_opt =>'for all columns size repeat',
cascade=>true，
degree =>7
)
end
七）创建直方图的考虑因素：
如果想为某一列创建直方图，示例如下：
dbms_stats.gather_ table _stats(
ownname =>'',
tabname=>''，
estimate_percent =>dbms_stats.auto_sample_size,
method_opt =>'for columns size 10 列名',
cascade=>true，
degree =>7
)
end
其中size 10指定的是直方图所需的存储桶(bucket)数，所谓存储桶可以理解为存储数据的容器，这个容器会按照数据的分布将数据尽量平均到各个桶里，如一张表如果有6000条记
录，那么每个桶中平均就会有600条记录，但这只是一个平均数，每个桶中的记录数并不会相等，它会将高频出现记录集中在某一些桶中，低频记录会存放在少量桶中，因此如果存
储桶(bucket)数合适的增加就会减少高频记录使用的桶数，统计结果也会更加准确（可以避免被迫将低频记录存入高频桶中，影响优化器生成准确的执行计划）。所以我们最后得
到的直方图信息的准确性就由两个数值决定，一个是BUCTET的个数，一个NUM_DISTINCT的个数。所以创建直方图时首先要正确地估计存储桶(bucket)数。默认情况时，Oracle的直
方图会产生75个存储桶。可以把SIZE的值指定在1~254之间。
八）删除直方图信息：
在oracle中要删除直方图信息就是设置bucket的数据为1，如下：
Analyze table 表 compute statistics for table for columns id size 1；
exec dbms_stats.gather_table_stats('用户', '表',cascade=>false, method_opt=>'for columns 列 size 1')
但这却得再次收集表的统计信息，十分不合理，11g有如下方法可以直接删除直方图信息
dbms_stats.delete_column_stats(
ownname =>'',
tabname =>'',
colname =>'',
col_stat_type =>'HISTOGRAM')
相关测试实验可参考：http://blog.sina.com.cn/s/blog_63972ba20100x3mh.html
九）直方图与绑定变量
主要测试直方图不适合在sql语句使用绑定变量场合下使用：http://blog.163.com/scott_guo/blog/static/18102608320122551225491/
十）查看表是否有直方图信息
User_Tab_Histograms(普通表)、User_Part_Histograms(表分区)中是否有相关表的记录
查询索引的选择率：索引的选择率=distinct_keys/num_rows*100%。如果此值比较小，则说明数据分布不均匀。
select a.owner,
a.index_name,
a.index_type,
partitioned,
b.num_rows,--表示表中有多少条记录
b.distinct_keys,--表示索引的列上有多少个不同的值
b.num_rows / b.distinct_keys avg_row_per_key,
b.distinct_keys / b.num_rows SELECTIVITY,
b.last_analyzed,
b.stale_stats
from dba_indexes a, dba_ind_statistics b
where a.owner = b.owner
and a.index_name = b.index_name
--and a.table_name='Q_PARTITION_RANGE'
and a.index_name = 'INDEX_LR_QOSSTAFFID_RANGE'
查询列上有没有直方图：
select owner,table_name,column_name,num_distinct,histogram,num_buckets from dba_tab_col_statistics
where table_name='Q_PARTITION_RANGE' and column_name='LR_STAFFID'
num_buckets为1表示此列没有直方图
有时，使用dbms_stats收集统计信息后反而不会走索引或者说走我们期望走的索引，此时用analyze收集确能走索引；但是使用analyze收集统计信息后，索引对应的列上可能没有直
方图信息，此时可改用dbms_stats来收集一下，然后查询该列是否有直方图信息，如果有了再次执行语句，看是否走索引，不走索引可以通过dbms_stats删除改列的直方图信息：
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'',TABNAME=>'',ESTIMATE_PERCENT=>100,DEGREE=>16,method_opt=>'for columns size 1 LR_STAFFID',CASCADE=>TRUE)
然后验证该列是否有直方图信息，并执行sql查看是否走索引。
做个比喻，你表中有字段A,该字段用来存储用户所属的民族以中国的国情，你会发现字段A中存储的值大多数都是汉族。。。其他民族的值就很少，这时候就产生了倾斜。
如果没有对该字段做直方图，那么oracle就不知道A字段里面值的具体情况，它会认为里面不同的值是均匀分布的，从而对生成执行计划cost的评估出现问题，容易导致错误的执行计划。
而直方图就是用来收集字段值分布信息的，对字段做了直方图后，oracle就知道字段里面值的倾斜情况了。
举个查询例子：
如果你没有对字段A做直方图
当你select * from table where a='维吾尔族'时候，oracle可能给你生成一个全表扫描的执行计划，而A字段上是有索引，尽管你知道维吾尔族的数据量很少，走索引效率会很高。。。但oracle不知道，oracle会认为每个民族的值都差不多，便可能生成错误执行计划。。
有客户遇到SQL性能不稳定 突然变差导致系统性能出现严重问题的情况 对于大型的系统来说 SQL性能不稳定 有时突然变差 这是常常遇到的问题 这也是一些DBA的挑战
对于使用Oracle数据库的应用系统 有时会出现运行得好好的SQL 性能突然变差 特别是对于OLTP类型系统执行频繁的核心SQL 如果出现性能问题 通常会影响整个数据库的性能 进而影响整个系统的正常运行 对于个别的SQL 比如较少使用的查询报表之类的SQL 如果出现问题 通常只影响少部分功能模块 而不会影响整个系统
那么应该怎么样保持SQL性能的稳定性？
SQL的性能变差 通常是在SQL语句重新进行了解析 解析时使用了错误的执行计划出现的 下列情况是SQL会重新解析的原因
SQL语句没有使用绑定变量 这样SQL每次执行都要解析
SQL长时间没有执行 被刷出SHARED POOL 再次执行时需要重新解析
在SQL引用的对象（表 视图等）上执行了DDL *** 作 甚至是结构发生了变化 比如建了一个索引
对SQL引用的对象进行了权限更改
重新分析（收集统计信息）了SQL引用的表和索引 或者表和索引统计信息被删除
修改了与性能相关的部分参数
刷新了共享池
当然重启数据库也会使所有SQL全部重新解析
SQL重新解析后 跟以前相比 性能突然变差 通常是下列原因
表和索引的优化统计信息被删除 或者重新收集后统计信息不准确 重新收集统计信息通常是由于收集策略（方法）不正确引起 比如对分区表使用 *** yze命令而不是用dbms_stats包 收集统计信息时采样比例过小等等 Oracle优化器严重依赖于统计信息 如果统计信息有问题 则很容易导致SQL不能使用正确的执行计划
SQL绑定变量窥探(bind peeking) 同时绑定变量对应的列上有直方图 或者绑定变量的值变化范围过大 分区数据分布极不均匀
） 绑定变量的列上有直方图
假如表orders存储所有的订单 state列有 种不同的值 表示未处理 表示处理成功完成 表示处理失败 State列上有一个索引 表中绝大部分数据的state列为 和 占少数 有下面的SQL
select * from orders where state=:b
这里:b 是变量 在大多数情况下这个值为 则应该使用索引 但是如果SQL被重新解析 而第一次执行时应用传给变量b 值为 则不会使用索引 采用全表扫描的方式来访问表 对于绑定变量的SQL 只在第一次执行时才会进行绑定变量窥探 并以此确定执行计划 该SQL后续执行时全部按这个执行计划 这样在后续执行时 b 变量传入的值为 的时候 仍然是第一次执行时产生的执行计划 即使用的是全表扫描 这样会导致性能很差
） 绑定变量的值变化范围过大
同样假如orders表有一列created_date表示一笔订单的下单时间 orders表里面存储了最近 年的数据 有如下的SQL
Select * from orders where created_date >=:b
假如大多数情况下 应用传入的b 变量值为最近几天内的日期值 那么SQL使用的是created_date列上的索引 而如果b 变量值为 个月之前的一个值 那么就会使用全表扫描 与上面描述的直方图引起的问题一样 如果SQL第 次执行时传入的变量值引起的是全表扫描 那么将该SQL后续执行时都使用了全表扫描 从而影响了性能
） 分区数据量不均匀
对于范围和列表分区 可能存在各个分区之间数据量极不均匀的情况下 比如分区表orders按地区area进行了分区 P 分区只有几千行 而P 分区有 万行数据 同时假如有一列product_id 其上有一个本地分区索引 有如下的SQL
select * from orders where area=:b and product_id =:b
这条SQL由于有area条件 因此会使用分区排除 如果第 次执行时应用传给b 变量的值正好落在P 分区上 很可能导致SQL采用全表扫描访问 如前面所描述的 导致SQL后续执行时全部使用了全表扫描
其他原因 比如表做了类似于MOVE *** 作之后 索引不可用 对索引进行了更改 当然这种情况是属于维护不当引起的问题 不在本文讨论的范围
综上所述 SQL语句性能突然变差 主要是因为绑定变量和统计信息的原因 注意这里只讨论了突然变差的情况 而对于由于数据量和业务量的增加性能逐步变差的情况不讨论
为保持SQL性能或者说是执行计划的稳定性 需要从以下几个方面着手
规划好优化统计信息的收集策略 对于Oracle g来说 默认的策略能够满足大部分需求 但是默认的收集策略会过多地收集列上的直方图 由于绑定变量与直方图固有的矛盾 为保持性能稳定 对使用绑定变量的列 不收集列上的直方图 对的确需要收集直方图的列 在SQL中该列上的条件就不要用绑定变量 统计信息收集策略 可以考虑对大部分表 使用系统默认的收集策略 而对于有问题的 可以用DBMS_STATS LOCK_STATS锁定表的统计信息 避免系统自动收集该表的统计信息 然后编写脚本来定制地收集表的统计信息 脚本中类似如下
exec dbms_stats unlock_table_stats…
exec dbms_stats gather_table_stats…
exec dbms_stats lock_table_stats…
修改SQL语句 使用HINT 使SQL语句按HINT指定的执行计划进行执行 这需要修改应用 同时需要逐条SQL语句进行 加上测试和发布 时间较长 成本较高 风险也较大
修改隐含参数 _optim_peek_user_binds 为FALSE 修改这个参数可能会引起性能问题（这里讨论的是稳定性问题）
使用OUTLINE 对于曾经出现过执行计划突然变差的SQL语句 可以使用OUTLINE来加固其执行计划 在 g中DBMS_OUTLN CREATE_OUTLINE可以根据已有的执行正常的SQL游标来创建OUTLINE 如果事先对所有频繁执行的核心SQL使用OUTLINE加固执行计划 将最大可能地避免SQL语句性能突然变差
注 DBMS_OUTLN可以通过$ORACLE_HOME/rdbms/admin/dbmsol sql脚本来安装
使用SQL Profile SQL Profile是Oracle g之后的新功能 此处不再介绍 请参考相应的文档
除此之外 可以调整一些参数避免潜在的问题 比如将 _btree_bitmap_plans 参数设置为FALSE（这个参数请参考互联网上的文章或Oracle文档）
lishixinzhi/Article/program/Oracle/201311/18054
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）