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

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
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

Original text of this message