Find information about query duration, undo retention and stolen blocks statistics. This is the goal of this post<\/li>\n<\/ol>\n<\/p>\n
The first information comes from the alert.log where every ORA-1555 is logged with the query and the duration:<\/p>\n
Tue Sep 29 19:32:09 2015\nORA-01555 caused by SQL statement below (SQL ID: 374686u5v0qsh, Query Duration=4626 sec, SCN: 0x0022.c823dc12):<\/code><\/pre>\nSCN<\/h3>\n
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).
\nIt’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:<\/p>\n
\nSQL> select scn_to_timestamp(to_number('0022','xxxxxxx')*power(2,32)+to_number('c823dc12','xxxxxxxxxxxxxxxxxxxxxx') ) from dual;\n \nSCN_TO_TIMESTAMP(TO_NUMBER('0022','XXXXXXX')*POWER(2,32)+TO_NUMBER('C823D\n-------------------------------------------------------------------------\n29-SEP-15 06.15.02.000000000 PM\n<\/code><\/pre>\nNote that there can be a 3 second difference from the precision of SCN_TO_TIMESTAMP.<\/p>\n
Undo statistics<\/h3>\n
Then I want to know the undo retention:<\/p>\n
\nSQL> show parameter undo_retention\n \nNAME TYPE VALUE\n------------------------------------ ----------- ------------------------------\nundo_retention integer 900\n<\/code><\/pre>\n900 seconds means that it is possible to get ORA-1555 after one hour because blocks expire after 15 minutes.<\/p>\n
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:<\/p>\n
\nselect maxqueryid||' '||to_char(end_time,'hh24:mi')||' '||\nrtrim(lower(''\n--||decode(MAXCONCURRENCY,0,'','MAXCONCURRENCY='||MAXCONCURRENCY||' ')\n||decode(UNDOBLKS,0,'','UNDOBLKS='||UNDOBLKS||' ')\n||decode(ACTIVEBLKS,0,'','ACTIVEBLKS='||ACTIVEBLKS||' ')\n||decode(UNEXPIREDBLKS,0,'','UNEXPIREDBLKS='||UNEXPIREDBLKS||' ')\n||decode(EXPIREDBLKS,0,'','EXPIREDBLKS='||EXPIREDBLKS||' ')\n||decode(TUNED_UNDORETENTION,0,'','TUNED_UNDORETENTION(hour)='||to_char(TUNED_UNDORETENTION\/60\/60,'FM999.9')||' ')\n||decode(UNXPSTEALCNT,0,'','UNXPSTEALCNT='||UNXPSTEALCNT||' ')\n||decode(UNXPBLKRELCNT,0,'','UNXPBLKRELCNT='||UNXPBLKRELCNT||' ')\n||decode(UNXPBLKREUCNT,0,'','UNXPBLKREUCNT='||UNXPBLKREUCNT||' ')\n||decode(EXPSTEALCNT,0,'','EXPSTEALCNT='||EXPSTEALCNT||' ')\n||decode(EXPBLKRELCNT,0,'','EXPBLKRELCNT='||EXPBLKRELCNT||' ')\n||decode(EXPBLKREUCNT,0,'','EXPBLKREUCNT='||EXPBLKREUCNT||' ')\n||decode(SSOLDERRCNT,0,'','SSOLDERRCNT='||SSOLDERRCNT||' ')\n||decode(NOSPACEERRCNT,0,'','NOSPACEERRCNT='||NOSPACEERRCNT||' ')\n)) \"undostats covering ORA-1555\"\n from (\nselect BEGIN_TIME-MAXQUERYLEN\/24\/60\/60 SSOLD_BEGIN_TIME,END_TIME SSOLD_END_TIME from V$UNDOSTAT where SSOLDERRCNT>0\n) , lateral(select * from v$undostat\n where end_time>=ssold_begin_time and begin_time<=ssold_end_time)\norder by end_time;\n\/\n<\/code><\/pre>\nLateral join is possible in 12c, but there are other way to do the same in 11g.<\/p>\n
Here is a sample output:<\/p>\n
\nundostats covering ORA-1555\n-------------------------------------------------------------------------------------------------------------\nf3yfg50ga0r8n 18:14 activeblks=224 unexpiredblks=90472 expiredblks=34048 tuned_undoretention(hour)=92.2\nf3yfg50ga0r8n 18:24 activeblks=736 unexpiredblks=90472 expiredblks=34560 tuned_undoretention(hour)=43.9 expstealcnt=1 expblkrelcnt=1280\nf3yfg50ga0r8n 18:34 activeblks=1504 unexpiredblks=64320 expiredblks=61024 tuned_undoretention(hour)=11.4 expstealcnt=2 expblkrelcnt=14208\n374686u5v0qsh 18:44 activeblks=1120 unexpiredblks=57792 expiredblks=54752 tuned_undoretention(hour)=11.4\n374686u5v0qsh 18:54 activeblks=1888 unexpiredblks=74112 expiredblks=42784 tuned_undoretention(hour)=11.4\n374686u5v0qsh 19:04 activeblks=864 unexpiredblks=90400 expiredblks=34816 tuned_undoretention(hour)=2. expstealcnt=1 expblkrelcnt=9216\n374686u5v0qsh 19:14 activeblks=2784 unexpiredblks=91680 expiredblks=16896 tuned_undoretention(hour)=.9\n374686u5v0qsh 19:24 activeblks=1248 unexpiredblks=94232 expiredblks=3584 tuned_undoretention(hour)=.9\n374686u5v0qsh 19:34 activeblks=1504 unexpiredblks=94816 expiredblks=4352 tuned_undoretention(hour)=.9 ssolderrcnt=1\nf3yfg50ga0r8n 19:44 activeblks=2656 unexpiredblks=93024 expiredblks=2944 tuned_undoretention(hour)=1.\n<\/code><\/pre>\nThe ORA-1555 occurred where the ssolderrcnt is > 0 and we see the number of blocks stolen before – all expired in this case.
\nAll the detail about the statistics are in the V$UNDOSTAT documentation<\/a>.<\/p>\nThere is nothing more than V$UNDOSTAT here, but that query is easier if you are on command line.<\/p>\n","protected":false},"excerpt":{"rendered":"
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: Convince the developer that the rollback segment is not too small because the message text comes from old versions Find information about query duration, undo […]<\/p>\n","protected":false},"author":27,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229,59],"tags":[669,96],"type_dbi":[],"class_list":["post-5733","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","category-oracle","tag-ora-1555","tag-oracle"],"acf":[],"yoast_head":"\n
Query V$UNDOSTAT for relevant time window - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/query-vundostat-for-relevant-time-window\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Query V$UNDOSTAT for relevant time window\" \/>\n<meta property=\"og:description\" content=\"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: Convince the developer that the rollback segment is not too small because the message text comes from old versions Find information about query duration, undo […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/query-vundostat-for-relevant-time-window\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2015-09-30T14:30:55+00:00\" \/>\n<meta name=\"author\" content=\"Oracle Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Oracle Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/query-vundostat-for-relevant-time-window\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/query-vundostat-for-relevant-time-window\/\"},\"author\":{\"name\":\"Oracle Team\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"headline\":\"Query V$UNDOSTAT for relevant time window\",\"datePublished\":\"2015-09-30T14:30:55+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/query-vundostat-for-relevant-time-window\/\"},\"wordCount\":349,\"commentCount\":0,\"keywords\":[\"ORA-1555\",\"Oracle\"],\"articleSection\":[\"Database Administration & Monitoring\",\"Oracle\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/query-vundostat-for-relevant-time-window\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/query-vundostat-for-relevant-time-window\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/query-vundostat-for-relevant-time-window\/\",\"name\":\"Query V$UNDOSTAT for relevant time window - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2015-09-30T14:30:55+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/query-vundostat-for-relevant-time-window\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/query-vundostat-for-relevant-time-window\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/query-vundostat-for-relevant-time-window\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Query V$UNDOSTAT for relevant time window\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\",\"name\":\"Oracle Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"caption\":\"Oracle Team\"},\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/oracle-team\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Query V$UNDOSTAT for relevant time window - dbi Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/query-vundostat-for-relevant-time-window\/","og_locale":"en_US","og_type":"article","og_title":"Query V$UNDOSTAT for relevant time window","og_description":"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: Convince the developer that the rollback segment is not too small because the message text comes from old versions Find information about query duration, undo […]","og_url":"https:\/\/www.dbi-services.com\/blog\/query-vundostat-for-relevant-time-window\/","og_site_name":"dbi Blog","article_published_time":"2015-09-30T14:30:55+00:00","author":"Oracle Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Oracle Team","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/query-vundostat-for-relevant-time-window\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/query-vundostat-for-relevant-time-window\/"},"author":{"name":"Oracle Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"headline":"Query V$UNDOSTAT for relevant time window","datePublished":"2015-09-30T14:30:55+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/query-vundostat-for-relevant-time-window\/"},"wordCount":349,"commentCount":0,"keywords":["ORA-1555","Oracle"],"articleSection":["Database Administration & Monitoring","Oracle"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/query-vundostat-for-relevant-time-window\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/query-vundostat-for-relevant-time-window\/","url":"https:\/\/www.dbi-services.com\/blog\/query-vundostat-for-relevant-time-window\/","name":"Query V$UNDOSTAT for relevant time window - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2015-09-30T14:30:55+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/query-vundostat-for-relevant-time-window\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/query-vundostat-for-relevant-time-window\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/query-vundostat-for-relevant-time-window\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Query V$UNDOSTAT for relevant time window"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee","name":"Oracle Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","caption":"Oracle Team"},"url":"https:\/\/www.dbi-services.com\/blog\/author\/oracle-team\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/5733","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/27"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=5733"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/5733\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=5733"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=5733"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=5733"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=5733"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}