** 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;
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:
Post a Comment