Since you can't hint query directly, try to create a stored outline for it
to lock hash joins and then enable using stored outlines. Be aware that
stored outlines require exact match, bit for bit, of running query and the
one outline was created for. So you need to preserve the query exactly,
including case and spaces/newlines. One way to do this is to create
another user, create hinted views on base tables with the same names,
then run the query and capture the outline for it, and then enable stored
outlines for original user - if your queries match, outline will take effect
for original query forcing hash joins for it. Another way is to create one
named outline for original query, another one for hinted query, and then
swap names in OUTLN.OL$HINTS table so that outline for hinted query
will become outline for unhinted one. Whichever is simpler for you will do.
--
Vladimir Zakharychev (bob@dpsp-yes.com) http://www.dpsp-yes.com
Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
All opinions are mine and do not necessarily go in line with those of my employer.
"Chuck" <chuckh_at_softhome.net> wrote in message news:ahpa92$v2ith$1_at_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 Fri Jul 26 2002 - 10:12:51 CDT
