920的CBO还是不够'聪明'

husthxd

不能说rule聪明，是碰巧还是故意放后面的
hehe,这个我可以确认的是碰巧的
因为原来的数据库版本是901,优化器模式为cost.
升级到920后反而导致程序运行变慢,由于不能修改程序,只得走回头路,使用rule模式.

btw:尝试说服客户修改程序.毕竟在920还使用rule感觉总觉得不爽.

披发头陀

也遇到过类似情形，感觉cbo是完全生成执行计划然后执行，也就是说，先看看两表大小，然后就用小表驱动，可是实际情况是大表上有限定条件，经过过滤后反而比小表数据要少好多

ahlu

cbo的问题是oracle需要足够的统计信息，非常的不稳定，毕竟oracle不是人他不知道具体的业务，rule的优点是稳定不管你的数据量怎么变，只要你熟悉业务执行正确的执行计划就可以了，我觉得rule更能控制

husthxd

最初由 ahlu 发布
[B]cbo的问题是oracle需要足够的统计信息，非常的不稳定，毕竟oracle不是人他不知道具体的业务，rule的优点是稳定不管你的数据量怎么变，只要你熟悉业务执行正确的执行计划就可以了，我觉得rule更能控制 [/B]

毕竟cbo诞生到现在已经很长一段时间了,说其非常的不稳定是不确切的,只能说在某些情况下cbo会选择了错误的执行计划.
如果熟悉业务并且sql可以修改的话我觉得应该用cost优化器模式,并加hint指导cbo.毕竟基于cost是主流,rule(规则)总是有例外的.

tolywang

最初由 husthxd 发布
[B]

毕竟cbo诞生到现在已经很长一段时间了,说其非常的不稳定是不确切的,只能说在某些情况下cbo会选择了错误的执行计划.
如果熟悉业务并且sql可以修改的话我觉得应该用cost优化器模式,并加hint指导cbo.毕竟基于cost是主流,rule(规则)总是有例外的. [/B]

           是啊，大数据量的表 10% ， 甚至1%  的抽样分析应该具有一定的风险，对于CBO 优化模式来说 。 没有把握的SQL 加上Hint比较保险 。  而且单单根据cost 来判断远远不够， cost 小但是SQL 运行非常慢也经常发生 。  

    我们基本定义 100 w 笔记录都是小表，全分析 。 以上的都是 20% 的抽样， 不过index是全分析 。  运行还不错，只有 2， 3  个语句没有正确选择执行计划 ，导致问题，加入Hint就ok 了 。

husthxd

最初由 tolywang 发布
[B]


           是啊，大数据量的表 10% ， 甚至1%  的抽样分析应该具有一定的风险，对于CBO 优化模式来说 。 没有把握的SQL 加上Hint比较保险 。  而且单单根据cost 来判断远远不够， cost 小但是SQL 运行非常慢也经常发生 。  

    我们基本定义 100 w 笔记录都是小表，全分析 。 以上的都是 20% 的抽样， 不过index是全分析 。  运行还不错，只有 2， 3  个语句没有正确选择执行计划 ，导致问题，加入Hint就ok 了 。 [/B]

column histograms有没有做统计?

husthxd

SQL>
SQL> variable b1 char(10);
SQL> variable b2 char(6);
SQL> execute :b1 := '39101';

PL/SQL 过程已成功完成。

SQL> execute :b2 := '200503';

PL/SQL 过程已成功完成。

SQL>
SQL>
SQL> set autotrace off
SQL>
SQL> show parameter multi_

NAME                                 TYPE        VALUE                          
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count        integer     16                             
mts_multiple_listeners               boolean     FALSE                          
parallel_adaptive_multi_user         boolean     FALSE                          
SQL>
SQL> show parameter adj

NAME                                 TYPE        VALUE                          
------------------------------------ ----------- ------------------------------
optimizer_index_cost_adj             integer     50                             
SQL>
SQL> select count(*) from yl_ltxjbxx
  2  /

  COUNT(*)                                                                     
----------                                                                     
    311910                                                                     

SQL> select count(*) from yl_dyxmbd
  2  /

  COUNT(*)                                                                     
----------                                                                     
    311407                                                                     

SQL> select count(*) from VW_RYJBXX
  2  /

  COUNT(*)                                                                     
----------                                                                     
   1622782                                                                     

