RE: performance. 7 queries independent queries takes 3s with union all in view 98s
Date: Thu, 22 Oct 2015 14:30:48 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D9282AA761_at_EXMBX01.thus.corp>
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
_at_jloracle
From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on behalf of Iggy Fernandez [iggy_fernandez_at_hotmail.com] Sent: 22 October 2015 15:00
To: ORACLE-L; jcdrpllist_at_gmail.com
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_at_hpe.com
To: oracle-l_at_freelists.org
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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Iggy Fernandez
Sent: Wednesday, October 21, 2015 7:23 PM
To: jcdrpllist_at_gmail.com; 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_at_gmail.com<mailto:jcdrpllist_at_gmail.com>
To: mvshelton_at_chartermi.net<mailto:mvshelton_at_chartermi.net>
CC: iggy_fernandez_at_hotmail.com<mailto:iggy_fernandez_at_hotmail.com>; oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org>
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_at_chartermi.net<mailto:mvshelton_at_chartermi.net>>:
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_at_gmail.com<mailto:jcdrpllist_at_gmail.com>, 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_at_gmail.com<mailto:jcdrpllist_at_gmail.com>
To: oracle-l_at_freelists.org<mailto: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 Thu Oct 22 2015 - 16:30:48 CEST