Re: Helping the CBO

From: Chuck <chuckh_at_softhome.net>
Date: Thu, 25 Jul 2002 12:50:42 -0400
Message-ID: <ahpa92$v2ith$1@ID-85580.news.dfncis.de>

It occurred to me that you might also want to see the query. Here it is...

SELECT
    DISTINCT
    A.EMPLID,
    TO_CHAR(A.EFFDT,'YYYY-MM-DD'),

    A.EFFSEQ,
    A.LOCATION,
    B.DESCR,
    C.NAME,
    B.SETID,
    B.LOCATION,

    TO_CHAR(B.EFFDT,'YYYY-MM-DD')
FROM

    PS_JOB A,
    PS_FAST_EMPGL_VW1 A1,
    PS_LOCATION_TBL B,
    PS_SET_CNTRL_REC B2,
    PS_PERSONAL_DATA C,
    PS_FAST_PERSGL_VW1 C1

WHERE
    B.LOCATION = A.LOCATION

    AND B2.SETCNTRLVALUE = A.BUSINESS_UNIT
    AND B2.RECNAME = 'LOCATION_TBL'
    AND B2.SETID = B.SETID

    AND A.EMPLID = A1.EMPLID
    AND A.EMPL_RCD = A1.EMPL_RCD
    AND A1.ROWSECCLASS = 'ALLPANLS'
    AND C.EMPLID = C1.EMPLID
    AND C1.ROWSECCLASS = 'ALLPANLS'
    AND ( A.EFFDT =

        (SELECT MAX(A_ED.EFFDT) FROM PS_JOB A_ED
        WHERE A.EMPLID = A_ED.EMPLID
        AND A.EMPL_RCD = A_ED.EMPL_RCD
        AND A_ED.EFFDT <= SYSDATE)
    AND A.EFFSEQ =
        (SELECT MAX(A_ES.EFFSEQ) FROM PS_JOB A_ES
        WHERE A.EMPLID = A_ES.EMPLID
        AND A.EMPL_RCD = A_ES.EMPL_RCD
        AND A.EFFDT = A_ES.EFFDT)

    AND A.EMPL_STATUS IN ('A','L','P')
    AND B.EFFDT =

        (SELECT MAX(B_ED.EFFDT)
        FROM PS_LOCATION_TBL B_ED
        WHERE B.SETID = B_ED.SETID
        AND B.LOCATION = B_ED.LOCATION
        AND B_ED.EFFDT <= A.EFFDT)

    AND A.EMPLID = C.EMPLID )
/

"Chuck" <chuckh_at_softhome.net> wrote in message news:ahp9rl$uoldo$2_at_ID-85580.news.dfncis.de...
> 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:50:42 CDT