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
-~----------~----~----~----~------~----~------~--~---
