Larry, A good way to demonstrate the scalability if SQL is to use Tom Kyte's test harness.
http://govt.oracle.com/~tkyte/runstats.html This is a very simple but very effective demonstration of the difference in scalability of 2 pieces of SQL. I used it in a post a few weeks ago to demonstrate that indexing small tables ( 2 blocks ) was much more scalable than not indexing them. The performanance gains in a dev environment were neglibigle, but the degree of resource usage dramatically favored indexing small tables. Jared On Saturday 25 January 2003 11:58, Larry Elkins wrote: > I agree, I will probably crank up some concurrent tests later on the test I > built. > > I created a pretty simple test case (starting with a small sort and hash > area size) where the correlated history type of query "wins" over a > non-correlated approach (using a hash join) and an analytical approach and > it's sort. Wins quite handily. But I sure wouldn't want multiple versions > of the correlated approach running. And then as I bumped up the hash area > size and the sort area size, the gap began to close, and eventually the > non-correlated approach and the analytic approach started winning, > especially once the disk sorts went away. And in this test case, the > non-correlated approach and the analytic approach are still close. The > non-correlated approach is doing so well since the sub-query can resolve > completely in an index (a concatenated index), and then drive a hash into a > full on the table. If I take that concatenated index away and it has to hit > the table to resolve the other column, it's two full's hashed, and the > analytic then wins hands down. > > FWIW, in the "real world" cases I've had, it's never been close, with the > analytic smoking by the others by a wide margin. And back to your comments, > in those cases the concurrency *isn't* much of an issue for any approach > since these are batch jobs that are usually only running one at a time. > Anyway, I may try some more testing later to start to zero in on when one > may be better than the others. For me in real life, it's simply been a case > of try the approaches and compare. It would be nice to have some starting > guidelines. > > Regards, > > Larry G. Elkins > [EMAIL PROTECTED] > 214.954.1781 > > > -----Original Message----- > > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Jonathan > > Lewis > > Sent: Saturday, January 25, 2003 11:54 AM > > To: Multiple recipients of list ORACLE-L > > Subject: Re: Analytics Performance was RE: Tricky query question > > > > > > > > An odd thing, though. Sometimes the query that > > is (a little) slower when run stand-alone is the > > better bet when run in a highly concurrent environment > > because of latching issues. > > > > Converting self-joins to analytics can, on occasion, > > increase CPU usage but reduce latching - a strategy > > that seems to be favoured in recent versions of the > > Oracle kernel. > > > > > > Regards > > > > Jonathan Lewis > > http://www.jlcomp.demon.co.uk > > > > Coming soon a new one-day tutorial: > > Cost Based Optimisation > > (see http://www.jlcomp.demon.co.uk/tutorial.html ) > > > > ____UK_______March > > ____USA_(FL)_May > > > > > > Next Seminar dates: > > (see http://www.jlcomp.demon.co.uk/seminar.html ) > > > > ____USA_(CA, TX)_August > > > > > > The Co-operative Oracle Users' FAQ > > http://www.jlcomp.demon.co.uk/faq/ind_faq.html > > > > >Though I've never built test cases showing the counter examples, I'm > > > > sure it > > > > >would be pretty easy to build some where the analytics is going to be > > >slower. FWIW, another person on the list *is* working on such counter > > >examples ;-) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).
