IMHO, some hints are good, some are bad. For example,
hints like ORDERED and FIRST_ROWS are still giving the
optimizer some "room to move" should newer features
come along later...
If you use (say) INDEX(table,index), then you are
eliminating the benefit of possible future indexes,
and even worse, if you drop or rename the index, then
the hint silently gets ignored.
hth
connor
- "Koivu, Lisa" <lisa.koivu_at_efairfield.com> wrote:
> Remco, why do you say don't use hints unless you
> really have to?
>
> Lisa Koivu
> Oracle Database Administrator
> Fairfield Resorts, Inc.
> 954-935-4117
>
>
> > -----Original Message-----
> > From: Doug C [SMTP:dcowles_at_i84.net]
> > Sent: Friday, October 05, 2001 1:51 PM
> > To: Multiple recipients of list ORACLE-L
> > Subject: Re: perplexing plan?
> >
> > This was a vendor designed query. To my knowlege
> - it is complete. As
> > to the
> > performance problem - it was apparently caused by
> a "tool" that was
> > suppossed to
> > analyze statistics on the some of the associated
> tables and didn't. It
> > was
> > rectified, and now the query screams. I'm am
> somewhat anxious to see the
> > new
> > trace of it however. I'll probably post it
> shortly.
> >
> >
> >
> >
> > On Fri, 05 Oct 2001 08:20:21 -0800, you wrote:
> >
> > >Doug,
> > >
> > >Sorting is caused by the "distinct", and is
> probably the cause of your
> > >performance problem. Try to limit the sorting to
> a minimal number of
> > rows,
> > >e.g. by creating a temp table containing all
> (including the multiple
> > copies)
> > >rows and then select the distinct values of that
> table. You could also
> > try :
> > >select distinct * from (select ......) to replace
> the select distinct.
> > >
> > >Another tip: don't you hints unless you really
> have to ...
> > >
> > >HTH, Remco
> > >
> > >-----Oorspronkelijk bericht-----
> > >Van: Doug C [mailto:dcowles_at_i84.net]
> > >Verzonden: vrijdag 5 oktober 2001 16:30
> > >Aan: Multiple recipients of list ORACLE-L
> > >Onderwerp: perplexing plan?
> > >
> > >
> > >I'm a little perplexed by this query and it's
> associated plan. It's also
> > a
> > >big
> > >performance problem. The problem is the 35
> million row table clearly.
> > But
> > >looking at the plan at the bottom, I'm not sure
> where the sorting is
> > going
> > >on.
> > >Would anyone say the index full scan on the 35
> million row table is being
> > >sorted? Or does it look more like it's being fed
> to a nested loops
> > query?
> > >
> > >Thanks,
> > >Doug
> > >
> > >SELECT /*+ ORDERED INDEX(S_ S15_IX1)
> INDEX(BUS_FID F15_UK1) INDEX(STREET
> > >A15_IX1) */ SDE.STREET.CFCC, SDE.STREET.BUS_FID
> ,S_.eminx,S_.eminy,
> > > S_.emaxx,S_.emaxy,
> BUS_FID.fid,BUS_FID.numofpts,BUS_FID.entity,
> > > BUS_FID.points,BUS_FID.rowid
> > >FROM
> > > (SELECT /*+ INDEX(SP_ S15_IX1) */ DISTINCT
> > sp_fid,eminx,eminy,emaxx,emaxy
> > > FROM SDE.S15 SP_ WHERE SP_.gx >= :1 AND
> SP_.gx <= :2 AND SP_.gy >= :3
> > > AND SP_.gy <= :4 AND SP_.eminx <= :5 AND
> SP_.eminy <= :6 AND SP_.emaxx
> > >=
> > >:7
> > > AND SP_.emaxy >= :8) S_
> > >, SDE.STREET
> > > , SDE.F15 BUS_FID
> > >WHERE S_.sp_fid = BUS_FID.fid
> > >AND S_.sp_fid = SDE.STREET.BUS_FID
> > >
> > >
> > >call count cpu elapsed disk
> query current
> > >rows
> > >------- ------ -------- ---------- ----------
> ---------- ----------
> > >----------
> > >Parse 1 0.00 0.00 0
> 0 0
> > >0
> > >Execute 1 0.00 0.00 0
> 0 0
> > >0
> > >Fetch 45 473.15 475.04 223532
> 66153503 0
> > >4494
> > >------- ------ -------- ---------- ----------
> ---------- ----------
> > >----------
> > >total 47 473.15 475.04 223532
> 66153503 0
> > >4494
> > >
> > >Misses in library cache during parse: 1
> > >Optimizer goal: CHOOSE
> > >Parsing user id: 20
> > >
> > >Rows Row Source Operation
> > >-------
> ---------------------------------------------------
> > > 4494 HASH JOIN
> > > 4494 NESTED LOOPS
> > > 4495 VIEW
> > > 4495 SORT UNIQUE
> > > 4817 INDEX RANGE SCAN (object id
> 7356)
> > > 4494 TABLE ACCESS BY INDEX ROWID STREET
> > > 8988 INDEX UNIQUE SCAN (object id
> 7355)
> > >33065402 TABLE ACCESS BY INDEX ROWID F15
> > >33065403 INDEX FULL SCAN (object id 7283)
> > >--
> > >Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> > >--
> > >Author: Doug C
> > > INET: dcowles_at_i84.net
> > >
> > >Fat City Network Services -- (858) 538-5051
> FAX: (858) 538-5051
> > >San Diego, California -- Public Internet
> access / Mailing Lists
> >
>
>--------------------------------------------------------------------
> > >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).
> > >--
> > >Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> >
> > --
> > Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> > --
> > Author: Doug C
> > INET: dcowles_at_i84.net
> >
> > Fat City Network Services -- (858) 538-5051
> FAX: (858) 538-5051
> > San Diego, California -- Public Internet
> access / Mailing Lists
> >
>
> > 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).
>
Connor McDonald
http://www.oracledba.co.uk (mirrored at
http://www.oradba.freeserve.co.uk)
"Some days you're the pigeon, some days you're the statue"
Do You Yahoo!?
Get your free @yahoo.co.uk address at
http://mail.yahoo.co.uk
or your free @yahoo.ie address at
http://mail.yahoo.ie
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: =?iso-8859-1?q?Connor=20McDonald?=
INET: hamcdc_at_yahoo.co.uk
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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 Fri Oct 05 2001 - 16:45:15 CDT
