现有一张表,数据量比较大,3千万行,空间估计是40G左右。
在其中一个字段上建立索引,然后写了个查询语句执行(正常情况下这个查询语句是应该使用索引的)
但是做完oracle的直方图分析后,再看执行计划,这条语句不用索引了,直接全表扫描。上网查资料,有的人解答如下:
如果当前数据库的优化模式是CBO的话,在oracle做了直方图分析后会根据cost去优化当前SQL的执行计划,从而决定用不用索引。但是做实验结果如下:
用索引,执行计划中的开销低。实际执行语句,查出一条记录的时间约70秒左右;
不用索引,执行计划中的开销很大。如果实际执行语句,查出一条记录的时间约200秒左右。问题:
做了直方图分析,oracle会根据执行成本优化选择不用索引,但是实际的情况是不管执行计划的成本还是实际执行时间,都是用索引比较好,是不是oracle的直方图分析有问题?如果不是直方图而是使用或配置上的问题,请指教说明。
如果是oracle的直方图分析存在问题,这种情况证明解决?(注:更改SQL不考虑,最好能从数据库方面入手)
在其中一个字段上建立索引,然后写了个查询语句执行(正常情况下这个查询语句是应该使用索引的)
但是做完oracle的直方图分析后,再看执行计划,这条语句不用索引了,直接全表扫描。上网查资料,有的人解答如下:
如果当前数据库的优化模式是CBO的话,在oracle做了直方图分析后会根据cost去优化当前SQL的执行计划,从而决定用不用索引。但是做实验结果如下:
用索引,执行计划中的开销低。实际执行语句,查出一条记录的时间约70秒左右;
不用索引,执行计划中的开销很大。如果实际执行语句,查出一条记录的时间约200秒左右。问题:
做了直方图分析,oracle会根据执行成本优化选择不用索引,但是实际的情况是不管执行计划的成本还是实际执行时间,都是用索引比较好,是不是oracle的直方图分析有问题?如果不是直方图而是使用或配置上的问题,请指教说明。
如果是oracle的直方图分析存在问题,这种情况证明解决?(注:更改SQL不考虑,最好能从数据库方面入手)
某些值上,占据了多个桶。
很可能是你的语句在第一次解析时(此时有bind peeking),碰到了占据列多数的那些个特殊值,此时结合直方图来判断的话,cbo 自然觉得该列上的索引扫描不如全表来的快,因此走了全表扫描。在后面的软解析时,执行计划也会使用第一次的执行计划,走的全表扫描,而不再管绑定变量带入的值是否是那些特殊值。解决方案:去掉索引列上的直方图。
分析很有道理,我也意识到这个问题,但是解决方案能否更详细一点。
现在数据库是用的oracle的DBMS执行分析,建立JOB每天凌晨自动运行,如果我想要单独去掉这个索引上的直方图,有什么办法呢?
对于这个参数的解释,请看下面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.
不过设置了之后是对该用户下的所有表都生效。
如果你只想只对个别表不搜集直方图,那么可以在统一的用户模式搜集完了之后,再对个别表单独 table 级别搜集就行了(此时设置合适的method_opt )。
不过比较奇怪的是,9i 的默认method_opt 是 size 1 啊,如果你没有设定,应该是不搜集直方图的;
如果是10g 又是自动搜集统计信息的,不需要你手动去搜集。
不知道楼主你的oracle 版本是什么?