Re: Helping the CBO

8.1.7.2

I thought an explanation of what it was doing would be more concise. Here is the explain plan
Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

SELECT STATEMENT (cost:1243 card:1)
  SORT (UNIQUE) (cost:1243 card:1)
    FILTER

      NESTED LOOPS (cost:1240 card:1)
        NESTED LOOPS (cost:1239 card:1)
          NESTED LOOPS (cost:1238 card:1)
            NESTED LOOPS (cost:1238 card:1)
              HASH JOIN (cost:1238 card:4)
                INDEX (FULL SCAN) of PS0LOCATION_TBL (cost:1 card:37)
                TABLE ACCESS (FULL) of PS_JOB (cost:1236 card:4)
              INDEX (UNIQUE SCAN) of PSASET_CNTRL_REC
            INDEX (UNIQUE SCAN) of PS_FAST_SCRTY_1
          TABLE ACCESS (BY INDEX ROWID) of PS_PERSONAL_DATA (cost:1
card:47216
            INDEX (UNIQUE SCAN) of PS_PERSONAL_DATA
        INDEX (RANGE SCAN) of PSAFAST_SCRTY_1 (cost:1 card:9540)
      SORT (AGGREGATE)
        FIRST ROW (cost:2 card:5)
          INDEX (RANGE SCAN (MIN/MAX)) of PSAJOB (cost:2 card:5)
      SORT (AGGREGATE)
        FIRST ROW (cost:2 card:1)
          INDEX (RANGE SCAN (MIN/MAX)) of PSAJOB (cost:2 card:1)
      SORT (AGGREGATE)
        FIRST ROW (cost:1 card:1)
          INDEX (RANGE SCAN (MIN/MAX)) of PS_LOCATION_TBL (cost:1 card:1)

"Telemachus" <telemachus_at_ulysseswillreturn.net> wrote in message news:t1V%8.4763$zX3.4508_at_news.indigo.ie...
> We need versions !
> In any event you need to show the contents of Explain Plan
>
>
>
> "Chuck" <chuckh_at_softhome.net> wrote in message
> news:ahp2mo$unid4$1_at_ID-85580.news.dfncis.de...
> > I have a query which if run under the CBO with no hints takes about 1
> minute
> > to complete. It joins about 8 tables in a Peoplsoft database using NL
for
> > all but one join. The last remaining join is a hash join. If I force it
to
> > use hash joins with a hint for all tables it runs in 9 seconds even
though
> > the cost is slightly higher. What can I do to get the CBO to pick the
> > better hash join based query automatically since I can't change the app
to
> > use hints?
> >
> > I have tried increasing db_file_multiblock_read_count, sort_area_size,
and
> > hash_area_size. I've even tried increasing optimizer_index_cost_adj to
the
> > max of 10000. None of these changes helps it choose more hash joins. I
> have
> > tried analyzing the tables in the join several ways - compute and
> estimate -
> > with and without histgrams - nothing seems to help.
> >
> > I don't want to drop indexes because I don't know how it will affect the
> > rest of the application. Is there anything I can do?
> >
> >
>
>
Received on Thu Jul 25 2002 - 11:43:33 CDT