On Apr 20, 6:05 am, Daiesh <mcavenkad...@gmail.com> wrote:
> 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 |
>
> ...
>
> read more »- Hide quoted text -
>
> - Show quoted text -
Please read my original response again as it appears your table/index
statistics are not current. Compute/estimate statistics for the
object in question then run the update again. I expect you'll see a
different result.
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
-~----------~----~----~----~------~----~------~--~---
