On Apr 16, 7:36 am, Daiesh <mcavenkad...@gmail.com> wrote:
> Hi All,
>
> Please could you suggest how to create parallel query and execute it?
> I have used parallel hint even query executed normally.
>
> advance wishes.
>
> Thanks
> Daiesh
Using parallel query requires some init.ora parameters you may not
have set. In 11g these would be:
parallel_min_servers
parallel_max_servers
parallel_automatic_tuning
parallel_adaptive_multi_user
in 11g parallel_max_servers defaults to 20 (which, I expect, is based
upon the CPU count so the default may vary). You must also have
current statistics computed/estimated on the tables the parallel
queries access.
How do you know your parallel hint is not working? What did the query
plan report? Or did you not generate a query plan? Using the hint
against the DBA_OBJECTS view produces this plan:
Execution Plan
----------------------------------------------------------
Plan hash value: 383246012
--------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows |
Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 66075 |
13M| 141 (3)| 00:00:02 | | | |
| 1 | PX COORDINATOR | |
| | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ20001 | 66075 |
13M| 141 (3)| 00:00:02 | Q2,01 | P->S | QC (RAND) |
| 3 | VIEW | DBA_OBJECTS | 66075 |
13M| 141 (3)| 00:00:02 | Q2,01 | PCWP | |
| 4 | UNION-ALL | |
| | | | Q2,01 | PCWP | |
|* 5 | TABLE ACCESS BY INDEX ROWID | SUM$ | 1
| 9 | 1 (0)| 00:00:01 | Q2,01 | PCWP | |
|* 6 | INDEX UNIQUE SCAN | I_SUM$_1 | 1
| | 0 (0)| 00:00:01 | Q2,01 | PCWP | |
| 7 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 1 |
30 | 3 (0)| 00:00:01 | Q2,01 | PCWP | |
|* 8 | INDEX RANGE SCAN | I_OBJ1 | 1
| | 2 (0)| 00:00:01 | Q2,01 | PCWP | |
| 9 | BUFFER SORT | |
| | | | Q2,01 | PCWC | |
| 10 | PX RECEIVE | |
| | | | Q2,01 | PCWP | |
| 11 | PX SEND ROUND-ROBIN | :TQ20000 |
| | | | | S->P | RND-ROBIN |
|* 12 | FILTER | |
| | | | | | |
| 13 | PX COORDINATOR | |
| | | | | | |
| 14 | PX SEND QC (RANDOM) | :TQ10002 | 70169 |
8291K| 138 (3)| 00:00:02 | Q1,02 | P->S | QC (RAND) |
|* 15 | HASH JOIN | | 70169 |
8291K| 138 (3)| 00:00:02 | Q1,02 | PCWP | |
| 16 | PX RECEIVE | | 98 |
1568 | 2 (0)| 00:00:01 | Q1,02 | PCWP | |
| 17 | PX SEND BROADCAST | :TQ10001 | 98 |
1568 | 2 (0)| 00:00:01 | Q1,01 | P->P | BROADCAST |
| 18 | PX BLOCK ITERATOR | | 98 |
1568 | 2 (0)| 00:00:01 | Q1,01 | PCWC | |
| 19 | TABLE ACCESS FULL | USER$ | 98 |
1568 | 2 (0)| 00:00:01 | Q1,01 | PCWP | |
|* 20 | HASH JOIN | | 70169 |
7195K| 135 (2)| 00:00:02 | Q1,02 | PCWP | |
| 21 | BUFFER SORT | |
| | | | Q1,02 | PCWC | |
| 22 | PX RECEIVE | | 98 |
2254 | 1 (0)| 00:00:01 | Q1,02 | PCWP | |
| 23 | PX SEND BROADCAST | :TQ10000 | 98 |
2254 | 1 (0)| 00:00:01 | | S->P | BROADCAST |
| 24 | INDEX FULL SCAN | I_USER2 | 98 |
2254 | 1 (0)| 00:00:01 | | | |
| 25 | PX BLOCK ITERATOR | | 70169 |
5619K| 133 (1)| 00:00:02 | Q1,02 | PCWC | |
|* 26 | TABLE ACCESS FULL | OBJ$ | 70169 |
5619K| 133 (1)| 00:00:02 | Q1,02 | PCWP | |
|* 27 | TABLE ACCESS BY INDEX ROWID| IND$ | 1
| 8 | 2 (0)| 00:00:01 | | | |
|* 28 | INDEX UNIQUE SCAN | I_IND1 | 1
| | 1 (0)| 00:00:01 | | | |
| 29 | NESTED LOOPS | | 1 |
28 | 2 (0)| 00:00:01 | | | |
|* 30 | INDEX FULL SCAN | I_USER2 | 1 |
20 | 1 (0)| 00:00:01 | | | |
|* 31 | INDEX RANGE SCAN | I_OBJ4 | 1
| 8 | 1 (0)| 00:00:01 | | | |
| 32 | NESTED LOOPS | | 2 |
68 | 3 (0)| 00:00:01 | Q2,01 | PCWP | |
| 33 | PX BLOCK ITERATOR | |
| | | | Q2,01 | PCWC | |
| 34 | TABLE ACCESS FULL | LINK$ | 2 |
36 | 2 (0)| 00:00:01 | Q2,01 | PCWP | |
| 35 | TABLE ACCESS CLUSTER | USER$ | 1 |
16 | 1 (0)| 00:00:01 | Q2,01 | PCWP | |
|* 36 | INDEX UNIQUE SCAN | I_USER# | 1
| | 0 (0)| 00:00:01 | Q2,01 | PCWP | |
--------------------------------------------------------------------------------------------------------------------------------
which clearly shows parallelism at work. And if the plan didn't
explain it well enough the rest of the output from autotrace does:
Note
-----
- automatic DOP: computed degree of parallelism is 2
Please post the query plan for your suspect SQL statement.
David Fitzjarrell
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to Oracle-PLSQL@googlegroups.com
To unsubscribe from this group, send email to
oracle-plsql-unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
-~----------~----~----~----~------~----~------~--~---
