:)
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]>:
> 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]>
>
>> > 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>
>> >
>> >
>> >> 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>>:
>> >>
>> >> > 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>> wrote:
>> >> Kepada:
>> >> > [email protected]
>> >> > <indo-oracle%40yahoogroups.com><indo-oracle%
>> 40yahoogroups.com>
>> >> > Dari: "Yoel Susanto"
>> >> > <[EMAIL PROTECTED]<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>>
>> >> >
>> >> >> 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
>>
>>
