13 Replies Latest reply on Aug 27, 2019 12:40 AM by 2656205

    Parallel DDL

    user1699124

      Hi,

       

      I tried to move 1 particular LOB partition from one tablespace to another tablespace using parallel clause, but it was taking time & my understanding would be its going in serial fashion (parallel was not effect), Can you pls clarify ?

       

      I was referring some doc's, then from the execution plan "LOAD AS SELECT" comes before "Query Coordinator", So my feeling would be it went in serial not parallel. Pls correct me if I am wrong ? Thanks

       

      SQL> /
      
      
      PLAN_TABLE_OUTPUT
      ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      SQL_ID b9hry1x4scfqx
      --------------------
      alter table core.clo
      
      
      Plan hash value: 2771440782
      
      
      ----------------------------------------------------------------------------------------------------------------------------------
      | Id  | Operation              | Name       | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
      ----------------------------------------------------------------------------------------------------------------------------------
      |   0 | CREATE TABLE STATEMENT |            |       |       |   105 (100)|          |       |       |        |      |            |
      |   1 |  LOAD AS SELECT        |            |       |       |            |          |       |       |        |      |            |
      |   2 |   PX COORDINATOR       |            |       |       |            |          |       |       |        |      |            |
      |   3 |    PX SEND QC (RANDOM) | :TQ10000   |   834K|   117M|   105   (0)| 00:00:02 |       |       |  Q1,00 | P->S | QC (RAND)  |
      |   4 |     PX BLOCK ITERATOR  |            |   834K|   117M|   105   (0)| 00:00:02 |     4 |     4 |  Q1,00 | PCWC |            |
      |   5 |      TABLE ACCESS FULL | CLOB_DATA_ |   834K|   117M|   105   (0)| 00:00:02 |     4 |     4 |  Q1,00 | PCWP |            |
      ----------------------------------------------------------------------------------------------------------------------------------
      
      
      Query Block Name / Object Alias (identified by operation id):
      -------------------------------------------------------------
      
      
         1 - SEL$1
         5 - SEL$1 / CLOB_DATA_@SEL$1
      
      
      Outline Data
      -------------
      
      
        /*+
            BEGIN_OUTLINE_DATA
            IGNORE_OPTIM_EMBEDDED_HINTS
            OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
            DB_VERSION('11.2.0.4')
            OPT_PARAM('_b_tree_bitmap_plans' 'false')
            OPT_PARAM('_optim_peek_user_binds' 'false')
            OPT_PARAM('optimizer_dynamic_sampling' 0)
            OPT_PARAM('_optimizer_cost_based_transformation' 'off')
            OPT_PARAM('_optimizer_compute_index_stats' 'false')
            OPT_PARAM('_optimizer_better_inlist_costing' 'off')
            OPT_PARAM('_optimizer_extended_stats_usage_control' 255)
            OPT_PARAM('_optimizer_distinct_agg_transform' 'false')
            OPT_PARAM('_optimizer_use_feedback' 'false')
            OPT_PARAM('_fix_control' '6146906:0')
            ALL_ROWS
            OUTLINE_LEAF(@"SEL$1")
            FULL(@"SEL$1" "CLOB_DATA_"@"SEL$1")
            END_OUTLINE_DATA
        */
      

       

      Sql Statement

       

      alter session force parallel ddl parallel 64;
      alter table core.clob_data_ move partition clob_data04 tablespace NEW_D01 lob (LOB_DATA) store as (tablespace NEW_D01);
      
        • 1. Re: Parallel DDL
          Jonathan Lewis

          Your understanding is correct.

           

          Basically you read UP the diagonal to get the order of activity - this says the table is scanned in parallel, but the PX slaves then feed values to the PX Coordinator, then the PX coordinator does the (serial) load as select.


          Regards

          Jonathan Lewis

          1 person found this helpful
          • 2. Re: Parallel DDL
            user1699124

            Thanks a lot for your reply! Just to followup on this, How do i force parallel in this case ? Can you please advice ?

            • 3. Re: Parallel DDL
              Jonathan Lewis

              I don't think it's possible in 11g (but someone might correct me on that).

               

              You might be able to do something with the dbms_parallel_execute package which allows you to define how to break a table into chunks and then hand out chunks to multiple jobs. You'd have to lock the partition, create a new empty table, use the package to populate the table, then exchange the table with the partition.

               

              Regards

              Jonathan Lewis

              • 4. Re: Parallel DDL
                John Thorton

                user1699124 wrote:

                 

                Thanks a lot for your reply! Just to followup on this, How do i force parallel in this case ? Can you please advice ?

                While you may  start with parallel at the software level, reality devolves the actual writing of data onto physical disk into serial action. HDD is at least 1000 times slower than CPU cycles so the disk quickly becomes primary performance bottleneck; regardless of how many software parallel session feed the disk controller. There is only a single physical path for data to be accessed on disk & compared to accessing data in RAM by the CPU the disk access time are slow like a glacier. The task to move data from one tablespace to a different tablespace does NOT really benefit from have multiple sessions run in parallel. Data must be read serially off the source tablespace & written serially onto the target tablespace. The fact that you may force multiple parallel sessions into the data stream won't materially change the duration to complete the task. In fact PARALLEL may increase elapsed time.

                • 5. Re: Parallel DDL
                  Jonathan Lewis

                  @John Thorton

                   

                  While your thinking is so last century it does raise a couple of elements that needs to be considered - even if the OP is using solid state disc with multiple channels rather than hard disk drives and only one or two HBAs

                  • Parallel 64 does look a little optimistic - it would be a rare setup that had enough channels to support that level of concurrency.
                  • The configuration of the LOBs could make an enormous difference particularly if the OP is using basicfile LOBs because not only would there be the read and writes of the LOBs, there's also the potential for duplicating all the LOB writes to the online redo logs and then reading the online redo logs to create archived redo logs. Then there's the issue of contention on "freepools" which nobody seems to know about and use.

                   

                  Regards

                  Jonathan Lewis

                  • 6. Re: Parallel DDL
                    jaramill

                    Also per the forum guidelines on the FAQ link --> Re: 2. How do I ask a question on the forums?

                    please always answer #5 as that can determine the type answer you get:

                     

                    5) Database Version and IDE Version
                    Ensure you provide your database version number e.g. 11.2.0.3 so that we know what features we can use when answering.

                     

                    If you're not sure what it is you can do the following:

                    select * from v$version;

                    in an SQL*Plus session and paste the results.

                    • 7. Re: Parallel DDL
                      user1699124

                      Sorry for delay in reply ! Just curious is it only applicable to LOB's (Basicfile) ? because when i tried to do the move for normal table & also for IOT its going in parallel as per execution plan. Also i noticed the drastic change in timing as well. Pls refer below

                       

                      Sql Stmt & execution plan for normal table partition move

                       

                      alter session force parallel ddl parallel 64;
                      alter table core.custom_entity_data_ movie partition custom_entity_data05 tablespace NEW_D01;
                      

                       

                      SQL> /
                      Enter value for a: gwx9guaan8wgr
                      old  1: SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(SQL_ID=>'&a',FORMAT=>'ADVANCED'))
                      new  1: SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(SQL_ID=>'gwx9guaan8wgr',FORMAT=>'ADVANCED'))
                      
                      
                      PLAN_TABLE_OUTPUT
                      ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                      SQL_ID  gwx9guaan8wgr, child number 0
                      -------------------------------------
                      alter table core.cus
                      
                      
                      Plan hash value: 24323450
                      
                      
                      -------------------------------------------------------------------------------------------------------------------------------------------
                      | Id  | Operation              | Name                | Rows  | Bytes | Cost (%CPU)| Time    | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
                      -------------------------------------------------------------------------------------------------------------------------------------------
                      |  0 | CREATE TABLE STATEMENT |                    |      |      |  1249 (100)|          |      |      |        |      |            |
                      |  1 |  PX COORDINATOR        |                    |      |      |            |          |      |      |        |      |            |
                      |  2 |  PX SEND QC (RANDOM)  | :TQ10000            |  1391K|  1386M|  1249  (2)| 00:00:15 |      |      |  Q1,00 | P->S | QC (RAND)  |
                      |  3 |    LOAD AS SELECT      |                    |      |      |            |          |      |      |  Q1,00 | PCWP |            |
                      |  4 |    PX BLOCK ITERATOR  |                    |  1391K|  1386M|  1249  (2)| 00:00:15 |    6 |    6 |  Q1,00 | PCWC |            |
                      |*  5 |      TABLE ACCESS FULL | CUSTOM_ENTITY_DATA_ |  1391K|  1386M|  1249  (2)| 00:00:15 |    6 |    6 |  Q1,00 | PCWP |            |
                      -------------------------------------------------------------------------------------------------------------------------------------------
                      
                      
                      Query Block Name / Object Alias (identified by operation id):
                      -------------------------------------------------------------
                      
                      
                        1 - SEL$1
                        5 - SEL$1 / CUSTOM_ENTITY_DATA_@SEL$1
                      
                      
                      Outline Data
                      -------------
                      
                      
                        /*+
                            BEGIN_OUTLINE_DATA
                            IGNORE_OPTIM_EMBEDDED_HINTS
                            OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
                            DB_VERSION('11.2.0.4')
                            OPT_PARAM('_b_tree_bitmap_plans' 'false')
                            OPT_PARAM('_optim_peek_user_binds' 'false')
                            OPT_PARAM('optimizer_dynamic_sampling' 0)
                            OPT_PARAM('_optimizer_cost_based_transformation' 'off')
                            OPT_PARAM('_optimizer_compute_index_stats' 'false')
                            OPT_PARAM('_optimizer_better_inlist_costing' 'off')
                            OPT_PARAM('_optimizer_extended_stats_usage_control' 255)
                            OPT_PARAM('_optimizer_distinct_agg_transform' 'false')
                            OPT_PARAM('_optimizer_use_feedback' 'false')
                            OPT_PARAM('_fix_control' '6146906:0')
                            ALL_ROWS
                            OUTLINE_LEAF(@"SEL$1")
                            FULL(@"SEL$1" "CUSTOM_ENTITY_DATA_"@"SEL$1")
                            END_OUTLINE_DATA
                        */
                      

                       

                      Sql & execution plan for iot overflow

                       

                      alter session force parallel ddl parallel 64;
                      alter table core.name_denorm_ movie partition name_denorm01 tablespace NEW_D01 overflow tablespace NEW_D01;
                      

                       

                      SQL> /
                      Enter value for a: 8z8wm5mqmmyb8
                      old   1: SELECT * FROM table(DBMS_XPLAN.DISPLAY_AWR(SQL_ID=>'&a',FORMAT=>'ADVANCED'))
                      new   1: SELECT * FROM table(DBMS_XPLAN.DISPLAY_AWR(SQL_ID=>'8z8wm5mqmmyb8',FORMAT=>'ADVANCED'))
                      SQL_ID 8z8wm5mqmmyb8
                      --------------------
                      alter table CORE.NAM
                      
                      
                      Plan hash value: 2274906226
                      
                      
                      ---------------------------------------------------------------------------------------------------------------------------------------
                      | Id  | Operation                | Name          | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
                      ---------------------------------------------------------------------------------------------------------------------------------------
                      |   0 | CREATE TABLE STATEMENT   |               |       |       |  2754 (100)|          |       |       |        |      |            |
                      |   1 |  PX COORDINATOR          |               |       |       |            |          |       |       |        |      |            |
                      |   2 |   PX SEND QC (RANDOM)    | :TQ10000      |  5828K|   483M|  2754   (1)| 00:00:34 |       |       |  Q1,00 | P->S | QC (RAND)  |
                      |   3 |    LOAD AS SELECT        |               |       |       |            |          |       |       |  Q1,00 | PCWP |            |
                      |   4 |     PX BLOCK ITERATOR    |               |  5828K|   483M|  2754   (1)| 00:00:34 |     1 |     1 |  Q1,00 | PCWC |            |
                      |   5 |      INDEX FAST FULL SCAN| PKNAME_DENORM |  5828K|   483M|  2754   (1)| 00:00:34 |     1 |     1 |  Q1,00 | PCWP |            |
                      ---------------------------------------------------------------------------------------------------------------------------------------
                      
                      
                      Query Block Name / Object Alias (identified by operation id):
                      -------------------------------------------------------------
                      
                      
                         1 - SEL$1
                         5 - SEL$1 / NAME_DENORM_@SEL$1
                      
                      
                      Outline Data
                      -------------
                      
                      
                        /*+
                            BEGIN_OUTLINE_DATA
                            IGNORE_OPTIM_EMBEDDED_HINTS
                            OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
                            DB_VERSION('11.2.0.4')
                            OPT_PARAM('_b_tree_bitmap_plans' 'false')
                            OPT_PARAM('_optim_peek_user_binds' 'false')
                            OPT_PARAM('optimizer_dynamic_sampling' 0)
                            OPT_PARAM('_optimizer_cost_based_transformation' 'off')
                            OPT_PARAM('_optimizer_compute_index_stats' 'false')
                            OPT_PARAM('_optimizer_better_inlist_costing' 'off')
                            OPT_PARAM('_optimizer_extended_stats_usage_control' 255)
                            OPT_PARAM('_optimizer_distinct_agg_transform' 'false')
                            OPT_PARAM('_optimizer_use_feedback' 'false')
                            OPT_PARAM('_fix_control' '6146906:0')
                            ALL_ROWS
                            OUTLINE_LEAF(@"SEL$1")
                            INDEX_FFS(@"SEL$1" "NAME_DENORM_"@"SEL$1" ("NAME_DENORM_"."ORGANIZATION_ID" "NAME_DENORM_"."ENTITY_ID"))
                            END_OUTLINE_DATA
                        */
                      
                      • 8. Re: Parallel DDL
                        Jonathan Lewis

                        It's about the LOBs and the Oracle version.

                         

                        Without running up a couple of tests (or hunting through all the right manuals) I can't claim the following is correct, but from memory:

                        11g - Securefile and Basicfile LOBs serialize

                        12.2 - Basicfile LOBs still serialize, but Securefile LOBs allow for parallel rebuilds.

                         

                        If you're going to do the move and you're still using basicfile I'd consider converting to securefile at the same time (though I'd do a bit of testing and a little research for known bugs beforehand).

                         

                        Regards

                        Jonathan Lewis

                        • 9. Re: Parallel DDL
                          jaramill

                          What is your DB version (as I asked earlier)?  post it or update your original post with that information.

                          • 10. Re: Parallel DDL
                            Jonathan Lewis

                            @jaramill

                             

                             

                            While it's not conclusive proof, the original post includes the outline information which include db_verions('11.2.0.4').

                             

                             

                            Regards

                            Jonathan Lewis

                            • 11. Re: Parallel DDL
                              jaramill

                              Jonathan Lewis wrote:

                               

                              @jaramill

                               

                               

                              While it's not conclusive proof, the original post includes the outline information which include db_versions('11.2.0.4').

                               

                               

                              Regards

                              Jonathan Lewis

                              Thanks Jonathan. I actually did see that in the text output but just for the general public that may not spot it.

                              • 12. Re: Parallel DDL
                                Jonathan Lewis

                                jaramill wrote:

                                 

                                Thanks Jonathan. I actually did see that in the text output but just for the general public that may not spot it.

                                 

                                Good point,

                                When I wrote:

                                I don't think it's possible in 11g (but someone might correct me on that).

                                I didn't occur to me to point out (a) that I was confident the OP was probably using 11.2.0.4, and (b) what I had seen that gave me that confidence.  I do sometimes manage to start with comments like: "I see from the Outline Information that ...", but I often forget that many people on the forum haven't had the experience of picking up little details like that.

                                 

                                Regards

                                Jonathan Lewis

                                • 13. Re: Parallel DDL
                                  2656205

                                  Sorry for the confusion ! db version is 11.2.0.4