RE: Analytics Performance was RE: Tricky query question

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: Larry Elkins
  INET: elkinsl_at_flash.net

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