terima kasih pak, saya masih bingung tentang hal undo_retention seperti yang
bapak jelaskan.
saya lihat2 diinternet, saya coba tampilkan hasilnya
SELECT SUM(a.bytes) "UNDO_SIZE"
FROM v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
AND a.ts# = b.ts#;
hasil = 83886080
SELECT TO_NUMBER(value) "DB_BLOCK_SIZE [KByte]"
FROM v$parameter
WHERE name = 'db_block_size';
hasil = 8192
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
"UNDO_BLOCK_PER_SEC"
FROM v$undostat;
hasil = 3.17666666666667
SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
ROUND((d.undo_size / (to_number(f.value) *
g.undo_block_per_sec))) "OPTIMAL UNDO RETENTION [Sec]"
FROM (
SELECT SUM(a.bytes) undo_size
FROM v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
AND a.ts# = b.ts#
) d,
v$parameter e,
v$parameter f,
(
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
undo_block_per_sec
FROM v$undostat
) g
WHERE e.name = 'undo_retention'
AND f.name = 'db_block_size'
ACTUAL UNDO SIZE UNDO RETENTION [Sec] OPTIMAL UNDO RETENTION [Sec]
80 900 3224
NEEDED UNDO SIZE [MByte]
22.3359375
apakah undo_retention harus saya samakan dengan optimal undo retention (3224),
bagaimana caranya.
apakah ada hal2 yang lain, mohon bantuannya.
tks
--- In [email protected], defri afrian <def_ria...@...> wrote:
>
> Coba cek parameter undo_retention apakah sudah optimal dengan kondisi data
> dan transaksi yang sekarang ?
> Dan cek juga tablespace undo, apakah sudah optimal atau masih perlu
> ditambahkan lagi sizenya
>
> Regards,
> Defry
>
