Oracle-L: Re: CBO and connect by prior

From: <rgaffuri_at_cox.net>
Date: Mon, 19 May 2003 09:27:02 -0800
Message-ID: <F001.0059CADA.20030519092702@fatcity.com>

your hash join semi is what is causing the slowdown. hash joins require full table scans and are only useful when you are basically getting most or all of the rows in both tables.

Semi joins by nature are slow. I cant read your plan, but is the CBO forcing a semi join off your connect by prior and your connect by prior is returning just 5 rows? OK, then try moving the connect by prior to an in-line view. Your want to get rid of the hash join.

I think. I cant read your plan. Its jumbled.
>
> From: "Jamadagni, Rajendra" <Rajendra.Jamadagni_at_espn.com>
> Date: 2003/05/19 Mon AM 11:51:55 EDT
> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> Subject: CBO and connect by prior
>
> 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
>
>

CBO and connect by prior

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 !

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: <rgaffuri_at_cox.net
  INET: rgaffuri_at_cox.net

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 - 12:27:02 CDT