一次 read by other session 的处理过程--数据走索引需要看索引字段的数据分布情况

http://blog.csdn.net/robinson1988/article/details/44244957  文章最后连接了直方图的使用原因：

总结：一次 read by other session 的处理过程--数据走索引需要看索引字段的数据分布情况，若数据分布不均匀，oracle本不应该走索引， 但是如果oracle还是走了该索引，有可能是未收集统计信息， 没收集直方图。导致oracle认为数据分布均匀，仍然走了索引。 

http://czmmiao.iteye.com/blog/1484298  --直方图的连接

在某些情况下，表的列中的数值分布将会影响优化器使用索引还是执行全表扫描的决策。当where 子句的值具有不成比例数量的数值时，将出现这种情况，使得全表扫描比索引访问的成本更低。这种情况下如果where 子句的过滤谓词列之上有一个合理的正确的直方图，将会对优化器做出正确的选择发挥巨大的作用，使得SQL语句执行成本最低从而提升性能。
在分析表或索引时，直方图用于记录数据的分布。通过获得该信息，基于成本的优化器就可以决定使用将返回少量行的索引，而避免使用基于限制条件返回许多行的索引。直方图的使用不受索引的限制，可以在表的任何列上构建直方图。
构造直方图最主要的原因就是帮助优化器在表中数据严重偏斜时做出更好的规划 。例如，如果一到两个值构成了表中的大部分数据(数据偏斜)，相关的索引就可能无法帮助减少满足查询所需的I/O数量。创建直方图可以让基于成本的优化器知道何时使用索引才最合适，或何时应该根据WHERE子句中的值返回表中80％的记录。
通常情况下在以下场合中建议使用直方图：
1、当Where子句引用了列值分布存在明显偏差的列时：当这种偏差相当明显时，以至于WHERE 子句中的值将会使优化器选择不同的执行计划。这时应该使用直方图来帮助优化器来修正执行路径。（注意：如果查询不引用该列，则创建直方图没有意义。这种错误很常见，许多 DBA 会在偏差列上创建柱状图，即使没有任何查询引用该列。）
2、当列值导致不正确的判断时：这种情况通常会发生在多表连接时，例如，假设我们有一个五项的表联接，其结果集只有10行。Oracle 将会以一种使第一个联接的结果集（集合基数）尽可能小的方式将表联接起来。通过在中间结果集中携带更少的负载，查询将会运行得更快。为了使中间结果最小化，优化器尝试在SQL执行的分析阶段评估每个结果集的集合基数。在偏差的列上拥有直方图将会极大地帮助优化器作出正确的决策。如优化器对中间结果集的大小作出不正确的判断，它可能会选择一种未达到最优化的表联接方法。因此向该列添加直方图经常会向优化器提供使用最佳联接方法所需的信息。

一个哥们给我打电话，他说系统中一直出现等待事件 read by other session ，并且该等待都是同一个sql引起的，比较紧急，请我帮忙远程看看。

远程过去之后，用脚本把 等待事件给抓出来

从图中看到 read by other session 是在运行同一个SQL , sql_id 是 1svyhsn0g56qd

于是查看执行计划

该SQL走的是 ILMCU 这个列的索引，过滤条件有4个列，但是只走了一个列的索引。

先别管执行计划，先来看一下等待事件 read by other session 究竟是被哪些进程阻塞，这些进程又在跑什么SQL

最后发现， 还是同一个SQL。 然后仔细问了一下业务。原来该系统是一个 沙发厂的 ERP 系统。

前台的用户点击某个按钮，等了半天没响应，然后就一直点，一直点 就导致这个 SQL 一直重复的运行，

但是呢，这个SQL 跑不出结果，所以就产生大量的 read by other session

所以呢，最终优化这个SQL就可以解决该问题，跑的SQL 如下：

[html] view plain copy print ?

