Re: Parallel Distributed CTAS

What is a pipe ? You mean network ?
My understanding is that network is not an issue.

It is how oracle handles it. Execution plans do differ for remote and local, that's true:

  SQL Statement from editor:

  create /*parallel (a 2) */ table u_kaunas.ch_acct_mast_2   parallel 2
  as select /*parallel(t 2)*/* from u_kaunas.ch_acct_mast t

  Statement Id=2 Type=PX SEND
  Cost=21708 TimeStamp=04-02-11::14::22:44

       (1)  CREATE TABLE STATEMENT  ALL_ROWS
     Est. Rows: 1.589.679  Cost: 90.585
    PX COORDINATOR
           (5)  PX SEND QC (RANDOM) SYS.:TQ10000
                Est. Rows: 1.589.679  Cost: 21.708
               (4)  LOAD AS SELECT U_KAUNAS.CH_ACCT_MAST_2
                   (3)  PX BLOCK ITERATOR
                        Est. Rows: 1.589.679  Cost: 21.708


                       (2)  TABLE TABLE ACCESS FULL U_KAUNAS.CH_ACCT_MAST

[Not Analyzed]


                       (2)   Est. Rows: 1.589.679  Cost: 21.708

                            Tablespace: STAGE_DATA

  SQL Statement from editor:

  create /*test1:1*/ table u_kaunas.ch_acct_mast   tablespace stage_data
  parallel 2
  as select /*+parallel(t 2)*/* from ch_acct_mast_at_cbrfcrB t.

  Statement Id=3 Type=LOAD AS SELECT
  Cost=0 TimeStamp=04-02-11::14::22:50

       (1)  CREATE TABLE STATEMENT  ALL_ROWS
     Est. Rows: 1.549.723  Cost: 50.892
    PX COORDINATOR
           (7)  PX SEND QC (RANDOM) SYS.:TQ10001
                Est. Rows: 1.549.723  Cost: 26.475
               (6)  LOAD AS SELECT U_KAUNAS.CH_ACCT_MAST
                   (5)  BUFFER SORT


                       (4)  PX RECEIVE

                            Est. Rows: 1.549.723  Cost: 26.475
                           (3)  PX SEND ROUND-ROBIN SYS.:TQ10000
                                Est. Rows: 1.549.723  Cost: 26.475
                               (2)  REMOTE REMOTE.CH_ACCT_MAST
                                    Est. Rows: 1.549.723  Cost: 26.475




---------------------------------------------------------------------------------

Please consider the environment before printing this e-mail

                                                                           
             Ghassan Salem                                                 
             <salem.ghassan_at_gm                                             
             ail.com>                                                   To 
                                       Laimutis.Nedzinskas_at_seb.lt          
             2011.02.04 14:13                                           cc 
                                                                           
                                                                   Subject 
                                       Re: Parallel Distributed CTAS       
                                                                           
                                                                           
                                                                           
                                                                           
                                                                           
                                                                           

Laimutis,
You've got only one pipe between the 2 databases, I mean just one session, so using parallel in this case is surely not going to help. If you have a partitioned table, try starting several sessions each reading a partition, and inserting into the corresponding one. Otherwise, you may fair better if you insert into the table (not append), using several streams.

Regards

      Thank you in advance, Liamis

      --
      http://www.freelists.org/webpage/oracle-l

--
http://www.freelists.org/webpage/oracle-l