Hi Javor, Thanks for your reply.
I have executed the below queries, explain plan for UPDATE /*+ PARALLEL (REWARDS_MASTER, 2) */ REWARDS_MASTER SET BAL_FLAG = 1 Where ACCOUNT_NUMBER= :V_Account_Number AND SCHEME_ID = :V_SCHEME_ID AND TXN_STATUS = 'active' AND REWARD_STATUS = 'active' AND BAL_FLAG = 0 select * from table( dbms_xplan.display ); i got the below explained plan PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | -------------------------------------------------------------------------------- | 0 | UPDATE STATEMENT | | 1 | 47 | 5 | | 1 | UPDATE | REWARDS_MASTER | | | | | 2 | INDEX RANGE SCAN | ACC_SCM_TXST_RDST_BRRC | 1 | 47 | 5 | -------------------------------------------------------------------------------- Note: cpu costing is off, PLAN_TABLE' is old version Please could you suggest me its using parallel processing? Advance wishes Thanks Daieish On Apr 20, 2:09 pm, javor <nikolov.ja...@gmail.com> wrote: > Hi Daiesh, > > I'm not sure it doesn't use parallel - could you upload execution plan > generated from sqlplus: > > explain plan for > UPDATE .... your statement here ... > / > > set linesize 120 > select * from table( dbms_xplan.display ); > > Here is the plan which I get for the same query: > PLAN_TABLE_OUTPUT > ------------------------------------------------------------------------------------------------------------------------ > Plan hash value: 131937247 > > --------------------------------------------------------------------------------------------------------------------- > | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| > Time | TQ |IN-OUT| PQ Distrib | > --------------------------------------------------------------------------------------------------------------------- > | 0 | UPDATE STATEMENT | | 1 | 343 | 2 (0)| > 00:00:01 | | | | > | 1 | UPDATE | REWARDS_MASTER | | | | > | | | | > | 2 | PX COORDINATOR | | | | | > | | | | > | 3 | PX SEND QC (RANDOM)| :TQ10000 | 1 | 343 | > 2 (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) | > | 4 | PX BLOCK ITERATOR | | 1 | 343 | 2 (0)| > 00:00:01 | Q1,00 | PCWC | | > |* 5 | TABLE ACCESS FULL| REWARDS_MASTER | 1 | 343 | > 2 (0)| 00:00:01 | Q1,00 | PCWP | | > > PLAN_TABLE_OUTPUT > ------------------------------------------------------------------------------------------------------------------------ > --------------------------------------------------------------------------------------------------------------------- > > Predicate Information (identified by operation id): > --------------------------------------------------- > > 5 - filter("TXN_STATUS"='active' AND "REWARD_STATUS"='active' AND > "BAL_FLAG"=0 AND > "ACCOUNT_NUMBER"=TO_NUMBER(:V_ACCOUNT_NUMBER) AND > "SCHEME_ID"=TO_NUMBER(:V_SCHEME_ID)) > > The last 3 columns in this plan are related to parallel execution. > > Regards, > Javor > > On Apr 20, 8:05 am, Daiesh <mcavenkad...@gmail.com> wrote:> Hi David, > > > Thanks for you valuable reply. > > > The Oracle parameters are below > > > parallel_max_servers = 4 > > parallel_min_servers = 2 > > parallel_automatic_tuning = FALSE > > parallel_adaptive_multi_user = FALSE > > > before execute the below query i have altered the parallel session as > > TRUE > > > UPDATE /*+ PARALLEL (REWARDS_MASTER, 2) */ > > REWARDS_MASTER > > SET BAL_FLAG = 1 > > Where ACCOUNT_NUMBER= :V_Account_Number > > AND SCHEME_ID = :V_SCHEME_ID > > AND TXN_STATUS = 'active' > > AND REWARD_STATUS = 'active' > > AND BAL_FLAG = 0 > > > UPDATE STATEMENT, GOAL = CHOOSE 5 1 47 > > 4/20/2009 10:29:43 AM > > CHOOSE > > UPDATE RDCS REWARDS_MASTER 4/20/2009 > > 10:29:43 AM > > INDEX RANGE SCAN RDCS ACC_SCM_TXST_RDST_BRRC 5 1 47 > > 4/20/2009 > > 10:29:43 AM ANALYZED > > > which seems to not use parallel ??? > > > Please could you suggest on this? > > > Thanks > > Daiesh > > > On Apr 16, 9:04 pm, ddf <orat...@msn.com> wrote: > > > > 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 -~----------~----~----~----~------~----~------~--~---