Re: Memory Sort Puzzle

artmt_at_hotmail.com wrote:
> > 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.

If I/O is not a bottleneck on your system, then their no point in going out of your way to reduce it. It it ain't broke (and in this case if no one is complaining it probably ain't broke) don't fix it.

> My main objective at this time is not as much to speed up particular
> queries as to improve overall performance during peak load.

Is your concern that the I/O motivated by this type of query is interfering with other queries of the same type? Or that they are interfering with completely different types of query altogether?

>
> 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

That work all or almost all of the time? Probably not.

> - short of building another system
> with all code in question replaced and then testing both under full
> load?

If you know enough about the nature of the work your system does, you can sometimes create an artificial load that is similar to a full load with just a few simple scripts. But here is always the chance that you will get it wrong.

As a starting case, I'd just make a script which forks off 32 copies of itself (or however many simultaneous executions you think is reasonable for this query, plus a few more to make up for the fact that you don't have other types of queries running) and then each fork connects to your test database and runs the query under question a few hundred times, with a different randomly chosen but reasonable bind value each time. Then change to the new type of query and try again. This will let you know how they are interfering with themselves, at least.

Unfortunately, if you want more definitive results, you need to accurately replicate the full load on your server.

Xho

-- 
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service                        $9.95/Month 30GB