oracle的执行计划居然出错！

li2

让oracle 自行进行类型变换不是个好习惯.
没看清你贴子,不好意思

yangtingkun

重建之后是没有问题的。

现在估计是快速刷新导致的这个问题。

husthxd

最初由 yangtingkun 发布
[B]重建之后是没有问题的。

现在估计是快速刷新导致的这个问题。 [/B]

bug？
oracle support怎么说？

yangtingkun

还没有找oracle的support。

发现无论是否重建，基于rule的查询结构都是正确的。
如果使用analyze分析，for all indexed columns或for all columns可以使查询得到正确的结果。
如果使用dbms_stats.gather_table_stats，无论method_opt指定for all indexed columns或for all columns都无法使查询得到正确的结果。

oracle-plus

找找oracle的support然後把結果跟大家分享一下

yangtingkun

终于找到错误原因了。

居然是因为初始化参数query_rewrite_enabled的值是TRUE

一直没有注意这个参数，是因为的建立物化视图复制的时候并没有指定ENABLE QUERY REWRITE子句，在PLT_PLAT表上也没有建立其它物化视图，而且初始化参数query_rewrite_integrity的值是enforced，这是最严格的设置，没有想到在这种情况下oracle仍然使用了query rewrite，而且查询重写后得到的结果也不正确。
这样看来oracle的查询重写、统计信息的收集以及CBO执行策略还是有些问题的。

yangtingkun

下面简要描述一下不同情况下会出现什么问题。
1.将查询重写关闭，无论是否有统计信息，也无论统计信息生成的方式，查询都不会出错。
SQL> alter session set query_rewrite_enabled = false;
Session altered.
SQL> set autot on exp
SQL> select pp.id, pp.plat_name, pp.plat_class, city.id, city.plat_name, city.plat_class
  2  ,province.id,province.plat_name,province.plat_class
  3  from plt_plat pp,
  4  plt_plat city,
  5  plt_plat province
  6  where pp.plat_father=city.id
  7  and city.plat_father=province.id
  8  and pp.plat_class=4
  9  ;
149 rows selected.
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=11 Card=84 Bytes=14028)
   1    0   HASH JOIN (Cost=11 Card=84 Bytes=14028)
   2    1     HASH JOIN (Cost=7 Card=84 Bytes=10752)
   3    2       TABLE ACCESS (FULL) OF 'PLT_PLAT' (Cost=3 Card=84 Bytes=5376)
   4    2       TABLE ACCESS (FULL) OF 'PLT_PLAT' (Cost=3 Card=334 Bytes=21376)
   5    1     TABLE ACCESS (FULL) OF 'PLT_PLAT' (Cost=3 Card=334 Bytes=13026)
SQL> analyze table plt_plat delete statistics;
Table analyzed.
SQL> select pp.id, pp.plat_name, pp.plat_class, city.id, city.plat_name, city.plat_class
  2  ,province.id,province.plat_name,province.plat_class
  3  from plt_plat pp,
  4  plt_plat city,
  5  plt_plat province
  6  where pp.plat_father=city.id
  7  and city.plat_father=province.id
  8  and pp.plat_class=4
  9  ;
149 rows selected.
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'PLT_PLAT'
   2    1     NESTED LOOPS
   3    2       NESTED LOOPS
   4    3         TABLE ACCESS (FULL) OF 'PLT_PLAT'
   5    3         TABLE ACCESS (BY INDEX ROWID) OF 'PLT_PLAT'
   6    5           INDEX (RANGE SCAN) OF 'IND_PLT_PLAT_PLAT_FATHER' (NON-UNIQUE)
   7    2       INDEX (RANGE SCAN) OF 'IND_PLT_PLAT_PLAT_FATHER' (NON-UNIQUE)

yangtingkun

2.打开查询重写，基于rule的查询结果正确，如果没有统计信息，all_rows和first_rows结果都是错误的。


SQL> alter session set query_rewrite_enabled = true;


Session altered.


SQL> analyze table plt_plat delete statistics;


Table analyzed.


SQL> alter session set optimizer_mode = rule;


Session altered.


SQL> select pp.id, pp.plat_name, pp.plat_class, city.id, city.plat_name, city.plat_class
  2  ,province.id,province.plat_name,province.plat_class
  3  from plt_plat pp,
  4  plt_plat city,
  5  plt_plat province
  6  where pp.plat_father=city.id
  7  and city.plat_father=province.id
  8  and pp.plat_class=4
  9  ;


149 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=RULE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'PLT_PLAT'
   2    1     NESTED LOOPS
   3    2       NESTED LOOPS
   4    3         TABLE ACCESS (FULL) OF 'PLT_PLAT'
   5    3         TABLE ACCESS (BY INDEX ROWID) OF 'PLT_PLAT'
   6    5           INDEX (RANGE SCAN) OF 'IND_PLT_PLAT_PLAT_FATHER' (NON-UNIQUE)
   7    2       INDEX (RANGE SCAN) OF 'IND_PLT_PLAT_PLAT_FATHER' (NON-UNIQUE)



SQL> alter session set optimizer_mode = first_rows;


Session altered.


