9i 和 10g对 connect by的执行计划不同

一个数据库，ORACLE10.2.0.4，数据是从一个9208的库EXP再IMP的，同样一个SQL，在原来的DB上执行只要21msec，新的DB要5分多钟。

SELECT   MAX (SYS_CONNECT_BY_PATH (a.from_station, '/')) station_path
      FROM   eservice.es_repair_item_tran_tbl a
     WHERE   1 = 1 AND a.item_id = '0EBED3F0-0A86-825C-013D-C7B4D8FA076C'
START WITH   a.seq = 1 AND a.item_id = '0EBED3F0-0A86-825C-013D-C7B4D8FA076C'
CONNECT BY   PRIOR a.seq + 1 = a.seq
             AND a.item_id = '0EBED3F0-0A86-825C-013D-C7B4D8FA076C'

Plan
SELECT STATEMENT  ALL_ROWSCost: 50,202  Bytes: 44  Cardinality: 1                                         
        6 SORT AGGREGATE  Bytes: 44  Cardinality: 1                                 
                5 FILTER                         
                        4 CONNECT BY WITHOUT FILTERING                 
                                2 TABLE ACCESS BY INDEX ROWID TABLE ESERVICE.ES_REPAIR_ITEM_TRAN_TBL Cost: 4  Bytes: 105  Cardinality: 1         
                                        1 INDEX UNIQUE SCAN INDEX (UNIQUE) ESERVICE.PK_ES_REPAIR_ITEM_TRAN_TBL Cost: 3  Cardinality: 1  
                                3 TABLE ACCESS FULL TABLE ESERVICE.ES_REPAIR_ITEM_TRAN_TBL Cost: 50,202  Bytes: 678,701,276  Cardinality: 15,425,029  

这个表有1500W笔记录，有分析过，但是现在涉及到这个表的应用慢得要命。

SQL> show parameter optimizer_mode

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode                       string      ALL_ROWS

原来9I的优化器是CHOOSE，现在换到10G，默认是ALL_ROWS

 

alter session set optimizer_features_enable='9.2.0.8';

或者是：alter system set optimizer_features_enable='9.2.0.8';

 

Plan
SELECT STATEMENT  CHOOSECost: 2  Bytes: 44  Cardinality: 1                                                 
        10 SORT AGGREGATE  Bytes: 44  Cardinality: 1                                         
                9 FILTER                                 
                        8 CONNECT BY WITH FILTERING                         
                                2 TABLE ACCESS BY INDEX ROWID TABLE ESERVICE.ES_REPAIR_ITEM_TRAN_TBL                
                                        1 INDEX UNIQUE SCAN INDEX (UNIQUE) ESERVICE.PK_ES_REPAIR_ITEM_TRAN_TBL Cost: 2  Bytes: 38  Cardinality: 1         
                                6 NESTED LOOPS                 
                                        3 CONNECT BY PUMP         
                                        5 TABLE ACCESS BY INDEX ROWID TABLE ESERVICE.ES_REPAIR_ITEM_TRAN_TBL Cost: 2  Bytes: 44  Cardinality: 1         
                                                4 INDEX UNIQUE SCAN INDEX (UNIQUE) ESERVICE.PK_ES_REPAIR_ITEM_TRAN_TBL Cost: 2  Cardinality: 1  
                                7 TABLE ACCESS FULL TABLE ESERVICE.ES_REPAIR_ITEM_TRAN_TBL Cost: 2  Bytes: 44  Cardinality: 1                 


现在的执行时间为16msec

在connect by 的语句里加hint /*+ optimizer_features_enable('9.2.0.8') */

或是是在instance级别设置了optimizer_features_enable＝'9.2.0', 比之前快了很多

设置后，还是会有不少全表扫描的情况，有些是百万级的表

---------------------------------------------------------------------------------------------------------------------------------------------------

SELECT   MAX (SYS_CONNECT_BY_PATH (a.from_station, '/')) station_path
      FROM   (select /*+ no_merge() */ * from  eservice.es_repair_item_tran_tbl a
     WHERE   1 = 1 AND a.item_id = '0EBED3F0-0A86-825C-013D-C7B4D8FA076C'
     ) a
START WITH   a.seq = 1
CONNECT BY   PRIOR a.seq + 1 = a.seq

执行效果也不错

 

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/13165828/viewspace-605835/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/13165828/viewspace-605835/