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]> 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]> > >> :) >> 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> >> >: >> >> > 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> >> > >> > >> >> > 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> >> >> >> > >> >> > >> >> >> 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>>: >> >> >> >> >> >> > 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>> wrote: >> >> >> Kepada: >> >> >> > [email protected] <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>> >> >> >> > 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>> >> >> >> > >> >> >> >> 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~
