OraFAQ Forum: Performance Tuning

Home » RDBMS Server » Performance Tuning » parallel query

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

parallel query [message #274416]

Tue, 16 October 2007 00:41

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,

Report message to a moderator

Re: parallel query [message #274418 is a reply to message #274416]

Tue, 16 October 2007 00:47

BlackSwan
Messages: 26766
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.

Report message to a moderator

Re: parallel query [message #274420 is a reply to message #274418]

Tue, 16 October 2007 00:51

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.

Report message to a moderator

Re: parallel query [message #274433 is a reply to message #274420]

Tue, 16 October 2007 01:21

Michel Cadot
Messages: 68664
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

Report message to a moderator

Re: parallel query [message #274468 is a reply to message #274433]

Tue, 16 October 2007 03:15

rleishman
Messages: 3728
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

Report message to a moderator

Previous Topic:	performance issue on oracle 8
Next Topic:	Reading DBMS_XPLAN output

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Jun 25 01:46:08 CDT 2024