RE: performance. 7 queries independent queries takes 3s with union all in view 98s

From: mvshelton <mvshelton_at_chartermi.net>
Date: Wed, 21 Oct 2015 15:08:36 -0400
Message-ID: <ejffjoimfpj4nch885ynk8q9.1445454515357_at_email.android.com>

If your hash joins are slower are you sorting to disk, if so you may need to increase your pga.

Matt

Sent via the Samsung Galaxy S™ III, an AT&T 4G LTE smartphone

<div>-------- Original message --------</div><div>From: Iggy Fernandez <iggy_fernandez_at_hotmail.com> </div><div>Date:10/21/2015 2:51 PM (GMT-05:00) </div><div>To: jcdrpllist_at_gmail.com, ORACLE-L <oracle-l_at_freelists.org> </div><div>Subject: RE: performance. 7 queries independent queries takes 3s with union all in view 98s </div><div>
</div>The answer should leap out right out of the query plan

Perhaps there's join factorization going on. See https://blogs.oracle.com/optimizer/entry/optimizer_transformations_join_factorization

But the best bet is to examine the query plan.

Iggy

Date: Wed, 21 Oct 2015 11:47:18 -0400
Subject: performance. 7 queries independent queries takes 3s with union all in view 98s From: jcdrpllist_at_gmail.com
To: oracle-l_at_freelists.org

Hello if you can help please
I found something very interesting, I tested two times to be sure. 11.2.0.3

There is a view having 7 queries joined with union all query1
union all
query2
union all
...

When executed in the view all 7 takes 98s If in the same view I put only one query at a time I get 3s total, the query is unchanged

The problem is using a hash join instead of nested loops

1 1.38s
2 0.04s
3 1.11s
4 0.05s
5 0.35s
6 0.02s
7 0.09s

all 7 98x

Thank you :)

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Oct 21 2015 - 21:08:36 CEST