Home » SQL & PL/SQL » SQL & PL/SQL » Union Run in Parallel (Oracle 10G, Windows XP)
Union Run in Parallel [message #477429] Thu, 30 September 2010 11:13 Go to next message
freakabhi
Messages: 74
Registered: November 2007
Location: mumbai
Member
All,

I have a query which has 5 unions, each clause of the union takes 1 hr to run and query results come back in 5 hrs,

Is there any way I can make these clause to run in parallel?

Thanks in advance.
Re: Union Run in Parallel [message #477430 is a reply to message #477429] Thu, 30 September 2010 11:18 Go to previous messageGo to next message
BlackSwan
Messages: 22911
Registered: January 2009
Senior Member
Since NOBODY can optimize SQL they can not see more details are required.
http://www.orafaq.com/forum/m/433888/136107/#msg_433888
Please refer to URL above & be sure to provide the details requested:
Re: Union Run in Parallel [message #477431 is a reply to message #477429] Thu, 30 September 2010 11:25 Go to previous messageGo to next message
Michel Cadot
Messages: 59427
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do not use UNION use UNION ALL.

Regards
Michel
Re: Union Run in Parallel [message #477444 is a reply to message #477429] Thu, 30 September 2010 12:09 Go to previous messageGo to next message
sunroser
Messages: 16
Registered: September 2010
Junior Member
Hi, you can parallelize it but not necessarily on Union level, try this /*+ parallel(table_alias_name,35)*/, then you are getting 35 parallelized processes more than 5 as you want ..., but you DBA might not be happy with this when they are monitoring the database though.
Re: Union Run in Parallel [message #477450 is a reply to message #477444] Thu, 30 September 2010 12:46 Go to previous messageGo to next message
Michel Cadot
Messages: 59427
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First forget hints they do not exist
Second learn how to write SQL
Third learn how to better write SQL
Fourth learn Oracle
Fifth learn how write Oracle SQL
Sixth learn hints

Hints should not be allowed to developers.

Regards
Michel
Re: Union Run in Parallel [message #477471 is a reply to message #477450] Thu, 30 September 2010 18:21 Go to previous messageGo to next message
rleishman
Messages: 3700
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Michel Cadot wrote on Fri, 01 October 2010 03:46
Hints should not be allowed to developers.


What a lot of offensive DBA bullsplat.

A developer might equally say that DBA's should not be allowed to touch code, and justify it by the increased likelihood that they will change the semantics by not understanding the data or application.

The fact is that hints are part of the language. There are developers that are poor practitioners of our language (SQL), same as every other language out there. They will do countless stupid things, like using functions on indexed columns, or joining tables on incomplete composite keys, or nesting cursors within cursors. We see it ALL THE TIME in this forum.

And yes, these people will also misuse hints.

The people who should not be allowed to use hints are cowboys and poor programmers, but they should not be allowed to program AT ALL. Experienced judicious programmers can and should feel free to use all of the capabilities of the toolset.

Inexperienced programmers should be encouraged to explore all of the capabilities of their toolset, under supervision. That supervision - and the feedback they receive - is the way they will learn.

Ross Leishman
Re: Union Run in Parallel [message #477492 is a reply to message #477471] Fri, 01 October 2010 02:03 Go to previous messageGo to next message
Michel Cadot
Messages: 59427
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
A developer might equally say that DBA's should not be allowed to touch code

I agree!

Quote:
The fact is that hints are part of the language

I disagree! If you want an analogy they are like indexes that is way to optimize query execution access path BUT much far from SQL and the data.

Quote:
They will do countless stupid things, like using functions on indexed columns, or joining tables on incomplete composite keys, or nesting cursors within cursors. We see it ALL THE TIME in this forum.

This is the pirpose of my points 2 and 3.

Quote:
Experienced judicious programmers can and should feel free to use all of the capabilities of the toolset.

They do not control the hardware, they do not control the OS, they do not control Oracle behaviour.

Hints should be used ONLY after a problem is detected from a team made of developer, dba (developer dba as well as production dba) and sysadmin, not a priori.

Regards
Michel
Re: Union Run in Parallel [message #477532 is a reply to message #477492] Fri, 01 October 2010 08:10 Go to previous messageGo to next message
rleishman
Messages: 3700
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I can see we're not going to get anywhere here. I see no common ground whatsoever.

To anyone else reading, what Michel is expressing is an opinion; same as me. Like most things on the internet, opinions are free and are worth every cent.

My opinion - if it is not already clear - is that Oracle's CBO hints are presented in the SQL Reference manual and in Part IV (Optimizing SQL Statements) of the Performance Tuning Guide - both bibles for the SQL programmer - for a reason: that is for programmers to acquaint themselves with ALL of the tools in their kit.

Like all of the other tools in that kit, hints can be mis-used in ways that do more harm than good. In my opinion, these consequences are no worse than the consequences of mis-using many of the other tools Oracle provides. This is where I believe Michel disagrees. In his opinion, the consequences of mis-using hints are more insidious and more damaging to the database and perhaps the application.

Make up your own mind, but be aware that there are differing schools of thought.

Oh, and just so we are not completely off-topic, I second @sunroser's suggestion of a PARALLEL hint in order to achieve a parallel read on the query, although @sunroser rightly hints at the fact that parallelism is a shared resource, and you should consider the impact on the rest of the system when you start monopolizing a shared resource.

I know of no way to issue the components of a UNION [ALL] query separately and in parallel.

Ross Leishman
Re: Union Run in Parallel [message #477535 is a reply to message #477532] Fri, 01 October 2010 08:38 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Quote:

I second @sunroser's suggestion of a PARALLEL hint in order to achieve a parallel read on the query, although @sunroser rightly hints at the fact that parallelism is a shared resource, and you should consider the impact on the rest of the system when you start monopolizing a shared resource.

The bit which puzzles me is how can we suggest something without knowing the actual cause of the problem (i.e) without seeing the actual query which is being executed.

What if the original query is starved for CPU ? Then this hint will make it even worse. Isn't it.

To some extent I agree with Michel. Most often I haven seen people using hint as a silver bullet where it should be the last option. As Tom rightly says in this post it should be the last resort and it should be used by developers/dba who understand how Oracle works.

http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:7038986332061

Regards

Raj
Re: Union Run in Parallel [message #477583 is a reply to message #477535] Fri, 01 October 2010 18:01 Go to previous messageGo to next message
rleishman
Messages: 3700
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Nobody suggested that this would solve "the problem"; in fact - there was no problem stated. The OP simply asked whether it was possible to parallelize the separate components of a UNION query. I don't think it is possible, nor does @sunroser, but he suggested the next best thing: a different way to exploit parallelism.

It is now up to the OP to take this away and decide what to do with it. Assuming there is a real-world problem somewhere in one of his UNION queries, maybe parallelism will help, maybe not. But at least he now has a means by which he can try it out.

I am very much against this trend to make people who post questions on this forum to prove that they are worthy of the answer before it is provided. If someone asks a simple question - even in the abstract as this one is - then they deserve a simple answer.

Ross Leishman
Re: Union Run in Parallel [message #477585 is a reply to message #477583] Fri, 01 October 2010 23:04 Go to previous messageGo to next message
Frank
Messages: 7877
Registered: March 2000
Senior Member
rleishman wrote on Sat, 02 October 2010 01:01
I am very much against this trend to make people who post questions on this forum to prove that they are worthy of the answer before it is provided. If someone asks a simple question - even in the abstract as this one is - then they deserve a simple answer.

Ross Leishman


Hear hear!
Re: Union Run in Parallel [message #477684 is a reply to message #477429] Sun, 03 October 2010 13:47 Go to previous messageGo to next message
Kevin Meade
Messages: 1943
Registered: December 1999
Location: Connecticut USA
Senior Member
I spent considerable time searching the web looking for examples of parallel query with union. The manuals are a bit vague. They say that union can be parallelized but in other parts of the docs they also note that parallel query requires a FTS or other similar access. I always thought it was possible to do union in parallel with doing a FTS (et.al.) but maybe not. In any event, here is an example (forced to be sure) which shows what a parallel query with union all looks like.

SQL> l
  1  select /*+ parallel (b,2) */ *
  2  from (
  3         select * from PARTY_SERVICE_MODEL_H
  4         union
  5         select * from PARTY_SERVICE_MODEL_H
  6         union
  7         select * from PARTY_SERVICE_MODEL_H
  8         union
  9         select * from PARTY_SERVICE_MODEL_H
 10*      ) b
SQL> /

460 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=13 Card=1840 Bytes=167440)
   1    0   PX COORDINATOR
   2    1     PX SEND* (QC (RANDOM)) OF ':TQ10001' (Cost=13 Card=1840 Bytes=167440)
   3    2       VIEW* (Cost=13 Card=1840 Bytes=167440)
   4    3         SORT* (UNIQUE) (Cost=13 Card=1840 Bytes=69920)
   5    4           PX RECEIVE*
   6    5             PX SEND* (HASH) OF ':TQ10000'
   7    6               UNION-ALL*
   8    7                 PX BLOCK* (ITERATOR) (Cost=2 Card=460 Bytes=17480)
   9    8                   TABLE ACCESS* (FULL) OF 'PARTY_SERVICE_MODEL_H' (TABLE) (Cost=2 Card=460 Bytes=17480)
  10    7                 PX BLOCK* (ITERATOR) (Cost=2 Card=460 Bytes=17480)
  11   10                   TABLE ACCESS* (FULL) OF 'PARTY_SERVICE_MODEL_H' (TABLE) (Cost=2 Card=460 Bytes=17480)
  12    7                 PX BLOCK* (ITERATOR) (Cost=2 Card=460 Bytes=17480)
  13   12                   TABLE ACCESS* (FULL) OF 'PARTY_SERVICE_MODEL_H' (TABLE) (Cost=2 Card=460 Bytes=17480)
  14    7                 PX BLOCK* (ITERATOR) (Cost=2 Card=460 Bytes=17480)
  15   14                   TABLE ACCESS* (FULL) OF 'PARTY_SERVICE_MODEL_H' (TABLE) (Cost=2 Card=460 Bytes=17480)


   2 PARALLEL_TO_SERIAL
   3 PARALLEL_COMBINED_WITH_PARENT
   4 PARALLEL_COMBINED_WITH_PARENT
   5 PARALLEL_COMBINED_WITH_PARENT
   6 PARALLEL_TO_PARALLEL
   7 PARALLEL_COMBINED_WITH_PARENT
   8 PARALLEL_COMBINED_WITH_CHILD
   9 PARALLEL_COMBINED_WITH_PARENT
  10 PARALLEL_COMBINED_WITH_CHILD
  11 PARALLEL_COMBINED_WITH_PARENT
  12 PARALLEL_COMBINED_WITH_CHILD
  13 PARALLEL_COMBINED_WITH_PARENT
  14 PARALLEL_COMBINED_WITH_CHILD
  15 PARALLEL_COMBINED_WITH_PARENT


Statistics
----------------------------------------------------------
         12  recursive calls
          0  db block gets
         68  consistent gets
          0  physical reads
          0  redo size
      24018  bytes sent via SQL*Net to client
       1506  bytes received via SQL*Net from client
         32  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
        460  rows processed


We can see that how the table is scanned for each query and this scan is done in parallel. We can also see how steps are feeding each other such that the query remains in parallel right up to the co-ordinator step.

But what we cannot tell is if the union parts are done in parallel with each other or in serial with each other.

It is clear from the plan that each FTS is done in parallel, but it is not clear if the unions are. Changing the degree of parallelism does not change the plan. Thus I suspect the individuals union parts are each done in serial. Too bad.

Maybe someone smarter than me can figure out something different.

Kevin
Re: Union Run in Parallel [message #477693 is a reply to message #477684] Sun, 03 October 2010 16:22 Go to previous messageGo to next message
rleishman
Messages: 3700
Registered: October 2005
Location: Melbourne, Australia
Senior Member
My reading of this plan is that there are N parallel query slaves, each of which scans selected blocks of the 4 tables. Since one slave can only do one thing at a time, those 4 scans will be serialized within each slave.

However, it is reasonable to expect that not every slave will proceed at EXACTLY the same speed. It is likely that one of the N slaves will finish scanning table 1 a little earlier. Since there are no serialisation points (except at the projection to the cooordinator), it will then progress onto Table 2 whilst the others are still scanning table 1. In some way we are processing two parts of the UNION at the same time, but it is not in the sense that the OP suggested.

My understanding of Oracle's PQ server is that every slave is issued the same job, it's just the data that is divided up between them. I don't believe that there are any cases where there are special-purpose slaves that have different jobs from the others.

Ross Leishman
Re: Union Run in Parallel [message #477734 is a reply to message #477693] Mon, 04 October 2010 02:50 Go to previous message
Kevin Meade
Messages: 1943
Registered: December 1999
Location: Connecticut USA
Senior Member
Thanks Ross. Your additional commentary helps me as much as it does others. Much appreciated.

Kevin
Previous Topic: How to alter PCTFREE
Next Topic: Date Operation
Goto Forum:
  


Current Time: Sat Oct 25 06:15:51 CDT 2014

Total time taken to generate the page: 0.13421 seconds