Maximum DOP ditentukan oleh : 1. Jumlah partition. Kalau jumlah partitions hanya 2 dan diset parallel query server-nya 6, tetap yg terpakai hanya 2.
2. parameter "parallel_max_servers" Thread (sub process - round robin) per CPU-nya ditentukan oleh "parallel_threads_per_cpu" Cepat tidaknya query, selain ditentukan oleh jumlah prosess/cpu, bisa juga dipengaruhi oleh I/O (I/O Controller & HardDisk). CMIIW Bowo --- In [email protected], "cyuniart" <[EMAIL PROTECTED]> wrote: > Terima kasih pak bowo, ada lagi yang saya mau tanyakan, > Seberapa besar peningkatan yang diperoleh jika memakai parallel query, > saya mencoba select dgn parallel query kq respon time nya beda tipis > dgn yang biasa (00:13:58.37 vs 00:14:03.35), > > select SUBSTR(kode,1,1), count(*) from trans > GROUP BY SUBSTR(kode,1,1); > > tabel partisi, DOP 2 dan index local (join 3 field), server 2 > prosessor dan memori 8G, saat itu tidak ada proses lain. Saat parallel > query, 2 cpu benar2 dipake semuanya (yg serial cuma 1 cpu yg kepake), > tapi kenapa respon time yang dihasilkan hampir bisa dikatakan sama ... > (saya coba dgn 'alter session disable parallel query' dan 'alter > session force parallel query', bener gak ya ..) > > Apakah ada settingan khusus agar parallel execution bisa mempercepat > query .. ? > o ya, untuk DOP yg dipake apakah harus sesuai dgn jumlah cpu server? > > > terima kasih banyak sebelumnya, > > Condro > > --- In [email protected], "Yulius Wibowo" <[EMAIL PROTECTED] > .> wrote: > > > > Betul, salah satu syarat utk dapat PARALLEL QUERY, adalah Full Table > > Scan > > > > Bowo > > > > --- In [email protected], "cyuniart" <[EMAIL PROTECTED]> > wrote: > > > Pak Bowo, apakah parallel query hanya bisa dipakai dalam kondisi > > full > > > table scan? explain query yang terkena index kq hasilnya seperti > > ini > > > > > > SQL> select /*+ parallel(TRANS,2) */ COUNT(*) from TRANS; > > > > > > Execution Plan > > > ---------------------------------------------------------- > > > 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1) > > > 1 0 SORT (AGGREGATE) > > > 2 1 PARTITION RANGE (ALL) > > > 3 2 INDEX (FAST FULL SCAN) OF 'TRANS_J2_I' (NON- > UNIQUE) > > > (Cost=3 Card=551937334) > > > > > > jika full table scan > > > > > > select /*+ parallel(TRANS,2) */ kode, COUNT(*) from TRANS GROUP BY > > > kode; > > > > > > Execution Plan > > > ---------------------------------------------------------- > > > 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=237481 > > > Card=551937334 Bytes=2207749336) > > > > > > 1 0 SORT* (GROUP BY) (Cost=237481 Card=551937334 > > Bytes=2207749 > > > :Q22001336) > > > > > > 2 1 SORT* (GROUP BY) (Cost=237481 Card=551937334 > > Bytes=22077 > > > :Q2200049336) > > > > > > 3 2 PARTITION RANGE* > > (ALL) > > > :Q22000 > > > 4 3 TABLE ACCESS* (FULL) OF 'TRANS' (Cost=102802 Car > : > > > Q22000d=551937334 Bytes=2207749336) > > > > > > > > > > > > 1 PARALLEL_TO_SERIAL SELECT /*+ CIV_GB */ A1.C0, > > > COUNT(SYS_OP_CSR( > > > A1.C1,0)) FROM :Q22000 A1 GROUP > > BY > > > A > > > > > > 2 PARALLEL_TO_PARALLEL SELECT /*+ PIV_GB */ A1.C0 C0, > > > SYS_OP_MSR(COU > > > NT(*)) C1 FROM (SELECT /*+ > > > NO_EXPAND > > > > > > 3 PARALLEL_COMBINED_WITH_PARENT > > > 4 PARALLEL_COMBINED_WITH_PARENT > > > > > > maaf jika saya keliru membaca plan .. masih awam pak .. > > > > > > > > > --- In [email protected], "Yulius Wibowo" > > <[EMAIL PROTECTED] > > > .> wrote: > > > > Pastikan nilai parameter parallel_max_servers nya lebih dari 0. > > > > > > > > Contoh: > > > > > > > > SQL> conn / as sysdba > > > > SQL> alter system set parallel_max_servers=4; > > > > SQL> show parameter parallel > > > > > > > > NAME TYPE VALUE > > > > ------------------------------------ ----------- ------ > > > > parallel_max_servers integer 4 > > > > > > > > SQL> conn scott/tiger > > > > > > > > SQL> set autotrace traceonly explain > > > > SQL> select /*+ PARALLEL(emp,4) */ * from emp; > > > > > > > > Execution Plan > > > > ---------------------------------------------------------- > > > > 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=16 > > > Bytes=13 > > > > 92) > > > > > > > > 1 0 PX COORDINATOR > > > > 2 1 PX SEND* (QC (RANDOM)) OF ':TQ10000' (Cost=1 > > Card=16 > > > > Byt :Q1000 > > > > es=1392) > > > > > > > > 3 2 PX BLOCK* (ITERATOR) (Cost=1 Card=16 > > > > Bytes=1392) :Q1000 > > > > 4 3 TABLE ACCESS* (FULL) OF 'EMP' (TABLE) (Cost=1 > > > > Card=1 :Q1000 > > > > 6 Bytes=1392) > > > > 2 PARALLEL_TO_SERIAL > > > > 3 PARALLEL_COMBINED_WITH_CHILD > > > > 4 PARALLEL_COMBINED_WITH_PARENT > > > > > > > > > > > > Bowo > > > > > > > > > > > > > > > > --- In [email protected], "cyuniart" <[EMAIL PROTECTED]> > > > wrote: > > > > > Selamat malam, > > > > > Agar query bisa dijalankan secara parallel apa saja ya yang > > harus > > > di > > > > > setting, saya coba jalankan skrip > > > > > > > > > > select /*+ parallel(t,2) */ * from t; > > > > > > > > > > tapi dilihat dari explain plan kq sepertinya tidak dijalankan > > > secara > > > > > parallel. Apakah parameter parallel_automatic_tuning > > > mempengaruhinya . > > > > > .(saya set true).. > > > > > > > > > > thx, > > > > > Condro -- -----------I.N.D.O - O.R.A.C.L.E--------------- Keluar: [EMAIL PROTECTED] Website: http://indo-oracle.blogspot.com ----------------------------------------------- Bergabung dengan Indonesia Thin Client User Groups, Terminal Server, Citrix, New Moon Caneveral, di: http://indo-thin.vze.com Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/indo-oracle/ <*> To unsubscribe from this group, send an email to: [EMAIL PROTECTED] <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
