Re: Analytics Performance was RE: Tricky query question

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
> elkinsl_at_flash.net
> 214.954.1781
>
> > -----Original Message-----
> > From: root_at_fatcity.com [mailto:root_at_fatcity.com]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: jkstill_at_cybcon.com

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: 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).