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/
