oracle直方图导致执行不使用索引

[wunan320]

现有一张表，数据量比较大，3千万行，空间估计是40G左右。
在其中一个字段上建立索引，然后写了个查询语句执行（正常情况下这个查询语句是应该使用索引的）
但是做完oracle的直方图分析后，再看执行计划，这条语句不用索引了，直接全表扫描。

上网查资料，有的人解答如下：
如果当前数据库的优化模式是CBO的话，在oracle做了直方图分析后会根据cost去优化当前SQL的执行计划，从而决定用不用索引。

但是做实验结果如下：
用索引，执行计划中的开销低。实际执行语句，查出一条记录的时间约70秒左右；
不用索引，执行计划中的开销很大。如果实际执行语句，查出一条记录的时间约200秒左右。

问题：
做了直方图分析，oracle会根据执行成本优化选择不用索引，但是实际的情况是不管执行计划的成本还是实际执行时间，都是用索引比较好，是不是oracle的直方图分析有问题？如果不是直方图而是使用或配置上的问题，请指教说明。
如果是oracle的直方图分析存在问题，这种情况证明解决？（注：更改SQL不考虑，最好能从数据库方面入手）

[zhu_gx]

顶

[zhu_gx]

帮你顶上去

[dawugui]

建议对此表做个完整行分析.

[kongkongye]

直方图反应的某个列上值的分布情况，如果搜集了直方图而不使用索引，那么几乎非常可能是你的数据分布不均匀，
某些值上，占据了多个桶。
很可能是你的语句在第一次解析时(此时有bind peeking)，碰到了占据列多数的那些个特殊值，此时结合直方图来判断的话，cbo 自然觉得该列上的索引扫描不如全表来的快,因此走了全表扫描。在后面的软解析时，执行计划也会使用第一次的执行计划，走的全表扫描，而不再管绑定变量带入的值是否是那些特殊值。

解决方案：去掉索引列上的直方图。

[kongkongye]

method_opt 参数中的“FOR ALL COLUMNS size 1” 是说对于这个表搜集的时候，所有字段上都不搜集直方图,size 1 ; 如果要搜集，可以 size auto ，是由oracle 自己图判断 直方图桶的多少；或者size number ，自己指定一个桶的数目等等。
对于这个参数的解释，请看下面oracle 文档中的相关部分

--用如下参数重新搜集可以去掉某个表的直方图
exec dbms_stats.gather_table_stats(ownname => 'OWNER',tabname => 'TABLE_NAME',method_opt => 'FOR ALL COLUMNS size 1',cascade => TRUE,no_invalidate => FALSE);



--oracle 9i 文档中的部分节选

method_opt


Accepts:

FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause]

FOR COLUMNS [size clause] column|attribute [size_clause] [,column|attribute [size_clause]...], where size_clause is defined as: size_clause := SIZE {integer | REPEAT | AUTO | SKEWONLY}

integer--Number of histogram buckets. Must be in the range [1,254].

REPEAT--Collects histograms only on the columns that already have histograms.

AUTO--Oracle determines the columns to collect histograms based on data distribution and the workload of the columns.

SKEWONLY--Oracle determines the columns to collect histograms based on the data distribution of the columns.

[wunan320]

To:4 楼
分析很有道理，我也意识到这个问题，但是解决方案能否更详细一点。
现在数据库是用的oracle的DBMS执行分析，建立JOB每天凌晨自动运行，如果我想要单独去掉这个索引上的直方图，有什么办法呢？

[zhu_gx]

再顶一下

[kongkongye]

dbms_stats.gather_schema_stats 搜集时也是可以指定 method_opt 的。
不过设置了之后是对该用户下的所有表都生效。


如果你只想只对个别表不搜集直方图，那么可以在统一的用户模式搜集完了之后，再对个别表单独 table 级别搜集就行了（此时设置合适的method_opt ）。


不过比较奇怪的是，9i 的默认method_opt 是 size 1 啊，如果你没有设定，应该是不搜集直方图的；
如果是10g 又是自动搜集统计信息的，不需要你手动去搜集。
不知道楼主你的oracle 版本是什么？

[wunan320]

数据库收集直方图的方式为整个用户的表直方图收集，表也比较多，差不多上万，不可能一个一个去列出来啊，现在只能按用户去收集，有没有方法从整个用户中去除某些表的收集呢？

[wunan320]

版本是9.2，现在也没什么好解决办法，只能考虑不用dbms_stats.gather_schema_stats 去收集，自己写个脚本，指定一些表。谢谢楼上的回答。

[zhu_gx]

？？