Tricking the name optimizer to use a different stored outline? I like that!
I'll give it a try.
"Vladimir M. Zakharychev" <bob_at_dpsp-yes.com> wrote in message
news:ahrotn$a0c$1_at_babylon.agtel.net...
> 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_at_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 - 12:27:58 CDT
