Karena diskusi ini sudah semakin tidak terbaca secara historical...tanggapan saya taruh di
http://www.indo-oracletech.com/forum/viewtopic.php?f=4&t=307&p=1582#p1582 Siilakan di baca pak Ujang :) 2008/9/24 Ujang Jaenudin <[EMAIL PROTECTED]> > 1. google saja DSI401 (dumps, crashes, and corruption) > 2. dbwr bisa memerintahkan lgwr untuk flush log buffer (dbwr write > ahead logging), kejadiannya bisa pas checkpoint, > atau pada saat free buffer inspected, etc > 3. pelaksana flushing tetep si lgwr, > > hm....saya kalo baca article si tom kok rasanya gak bisa secara mudah > diterima secara harfiah saja, butuh another way to prove it :) > bahkan kadang2 musti buka kamus basic arsitektur oracle :) > > > -- > thanks and regards > ujang | oracle dba > jakarta | http://ora62.wordpress.com > > On Tue, Sep 23, 2008 at 9:56 PM, Yoel Susanto <[EMAIL > PROTECTED]<yoel.susanto%40gmail.com>> > wrote: > > 1. hmmm menarik, bisa bantu saya untuk mengerti analisa anda dari hasil > test > > case itu pak? > > bagian mana yang menunjukan pada anda bahwa anda penambahan redo di redo > > file saat anda melakukan rollback. > > > > 2. anda bilang > > > > *rollback -> flush log buffer > > > > pada bagian ini sepertinya match : > > "rollback is buffered in the cache just like data blocks are. > > the rollback blocks are written into the buffer cache and if needed, > > dbwr will write them to disk - > > into the rollback segments on disk. "* > > > > - Which process flushes log buffer sih pak? DBWR atau LGWR? :) > > - Saya yakin rollback yang di maksud Tom disini adalah rollback blocks > not > > rollback event/execution. > > rollback segments juga bukan nama lain dari redo log file khan. > > > > 2008/9/23 Ujang Jaenudin <[EMAIL PROTECTED]<ujang.jaenudin%40gmail.com> > > > > > >> :) > >> mengenai log buffer space, ya....penyebabnya 2 itu.... > >> saya asumsikan "log buffer space" wait event ini berkorelasi dgn > >> statistic "redo log space requests". > >> > >> rollback -> flush log buffer > >> > >> pada bagian ini sepertinya match : > >> "rollback is buffered in the cache just like data blocks are. > >> the rollback blocks are written into the buffer cache and if needed, > >> dbwr will write them to disk - > >> into the rollback segments on disk. " > >> > >> untuk membuktikannya, saya coba test case di pc berikut ini: > >> > >> select * from v$log; > >> -- current redolog adalah group#1 > >> > >> select member from v$logfile; > >> > >> alter session set tracefile_identifier = TRACE_REDOLOG; > >> > >> alter system dump logfile 'E:\ORADATA\ORA10\REDO01.LOG'; > >> > >> ----- Redo read statistics for thread 1 ----- > >> Read rate (ASYNC): 8292Kb in 9.75s => 0.83 Mb/sec > >> Total physical reads: 12288Kb > >> Longest record: 11Kb, moves: 0/19413 (0%) > >> Change moves: 10634/35530 (29%), moved: 3Mb > >> Longest LWN: 600Kb, moves: 2/1914 (0%), moved: 0Mb > >> Last redo scn: 0x0000.c3f3c723 (3287533347) > >> ---------------------------------------------- > >> > >> begin > >> for i in (select dcid from cc.dialcodes) loop > >> update cc.dialcodes set dctext='wwwwwwwwwwwwwwwwwwwwwwwwwwww' where > >> dcid=i.dcid; > >> end loop; > >> rollback; > >> end; > >> > >> alter system dump logfile 'E:\ORADATA\ORA10\REDO01.LOG'; > >> > >> ----- Redo read statistics for thread 1 ----- > >> Read rate (ASYNC): 9496Kb in 10.73s => 0.86 Mb/sec > >> Total physical reads: 12288Kb > >> Longest record: 11Kb, moves: 0/22245 (0%) > >> Change moves: 12217/41052 (29%), moved: 3Mb > >> Longest LWN: 602Kb, moves: 2/1995 (0%), moved: 0Mb > >> Last redo scn: 0x0000.c3f3c92e (3287533870) > >> ---------------------------------------------- > >> > >> begin > >> for i in (select dcid from cc.dialcodes) loop > >> update cc.dialcodes set dctext='wwwwwwwwwwwwwwwwwwwwwwwwwwww' where > >> dcid=i.dcid; > >> end loop; > >> commit; > >> end; > >> > >> alter system dump logfile 'E:\ORADATA\ORA10\REDO01.LOG'; > >> > >> ----- Redo read statistics for thread 1 ----- > >> Read rate (ASYNC): 9887Kb in 11.79s => 0.82 Mb/sec > >> Total physical reads: 12288Kb > >> Longest record: 11Kb, moves: 0/23457 (0%) > >> Change moves: 12898/43338 (29%), moved: 4Mb > >> Longest LWN: 602Kb, moves: 2/2031 (0%), moved: 0Mb > >> Last redo scn: 0x0000.c3f3c9a4 (3287533988) > >> ---------------------------------------------- > >> > >> ====================================================== > >> detail rollback: > >> > >> REDO RECORD - Thread:1 RBA: 0x000031.00004a1e.0164 LEN: 0x00f8 VLD: 0x01 > >> SCN: 0x0000.c3f3c914 SUBSCN:1036 09/23/2008 19:05:46 > >> CHANGE #1 TYP:0 CLS: 1 AFN:6 DBA:0x018000a3 OBJ:51441 > >> SCN:0x0000.c3f3c914 SEQ: 98 OP:11.5 > >> KTB Redo > >> op: 0x02 ver: 0x01 > >> op: C uba: 0x00804ba1.0055.36 > >> KDO Op code: URP row dependencies Disabled > >> xtype: XR flags: 0x00000000 bdba: 0x018000a3 hdba: 0x0180008c > >> itli: 1 ispac: 0 maxfr: 4858 > >> tabn: 0 slot: 21(0x15) flag: 0x2c lock: 0 ckix: 0 > >> ncol: 7 nnew: 1 size: 0 > >> col 5: [28] > >> 77 77 77 77 77 77 77 77 77 77 77 77 77 77 77 77 77 77 77 77 77 77 77 77 > 77 > >> 77 77 77 > >> CHANGE #2 TYP:0 CLS:36 AFN:2 DBA:0x00804ba1 OBJ:4294967295 > >> SCN:0x0000.c3f3c914 SEQ: 6 OP:5.6 > >> ktubu redo: slt: 35 rci: 54 opc: 11.1 objn: 51441 objd: 51441 tsn: 6 > >> Undo type: Regular undo Undo type: User undo done Last > >> buffer split: No > >> Tablespace Undo: No > >> 0x00000000 > >> ktuxvoff: 0x04b4 ktuxvflg: 0x0002 > >> > >> REDO RECORD - Thread:1 RBA: 0x000031.00004a1f.006c LEN: 0x00f8 VLD: 0x01 > >> SCN: 0x0000.c3f3c914 SUBSCN:1037 09/23/2008 19:05:46 > >> CHANGE #1 TYP:0 CLS: 1 AFN:6 DBA:0x018000a3 OBJ:51441 > >> SCN:0x0000.c3f3c914 SEQ: 99 OP:11.5 > >> KTB Redo > >> op: 0x02 ver: 0x01 > >> op: C uba: 0x00804ba1.0055.35 > >> KDO Op code: URP row dependencies Disabled > >> xtype: XR flags: 0x00000000 bdba: 0x018000a3 hdba: 0x0180008c > >> itli: 1 ispac: 0 maxfr: 4858 > >> tabn: 0 slot: 20(0x14) flag: 0x2c lock: 0 ckix: 0 > >> ncol: 7 nnew: 1 size: 0 > >> col 5: [28] > >> 77 77 77 77 77 77 77 77 77 77 77 77 77 77 77 77 77 77 77 77 77 77 77 77 > 77 > >> 77 77 77 > >> > >> ================================================== > >> detail commit; > >> > >> REDO RECORD - Thread:1 RBA: 0x000031.00000006.0070 LEN: 0x01e4 VLD: 0x01 > >> SCN: 0x0000.c3e3e94a SUBSCN: 6 09/19/2008 18:00:53 > >> CHANGE #1 TYP:0 CLS:32 AFN:2 DBA:0x00800231 OBJ:4294967295 > >> SCN:0x0000.c3e3e94a SEQ: 3 OP:5.1 > >> ktudb redo: siz: 88 spc: 3526 flg: 0x0022 seq: 0x0073 rec: 0x31 > >> xid: 0x0008.00c.0000018a > >> ktubu redo: slt: 12 rci: 48 opc: 11.1 objn: 9001 objd: 9001 tsn: 2 > >> Undo type: Regular undo Undo type: Last buffer split: No > >> Tablespace Undo: No > >> 0x00000000 > >> KDO undo record: > >> KTB Redo > >> op: 0x02 ver: 0x01 > >> op: C uba: 0x00800231.0073.2f > >> KDO Op code: DRP row dependencies Disabled > >> xtype: XA flags: 0x00000000 bdba: 0x00c07e0d hdba: 0x00c00bcb > >> itli: 1 ispac: 0 maxfr: 4858 > >> tabn: 0 slot: 25(0x19) > >> CHANGE #2 TYP:0 CLS: 1 AFN:3 DBA:0x00c07e0d OBJ:9001 > >> SCN:0x0000.c3e3e94a SEQ: 1 OP:11.2 > >> KTB Redo > >> op: 0x02 ver: 0x01 > >> op: C uba: 0x00800231.0073.31 > >> KDO Op code: IRP row dependencies Disabled > >> xtype: XA flags: 0x00000000 bdba: 0x00c07e0d hdba: 0x00c00bcb > >> itli: 1 ispac: 0 maxfr: 4858 > >> tabn: 0 slot: 25(0x19) size/delt: 153 > >> fb: --H-FL-- lb: 0x1 cc: 37 > >> null: > >> > >> > >> > 01234567890123456789012345678901234567890123456789012345678901234567890123456789 > >> -------N-----N-------NNNNNN--NNNN--N- > >> col 0: [ 2] c1 42 > >> col 1: [ 6] c5 07 30 19 1d 3e > >> col 2: [13] 33 6b 62 6b 67 76 74 70 70 64 6e 70 67 > >> col 3: [ 6] c5 04 06 63 60 1f > >> col 4: [ 2] c1 18 > >> col 5: [12] 54 41 42 4c 45 20 41 43 43 45 53 53 > >> col 6: [ 7] 43 4c 55 53 54 45 52 > >> col 7: *NULL* > >> col 8: [ 2] c1 17 > >> col 9: [ 3] 53 59 53 > >> col 10: [ 5] 55 53 45 52 24 > >> col 11: [ 9] 49 54 55 40 53 45 4c 24 32 > >> col 12: [ 7] 43 4c 55 53 54 45 52 > >> col 13: *NULL* > >> col 14: [ 2] c1 06 > >> col 15: [ 2] c1 07 > >> col 16: [ 2] c1 03 > >> col 17: [ 1] 80 > >> col 18: [ 2] c1 02 > >> col 19: [ 2] c1 02 > >> col 20: [ 2] c1 04 > >> col 21: *NULL* > >> col 22: *NULL* > >> col 23: *NULL* > >> col 24: *NULL* > >> col 25: *NULL* > >> col 26: *NULL* > >> col 27: [ 3] c2 54 16 > >> col 28: [ 2] c1 02 > >> col 29: *NULL* > >> col 30: *NULL* > >> col 31: *NULL* > >> col 32: *NULL* > >> col 33: [ 2] c1 02 > >> col 34: [12] 53 45 4c 24 46 35 42 42 37 34 45 31 > >> col 35: *NULL* > >> col 36: [ 7] 78 6c 09 13 12 1a 33 > >> > >> ================================================== > >> > >> kalo saya tangkap dari dump redolog tsb, even rollback akan flush ke > >> redolog :) > >> > >> > >> -- > >> thanks and regards > >> ujang | oracle dba > >> jakarta | http://ora62.wordpress.com > >> > >> 2008/9/23 Yoel Susanto <[EMAIL > >> PROTECTED]<yoel.susanto%40gmail.com><yoel.susanto% > 40gmail.com> > >> >: > >> > >> > Pak Ujang, > >> > > >> > 1. yup, kalo gak salah itu angkanya statistic, namun hal tersebut > masih > >> > berhubungan..... > >> > redo log space requests itu hanya symptom, disini saya guessing saja > >> > rootcause nya :) > >> > > >> > -- Yoel > >> > Oohh gitu... > >> > Maap saya salah tangkap kemana arah argument anda soalnya di akhir nya > >> anda > >> > tulis > >> > > >> >>> penyebab utama redo log buffer space : > >> >>> - ukuran log buffer yg terlalu kecil > >> >>> - IO tempat redolog buffer terlalu lambat > >> > > >> > Jadi saya mengira anda menyalahkan log buffer-nya. > >> > > >> > 2. tidak mutlak berlaku, tergantung transaksinya, tapi kalau batch > >> > mustinya terjadi :) > >> > > >> > >> > http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1539606184994 > >> > > >> > -- Yoel > >> > Statement yang bagian mana yang harus saya baca nih. > >> > Saya tidak melihat atau menangkap statement yang berkata/berarti : > >> > Rollback event/operation akan memflush redo data dari log buffer ke > redo > >> log > >> > file > >> > > >> > 2008/9/23 Ujang Jaenudin > >> > <[EMAIL PROTECTED] <ujang.jaenudin%40gmail.com> > <ujang.jaenudin%40gmail.com> > > >> > > >> > > >> >> > 1. TS nya tanya tentang redo log space, bukan buffer space. > >> >> > "redo log space requests" itu berhubungan dengan space di redo log > >> FILE, > >> >> > bukan buffer. > >> >> > > >> >> > >> >> yup, kalo gak salah itu angkanya statistic, namun hal tersebut masih > >> >> berhubungan..... > >> >> redo log space requests itu hanya symptom, disini saya guessing saja > >> >> rootcause nya :) > >> >> > >> >> > 2. User melakukan Rollback transaction tidak akan mem-flush redo > >> buffer > >> >> ke > >> >> > redolog. > >> >> > >> >> tidak mutlak berlaku, tergantung transaksinya, tapi kalau batch > >> >> mustinya terjadi :) > >> >> > >> >> > >> >> > >> > >> > http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1539606184994 > >> >> > >> >> > >> >> -- > >> >> thanks and regards > >> >> ujang | oracle dba > >> >> jakarta | http://ora62.wordpress.com > >> >> > >> >> > > >> >> > 2008/9/23 Ujang Jaenudin > >> >> > <[EMAIL PROTECTED] <ujang.jaenudin%40gmail.com><ujang.jaenudin% > 40gmail.com> > >> <ujang.jaenudin%40gmail.com> > >> > >> >> > > >> >> > > >> >> >> hm... untuk lebih jelas, bisa cek angka "redo buffer allocation > >> >> >> retries" apakah selalu naik? > >> >> >> > >> >> >> redo log space request memang berhubungan dgn log_buffer, > >> >> >> ada beberapa event LGWR nulis buffer dari log_buffer ke redolog: > >> >> >> - setiap 3 detik > >> >> >> - commit > >> >> >> - rollback > >> >> >> - mencapai angka _LOG_IO_SIZE (biasanya angka terkecil dari 1/3 > dari > >> >> >> log_buffer ATAU 1MB) > >> >> >> - sudah mencapai 1MB > >> >> >> - ketika DBWR melakukan "write ahead logging" > >> >> >> > >> >> >> jika memang transaksinya batch dan commit per ribuan records, > >> >> >> kemungkinan belum sampai 3 detik log buffer sudah terisi > 1MB > atau > >> >> >> mungkin melebihi 10MB :), cek dgn query ini untuk mengetahui > average > >> >> >> sekali LGWR nulis dari log buffer ke redolog: > >> >> >> > >> >> >> select round((a.value / b.value) + 0.5,0) as > >> >> >> avg_redo_blks_per_write, > >> >> >> round((a.value / b.value) + 0.5,0) * c.lebsz as avg_io_size > >> >> >> from v$sysstat a, v$sysstat b, x$kccle c > >> >> >> where c.lenum = 1and a.name = 'redo blocks written' > >> >> >> and b.name = 'redo writes'; > >> >> >> > >> >> >> disisi lain redolog berukuran 20MB saja, dimana secara teori, LGWR > >> >> >> akan melakukan penulisan tidak sampai exactly 20M, tapi kira2 90% > >> saja > >> >> >> kalo gak lupa :), karena anda punya 4 group maka probability > >> transaksi > >> >> >> hanya akan survive sampai sekitar 72MB saja, jika sekali > >> >> >> commit-batch > >> >> >> transaksi anda melebihi 72MB, kemungkinan besar stuck atau log > >> >> >> buffer > >> >> >> space karena redo entries sebelumnya belum bisa dioverwrite karena > >> >> >> untuk kebutuhan instance recovery jika memang ada instance crash. > >> >> >> sehingga redolog size perlu diperhatikan disini.... > >> >> >> karena log buffer space bisa terjadi setelah log switch. > >> >> >> > >> >> >> penyebab utama redo log buffer space : > >> >> >> - ukuran log buffer yg terlalu kecil > >> >> >> - IO tempat redolog buffer terlalu lambat > >> >> >> > >> >> >> -- > >> >> >> thanks and regards > >> >> >> ujang | oracle dba > >> >> >> jakarta | http://ora62.wordpress.com > >> >> >> > >> >> >> 2008/9/23 Ivan Wahyudi <[EMAIL PROTECTED] > >> >> >> <ivan43x%40yahoo.com><ivan43x% > 40yahoo.com> > >> >> >> <ivan43x%40yahoo.com><ivan43x% > >> >> 40yahoo.com>>: > >> >> >> > >> >> >> > Database kami (9i) tidak dalam archive mode. > >> >> >> > pada saat itu sudah saya check tidak ada 'Incomplete > checkpoint'. > >> >> >> > kemudian angka dibawah ini bukanlah current value. > >> >> >> > *)redo log space requests = 47 > >> >> >> > *)redo log space wait time =58 > >> >> >> > saat transaksi tesebut nilainya mengalami penambahan. Tetapi > >> setelah > >> >> >> proses > >> >> >> > selesai saat ini 'log space request=21'. > >> >> >> > > >> >> >> > NB: kami melakukan input data dalam bentuk batch pada aplikasi, > >> jadi > >> >> >> > saat > >> >> >> > proses Save akan terjadi commit record dgn jumlah ribuan. > >> >> >> > > >> >> >> > apa yang sebaiknya saya lakukan pd DB ketika terjadi input data > >> >> >> > dlm > >> >> >> bentuk > >> >> >> > batch tersebut..? > >> >> >> > > >> >> >> > kemudian bagaimana cara mengatasi redo log space requests yang > >> terus > >> >> >> > meningkat pd transaksi OLTP..? > >> >> >> > > >> >> >> > NB: kami menggunakan Apps11i. > >> >> >> > > >> >> >> > Thanks, > >> >> >> > ~Ivan~ > >> >> >> > > >> >> >> > Yoel Susanto <[EMAIL PROTECTED] > >> >> >> > <yoel.susanto%40gmail.com><yoel.susanto% > 40gmail.com> > >> >> >> > <yoel.susanto%40gmail.com><yoel.susanto% > >> >> 40gmail.com>> wrote: > >> >> >> Kepada: > >> >> >> > [email protected] > >> >> >> > <indo-oracle%40yahoogroups.com><indo-oracle% > 40yahoogroups.com> > >> >> >> > <indo-oracle%40yahoogroups.com><indo-oracle% > >> >> 40yahoogroups.com> > >> >> >> > Dari: "Yoel Susanto" > >> >> >> > <[EMAIL PROTECTED] <yoel.susanto%40gmail.com><yoel.susanto% > 40gmail.com><yoel.susanto% > >> 40gmail.com><yoel.susanto% > >> >> 40gmail.com>> > >> >> >> > Tanggal: Mon, 22 Sep 2008 17:47:12 +0800 > >> >> >> > Topik: Re: [indo-oracle] redo log space requests > >> >> >> > > >> >> >> > naikan size redo log anda pak...20M terlalu kecil untuk system > >> anda. > >> >> >> > coba raise it to 100M > >> >> >> > > >> >> >> > 2008/9/20 Ivan Wahyudi <[EMAIL PROTECTED] > >> >> >> > <ivan43x%40yahoo.com><ivan43x% > 40yahoo.com> > >> >> >> > <ivan43x%40yahoo.com><ivan43x% > >> >> 40yahoo.com>> > >> >> >> > > >> >> >> >> Dear Master > >> >> >> >> > >> >> >> >> Kondisi Database saya saat ini menunjukan nilai, > >> >> >> >> *)redo log space requests = 47 > >> >> >> >> *)redo log space wait time =58 > >> >> >> >> > >> >> >> >> dengan alokasi memory : > >> >> >> >> buffer_cache = 200m > >> >> >> >> log_buffer = 10m > >> >> >> >> redo log files =4 group, 2 logfiles masing-masing=20m, total 8 > >> >> logfiles > >> >> >> >> > >> >> >> >> Buffer Chache Hit Ratio= 90% - 99% > >> >> >> >> > >> >> >> >> Dengan nilai Hit ratio tersebut seharusnya saya tidak perlu > >> khawatir > >> >> >> tapi > >> >> >> >> yang terjadi adalah > >> >> >> >> 1 ) mengapa proses Saving data memakan waktu cukup lama ? > >> >> >> >> 2) mengapa "redo log space requests" sangat tinggi..? > >> >> >> >> apakah nilai log_buffer kurang?? > >> >> >> >> > >> >> >> >> Mohon advice dan petunjuknya. > >> >> >> >> > >> >> >> >> > >> >> >> >> Note : melakukan entry data ribuan records melalui Appl. > >> >> >> >> > >> >> >> >> Terimakasih, > >> >> >> >> ~Ivan~ > > -- Cheers, Yoel Susanto www.indo-oracletech.com [Non-text portions of this message have been removed]
