terima kasih atas supportnya Pak Bowo, parallelmax server = 5, dopnya gak pake, smua tabel=1, trus saya jalankan setting dop untuk beberapa table yang aktif(datanya banyak dan banyak user yang akses ketable tersebut (hanya 4 table parallel 4)) untuk sort_area_size = 500000 sort_area_retained_size = 500000 apakah sort area yang besar juga berpengaruh terhadap lambat/cepatnya proses???
ini hasil saya cek pararelnya, SQL> show parameter cpu; NAME TYPE VALUE ------------------------------------ ------- ------------------ cpu_count integer 2 parallel_threads_per_cpu integer 2 SQL> show parameter parall; NAME TYPE VALUE ------------------------------------ ------- ------------------ fast_start_parallel_rollback string LOW optimizer_percent_parallel integer 0 parallel_adaptive_multi_user boolean FALSE parallel_automatic_tuning boolean FALSE parallel_broadcast_enabled boolean FALSE parallel_execution_message_size integer 2148 parallel_instance_group string parallel_max_servers integer 5 parallel_min_percent integer 0 parallel_min_servers integer 0 parallel_server boolean FALSE NAME TYPE VALUE ------------------------------------ ------- ------------------ parallel_server_instances integer 1 parallel_threads_per_cpu integer 2 recovery_parallelism integer 0 SQL> SQL> select table_name, degree from user_ta TABLE_NAME DEGREE ------------------------------ ---------- A01 1 A_ADM 1 BG_KAS 1 BG_TB 1 BG_TB1 1 BO_AGEN 1 BO_BARANG 1 BO_BARANG1 1 BO_DEPOT 1 BO_SUPPLIER 1 B_AGEN 1 TABLE_NAME DEGREE ------------------------------ ---------- B_AGEN1 1 B_BONUS 1 B_BONUS1 1 B_BONUS2 1 B_BONUS3 1 B_BONUS4 1 B_BONUS5 1 B_BONUS_LOG 1 B_BONUS_P 1 B_BONUS_STATUS 1 B_DEPOT 1 TABLE_NAME DEGREE ------------------------------ ---------- B_DEPOT1 1 B_GENERATE 1 B_INCOME_COST 1 B_KOREKSI 1 B_KOREKSI_BACKUP 1 B_OTHER 1 B_OTHER_BACKUP 1 B_TAX 1 D_SO_SAMPLE 1 H_BARANG_BELI 1 M_AGAMA 1 TABLE_NAME DEGREE ------------------------------ ---------- M_AGEN 1 M_AGEN1 1 M_AGEN1_BACKUP 1 M_AGEN2 1 M_AGEN21 1 M_AGEN2_BACKUP 1 M_AGEN_AUDIT 1 M_AGEN_BACKUP 1 M_AGEN_STRAINER 1 M_AGEN_STRAINER1 1 M_AREA 1 TABLE_NAME DEGREE ------------------------------ ---------- M_BANK 1 M_BANK_OWNER 1 M_BARANG 1 M_BARANG_CATALOG 1 M_BONUS 1 M_CATEGORY 1 M_CC 1 M_CFG_APPL 1 M_COA 1 M_DEPOT 1 M_DEPOT_AGEN 1 TABLE_NAME DEGREE ------------------------------ ---------- M_DEPOT_FEE 1 M_DEPOT_FEE2 1 M_DEPT 1 M_DEPT1 1 M_DEPT11 1 M_DEPT111 1 M_DISCOUNT 1 M_DIV 1 M_DOC 1 M_DOC1 1 M_EXIM 1 TABLE_NAME DEGREE ------------------------------ ---------- M_GENDER 1 M_GOL 1 M_KIRIM 1 M_KIRIM1 1 M_MATERIAL 1 M_MATERIAL1 1 M_MATERIAL11 1 M_MONTH 1 M_MSG_ERR 1 M_OTHER 1 M_PESANAN 1 TABLE_NAME DEGREE ------------------------------ ---------- M_PESANAN1 1 M_PESANAN2 1 M_PKP 1 M_POINT_CONVERSI 1 M_POSISI 1 M_POSISI1 1 M_POSISI2 1 M_RABAT 1 M_RAK 1 M_RAK1 1 M_REPORT 1 TABLE_NAME DEGREE ------------------------------ ---------- M_REPORT1 1 M_REPORT11 1 M_RPJT 1 M_RPJT1 1 M_RULE_BONUS 1 M_RULE_FEE 1 M_RULE_FEE1 1 M_RULE_KOMISI 1 M_RULE_KOMISI1 1 M_RULE_PARALEL 1 M_RULE_RABAT 1 TABLE_NAME DEGREE ------------------------------ ---------- M_RULE_REKRUT 1 M_RULE_ROYAL 1 M_RULE_ROYAL1 1 M_RULE_STRAINER 1 M_RULE_STRAINER1 1 M_RULE_STRAINER11 1 M_SIZE 1 M_SIZE_PACK 1 M_SUPPLIER 1 M_TGT_MARKET 1 M_UPLINE 1 TABLE_NAME DEGREE ------------------------------ ---------- M_WILAYAH 1 REF_BOBOT 1 REF_FASILITATOR 1 REF_PERIODE 1 REF_USER 1 RPT_SCRATCH 1 S_AGEN 1 S_AGEN_BACKUP 1 S_BARANG 1 S_BARANG1 1 S_BARANG2 1 TABLE_NAME DEGREE ------------------------------ ---------- S_DEPOT 1 S_SUPPLIER 1 S_TR_BARANG 1 TB_CN 1 TB_CN1 1 TB_DN 1 TB_DN1 1 TB_DN2 1 TB_IMPORT 1 TB_IMPORT1 1 TB_IMPORT11 1 TABLE_NAME DEGREE ------------------------------ ---------- TB_PAY 1 TB_PAY1 1 TB_PO 1 TB_PO1 1 TB_PO2 1 TB_RECEIVING 1 TB_RECEIVING1 1 TB_RETUR 1 TB_RETUR1 1 TB_RETUR2 1 TB_UM 1 TABLE_NAME DEGREE ------------------------------ ---------- TB_UM_REFUND 1 TG_CEK 1 TG_CEK1 1 TG_KASBANK 1 TG_KASBANK1 1 TG_KASBANK1_BACKUP 1 TG_KASBANK_BACKUP 1 TG_LEDGER 1 TG_UMUM 1 TG_UMUM1 1 TI_BAHAN 1 TABLE_NAME DEGREE ------------------------------ ---------- TI_BAHAN1 1 TI_BLOCK 1 TI_LOCK 1 TI_LOCK1 1 TI_MINUS 1 TI_MINUS1 1 TI_MINUS2 1 TI_OPNAME 1 TI_OPNAME1 1 TI_OPNAME2 1 TI_RAK 1 TABLE_NAME DEGREE ------------------------------ ---------- TI_RAK1 1 TI_RAK2 1 TI_RAK21 1 TI_RECEIVING 1 TI_RECEIVING1 1 TI_UBAH 1 TI_UBAH1 1 TI_UBAH2 1 TN_REFUND 1 TN_RETUR 1 TN_RETUR1 1 TABLE_NAME DEGREE ------------------------------ ---------- TN_SO 1 TN_SO1 1 TN_SO11 1 TN_SO2 1 TN_UM 1 TRN_DETAILSTOCK 1 TRN_HITBONUS 1 TS_DEPOT 1 TS_DEPOT1 1 TS_FEE 1 TS_REFUND 1 TABLE_NAME DEGREE ------------------------------ ---------- TS_RETUR 1 TS_RETUR1 1 TS_SO 1 TS_SO1 1 TS_SO11 1 TS_SO2 1 TS_SO_1 1 TS_STOK 1 TS_STOK1 1 TS_UM 1 T_DEPOT 1 TABLE_NAME DEGREE ------------------------------ ---------- T_DEPOT1 1 T_OPERATOR 1 T_POINT 1 W_AGEN 1 W_AGEN1 1 W_BARANG_KOREKSI 1 W_BARANG_NAMA 1 W_BONUS 1 W_BONUS5 1 W_BONUS_D 1 W_B_BONUS 1 TABLE_NAME DEGREE ------------------------------ ---------- W_DUMMY 1 W_DUMMY1 1 W_GENERATE_ID 1 W_PJG 1 W_PO 1 W_REPORT_GL 1 W_RLJ 1 W_RPJT 1 W_RPJT_P 1 W_SALES 1 W_TS_SO2 1 TABLE_NAME DEGREE ------------------------------ ---------- Z_ENABLED_ROLE 1 Z_FORM_ROLE 1 Z_GLUON 1 Z_GLUON_ 1 Z_ROLE_LIST 1 Z_ROLE_PRIVS 1 226 rows selected. SQL> alter table w_b_bonus parallel 4; Table altered. Thanks in advance Regards Widhi --- Yulius Wibowo <[EMAIL PROTECTED]> wrote: > Untuk menjalankan proses secara parallel, anda bisa > menggunakan > beberapa cara: > 1. Terlebih dulu dengan melakukan settingan > parameter : > parallel_max_servers > > 2. Dengan melakukan settingan DOP (degree of > parallelismnya di table) > SQL> SELECT table_name, degree FROM user_table; > SQL> ALTER TABLE table_name PARALLEL 4; > SQL> SELECT table_name, degree FROM user_table; > > 3. Dengan menggunakan HINT > SQL> SELECT /*+ PARALLEL (table_name,4) */ * from > table_name; > > 4. Atau by default DOP-nya sama dengan jumlah > partisi, pada table yg > dipartisi. > > 5. ...???... Any ideas ???.... > > Untuk monitoringnya: > - V$PROCESS > - melihat EXECUTION PLAN (EXPLAIN PLAN, SQL*Plus > Autotrace, OEM ...) > > CMIIW, > > Bowo > > > > > --- In [email protected], "Ujang Jaenudin" > > <[EMAIL PROTECTED]> wrote: > > > > pak tomi, > > > > [EMAIL PROTECTED] oracle]$ uname -r > > 2.4.2-2smp > > > > SQL> show parameter cpu; > > NAME TYPE > VALUE > > ------------------------------------ ----------- > ------------------- > ----------- > > cpu_count integer 2 > > parallel_threads_per_cpu integer 2 > > > > > > SQL> show parameter parall; > > NAME TYPE > VALUE > > ------------------------------------ ----------- > ------------------- > ----------- > > fast_start_parallel_rollback string > LOW > > log_parallelism integer 1 > > parallel_adaptive_multi_user boolean > FALSE > > parallel_automatic_tuning boolean > FALSE > > parallel_execution_message_size integer > 2148 > > parallel_instance_group string > > parallel_max_servers integer 0 > > parallel_min_percent integer 0 > > parallel_min_servers integer 0 > > parallel_server boolean > FALSE > > parallel_server_instances integer 1 > > parallel_threads_per_cpu integer 2 > > recovery_parallelism integer 0 > > > > dari parameter2 di atas, untuk memonitoring bahwa > kedua procs tsb > dipakai, > > darimana ya? > > > > > > regards > > > > --ujang > > http://blog.faaza.info > > ----- Original Message ----- > > From: "Tomi Wijanto" <[EMAIL PROTECTED]> > > To: <[email protected]> > > Sent: Friday, March 10, 2006 9:47 AM > > Subject: Re: [indo-oracle] bagaimana cara manage > db oracle di > server yang > > dual procesor > > > > > > > Setau saya pemakaian prosesor itu transparan > buat > > > Oracle. Kalau dual core ya bisa dipake dua2nya > (sorry, > > > asumsi gue unix, belum pernah perhatikan yg > windows, > > > tapi bisa dilihat dari Task Manager kan?). > > > Parameter init.ora cpu_count otomatis > menyesuaikan, > > > bisa coba dicheck.. > > > > > > Kalau 1 proses Anda (yg 26 jam) berjalan lama, > karena > > > itu adalah proses serial (bukan parallel), maka > memang > > > hanya 1 cpu yg bisa dipakai. > > > > > > Mungkin Anda bisa mencoba mengubah programnya, > > > sehingga bisa submit 2 program sekaligus yg > memproses > > > data yg berbeda. Kemungkinan besar kedua cpu > akan > > > terpakai. > > > > > > regards, > > > tomi > > > > > > --- wkus77 <[EMAIL PROTECTED]> wrote: > > > > > >> halo rekan-rekan > > >> > > >> saya mohon bantuannnya donk... > > >> akhir0akhir ini server kami jalannya lambat > sekali > > >> dan > > >> kadang datanya nggak konsisten.. kalo sudah > begini > > >> biasanya saya shutdown dbnya trus jalan lagi > seperti > > >> biasa, tapi kali ini jalannya lambat sekali. > ini > > >> terjadi setelah saya jalanin suatu proses yang > > >> selesai > > >> setelah 26 jam (di server single prosesor). > > >> > > >> nah saya kami punya server 1 lagi yang dual > > >> prosesor. > > >> saya pernah testing proses yang sama tapi > hasilnya > > >> ga > > >> jauh beda. saya takut yang bekerja di server > tesebut > > >> hanya 1 prosesor trus yang 1 lagi idle. > > >> > > >> nah saya mo tanya gimana tuning oracle agar > bisa > > >> maksimal jalan di dual prosesor... > > >> > > >> > > >> terima kasih sebelumnya > > >> > > >> Widhi > > > > > > > > > > __________________________________________________ > > > Do You Yahoo!? > > > Tired of spam? Yahoo! Mail has the best spam > protection around > > > http://mail.yahoo.com > > > > > > > > > -- > > > -----------I.N.D.O - O.R.A.C.L.E--------------- > > > Keluar: [EMAIL PROTECTED] > > > Website: http://indo-oracle.lizt.org (NEW) > > > ----------------------------------------------- > > > > > > Bergabung dengan Indonesia Thin Client User > Groups, > > > Terminal Server, Citrix, New Moon Caneveral, di: > > > http://indo-thin.vze.com > > > Yahoo! Groups Links > > > > > > > > > > > > > > > > > > > > > > > > > > > Send instant messages to your online friends > http://asia.messenger.yahoo.com > > > > > > > __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- -----------I.N.D.O - O.R.A.C.L.E--------------- Keluar: [EMAIL PROTECTED] Website: http://indo-oracle.lizt.org (NEW) ----------------------------------------------- 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/
