Re: performance on intermedia query with order by, help!!

1. First off, what version of ORACLE are you using? I saw this line
   --
   Trace file compatibility: 7.03.02
   --
   in your trace file.
2. Looks like you forgot to turn on the timed_statistics.
3. Not sure how much impact this would have on your situation, but for sure set the PCTINCREASE to 0 for your temp tbs. I would also make the extent size same as the sort_area_size. This should help the sorting to disk - if sorting to disk indeed occurrs.
4. How dynamic is your table? How often do you optimize the InterMedia indexes?

Your query is simple therefore your tuning options should also be simple. Either you have a sorting performance problem or your InterMedia is not performing to your satisfaction. I am begining to have doubt about your original claim that the response time of the InterMedia query jumped from 2 seconds to 31 seconds when the "order by" clause was added. Or did you do the reverse - first with the ORDER BY and then dropped it? If you run one immmediately after the other, the results might have been skewed, since the second query will be reading the data from the data buffer not the disk.

Hope you can get to the bottom of it. Received on Thu Jul 04 2002 - 18:18:46 CDT