Oracle-L: RBO and CBO query problem in 9.2

From: Jamadagni, Rajendra <Rajendra.Jamadagni_at_espn.com>
Date: Fri, 30 Aug 2002 13:23:43 -0800
Message-ID: <F001.004C495F.20020830132343@fatcity.com>

Hi all,

I have following query

SELECT SPT_SPCA_CAT||SPT_PGM_CODE SPORT_CODE,
       Spt_Spca_Cat,
       Spt_Pgm_Code,
       Est_DT Start_Date,
       Est_TM Start_Time,
       EST_DURA DURATION,
       Bc_DtTm, --CR_Start_DT,
       Bc_End_DtTm, --CR_End_DT,
       LIFE_CYCLE_STATUS STATUS,
       EP_NUM_UNIQ,
       Sched_CHG_IND,
       Chg_Ind,
       EP_Number, Log_Number,
       Log_Network, 
       Simulcast_EP_Number,
       Ep_Id,
       EDT_IND,
       Part_No

FROM V_EPISODE_AIRINGS
WHERE (Bc_DtTm BETWEEN Dbpk_bc.Add_Time(1, 20020812000000, 0)

                AND Dbpk_bc.Add_Time(1, 20020812086399, -1 )
        OR
        Bc_End_DtTm BETWEEN Dbpk_bc.Add_Time(1, 20020812000000, 1)
                    AND  Dbpk_bc.Add_Time(1, 20020812086399, -1) )
      AND NVL(DID_NOT_AIR_IND,'N') = 'N'
      AND log_number = 1
      AND Repeat_Id >= 0
      AND LIFE_CYCLE_STATUS IN (2,3,4)

ORDER BY Bc_DtTm, Est_Dura;

Where the v_episode_airings is a view with a union.

Under rule based optimizer I get this explain plan ...

PLAN_TABLE_OUTPUT

| 0 | SELECT STATEMENT | | |
| |
| 1 | SORT ORDER BY | | |
| |
| 2 | VIEW | V_EPISODE_AIRINGS | |
| |
| 3 | SORT UNIQUE | | |
| |
| 4 | UNION-ALL | | |
| |
| 5 | CONCATENATION | | |
| |

|*  6 |       TABLE ACCESS BY INDEX ROWID| EPISODE_AIRINGS    |       |


|       |

|*  7 |        INDEX RANGE SCAN          | BC_END_DT_INDX     |       |


|       |

|*  8 |       TABLE ACCESS BY INDEX ROWID| EPISODE_AIRINGS    |       |


|       |

|*  9 |        INDEX RANGE SCAN          | BC_START_DT_INDX   |       |


|       |

|* 10 |      TABLE ACCESS FULL           | EPISODE_AIRINGS    |       |


|       |

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

For CHOOSE I get following explain plan ...

PLAN_TABLE_OUTPUT

| 0 | SELECT STATEMENT | | 16 | 3184 | 11765 |
| 1 | SORT ORDER BY | | 16 | 3184 | 11765 |
| 2 | VIEW | V_EPISODE_AIRINGS | 16 | 3184 | 11764 |
| 3 | SORT UNIQUE | | 16 | 3096 | 11764 |
| 4 | UNION-ALL | | | | |

|*  5 |      TABLE ACCESS FULL| EPISODE_AIRINGS    |    15 |  2925 |  5880 |
|*  6 |      TABLE ACCESS FULL| EPISODE_AIRINGS    |     1 |   171 |  5880 |
----------------------------------------------------------------------------

These explains are gathered using following syntax ...

explain plan for ...<above query>
/
select * from table(dbms_xplan.display)
/

Question is when I have following indexes available

index EPISODE_AIRINGS.BC_DT_INDX(BC_DTTM, BC_END_DTTM)
index EPISODE_AIRINGS.BC_START_DT_INDX(BC_DTTM)
index EPISODE_AIRINGS.BC_END_DT_INDX (BC_END_DTTM)

Why is CBO avoiding index usage, even if I provide a hint ...? DBPK_BC is a package that does some date manipulation things ...

I can generate 10053 output if anyone is inclined to read through, I can't decipher all of that yet (even after reading that document). This is an important view and the performance off of this select is really killing. The parameters to dbpk_bc function are dynamic in real life. The table is analyzed with estimate option and all indexes are analyzed as well.

Any ideas are welcome ...

Thanks in advance
Raj

Rajendra Jamadagni MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.

QOTD: Any clod can have facts, but having an opinion is an art!

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jamadagni, Rajendra
  INET: Rajendra.Jamadagni_at_espn.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



text/plain attachment: InterScan_Disclaimer.txt

Received on Fri Aug 30 2002 - 16:23:43 CDT