Oracle Snapshot too old" (ORA-01555) occur/Undo space insufficient

** Myself & Oracle Note & SAP Notes
How often and when does "Snapshot too old" (ORA-01555) occur?
set linesize 140
select to_char(begin_time,'YYYY-MM-DD HH24:MI:SS') "Begin",
to_char(end_time,'YYYY-MM-DD HH24:MI:SS') "End ",
undoblks "UndoBlocks",undoblks*8/1024 "UndoBlocks(MB)”,
SSOLDERRCNT "ORA-1555",MAXQUERYLEN "Query Time(sec)",MAXQUERYLEN/60/60 "Query Time(hr)"
from v$undostat
where SSOLDERRCNT > 0;
clip_image002
 
Calculation Undo
Undospace         =UR*UPS*8k
(UR=Undo retention,UPS=Max undo blocks)


UPS=select max(undoblks/600) from v$undostat; 
 
MAX(UNDOBLKS/600) 
209.053333
----------------
UR=show parameter undo_retention
21600
-----------------
Undospace         =UR*UPS*8k
                             =(21600s)*(209 block/s)*(8k/block)=36115200Kb=34.44Gb
 
Calculate will be extend undospace to 35Gb  but PRD 30Gb (Insufficient free space for long query )
#PRD PSAPUNDO = 30Gb                    
#/oracle/PRD/sapdata2/undo_1/undo.data1      5120Mb
#/oracle/PRD/sapdata5/undo_2/undo.data2      4096Mb
#/oracle/PRD/sapdata7/undo_3/undo.data3     21504Mb

0 comments:

Loading