Re: Memory Sort Puzzle

> How much time is being spent on those memory sorts? Is overall performance
> going up or down?
>
> I think your examples on dual are pretty much useless in making decisions
> for the real thing. (Although they were useful for making a simple explain
> plan analogous to your real query, so if that is all you meant them to do,
> great.)
>
> Xho
>

I do see the same effect with real tables in the application.

I have a farly busy OLTP system with lots of queries that use self-joins via corelated subqueries.
These queries run fast - getting a small number of rows via fast index access.
However, because lots of these queries are being executed I think I can significantly reduce total I/O on the system by replacing self-joins with analytical functions.
My main objective at this time is not as much to speed up particular queries as to improve overall performance during peak load.

My concern is that while reducing memory reads I will be introducing new sorts. So how do I compare the relative cost and aggregate effects of lots of memory sorts vs lots of additional memory reads? Is there some rule of thumb or a simple test - short of building another system with all code in question replaced and then testing both under full load? Received on Thu Aug 04 2005 - 08:16:35 CDT