SQL> select pp.id, pp.plat_name, pp.plat_class, city.id, city.plat_name, city.plat_class
  2  ,province.id,province.plat_name,province.plat_class
  3  from plt_plat pp,
  4  plt_plat city,
  5  plt_plat province
  6  where pp.plat_father=city.id
  7  and city.plat_father=province.id
  8  and pp.plat_class=4
  9  ;


no rows selected


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=29 Card=13 Bytes=2132)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'PLT_PLAT' (Cost=2 Card=1 Bytes=72)
   2    1     NESTED LOOPS (Cost=29 Card=13 Bytes=2132)
   3    2       TABLE ACCESS (FULL) OF 'PLT_PLAT' (Cost=3 Card=13 Bytes=1196)
   4    2       INDEX (RANGE SCAN) OF 'IND_PLT_PLAT_PLAT_FATHER' (NON-UNIQUE) (Cost=1 Card=1)



SQL> alter session set optimizer_mode = all_rows;


Session altered.


SQL> select pp.id, pp.plat_name, pp.plat_class, city.id, city.plat_name, city.plat_class
  2  ,province.id,province.plat_name,province.plat_class
  3  from plt_plat pp,
  4  plt_plat city,
  5  plt_plat province
  6  where pp.plat_father=city.id
  7  and city.plat_father=province.id
  8  and pp.plat_class=4
  9  ;


no rows selected


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=7 Card=13 Bytes=2132)
   1    0   HASH JOIN (Cost=7 Card=13 Bytes=2132)
   2    1     TABLE ACCESS (FULL) OF 'PLT_PLAT' (Cost=3 Card=13 Bytes=1196)
   3    1     TABLE ACCESS (FULL) OF 'PLT_PLAT' (Cost=3 Card=1307 Bytes=94104)

yangtingkun

3.打开查询重写，只有使用带FOR ALL (INDEXED) COLUMNS子句的ANALYZE TABLE去收集统计信息，CBO的结果才是正确的，使用其他方式收集统计信息，CBO的查询结果都是错误的。


SQL> analyze table plt_plat delete statistics;


Table analyzed.


SQL> alter session set optimizer_mode = choose;


Session altered.


SQL> analyze table plt_plat compute statistics;


Table analyzed.


SQL> select pp.id, pp.plat_name, pp.plat_class, city.id, city.plat_name, city.plat_class
  2  ,province.id,province.plat_name,province.plat_class
  3  from plt_plat pp,
  4  plt_plat city,
  5  plt_plat province
  6  where pp.plat_father=city.id
  7  and city.plat_father=province.id
  8  and pp.plat_class=4
  9  ;


no rows selected


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=7 Card=155 Bytes=20460)
   1    0   HASH JOIN (Cost=7 Card=155 Bytes=20460)
   2    1     TABLE ACCESS (FULL) OF 'PLT_PLAT' (Cost=3 Card=84 Bytes=6048)
   3    1     TABLE ACCESS (FULL) OF 'PLT_PLAT' (Cost=3 Card=334 Bytes=20040)



SQL> analyze table plt_plat delete statistics;


Table analyzed.


SQL> exec dbms_stats.gather_table_stats(user, 'PLT_PLAT', method_opt => 'FOR ALL COLUMNS');


PL/SQL procedure successfully completed.


SQL> select pp.id, pp.plat_name, pp.plat_class, city.id, city.plat_name, city.plat_class
  2  ,province.id,province.plat_name,province.plat_class
  3  from plt_plat pp,
  4  plt_plat city,
  5  plt_plat province
  6  where pp.plat_father=city.id
  7  and city.plat_father=province.id
  8  and pp.plat_class=4
  9  ;


no rows selected


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=7 Card=84 Bytes=11928)
   1    0   HASH JOIN (Cost=7 Card=84 Bytes=11928)
   2    1     TABLE ACCESS (FULL) OF 'PLT_PLAT' (Cost=3 Card=151 Bytes=11778)
   3    1     TABLE ACCESS (FULL) OF 'PLT_PLAT' (Cost=3 Card=334 Bytes=21376)





SQL> exec dbms_stats.delete_table_stats(user, 'PLT_PLAT');


PL/SQL procedure successfully completed.


SQL> analyze table plt_plat compute statistics for all columns;


Table analyzed.


SQL> select pp.id, pp.plat_name, pp.plat_class, city.id, city.plat_name, city.plat_class
  2  ,province.id,province.plat_name,province.plat_class
  3  from plt_plat pp,
  4  plt_plat city,
  5  plt_plat province
  6  where pp.plat_father=city.id
  7  and city.plat_father=province.id
  8  and pp.plat_class=4
  9  ;


149 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'PLT_PLAT'
   2    1     NESTED LOOPS
   3    2       NESTED LOOPS
   4    3         TABLE ACCESS (FULL) OF 'PLT_PLAT'
   5    3         TABLE ACCESS (BY INDEX ROWID) OF 'PLT_PLAT'
   6    5           INDEX (RANGE SCAN) OF 'IND_PLT_PLAT_PLAT_FATHER' (NON-UNIQUE)
   7    2       INDEX (RANGE SCAN) OF 'IND_PLT_PLAT_PLAT_FATHER' (NON-UNIQUE)

yangtingkun

这里有完整的记录
http://blog.itpub.net/post/468/7086