{"id":979,"date":"2005-04-25T12:00:00","date_gmt":"2005-04-25T12:00:00","guid":{"rendered":"http:\/\/orcldoug.com\/blog\/?p=979"},"modified":"2005-04-25T12:00:00","modified_gmt":"2005-04-25T12:00:00","slug":"more-on-system-managed-undo","status":"publish","type":"post","link":"http:\/\/orcldoug.com\/blog\/2005\/04\/25\/more-on-system-managed-undo\/","title":{"rendered":"More on System Managed Undo"},"content":{"rendered":"

In an earlier blog entry, I mentioned that we’d run into pretty severe undo segment corruption problems using SMU on a high-throughput database. Whilst looking for something else on Metalink, I noticed Note 301432.1<\/a> which says <\/p>\n

Symptoms<\/p>\n

Severe database performance slowdown.<\/p>\n

(Text snipped out here)<\/p>\n

Cause<\/p>\n

Large numbers of Undo Segment onlines are being performed.Undo Segment online and offline operations require Serialization on US enqueue.In transactional databases where incoming workload varies with time it is common for idle Undo Segments to be offlined (or even dropped before 9.2.0.4) by SMON after a while, only to be onlined once again during the next “wave” of incoming transactions.This repetitive offlining and onlining of Undo Segments causes performance problems due to the US enqueue operations needed.<\/p>\n

Fix<\/p>\n

Prevent SMON from offlining idle Undo Segments using event 10511.Set the following parameter in init.ora\/spfile<\/p>\n

event=”10511 trace name context forever, level 2″<\/p>\n

Then restart the database.After the first onlinining of an Undo Segment, it will never be offlined and re-onlined.So we will only get the “US” serialization once (on the first online after a db restart.)<\/p><\/blockquote>\n

<\/em>Although this note discusses the possible performance problems associated with offlining of unused segments, rather than any corruption concerns, it suggests a similar strategy of using event 10511 to prevent this activity. It makes me wonder if Oracle might consider changing the default behaviour?<\/p>\n","protected":false},"excerpt":{"rendered":"

In an earlier blog entry, I mentioned that we’d run into pretty severe undo segment corruption problems using SMU on a high-throughput database. Whilst looking for something else on Metalink, I noticed Note 301432.1 which says Symptoms Severe database performance slowdown. (Text snipped out here) Cause Large numbers of Undo Segment onlines are being performed.Undo… Continue reading More on System Managed Undo<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-979","post","type-post","status-publish","format-standard","hentry","category-uncategorized","entry"],"jetpack_featured_media_url":"","jetpack-related-posts":[{"id":978,"url":"http:\/\/orcldoug.com\/blog\/2005\/04\/25\/and-theres-more-smu-again\/","url_meta":{"origin":979,"position":0},"title":"And there’s more … (SMU again)","date":"April 25, 2005","format":false,"excerpt":"Almost immediately after posting my last comment on SMU, I noticed that the TAR for the original corruption issue has been updated with a root causeOracle Development has found the root cause of the corruption problem which crippled your database. They have reproduced the problem on an in-house database, thereby\u2026","rel":"","context":"Similar post","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":982,"url":"http:\/\/orcldoug.com\/blog\/2005\/03\/23\/system-managed-undo-problem\/","url_meta":{"origin":979,"position":1},"title":"System Managed Undo Problem","date":"March 23, 2005","format":false,"excerpt":"Tired of trying to fix my daughter's PC (that nightmare's still ongoing) and dealing with the complex emotions of Oracle professionals (that'll be me then), I thought I'd try to post something potentially useful.We use System Managed Undo (or Automatic Undo Management - your choice) on a large number of\u2026","rel":"","context":"Similar post","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":888,"url":"http:\/\/orcldoug.com\/blog\/2005\/12\/21\/i-swear-i-didnt-know\/","url_meta":{"origin":979,"position":2},"title":"I swear I didn’t know","date":"December 21, 2005","format":false,"excerpt":"I noticed the following blog as an incoming link on Statcounterhttp:\/\/oracletoday.blogspot.com\/When I went to take a look, it turns out that SQL*Net message from client had been a blog subject back in September. It's worth taking a look at another example of how this 'idle' event can be a sign\u2026","rel":"","context":"With 3 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1284,"url":"http:\/\/orcldoug.com\/blog\/2007\/06\/10\/non-reusable-space\/","url_meta":{"origin":979,"position":3},"title":"Non-reusable Space?","date":"June 10, 2007","format":false,"excerpt":"A problem cropped up at work this week and, whilst it wasn't particularly tricky and didn't take long to solve, it struck me that the story of tracking it down might be useful and you might hit the problem yourself one day.A recently-deployed Data Warehouse database at work has a\u2026","rel":"","context":"With 13 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1614,"url":"http:\/\/orcldoug.com\/blog\/2010\/10\/03\/network-events-in-ash\/","url_meta":{"origin":979,"position":4},"title":"Network Events in ASH","date":"October 3, 2010","format":false,"excerpt":"Note - using ASH and the Top Activity screen require the use of the Diagnostics Pack License.This post was prompted by yet another performance problem identified using pretty pictures and Active Session History data. Although, as you'll see, some pretty old-fashioned tools played their part too!ASH entries only exist for\u2026","rel":"","context":"With 3 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1002,"url":"http:\/\/orcldoug.com\/blog\/2006\/06\/20\/mysterious\/","url_meta":{"origin":979,"position":5},"title":"Mysterious …","date":"June 20, 2006","format":false,"excerpt":"... and quite funny. I followed an incoming link through Statcounter and arrived here.At the moment, the content is :-SQL> startup mountORACLE instance started.Total System Global Area 645922816 bytes Fixed Size 1250644 bytes Variable Size 184552108 bytes Database Buffers 457179136 bytes Redo Buffers 2940928 bytes Database mounted.SQL> alter database open;Database\u2026","rel":"","context":"With 3 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"_links":{"self":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts\/979","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/comments?post=979"}],"version-history":[{"count":0,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts\/979\/revisions"}],"wp:attachment":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/media?parent=979"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/categories?post=979"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/tags?post=979"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}