1. SELECT * FROM PRODDTA.F4111 WHERE ((ILDCT = :1 AND ILFRTO = :2 AND ILMCU = :3 AND ILDOC = :4 )) ORDER BY ILUKID ASC

SELECT *  FROM PRODDTA.F4111 WHERE ((ILDCT = :1   AND ILFRTO = :2   AND ILMCU = :3   AND ILDOC = :4  )) ORDER BY ILUKID ASC

走的索引是 ILMCU 这个列的索引，首先看一下这个表一共有多少行

一共有250w行，那这个表其实不大啊，搞多了数据仓库，一个表没有几十亿数据那还真不算大。现在来看一下 ILMCU 这个列数据分布

同志们想一下 为啥我要用 full 这个hint? 因为现在 有 n 个进程 在跑 刚才的 SQL，并且就是 ILMCU 这个列的索引

要是这个时候我不加 full hint , 万一又走了 ILMCU 这个列的 索引，那不是火上浇油吗

并且 这个表一共才 250w条数据，走全表也没啥的，而且我不仅仅要看这个列数据分布，还要看其余3个列，那必须走全表了

最终发现 ILMCU 这个列分布 太他妈不均衡了， 问了一下 那哥们，现在的业务是不是做的 SF10 ，他回答说是的 。

从250w里面去选142w ,走索引， 卧槽，那肯定死啦死啦的 ，肯定产生大量的 db file sequen read 等待 ，

说白了， 表统计信息有问题，没收集直方图，哎，懒得管统计信息了，帮他搞定再说

于是又连续查看剩下的过滤列的数据分布

然后看了一下他的的数据库版本，11gR2 ，跑在 IBM 小鸡鸡上面 ， 因为是 11g 可以 online 创建 索引， 如果是 10g 不敢 online 创建 (10g 是假的online )

create index idx_F4111_docdctilmcufrto on F4111(ILDOC,ILDCT,ILMCU,ILFRTO) online nologging;

索引创建完之后，前台的用户 立马就搞定了， 之前是 搞了一天 ，我晕

问题再 总结一下 ：

这个 ERP 系统没有dba维护，所以呢 表没收集统计信息，表也缺乏索引， 这个表呢是 系统一直都有的 ，并且是一个核心表 ，因为没 dba ，他们不敢乱建立索引

刚开始数据量小，没问题，后来数据量越来越大，问题就来了。我看了一下 他那边所有的业务全都跑得慢，今天这个是跑了一天，没法忍受了才找人帮忙的

我只能说老板太他妈抠门了，招个dba，去干1--2个月，然后找个借口把 dba开除了 不就得了吗哈哈，或者要不来我这里培训一下 哈哈。

=========================

http://czmmiao.iteye.com/blog/1484298  --直方图的连接

