Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: CBO and connect by prior

Re: CBO and connect by prior

From: Joze Senegacnik <joze.senegacnik_at_aster.si>
Date: Mon, 19 May 2003 12:41:50 -0800
Message-ID: <F001.0059CF76.20030519124150@fatcity.com>


It seems that you have encountered the same problem as I had some time ago and it is registered on metalink (look for :"Connect by clause behaves different in 9.2.0.2 vs. 8.1.7") and as a bug# 2881650. In short: in 9i (9.0.1. and 9.2) Oracle changed execution plan  (CONNECT BY FILTERING, CONNECT BY PUMP, BUFFER SORT) and in your case the select statement for in clause is executed too many times. If you are interested I can send you by email all the stuff from TAR and also my conclusions made from trace files. This problem is still not solved. They gave me a workaround but it doesn't work.

Regards, Joze

-- 
Joze Senegacnik
S&T Slovenia, Hermes-Plus, d.d.
Oracle Business Unit (Aster) 
Chief Technology Officer 
e-mail: joze.senegacnik_at_aster.si 
tel: +386 1 58 94 341 
fax: +386 1 58 94 201 



Jamadagni, Rajendra wrote:


> Hi all,
>
> one of my developers sent me this query ...
>
> SELECT s.show_number
> FROM SHOWS s, APF_SHOWS h
> WHERE h.spca_cat = s.spca_cat
> AND h.spt_pgm_code = s.spt_pgm_code
> AND h.show_id = s.ID
> AND NVL(h.show_part,'~') = NVL(s.show_part,'~')
> AND h.flt_id IN --(91681)
> (SELECT f.flt_id
> FROM FLIGHT_DATES f
> START WITH f.flt_id = :b1
> CONNECT BY PRIOR f.flt_id = f.parent_flt_id)
> /
>
> complaining that it takes a long time ...
>
> Flight_dates has 76496 rows, shows has 305642 rows and apf_shows has
> 310542 rows. All tables are analyzed with dbms_stats at 10% estimate.
> The developers tells me that the sub_query by itself is very fast and
> will at most return 5 rows. If I hardcode the IN clause, the query
> returns very fast.
>
> Any ideas on how to influence the CBO so that it would *know* that the
> sub-query will return a small number of rows?
>
> following are the explain plans and the script I used to generate them.
>
> --------------- 1.sql -------------------------------
> conn tcs/devl
> set line 120
> set trimspool on
> set pagesize 10000
> explain plan for
> select s.show_number
> from shows s, apf_shows h
> where h.spca_cat = s.spca_cat
> and h.spt_pgm_code = s.spt_pgm_code
> and h.show_id = s.id
> and nvl(h.show_part,'~') = nvl(s.show_part,'~')
> and h.flt_id in
> (select f.flt_id
> from flight_dates f
> start with f.flt_id = &b1 connect by prior f.flt_id =
> f.parent_flt_id)
> /
> select *
> from table(dbms_xplan.display)
> /
> explain plan for
> select s.show_number
> from shows s, apf_shows h
> where h.spca_cat = s.spca_cat
> and h.spt_pgm_code = s.spt_pgm_code
> and h.show_id = s.id
> and nvl(h.show_part,'~') = nvl(s.show_part,'~')
> and h.flt_id in (&b1)
> /
> select *
> from table(dbms_xplan.display)
> /
> --------------- 1.sql -------------------------------
>
>
> 10:40:39 SQL> @1
> Connected.
> Enter value for b1: 91681
> old 11: start with f.flt_id = &b1 connect by prior f.flt_id
> = f.parent_flt_id)
> new 11: start with f.flt_id = 91681 connect by prior
> f.flt_id = f.parent_flt_id)
>
> Explained.
>
> PLAN_TABLE_OUTPUT
> ----------------------------------------------------------------------------------------------
>
> | Id | Operation | Name | Rows |
> Bytes |TempSpc| Cost |
> ----------------------------------------------------------------------------------------------
>
> | 0 | SELECT STATEMENT | | 1
> | 53 | | 1756 |
> |* 1 | HASH JOIN SEMI | | 1
> | 53 | | 1756 |
> |* 2 | HASH JOIN | | 1
> | 40 | 9560K| 1753 |
> | 3 | TABLE ACCESS FULL | SHOWS |
> 305K| 5974K| | 997 |
> | 4 | INDEX FAST FULL SCAN | APFSHOW_PK |
> 311K| 6089K| | 271 |
> | 5 | VIEW | VW_NSO_1 | 25499
> | 323K| | 2 |
> |* 6 | CONNECT BY WITH FILTERING | |
> | | | |
> | 7 | NESTED LOOPS | |
> | | | |
> |* 8 | INDEX UNIQUE SCAN | FLT_UN | 1
> | 5 | | 1 |
> | 9 | TABLE ACCESS BY USER ROWID | FLIGHT_DATES |
> | | | |
> | 10 | NESTED LOOPS | |
> | | | |
> | 11 | BUFFER SORT | | 25499
> | 149K| | |
> | 12 | CONNECT BY PUMP | |
> | | | |
> | 13 | TABLE ACCESS BY INDEX ROWID| FLIGHT_DATES | 25499
> | 149K| | 2 |
> |* 14 | INDEX RANGE SCAN | FLIGHT_PARENT_IDX | 1
> | | | 1 |
> ----------------------------------------------------------------------------------------------
>
>
> Predicate Information (identified by operation id):
> ---------------------------------------------------
> 1 - access("H"."FLT_ID"="VW_NSO_1"."$nso_col_1")
> 2 - access("H"."SPCA_CAT"="S"."SPCA_CAT" AND
> "H"."SPT_PGM_CODE"="S"."SPT_PGM_CODE" AND "
> H"."SHOW_ID"="S"."ID" AND
> NVL("H"."SHOW_PART",'~')=NVL("S"."SHOW_PART",'~')
> 6 - filter("F"."FLT_ID"=91681)
> 8 - access("F"."FLT_ID"=91681)
> 14 - access("F"."PARENT_FLT_ID"=NULL)
>
> Note: cpu costing is off
>
> 32 rows selected.
>
> Enter value for b1: 91681
> old 8: and h.flt_id in (&b1)
> new 8: and h.flt_id in (91681)
>
> Explained.
>
> PLAN_TABLE_OUTPUT
> ------------------------------------------------------------------------------
>
> | Id | Operation | Name | Rows | Bytes |
> Cost |
> ------------------------------------------------------------------------------
>
> | 0 | SELECT STATEMENT | | 1 | 40
> | 52 |
> | 1 | TABLE ACCESS BY INDEX ROWID| SHOWS | 1 | 20
> | 3 |
> | 2 | NESTED LOOPS | | 1 | 40
> | 52 |
> |* 3 | INDEX RANGE SCAN | APFSHOW_PK | 24 | 480
> | 3 |
> |* 4 | INDEX RANGE SCAN | SHOW_SPORT_UK | 1 |
> | 2 |
> ------------------------------------------------------------------------------
>
>
> Predicate Information (identified by operation id):
> ---------------------------------------------------
>
> 3 - access("H"."FLT_ID"=91681)
> 4 - access("H"."SPCA_CAT"="S"."SPCA_CAT" AND
> "H"."SPT_PGM_CODE"="S"."SPT
> _PGM_CODE" AND "H"."SHOW_ID"="S"."ID"
> filter(NVL("H"."SHOW_PART",'~')=NVL("S"."SHOW_PART",'~'))
>
> Note: cpu costing is off
> 20 rows selected.
>
>
> Any ideas will be greatly appreciated. BTW we are on 9202 ...
> Thanks in advance
> Raj
> --------------------------------------------------------------------------------
>
> Rajendra dot Jamadagni at nospamespn dot com
> All Views expressed in this email are strictly personal.
> QOTD: Any clod can have facts, having an opinion is an art !
>
>------------------------------------------------------------------------
>
>*********************************************************************This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*********************************************************************1
>
>
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Joze Senegacnik INET: joze.senegacnik_at_aster.si Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- 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).
Received on Mon May 19 2003 - 15:41:50 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US