Joining the party a bit late, so someone may already have made the following
comment:
If you have a query: select from VIEW where "predicate" then Oracle may not be
able to push the predicate inside the view to apply it to each individual
branch, whereas if you "test each branch" separately you are manually doing
that type of predicate push.
It gets more complicated for non-trivial branches in the view, of course, and,
as Iggy points out, without seeing the execution plan for the query (and,
ideally, the key features of the query text) we can have no idea what the
difference is between your testing and the optimizer mechanics.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on behalf
of Iggy Fernandez [iggy_fernandez@xxxxxxxxxxx]
Sent: 22 October 2015 15:00
To: ORACLE-L; jcdrpllist@xxxxxxxxx
Subject: RE: performance. 7 queries independent queries takes 3s with union all
in view 98s
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<mailto:jcdrpllist@xxxxxxxxx>
To: mvshelton@xxxxxxxxxxxxx<mailto:mvshelton@xxxxxxxxxxxxx>
CC: iggy_fernandez@xxxxxxxxxxx<mailto:iggy_fernandez@xxxxxxxxxxx>;
oracle-l@xxxxxxxxxxxxx<mailto: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<mailto: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<mailto: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<mailto:jcdrpllist@xxxxxxxxx>
To: oracle-l@xxxxxxxxxxxxx<mailto: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 :)