直方图概述
直方图是一种统计学上的工具，并非Oracle专有，通常情况下它会表现为一种几何图形表，这个图形表是根据从实际环境中所收集来的被管理对象某个方面的质量分布情况的数据所绘制成的，通常会画成以数量为底边，以频度为高度的一系列连接起来的矩形图，因此直方图在统计学上也称为质量分布图。
在Oracle中直方图是一种对数据分布质量情况进行描述的工具。它会按照某一列不同值出现数量多少，以及出现的频率高低来绘制数据的分布情况，以便能够指导优化器根据数据的分布做出正确的选择。在某些情况下，表的列中的数值分布将会影响优化器使用索引还是执行全表扫描的决策。当where 子句的值具有不成比例数量的数值时，将出现这种情况，使得全表扫描比索引访问的成本更低。这种情况下如果where 子句的过滤谓词列之上有一个合理的正确的直方图，将会对优化器做出正确的选择发挥巨大的作用，使得SQL语句执行成本最低从而提升性能。
在分析表或索引时，直方图用于记录数据的分布。通过获得该信息，基于成本的优化器就可以决定使用将返回少量行的索引，而避免使用基于限制条件返回许多行的索引。直方图的使用不受索引的限制，可以在表的任何列上构建直方图。
构造直方图最主要的原因就是帮助优化器在表中数据严重偏斜时做出更好的规划 。例如，如果一到两个值构成了表中的大部分数据(数据偏斜)，相关的索引就可能无法帮助减少满足查询所需的I/O数量。创建直方图可以让基于成本的优化器知道何时使用索引才最合适，或何时应该根据WHERE子句中的值返回表中80％的记录。
通常情况下在以下场合中建议使用直方图：
1、当Where子句引用了列值分布存在明显偏差的列时：当这种偏差相当明显时，以至于WHERE 子句中的值将会使优化器选择不同的执行计划。这时应该使用直方图来帮助优化器来修正执行路径。（注意：如果查询不引用该列，则创建直方图没有意义。这种错误很常见，许多 DBA 会在偏差列上创建柱状图，即使没有任何查询引用该列。）
2、当列值导致不正确的判断时：这种情况通常会发生在多表连接时，例如，假设我们有一个五项的表联接，其结果集只有10行。Oracle 将会以一种使第一个联接的结果集（集合基数）尽可能小的方式将表联接起来。通过在中间结果集中携带更少的负载，查询将会运行得更快。为了使中间结果最小化，优化器尝试在SQL执行的分析阶段评估每个结果集的集合基数。在偏差的列上拥有直方图将会极大地帮助优化器作出正确的决策。如优化器对中间结果集的大小作出不正确的判断，它可能会选择一种未达到最优化的表联接方法。因此向该列添加直方图经常会向优化器提供使用最佳联接方法所需的信息。
等频直方图与等高直方图
默认的，如果一个倾斜列上的唯一值超过了254个，那么ORACLE会对此列建立等高直方图，否则建立等频直方图。我们先来看下等频直方图。
所谓的等频即按照列上的不同数据值进行划分，由于每个数值的频度相同，高度不同，故称为等频。下面是具体例子：
通过如下方式，建立表TAB，更新字段B，让列B产生倾斜。并在B列上创建索引。
SQL> create table tab(a number,b number);

Table created.
SQL> insert into tab select rownum,rownum from dual connect by level <=10000;

10000 rows created.

SQL> commit;

Commit complete.

SQL> update tab set b=5 where b between 6 and 9995;

9990 rows updated.

SQL> commit;

Commit complete.

SQL> create index tab_b_idx on tab(b);

Index created.
然后分析表，强制使列B不产生直方图。
SQL> exec dbms_stats.gather_table_stats('HR','TAB',cascade=>true,method_opt=>'FOR COLUMNS B SIZE 1');

PL/SQL procedure successfully completed.
查看视图USER_TAB_HISTOGRAMS 或者DBA_TAB_COL_STATISTICS
SQL> select TABLE_NAME,COLUMN_NAME,ENDPOINT_NUMBER,ENDPOINT_VALUE from user_histograms where TABLE_NAME='TAB'

TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE
------------ ------------ --------------- --------------
TAB B 0 1
TAB B 1 10000
列B上只有最大值，最小值两条记录分别对应端点号(endpoint_number)0和1，这种显示说明列B没有直方图信息。
在没有直方图的情况下，在B列上进行等值查询的时候，都是索引范围扫描。
SQL> select * from tab where b=5

Execution Plan
----------------------------------------------------------
Plan hash value: 157166354

-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 6000 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TAB | 1000 | 6000 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TAB_B_IDX | 1000 | | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("B"=5)
收集直方图信息。看看是什么效果。由于列B唯一值的个数没有超过254因此产生的是等频直方图。
SQL> exec dbms_stats.gather_table_stats('HR','TAB',cascade=>true);

PL/SQL procedure successfully completed.
默认是对所有列分析直方图

在B=1时候采用索引扫描，而B=5时候，已经采用全表扫描了，说明直方图起了作用。
SQL> select * from tab where b=1;

