{"id":1673,"date":"2012-01-17T12:00:00","date_gmt":"2012-01-17T12:00:00","guid":{"rendered":"http:\/\/orcldoug.com\/blog\/?p=1673"},"modified":"2012-01-17T12:00:00","modified_gmt":"2012-01-17T12:00:00","slug":"randolf-geist-on-11g-incremental-statistics","status":"publish","type":"post","link":"http:\/\/orcldoug.com\/blog\/2012\/01\/17\/randolf-geist-on-11g-incremental-statistics\/","title":{"rendered":"Randolf Geist on 11g Incremental Statistics"},"content":{"rendered":"

Well it wasn’t the post I planned to return to technical matters with. <\/p>\n

Lots of readers here have asked me when I’m going to get round to
\nwriting about 11g Incremental Statistics as part of the stats series<\/a>. Although Incrementals are on my To Do list, I wanted to finish off the stats copying posts first. In any case, Randolf Geist got there already<\/a> so I’ll cross it off my list and point you towards his post instead.<\/p>\n

Yes, I know there have been a lot of Incrementals posts already by people like Robin Moffat<\/a> and John Hallas<\/a>, but Randolf’s post maps most closely on to the post I planned, which is an overview of Incrementals that highlights some of the practicalities of using them in “The Real World”. I’d particularly draw attention to a couple of aspects which I think people keep misunderstanding.<\/p>\n

    \n
  1. The first time you implement Incrementals on a table, Oracle will have to trawl through the entire table in order to build the initial synposes. This has always seemed obvious to me – how can you incrementally build on synposes that haven’t been created yet? But the long duration initial gather seems to surprise people and they decide that Incrementals are ‘slow’.\n<\/li>\n
  2. Incrementals are a replacement for GRANULARITY=>’GLOBAL AND PARTITION’ and not ‘PARTITION’! Expecting an option which gathers Partition stats and then goes around updating synposes to perform as well as a simple partition gather is unrealistic*<\/sup>. Any performance improvement needs to be measured against both gathering the Partition stats and<\/em> maintaining the Global stats. Incrementals will almost definitely be quicker than that! I prefer to think of Incrementals not so much as a performance improvement (because most people probably didn’t regather Global statistics every time they gathered individual Partition statistics because they didn’t have the time on an active system), but an improvement to the quality of your Global stats because you can now afford to maintain them with the same frequency as your Partition stats, rather than scheduling an out-of-hours Global stats gather or depending on the inaccurate NDVs that result from the previous aggregation process.<\/li>\n<\/ol>\n

    Good post, anyway. Thanks Randolf!

    *<\/sup> However, it’s fair to say that Oracle have continued trying to improve the performance of synposis maintenance.<\/p>\n","protected":false},"excerpt":{"rendered":"

    Well it wasn’t the post I planned to return to technical matters with. Lots of readers here have asked me when I’m going to get round to writing about 11g Incremental Statistics as part of the stats series. Although Incrementals are on my To Do list, I wanted to finish off the stats copying posts… Continue reading Randolf Geist on 11g Incremental Statistics<\/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-1673","post","type-post","status-publish","format-standard","hentry","category-uncategorized","entry"],"jetpack_featured_media_url":"","jetpack-related-posts":[{"id":1674,"url":"http:\/\/orcldoug.com\/blog\/2012\/02\/07\/hotsos-symposium-2012\/","url_meta":{"origin":1673,"position":0},"title":"Hotsos Symposium 2012","date":"February 7, 2012","format":false,"excerpt":"Oh, well ... having decided that I was going to skip the Symposium this year, everything changed. My friend Randolf Geist had to cancel his attendance so when he saw my previous post he asked if I'd be prepared to step in with a couple of presentations so that he\u2026","rel":"","context":"With 3 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1714,"url":"http:\/\/orcldoug.com\/blog\/2014\/01\/29\/recurring-conversations-incremental-statistics-part-1\/","url_meta":{"origin":1673,"position":1},"title":"Recurring Conversations \u2013 Incremental Statistics (Part 1)","date":"January 29, 2014","format":false,"excerpt":"When I first started blogging, most of the material came from issues that I'd run into and how they were solved but, as I've spent most of the last 7 months not logging in to anything much ;-), it occurred to me that another area which I haven't used for\u2026","rel":"","context":"With 1 comment","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1562,"url":"http:\/\/orcldoug.com\/blog\/2010\/02\/17\/statistics-on-partitioned-tables-part-1\/","url_meta":{"origin":1673,"position":2},"title":"Statistics on Partitioned Tables – Part 1","date":"February 17, 2010","format":false,"excerpt":"If you've ever worked on large databases that use partitioned and subpartitioned tables, you'll be aware that there are significant challenges in maintaining up-to-date\/appropriate statistics. We've encountered a few problems at work recently and I decided it would be an idea to put together a series of posts covering the\u2026","rel":"","context":"With 11 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1616,"url":"http:\/\/orcldoug.com\/blog\/2010\/11\/03\/poor-performance-when-gathering-partitions-stats-in-11g\/","url_meta":{"origin":1673,"position":3},"title":"Poor performance when gathering partitions stats in 11g","date":"November 3, 2010","format":false,"excerpt":"Being busy at work is both a blessing and a curse to blogging activity. On the one hand, the more that's going on, the more technical issues there are likely to be to blog about. On the other, when the pace is frantic and problems are coming thick and fast,\u2026","rel":"","context":"With 10 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1604,"url":"http:\/\/orcldoug.com\/blog\/2010\/05\/17\/resource-manager-and-11g\/","url_meta":{"origin":1673,"position":4},"title":"Resource Manager and 11g","date":"May 17, 2010","format":false,"excerpt":"I will get back to the stats stuff at some point, but I'm quite busy at the moment working on something that I can't talk too much about, but which is throwing up enough generic issues to talk about. This is one that I meant to blog about ages ago\u2026","rel":"","context":"With 5 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1308,"url":"http:\/\/orcldoug.com\/blog\/2007\/08\/11\/does-anyone-know-when-11g-will-be-released\/","url_meta":{"origin":1673,"position":5},"title":"Does Anyone Know When 11g Will Be Released?","date":"August 11, 2007","format":false,"excerpt":"Sorry, I shouldn't be so sarcastic, but try to show some sympathy for my schedule.Thursday 9th August 22:30 BST - Go to bed, unusually early.Friday 10th 06:00 BST - Wake up, check Netvibes and noticed several 11g release blogs, including Eddie's initial notification and Howard's installation!07:30 BST - Leave for\u2026","rel":"","context":"With 4 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"_links":{"self":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts\/1673","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=1673"}],"version-history":[{"count":0,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts\/1673\/revisions"}],"wp:attachment":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/media?parent=1673"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/categories?post=1673"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/tags?post=1673"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}