{"id":1055,"date":"2006-08-18T12:00:00","date_gmt":"2006-08-18T12:00:00","guid":{"rendered":"http:\/\/orcldoug.com\/blog\/?p=1055"},"modified":"2006-08-18T12:00:00","modified_gmt":"2006-08-18T12:00:00","slug":"historical-perspective-part-3","status":"publish","type":"post","link":"http:\/\/orcldoug.com\/blog\/2006\/08\/18\/historical-perspective-part-3\/","title":{"rendered":"Historical Perspective (Part 3)"},"content":{"rendered":"

Previously I talked about the importance of historical perspective to server performance tuning<\/a>, but it applies to many areas of working with databases. For the last part of this mini-series, I thought I’d pull together a few more examples.<\/p>\n

Execution Plans and Optimiser Statistics<\/strong><\/p>\n

I’ve written several blogs recently about the value of keeping a history of optimiser statistics<\/a>. Even if you don’t suffer problems with changing execution plans most of the time and your system performs well, there’s always the possibility that execution plans could change when you refresh the statistics on database objects. <\/p>\n

Once an execution plan changes and all you have is the current execution plan and the current statistics you might be able to work on the plan to improve it, but it’s much more difficult than being able to\u00a0compare the old ‘good’ plan with the ‘bad’ plan and work out what’s changed and why. If you have the old statistics, you should be able to\u00a0generate the old plan for comparison.<\/p>\n

That’s why the introduction of automatic optimiser stats history in 10g is so welcome.<\/p>\n

Space Usage<\/strong><\/p>\n

One of the most important ongoing DBA tasks is working out when we’re going to run out of space. As well as the immediate need to monitor any tablespaces which are close to filling so that emergency action can be taken, longer term projections are normally required to make sure that sufficient storage is allocated before it’s needed. That might include the need to provision more hardware, which can take time.<\/p>\n

The key word in the previous paragraph is ‘projections’. How can we project how things are going to look in the future if we don’t know how they looked before today?<\/p>\n

System Load<\/strong><\/p>\n

There’s one\u00a0consequence of my insistence on running Statspack snapshots by default that’s repaid itself\u00a0many times\u00a0over the years. Imagine a scenario where a server is running at over 90% CPU load over long periods of the day. Response times are plummeting, the business users are going crazy and want to know what’s going on.<\/p>\n

Maybe there is some problem with the performance of the server, maybe an application release has back-fired or maybe, just maybe, there concurrent user population has doubled over the past couple of months? But how would you know for sure? It might be that the system just doesn’t have enough capacity to cope with the increasing workload. The only way to measure ‘increasing’ is to retain some history and, let’s face it, a database is an ideal place to retain it!<\/p>\n

Looking back at these few examples, they’re all really examples of capacity planning. Whilst we can put together capacity plans based on calculated projections and\u00a0estimated loads, projections based on past history have a real-world element to them that I’ve found is likely to make them more reliable.<\/p>\n

I hope it’s clear by now that, if you don’t know how things used to look in the past, it’s\u00a0much more\u00a0difficult to analyse the present and predict the future.<\/p>\n","protected":false},"excerpt":{"rendered":"

Previously I talked about the importance of historical perspective to server performance tuning, but it applies to many areas of working with databases. For the last part of this mini-series, I thought I’d pull together a few more examples. Execution Plans and Optimiser Statistics I’ve written several blogs recently about the value of keeping a… Continue reading Historical Perspective (Part 3)<\/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-1055","post","type-post","status-publish","format-standard","hentry","category-uncategorized","entry"],"jetpack_featured_media_url":"","jetpack-related-posts":[{"id":999,"url":"http:\/\/orcldoug.com\/blog\/2012\/06\/18\/saving-optimiser-stats-9i\/","url_meta":{"origin":1055,"position":0},"title":"Saving Optimiser Stats – 9i","date":"June 18, 2012","format":false,"excerpt":"In a recent blog I described how a mis-timed optimiser statistics collection job led to a bad execution plan for one of the SQL statements in a regular batch jobIt's no coincidence that we were already in the process of implementing a change to our stats collection period to retain\u2026","rel":"","context":"With 4 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1000,"url":"http:\/\/orcldoug.com\/blog\/2006\/06\/18\/saving-optimiser-stats-10g\/","url_meta":{"origin":1055,"position":1},"title":"Saving Optimiser Stats – 10g","date":"June 18, 2006","format":false,"excerpt":"In my previous blog I showed how you can save your current optimiser stats into a seperate table whenever you refresh them, so that they can be restored should the new statistics lead to poor SQL execution plans. Oracle have added an improved version of this facility to 10g (which\u2026","rel":"","context":"Similar post","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":992,"url":"http:\/\/orcldoug.com\/blog\/2006\/06\/13\/production-call-out\/","url_meta":{"origin":1055,"position":2},"title":"Production Call-out","date":"June 13, 2006","format":false,"excerpt":"On Saturday morning, we ran into a problem on one of our 9.2.0.7 Production databases during an Extract, Transform and Load (ETL) batch process.I was called at 5:15am to look at a job that normally runs in a few minutes but had failed after 30 or 40 minutes when trying\u2026","rel":"","context":"With 4 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1596,"url":"http:\/\/orcldoug.com\/blog\/2010\/04\/22\/statistics-on-partitioned-tables-part-6a-copy_table_stats-intro\/","url_meta":{"origin":1055,"position":3},"title":"Statistics on Partitioned Tables – Part 6a – COPY_TABLE_STATS – Intro","date":"April 22, 2010","format":false,"excerpt":"[Phew. At last. The first draft of this was dated more than two weeks ago .... One of the problems with blogging about copying stats was the balance between explaining it and pointing out some of the problems I've encountered. So I've broken up this post, with a little explanation\u2026","rel":"","context":"With 4 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1684,"url":"http:\/\/orcldoug.com\/blog\/2012\/07\/14\/other_xml\/","url_meta":{"origin":1055,"position":4},"title":"OTHER_XML","date":"July 14, 2012","format":false,"excerpt":"Some features in this post require a Diagnostics Pack license.Just a small tip that could make things a little easier for you one day when you are trying to work out the underlying cause of a SQL execution plan change that leads to degraded performance, after the problem has occurred.There\u2026","rel":"","context":"With 2 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1010,"url":"http:\/\/orcldoug.com\/blog\/2006\/07\/03\/stattab-contents\/","url_meta":{"origin":1055,"position":5},"title":"stattab contents","date":"July 3, 2006","format":false,"excerpt":"In an earlier blog I talked about saving optimiser statistics and suggested that there are a couple of approaches to viewing saved statistics.It's not rocket science to view the contents of the stats table directly, but Oracle could change the format over time, so it's probably best to use the\u2026","rel":"","context":"Similar post","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"_links":{"self":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts\/1055","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=1055"}],"version-history":[{"count":0,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts\/1055\/revisions"}],"wp:attachment":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/media?parent=1055"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/categories?post=1055"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/tags?post=1055"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}