| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: perplexing plan?
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
------- ------ -------- ---------- ---------- ---------- ----------
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 ListsReceived on Fri Oct 05 2001 - 10:46:20 CDT
--------------------------------------------------------------------
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 -- Author: Daemen, Remco INET: R.Daemen_at_facent.nl 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).
 |
 |