Re: Parallel Distributed CTAS

Hi Ghassan


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@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@gm                                             
             ail.com>                                                   To 
                                       Laimutis.Nedzinskas@xxxxxx          
             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

Other related posts: