If selecting from the individual branches view is faster than selecting from
the UNION ALL view, it is probable that the CBO is transforming the UNION ALL
view into something that we were not expecting. But without the query plan, we
are blind.
From: mark.powell2@xxxxxxx
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: performance. 7 queries independent queries takes 3s with union all
in view 98s
Date: Thu, 22 Oct 2015 13:26:05 +0000
Have you checked the last_analyzed date for the tables and indexes involved in
this query? Perhaps running dbms_stats with 100% sample size might be an
option.
Posting the full SQL and plan is necessary for anyone to really provided target
suggestions.
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Iggy Fernandez
Sent: Wednesday, October 21, 2015 7:23 PM
To: jcdrpllist@xxxxxxxxx; mvshelton
Cc: ORACLE-L
Subject: RE: performance. 7 queries independent queries takes 3s with union all
in view 98s
re: any idea is welcome
The query plans?
alter session set "_rowsource_execution_statistics"=true;
set linesize 200
set pagesize 1000
set tab off
-- first execute the query using the view
-- then display the query plan
select * from table(dbms_xplan.display_cursor(null, null, 'ADVANCED IOSTATS
LAST +PEEKED_BINDS'));
-- next execute each individual branch of the UNION ALL
-- then display the query plan
select * from table(dbms_xplan.display_cursor(null, null, 'ADVANCED IOSTATS
LAST +PEEKED_BINDS'));
Date: Wed, 21 Oct 2015 18:20:58 -0400
Subject: Re: performance. 7 queries independent queries takes 3s with union all
in view 98s
From: jcdrpllist@xxxxxxxxx
To: mvshelton@xxxxxxxxxxxxx
CC: iggy_fernandez@xxxxxxxxxxx;
oracle-l@xxxxxxxxxxxxx
Thank you
To try I doubled the pga_aggregate_target, and it didn't fixed, in theory I
should reduce the memory because hash happens when there is a lot of memory, as
I remember.
So I tried to reduce the pga_aggregate_target and memory_target and the problem
is the same.
Based on the article of Iggy I added the hint
/*+ NO_QUERY_TRANSFORMATION */ and nothing.
disables alter system set "_optimizer_cost_based_transformation" = off ;
I remember it was of no use but I tried too
alter system set "_optimizer_max_permutations"=3000;
and either, any idea is welcome, thank you :)
2015-10-21 15:08 GMT-04:00 mvshelton <mvshelton@xxxxxxxxxxxxx>:
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
-------- Original message --------
From: Iggy Fernandez
Date:10/21/2015 2:51 PM (GMT-05:00)
To:
jcdrpllist@xxxxxxxxx, ORACLE-L
Subject: RE: performance. 7 queries independent queries takes 3s with union all
in view 98s
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@xxxxxxxxx
To: oracle-l@xxxxxxxxxxxxx
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 :)
