RE: performance. 7 queries independent queries takes 3s with union all in view 98s
Date: Thu, 22 Oct 2015 13:26:05 +0000
Message-ID: <1E24812FBE5611419EFAFC488D7CCDD130669A14_at_G9W0741.americas.hpqcorp.net>
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(tm) 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 - 15:26:05 CEST