Oracle üzerinde "ORA-01555 Snapshot Too Old" hata mesajı

Oracle performans iyileştirme danışmanı olarak çalıştığım firmada son zamanlarda ara sıra bu hata mesajı almaktayım. Bilhassa Crystal Reports kullanarak farklı veritabanlarından(SQL 2005) veri alışverişi en üst seviyelere çıktığında ve SQL iyileştirmeden bilinçsiz(!) raporlama uzmanları sayesinde Oracle 9i üzerinde ara ara olmakta.Eğer "ORA-01555 Snapshot Too Old" hata mesajı ile karşılaşırsanız, bunun sebebi genişleyen veritabanı mimarisinde undo segmentinin yetersiz büyüklükte kalması veya undo retention parametresinin değerinin düşük kalmasıdır.

Eğer undo segmentiniz yeterli büyüklükte ise bu durumda undo retention değerini alttaki sorgu sonucunda tavsiye edilen optimal değere  "ALTER SYSTEM SET UNDO_RETENTION = <optimal_undo_retention_value>" ile yükseltmeniz gerekecektir. Bir önemli nokta; veritabanınız kullanıcı ve veri miktarı olarak aşırı büyüdüğü durumlarda bu analizi ara ara yapmanızda fayda var...

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'

Undo segmentinizin Oracle 9i veritabanınca tavsiye edilen ebadını öğrenmek için ise alttaki sorguyu çalıştırabilirsiniz.

SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
       SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
       (TO_NUMBER(e.value) * TO_NUMBER(f.value) *
       g.undo_block_per_sec) / (1024*1024)
      "NEEDED UNDO SIZE [MByte]"
  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';

Bunun yanında Oracle performansı ile ilgili diğer yazılarıma http://uguroracle.blogspot.com  blog adresimden ulaşabilirsiniz.