SQL>
SQL> alter session set optimizer_mode = 'RULE';

会话已更改。

SQL>
SQL> set autotrace traceonly
SQL>
SQL> SELECT SUM(NVL(A.JCYLJ,0)), SUM(NVL(A.GDXYLJ,0)), SUM(NVL(A.GRZHYLJ,0)),
  2               SUM(NVL(A.HLF,0)), SUM(NVL(A.DFBCLXDY,0)), SUM(NVL(A.HYGDXBT,0)),
  3               SUM(NVL(A.DYZE,0))
  4          FROM YL_DYXMBD A, YL_LTXJBXX B, VW_RYJBXX C
  5         WHERE A.GRBH = B.GRBH AND
  6               A.GRBH = C.GRBH AND
  7               C.DWBH = :b1 AND
  8               B.JZSJ = :b2 AND
  9               B.YWZJBZ = '2';


Execution Plan
----------------------------------------------------------                     
   0      SELECT STATEMENT Optimizer=RULE                                       
   1    0   SORT (AGGREGATE)                                                   
   2    1     NESTED LOOPS (OUTER)                                             
   3    2       NESTED LOOPS                                                   
   4    3         NESTED LOOPS                                                  
   5    4           TABLE ACCESS (BY INDEX ROWID) OF 'TB_GRJBXX'               
   6    5             INDEX (RANGE SCAN) OF 'IDX_TB_GRJBXX_DWBH' (NON-         
          UNIQUE)                                                               
                                                                                
   7    4           TABLE ACCESS (BY INDEX ROWID) OF 'YL_DYXMBD'               
   8    7             INDEX (UNIQUE SCAN) OF 'SYS_C007434' (UNIQUE)            
   9    3         TABLE ACCESS (BY INDEX ROWID) OF 'YL_LTXJBXX'                 
  10    9           INDEX (UNIQUE SCAN) OF 'SYS_C007420' (UNIQUE)               
  11    2       INDEX (RANGE SCAN) OF 'IDX_TB_DWJBXX_DWBH_DWZT' (NON-U         
          NIQUE)                                                               
                                                                                




Statistics
----------------------------------------------------------                     
          0  recursive calls                                                   
          0  db block gets                                                      
      17762  consistent gets                                                   
          0  physical reads                                                     
          0  redo size                                                         
        487  bytes sent via SQL*Net to client                                   
        275  bytes received via SQL*Net from client                             
          2  SQL*Net roundtrips to/from client                                 
          0  sorts (memory)                                                     
          0  sorts (disk)                                                      
          1  rows processed                                                     

SQL>
SQL> -- 调换位置
执行计划一样
SQL> SELECT SUM(NVL(A.JCYLJ,0)), SUM(NVL(A.GDXYLJ,0)), SUM(NVL(A.GRZHYLJ,0)),
  2               SUM(NVL(A.HLF,0)), SUM(NVL(A.DFBCLXDY,0)), SUM(NVL(A.HYGDXBT,0)),
  3               SUM(NVL(A.DYZE,0))
  4          FROM YL_DYXMBD A, VW_RYJBXX C,YL_LTXJBXX B
  5         WHERE A.GRBH = B.GRBH AND
  6               A.GRBH = C.GRBH AND
  7               C.DWBH = :b1 AND
  8               B.JZSJ = :b2 AND
  9               B.YWZJBZ = '2';


Execution Plan
----------------------------------------------------------                     
   0      SELECT STATEMENT Optimizer=RULE                                       
   1    0   SORT (AGGREGATE)                                                   
   2    1     NESTED LOOPS (OUTER)                                             
   3    2       NESTED LOOPS                                                   
   4    3         NESTED LOOPS                                                  
   5    4           TABLE ACCESS (BY INDEX ROWID) OF 'TB_GRJBXX'               
   6    5             INDEX (RANGE SCAN) OF 'IDX_TB_GRJBXX_DWBH' (NON-         
          UNIQUE)                                                               
                                                                                
   7    4           TABLE ACCESS (BY INDEX ROWID) OF 'YL_DYXMBD'               
   8    7             INDEX (UNIQUE SCAN) OF 'SYS_C007434' (UNIQUE)            
   9    3         TABLE ACCESS (BY INDEX ROWID) OF 'YL_LTXJBXX'                 
  10    9           INDEX (UNIQUE SCAN) OF 'SYS_C007420' (UNIQUE)               
  11    2       INDEX (RANGE SCAN) OF 'IDX_TB_DWJBXX_DWBH_DWZT' (NON-U         
          NIQUE)                                                               
                                                                                




