Home » RDBMS Server » Performance Tuning » parallel query
parallel query [message #274416] Tue, 16 October 2007 00:41 Go to next message
vkrmhj
Messages: 9
Registered: May 2007
Junior Member
I have 9 queries that are simple union.I want to run these individual query in parallel(may be through a stored procedure, function) so that it takes less time.Is there any way to do this.Pls help.
Regards,
Re: parallel query [message #274418 is a reply to message #274416] Tue, 16 October 2007 00:47 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>I have 9 queries that are simple union.
This is an oxymoron & I doubt design/architecture is in Third Normal Form.

just use undocumented parameter _MAKE_SQL_FASTER=TRUE instead.
Re: parallel query [message #274420 is a reply to message #274418] Tue, 16 October 2007 00:51 Go to previous messageGo to next message
vkrmhj
Messages: 9
Registered: May 2007
Junior Member
I also doubt this..the database is not properly designed..but is there any way of calling all the nine queries at the same time(parallely) through a stored procedure.
Re: parallel query [message #274433 is a reply to message #274420] Tue, 16 October 2007 01:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Have a look at Threading in Pl/SQL topic that I immediatly found when typing "parallel" in search field.

Regards
Michel
Re: parallel query [message #274468 is a reply to message #274433] Tue, 16 October 2007 03:15 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Hints seem to do the trick:

C985675@ODM> select /*+parallel(a)*/ *
  2  from sys.obj$ a
  3  union all
  4  select /*+parallel(a)*/ *
  5  from sys.obj$ a
  6
C985675@ODM> @planb
Wrote file /home/c985675/.planb.sql

Explained.


Plan hash value: 1111539763

---------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |          |   110K|  8503K|   168  (52)| 00:00:03 |        |      |            |
|   1 |  PX COORDINATOR       |          |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM) | :TQ10000 |       |       |            |          |  Q1,00 | P->S | QC (RAND)  |
|   3 |    UNION-ALL          |          |       |       |            |          |  Q1,00 | PCWP |            |
|   4 |     PX BLOCK ITERATOR |          | 55112 |  4251K|    84   (4)| 00:00:02 |  Q1,00 | PCWC |            |
|   5 |      TABLE ACCESS FULL| OBJ$     | 55112 |  4251K|    84   (4)| 00:00:02 |  Q1,00 | PCWP |            |
|   6 |     PX BLOCK ITERATOR |          | 55112 |  4251K|    84   (4)| 00:00:02 |  Q1,00 | PCWC |            |
|   7 |      TABLE ACCESS FULL| OBJ$     | 55112 |  4251K|    84   (4)| 00:00:02 |  Q1,00 | PCWP |            |
---------------------------------------------------------------------------------------------------------------

14 rows selected.

  1  select /*+parallel(a)*/ *
  2  from sys.obj$ a
  3  union all
  4  select /*+parallel(a)*/ *
  5* from sys.obj$ a


Ross Leishman
Previous Topic: performance issue on oracle 8
Next Topic: Reading DBMS_XPLAN output
Goto Forum:
  


Current Time: Wed Dec 07 12:29:06 CST 2016

Total time taken to generate the page: 0.09541 seconds