Execution Plan
----------------------------------------------------------
Plan hash value: 157166354

-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TAB | 1 | 6 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TAB_B_IDX | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("B"=1)
SQL> select * from tab where b=5;

Execution Plan
----------------------------------------------------------
Plan hash value: 1995730731

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9991 | 59946 | 6 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TAB | 9991 | 59946 | 6 (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("B"=5)

查看此时的直方图信息：

SQL> select TABLE_NAME,COLUMN_NAME,ENDPOINT_NUMBER,ENDPOINT_VALUE from user_histograms where table_name='TAB';

TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE
------------ ------------ --------------- --------------
TAB B 1 1
TAB B 2 2
TAB B 3 3
TAB B 4 4
TAB B 9995 5
TAB B 9996 9996
TAB B 9997 9997
TAB B 9998 9998
TAB B 9999 9999
TAB B 10000 10000
TAB A 0 1

TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE
------------ ------------ --------------- --------------
TAB A 1 10000

12 rows selected.

其中EDNPOINT_NUMBER是累计值。EDNPOINT_VALUE是列的值。可以看出这种等频直方图统计的列的信息是非常精确的。它为每一个列值分配了一个bucket。从执行计划的ROWS部分也可以看出ORACLE计算出来的cardinality是9991，和实际的情况完全吻合。
如果想知道每一个列值对应的数量是多少，需要做一下简单的减法运算：假如想知道列值等于5的个数，那么可以通过：9995-4=9991得到。这就是ENDPOINT_NUMBER累计值的含义。
等高直方图，当列上的数据不同值超过254时，Oracle将会默认将列上的数据划分为高度一致但频度不一致的等高直方图。
SQL> exec dbms_stats.gather_table_stats('HR','TAB',cascade=>true,method_opt=>'FOR COLUMNS B SIZE 8);

PL/SQL procedure successfully completed.
由于列B有10个唯一值，通过上面的size 8可以强制ORACLE使用等高直方图。
查看直方图信息.
SQL> select TABLE_NAME,COLUMN_NAME,ENDPOINT_NUMBER,ENDPOINT_VALUE from user_histograms where table_name='TAB';

TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE
------------ ------------ --------------- --------------
TAB B 0 1
TAB B 7 5
TAB B 8 10000
TAB A 0 1
TAB A 1 10000

从查询结果惊奇的发现只有三个桶0 7 8，原来ORACLE会自动省去EDNPOINT_VALUE值相同且ENDPOINT_NUMBER相邻的桶的值。省去了桶（EDNPOINT_NUMBER）为1 2 3 4 5 6 ，EDNPOINT_VALUE为5的六条内容。
说明：在等高直方图中，EDNPOINT_NUMBER代表桶号,这一点与等频直方图不同。再看等高直方图下的执行计划：
SQL> select * from tab where b=5

Execution Plan
----------------------------------------------------------
Plan hash value: 1995730731

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9982 | 59892 | 6 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TAB | 9982 | 59892 | 6 (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("B"=5)
有执行计划的ROWS部分，ORACLE计算出来的cardinality不是特别精确的。9991才是精确值。而等频直方图可以精确到9991，因此可以说等频直方图比等高直方图稳定，精确。
可是现实很多时候，列的唯一值是超过254的。只能使用等高直方图了。
一个注意点
如果需要删除直方图信息，10g中可以通过上面提到的
exec dbms_stats.gather_table_stats('HR','TAB',cascade=>true,method_opt=>'FOR COLUMNS B SIZE 1');
但这却得再次手机表的统计信息，十分不合理，11g有如下方法可以直接删除直方图信息
dbms_stats.delete_column_stats(ownname => user,
tabname => 'T',
colname => 'VAL',
col_stat_type => 'HISTOGRAM')

参考至:《让Oracle跑得更快》谭怀远著
http://www.itpub.net/thread-1350285-1-1.html
http://chenxy.blog.51cto.com/729966/743065