Statistics
----------------------------------------------------------                     
          0  recursive calls                                                   
          0  db block gets                                                      
      17762  consistent gets                                                   
          0  physical reads                                                     
          0  redo size                                                         
        487  bytes sent via SQL*Net to client                                   
        275  bytes received via SQL*Net from client                             
          2  SQL*Net roundtrips to/from client                                 
          0  sorts (memory)                                                     
          0  sorts (disk)                                                      
          1  rows processed                                                     

SQL>
SQL> alter session set optimizer_mode = 'CHOOSE';

会话已更改。

SQL>
SQL> SELECT SUM(NVL(A.JCYLJ,0)), SUM(NVL(A.GDXYLJ,0)), SUM(NVL(A.GRZHYLJ,0)),
  2               SUM(NVL(A.HLF,0)), SUM(NVL(A.DFBCLXDY,0)), SUM(NVL(A.HYGDXBT,0)),
  3               SUM(NVL(A.DYZE,0))
  4          FROM YL_DYXMBD A, YL_LTXJBXX B, VW_RYJBXX C
  5         WHERE A.GRBH = B.GRBH AND
  6               A.GRBH = C.GRBH AND
  7               C.DWBH = :b1 AND
  8               B.JZSJ = :b2 AND
  9               B.YWZJBZ = '2';


Execution Plan
----------------------------------------------------------                     
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=459 Card=1 Bytes=91)         
   1    0   SORT (AGGREGATE)                                                   
   2    1     NESTED LOOPS (OUTER) (Cost=459 Card=3 Bytes=273)                  
   3    2       HASH JOIN (Cost=458 Card=3 Bytes=240)                           
   4    3         NESTED LOOPS (Cost=450 Card=244 Bytes=14152)                  
   5    4           TABLE ACCESS (FULL) OF 'YL_LTXJBXX' (Cost=328 Card         
          =244 Bytes=4880)                                                      
                                                                                
   6    4           TABLE ACCESS (BY INDEX ROWID) OF 'YL_DYXMBD' (Cost         
          =1 Card=1 Bytes=38)                                                   
                                                                                
   7    6             INDEX (UNIQUE SCAN) OF 'SYS_C007434' (UNIQUE)            
   8    3         INDEX (RANGE SCAN) OF 'IDX_TB_GRJBXX_GRBH_RYZT' (UNI         
          QUE) (Cost=13 Card=4105 Bytes=90310)                                 
                                                                                
   9    2       INDEX (RANGE SCAN) OF 'IDX_TB_DWJBXX_DWBH_DWZT' (NON-U         
          NIQUE) (Cost=1 Card=1 Bytes=11)                                       
                                                                                




Statistics
----------------------------------------------------------                     
          0  recursive calls                                                   
          0  db block gets                                                      
       5231  consistent gets                                                   
          0  physical reads                                                     
          0  redo size                                                         
        487  bytes sent via SQL*Net to client                                   
        275  bytes received via SQL*Net from client                             
          2  SQL*Net roundtrips to/from client                                 
          0  sorts (memory)                                                     
          0  sorts (disk)                                                      
          1  rows processed                                                     

SQL>
SQL> -- 加hint优化
SQL> SELECT /*+ordered(C,A)*/ SUM(NVL(A.JCYLJ,0)), SUM(NVL(A.GDXYLJ,0)), SUM(NVL(A.GRZHYLJ,0)),
  2               SUM(NVL(A.HLF,0)), SUM(NVL(A.DFBCLXDY,0)), SUM(NVL(A.HYGDXBT,0)),
  3               SUM(NVL(A.DYZE,0))
  4          FROM VW_RYJBXX C,YL_DYXMBD A, YL_LTXJBXX B
  5         WHERE A.GRBH = B.GRBH AND
  6               A.GRBH = C.GRBH AND
  7               C.DWBH = :b1 AND
  8               B.JZSJ = :b2 AND
  9               B.YWZJBZ = '2';


