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
