Query V$UNDOSTAT for relevant time window

By Franck Pachot

.
When you have a query failing in ‘ORA-01555: snapshot too old: rollback segment number … with name … too small’ you have two things to do:

1. Convince the developer that the rollback segment is not too small because the message text comes from old versions
2. Find information about query duration, undo retention and stolen blocks statistics. This is the goal of this post

The first information comes from the alert.log where every ORA-1555 is logged with the query and the duration:

Tue Sep 29 19:32:09 2015
ORA-01555 caused by SQL statement below (SQL ID: 374686u5v0qsh, Query Duration=4626 sec, SCN: 0x0022.c823dc12):

SCN

This means that at 19:32:09 the query 374686u5v0qsh running since 18:15:03 (4626 seconds ago) wasn’t able to find the undo blocks necessary to build the consistent image as of 18:15:02. How do I know that ‘as of’ point-in-time? Usually it’s the beginning of the query, but there are cases where it can be earlier (in serializable isolation mode, flashback queries) or later (query restart for example).
It’s better to check it: we have the SCN in hexadecimal given as ‘base’ and ‘wrap’ and we can convert it to a timestamp with the following formula:

SQL> select scn_to_timestamp(to_number('0022','xxxxxxx')*power(2,32)+to_number('c823dc12','xxxxxxxxxxxxxxxxxxxxxx') ) from dual;
 
SCN_TO_TIMESTAMP(TO_NUMBER('0022','XXXXXXX')*POWER(2,32)+TO_NUMBER('C823D
-------------------------------------------------------------------------
29-SEP-15 06.15.02.000000000 PM

Note that there can be a 3 second difference from the precision of SCN_TO_TIMESTAMP.

Undo statistics

Then I want to know the undo retention:

SQL> show parameter undo_retention
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_retention                       integer     900

900 seconds means that it is possible to get ORA-1555 after one hour because blocks expire after 15 minutes.

We can check how the expired undo blocks where reused from V$UNDOSTAT but I use the following query to get only the lines that are relevant to my query (those that cover the query duration up to the ORA-1555 ) and only the non-zero columns:

select maxqueryid||' '||to_char(end_time,'hh24:mi')||' '||
rtrim(lower(''
--||decode(MAXCONCURRENCY,0,'','MAXCONCURRENCY='||MAXCONCURRENCY||' ')
||decode(UNDOBLKS,0,'','UNDOBLKS='||UNDOBLKS||' ')
||decode(ACTIVEBLKS,0,'','ACTIVEBLKS='||ACTIVEBLKS||' ')
||decode(UNEXPIREDBLKS,0,'','UNEXPIREDBLKS='||UNEXPIREDBLKS||' ')
||decode(EXPIREDBLKS,0,'','EXPIREDBLKS='||EXPIREDBLKS||' ')
||decode(TUNED_UNDORETENTION,0,'','TUNED_UNDORETENTION(hour)='||to_char(TUNED_UNDORETENTION/60/60,'FM999.9')||' ')
||decode(UNXPSTEALCNT,0,'','UNXPSTEALCNT='||UNXPSTEALCNT||' ')
||decode(UNXPBLKRELCNT,0,'','UNXPBLKRELCNT='||UNXPBLKRELCNT||' ')
||decode(UNXPBLKREUCNT,0,'','UNXPBLKREUCNT='||UNXPBLKREUCNT||' ')
||decode(EXPSTEALCNT,0,'','EXPSTEALCNT='||EXPSTEALCNT||' ')
||decode(EXPBLKRELCNT,0,'','EXPBLKRELCNT='||EXPBLKRELCNT||' ')
||decode(EXPBLKREUCNT,0,'','EXPBLKREUCNT='||EXPBLKREUCNT||' ')
||decode(SSOLDERRCNT,0,'','SSOLDERRCNT='||SSOLDERRCNT||' ')
||decode(NOSPACEERRCNT,0,'','NOSPACEERRCNT='||NOSPACEERRCNT||' ')
)) "undostats covering ORA-1555"
 from (
select BEGIN_TIME-MAXQUERYLEN/24/60/60 SSOLD_BEGIN_TIME,END_TIME SSOLD_END_TIME from V$UNDOSTAT where SSOLDERRCNT>0
) , lateral(select * from v$undostat
 where end_time>=ssold_begin_time and begin_time<=ssold_end_time)
order by end_time;
/

Lateral join is possible in 12c, but there are other way to do the same in 11g.

Here is a sample output:

undostats covering ORA-1555
-------------------------------------------------------------------------------------------------------------
f3yfg50ga0r8n 18:14 activeblks=224  unexpiredblks=90472 expiredblks=34048 tuned_undoretention(hour)=92.2
f3yfg50ga0r8n 18:24 activeblks=736  unexpiredblks=90472 expiredblks=34560 tuned_undoretention(hour)=43.9 expstealcnt=1 expblkrelcnt=1280
f3yfg50ga0r8n 18:34 activeblks=1504 unexpiredblks=64320 expiredblks=61024 tuned_undoretention(hour)=11.4 expstealcnt=2 expblkrelcnt=14208
374686u5v0qsh 18:44 activeblks=1120 unexpiredblks=57792 expiredblks=54752 tuned_undoretention(hour)=11.4
374686u5v0qsh 18:54 activeblks=1888 unexpiredblks=74112 expiredblks=42784 tuned_undoretention(hour)=11.4
374686u5v0qsh 19:04 activeblks=864  unexpiredblks=90400 expiredblks=34816 tuned_undoretention(hour)=2.   expstealcnt=1 expblkrelcnt=9216
374686u5v0qsh 19:14 activeblks=2784 unexpiredblks=91680 expiredblks=16896 tuned_undoretention(hour)=.9
374686u5v0qsh 19:24 activeblks=1248 unexpiredblks=94232 expiredblks=3584  tuned_undoretention(hour)=.9
374686u5v0qsh 19:34 activeblks=1504 unexpiredblks=94816 expiredblks=4352  tuned_undoretention(hour)=.9   ssolderrcnt=1
f3yfg50ga0r8n 19:44 activeblks=2656 unexpiredblks=93024 expiredblks=2944  tuned_undoretention(hour)=1.

The ORA-1555 occurred where the ssolderrcnt is > 0 and we see the number of blocks stolen before – all expired in this case.
All the detail about the statistics are in the V$UNDOSTAT documentation.

There is nothing more than V$UNDOSTAT here, but that query is easier if you are on command line.