Execution Plan
----------------------------------------------------------                     
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=533 Card=1 Bytes=91)         
   1    0   SORT (AGGREGATE)                                                   
   2    1     HASH JOIN (Cost=533 Card=3 Bytes=273)                             
   3    2       TABLE ACCESS (FULL) OF 'YL_LTXJBXX' (Cost=328 Card=244         
           Bytes=4880)                                                         
                                                                                
   4    2       HASH JOIN (Cost=204 Card=4105 Bytes=291455)                     
   5    4         HASH JOIN (OUTER) (Cost=10 Card=4105 Bytes=135465)            
   6    5           INDEX (RANGE SCAN) OF 'IDX_TB_GRJBXX_GRBH_RYZT' (U         
          NIQUE) (Cost=13 Card=4105 Bytes=90310)                                
                                                                                
   7    5           INDEX (RANGE SCAN) OF 'IDX_TB_DWJBXX_DWBH_DWZT' (N         
          ON-UNIQUE) (Cost=2 Card=1 Bytes=11)                                   
                                                                                
   8    4         TABLE ACCESS (FULL) OF 'YL_DYXMBD' (Cost=151 Card=31         
          0767 Bytes=11809146)                                                  
                                                                                




Statistics
----------------------------------------------------------                     
          0  recursive calls                                                   
          0  db block gets                                                      
      16049  consistent gets                                                   
          0  physical reads                                                     
          0  redo size                                                         
        487  bytes sent via SQL*Net to client                                   
        275  bytes received via SQL*Net from client                             
          2  SQL*Net roundtrips to/from client                                 
          0  sorts (memory)                                                     
          0  sorts (disk)                                                      
          1  rows processed                                                     

SQL>
SQL> SELECT /*+ordered(C,A) use_nl(C,A)*/ SUM(NVL(A.JCYLJ,0)), SUM(NVL(A.GDXYLJ,0)), SUM(NVL(A.GRZHYLJ,0)),
  2               SUM(NVL(A.HLF,0)), SUM(NVL(A.DFBCLXDY,0)), SUM(NVL(A.HYGDXBT,0)),
  3               SUM(NVL(A.DYZE,0))
  4          FROM VW_RYJBXX C,YL_DYXMBD A, YL_LTXJBXX B
  5         WHERE A.GRBH = B.GRBH AND
  6               A.GRBH = C.GRBH AND
  7               C.DWBH = :b1 AND
  8               B.JZSJ = :b2 AND
  9               B.YWZJBZ = '2';


Execution Plan
----------------------------------------------------------                     
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=533 Card=1 Bytes=91)         
   1    0   SORT (AGGREGATE)                                                   
   2    1     HASH JOIN (Cost=533 Card=3 Bytes=273)                             
   3    2       TABLE ACCESS (FULL) OF 'YL_LTXJBXX' (Cost=328 Card=244         
           Bytes=4880)                                                         
                                                                                
   4    2       HASH JOIN (Cost=204 Card=4105 Bytes=291455)                     
   5    4         HASH JOIN (OUTER) (Cost=10 Card=4105 Bytes=135465)            
   6    5           INDEX (RANGE SCAN) OF 'IDX_TB_GRJBXX_GRBH_RYZT' (U         
          NIQUE) (Cost=13 Card=4105 Bytes=90310)                                
                                                                                
   7    5           INDEX (RANGE SCAN) OF 'IDX_TB_DWJBXX_DWBH_DWZT' (N         
          ON-UNIQUE) (Cost=2 Card=1 Bytes=11)                                   
                                                                                
   8    4         TABLE ACCESS (FULL) OF 'YL_DYXMBD' (Cost=151 Card=31         
          0767 Bytes=11809146)                                                  
                                                                                




Statistics
----------------------------------------------------------                     
          0  recursive calls                                                   
          0  db block gets                                                      
      16049  consistent gets                                                   
          0  physical reads                                                     
          0  redo size                                                         
        487  bytes sent via SQL*Net to client                                   
        275  bytes received via SQL*Net from client                             
          2  SQL*Net roundtrips to/from client                                 
          0  sorts (memory)                                                     
          0  sorts (disk)                                                      
          1  rows processed                                                     

SQL>
SQL> spool off

看来要用上执行计划的稳定性把执行计划固化起来了。

husthxd

视图VW_RYJBXX
的结构为
select
xxx
from t1 , t2
where t1.grbh = t2.grbh(+);

rollingpig

来个10053，看看CBO是如何选择的？

husthxd

最初由 rollingpig 发布
[B]来个10053，看看CBO是如何选择的？ [/B]

见附件.