CBO is unable to pick up good execution plan

The problem ocurred in an Oracle 9.2.0.8 database. Many queries suddently run much longer from one day. What kind of changes have been made to the database is not obvious. Checking their execution plans, typically seeing the CBO choose hash join and full table scan for some large tables. By trial and error, the resolution to this problem is to disable hash join at the session level and add index hints. Typically by doing so, the queries can be executed in a few minutes instead of few hours. The execution plans before the problem occurrs have not been kown. It is evident that by collecting optimizer statistics more accuratly can help the CBO choose better plan. It has also been tested that setting the optimizer_index_cost_adj parameter to 10 or 20 can make the CBO choose better plans for some queries. It seems that CBO under-estimate the cost of full table scan.

Below is an example. The query is a relative simple one, which involves join of two large tables:

 select 'Pots,',count(distinct(od.master_order_id))
   from tableOD od,
        tableSD sd
  where
        sd.MASTER_ORDER_ID=od.MASTER_ORDER_ID
    and sd.SUB_ORDER_TYPE in (1,3,4)
    and sd.product_category in (2,3,17)
    and sd.SUPP_ORDER_ID=0
    and sd.service_type='Install'
    and od.RECEIVED_DATE between To_Date('05/20/08 00:00:00','MM/DD/YY HH24:MI:SS')
    and    To_Date('05/20/08 23:59:59','MM/DD/YY HH24:MI:SS')
    and (sd.SSPUNITY in ('N') or sd.SSPUNITY is null )
     and (sd.DRY_LOOP_IND in ('N') or  sd.DRY_LOOP_IND is null )
    and (sd.HARMONY_DATA_RECEIVED = 'N' or sd.HARMONY_DATA_RECEIVED is null )
   group by trunc(od.RECEIVED_DATE),
               sd.SSPUNITY,
               sd.HARMONY_DATA_RECEIVED,
               sd.DRY_LOOP_IND;

The execution plan obtained from v$sql_plan is shown as follows:

##### bad plan: if no index hint and not disable hash join

----------------------------------------------------------------------------------
 Id   Operation                        Name             Rows   Bytes  Cost (%CPU)
----------------------------------------------------------------------------------
   0  SELECT STATEMENT                                                        
   1   SORT GROUP BY                                     1042  63562    504K  (5)
   2    FILTER                                                                
   3     HASH JOIN                                       1042  63562    504K  (5)
   4      TABLE ACCESS FULL           TABLESD            1042  39596    443K  (6)
   5      TABLE ACCESS BY INDEX ROWID TABLEOD             103K  2314K 61262   (1)
   6       INDEX RANGE SCAN           ORD_DTL_RECV_DATE   185K          581   (1)
----------------------------------------------------------------------------------

The better execution plan is obtained by:
1. alter session set hash_join_enabled=false;
2. add: select /*+ INDEX(od ORD_DTL_RECV_DATE) INDEX(sd SSD_MASTER_ORDER_ID_NUK) */

Note we won't get the better plan if only adopting one of the above conditions.


##### good plan

-------------------------------------------------------------------------------------
 Id   Operation                         Name                  Rows   Bytes  Cost (%CPU)
----------------------------------------------------------------------------------------
   0  SELECT STATEMENT                                                                 
   1   SORT GROUP BY                                           1042  63562    576K  (1)
   2    FILTER                                                                         
   3     TABLE ACCESS BY INDEX ROWID   TABLESD                   1     38      6  (17)
   4      NESTED LOOPS                                         1042  63562    576K  (1)
   5       TABLE ACCESS BY INDEX ROWID TABLEOD                 103K  2314K 61262   (1)
   6        INDEX RANGE SCAN           ORD_DTL_RECV_DATE       185K          581   (1)
   7       INDEX RANGE SCAN            SSD_MASTER_ORDER_ID_NUK   2             4  (25)
-------------------------------------------------------------------------------

I also checked the execution plan after collecting more accurate statitics on the table
TABLESD:

exec dbms_stats.gather_table_stats('SSP_FLOW',
'TABLESD',
method_opt => 'FOR COLUMNS MASTER_ORDER_ID SIZE 1',
estimate_percent => 100, cascade =>true);

Note: this analyze job took more than 8 hours


The plan is still not optimal in this case:

--------------------------------------------------------------------------------
 Id   Operation                        Name     Rows   Bytes  Cost (%CPU)
--------------------------------------------------------------------------------
   0  SELECT STATEMENT                                                
   1   SORT GROUP BY                             1042  63562    420K  (6)
   2    FILTER                                                        
   3     NESTED LOOPS                            1042  63562    420K  (6)
   4      TABLE ACCESS FULL           TABLESD    1042  39596    416K  (6)
   5      TABLE ACCESS BY INDEX ROWID TABLEOD       1     23      5  (20)
   6       INDEX RANGE SCAN           TEMP_1        1             4  (25)
--------------------------------------------------------------------------------