{"id":1623,"date":"2011-01-18T12:00:00","date_gmt":"2011-01-18T12:00:00","guid":{"rendered":"http:\/\/orcldoug.com\/blog\/?p=1623"},"modified":"2011-01-18T12:00:00","modified_gmt":"2011-01-18T12:00:00","slug":"cost","status":"publish","type":"post","link":"http:\/\/orcldoug.com\/blog\/2011\/01\/18\/cost\/","title":{"rendered":"Cost"},"content":{"rendered":"

As soon as I saw the title of Jonathan Lewis’ post<\/a>, I had an inkling of what it might have to say and I wasn’t too far off the mark. Although I don’t disagree with a single statement of his post (I’ve read it a few times to make sure), I tend to take a different line at work because I keep finding myself in conversations with perfectly professional, bright and knowledgeable people that go something like this<\/p>\n

Them: ‘So it’s picking this new plan and it’s much slower than the old plan, but the new plan has a much lower cost.’<\/p>\n

<\/em>Me: ‘Of course it has a lower cost. That’s why it’s picked it. That’s what a cost-based optimiser does.’<\/p>\n

<\/em>Them: ‘But it’s slower’<\/p>\n

<\/em>Me: ‘Well spotted’ <\/p>\n

<\/em>Them: ‘Well then why is the cost lower?’<\/em><\/p>\n

Now at this point there are diverging paths you could take and it’s utterly valid to find out *why* the calculations haven’t delivered the best plan. The cost *is* the result of a calculation and not an arbritary number plucked from the air. The optimiser selected the ‘bad’ plan because it had the lowest cost.<\/p>\n

To quote Jonathan <\/p>\n

‘Cost IS time – but only in theory.’<\/p>\n

However, it’s equally valid to say, I don’t care why the cost is wrong, I *know* the plan that performs most efficiently and that’s the plan I want the server to use. In fact, I’d suggest that *most* SQL performance problems I look at have at their very core a plan with a lower cost that doesn’t deliver the best response time! My users couldn’t care less about optimiser calculations. (I use the cliche carefully – they probably don’t care at all.)<\/p>\n

What isn’t easy for me is to watch people spend a day aiming for the lowest cost, brandish it at me proudly and then be disappointed when it runs more slowly than the previous version!<\/p>\n

Them: ‘But look at the cost!’<\/em><\/p>\n

Me: ‘Who cares? It’s slower.’<\/em><\/p>\n

My take on it is this. Cost is the output of a model designed to deliver the lowest execution time but sometimes the model gets things wrong and, in the face of actual run times, I’ll take reality over the model every time. When people arrive at my desk (perhaps electronically) with a SQL performance problem, one of the first things I say if the conversation begins like that one above is <\/p>\n

“Ignore the cost!”
<\/em>
Jonathan’s telling the truth and the true answer to a question is important, as long as you understand what the original question was. I know from experience that many people react to the cost without understanding how the optimiser arrives at that answer. Of course, it could be that I happen to see the problem queries, where the optimiser calculation isn’t working out too well, but I come across plenty of them and it would save a lot of time if people didn’t spend so long arguing about costs!<\/p>\n

Cost is a fundamental metric if you care about the CBO, develop it, write about it (Jonathan) or are trying to work out what the hell it’s doing (anyone who has ever looked at a 10053 trace in desperation). But as a performance metric? I’m not sure it’s very important at all. <\/p>\n

How about time?<\/p>\n

P.S. No more blog posts for a while now. That Hotsos presentation looms large. Although, now I’ve said that, I’ll probably contradict myself ….<\/p>\n

P.P.S. I should say that I’ve missed the main thrust of Jonathan’s post and picked up on the bit that suited me. Really, it was about this …<\/p>\n

“As I\u2019ve pointed out in the past, \u201cCost is Time\u201d. The cost of a query represents the optimizer\u2019s estimate of how long it will take that query to run \u2013 so it is perfectly valid\u00a0 to compare the cost of two queries to see which one the optimizer thinks will be faster but, thanks to limitations and defects in the optimizer it may not be entirely sensible to do so.<\/p>\n

The point I want to address in this post though is the comment that ‘it\u2019s valid to compare the cost of different plans, but not to compare the cost of two different queries’.”
<\/em>
‘Valid’ and ‘sensible’. I liked that and I hope it’s what people absorbed.<\/p>\n

[Late update: I happened to look at AskTom today. I don’t look as often now that the pace of interesting follow-ups has slowed down. That’s no bad thing. Lots of things have been answered (for now) and I still search on old threads constantly. Anyway, I should have known that this subject would crop up there<\/a>.]<\/p>\n","protected":false},"excerpt":{"rendered":"

As soon as I saw the title of Jonathan Lewis’ post, I had an inkling of what it might have to say and I wasn’t too far off the mark. Although I don’t disagree with a single statement of his post (I’ve read it a few times to make sure), I tend to take a… Continue reading Cost<\/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-1623","post","type-post","status-publish","format-standard","hentry","category-uncategorized","entry"],"jetpack_featured_media_url":"","jetpack-related-posts":[{"id":868,"url":"http:\/\/orcldoug.com\/blog\/2006\/02\/05\/we-never-learn-do-we\/","url_meta":{"origin":1623,"position":0},"title":"We Never Learn, Do We?","date":"February 5, 2006","format":false,"excerpt":"I had an interesting day at work yesterday. We've run into major performance problems when upgrading a number of Production databases from 8i to 9i. How could that happen, when we've performed regression tests to ensure that the applications perform as well as or better than they did on 8i?Probably\u2026","rel":"","context":"With 5 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1706,"url":"http:\/\/orcldoug.com\/blog\/2013\/09\/01\/blogging\/","url_meta":{"origin":1623,"position":1},"title":"Blogging","date":"September 1, 2013","format":false,"excerpt":"I think it was Andy C who first mentioned to me that one of the golden rules of blogging was never to apologise or justify your reasons for not blogging. I guess it's because so many people do it so often that it becomes tedious. But my blogging output has\u2026","rel":"","context":"With 6 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1532,"url":"http:\/\/orcldoug.com\/blog\/2009\/10\/16\/oow-2009-wednesday\/","url_meta":{"origin":1623,"position":2},"title":"OOW 2009 – Wednesday","date":"October 16, 2009","format":false,"excerpt":"I always had Wednesday and Thursday in mind as rest days, compared to the first three days and it almost worked out that way. There was certainly even more socialising although whether that was truly restful is debatable \ud83d\ude09I kicked off Wednesday by attending \"HA DBA Roundtable: How Do You\u2026","rel":"","context":"Similar post","img":{"alt_text":"","src":"https:\/\/i0.wp.com\/18.133.199.212\/wp-content\/uploads\/recovered\/asktom.jpg?resize=350%2C200","width":350,"height":200},"classes":[]},{"id":1469,"url":"http:\/\/orcldoug.com\/blog\/2009\/02\/06\/grid-control-accessibility\/","url_meta":{"origin":1623,"position":3},"title":"Grid Control Accessibility","date":"February 6, 2009","format":false,"excerpt":"I work at a site that has Diagnostic and Tuning Pack licenses (yippee!) so, before we started deploying Grid Control, I tried to encourage the installation of Database Control in the mean-time so that people could get used to the Performance Pages that I find incredibly useful. I was fascinated\u2026","rel":"","context":"With 6 comments","img":{"alt_text":"","src":"https:\/\/i0.wp.com\/18.133.199.212\/wp-content\/uploads\/recovered\/OEM_text_link.gif?resize=350%2C200","width":350,"height":200},"classes":[]},{"id":1563,"url":"http:\/\/orcldoug.com\/blog\/2010\/02\/22\/statistics-on-partitioned-tables-part-2\/","url_meta":{"origin":1623,"position":4},"title":"Statistics on Partitioned Tables – Part 2","date":"February 22, 2010","format":false,"excerpt":"In the last part, I asked you to trust me that true Global Stats are a good thing so in this post I hope to show you why they are, to make sure you don't kid yourself that you can avoid them. (Updated later - this is all on 10.2.0.4)Why\u2026","rel":"","context":"With 6 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1585,"url":"http:\/\/orcldoug.com\/blog\/2010\/03\/11\/hotsos-2010-day-4\/","url_meta":{"origin":1623,"position":5},"title":"Hotsos 2010 – Day 4","date":"March 11, 2010","format":false,"excerpt":"First up was Cary Millsap's - Lessons Learned, Version 2010.03 As Cary pointed out, they always try to put the best speakers in the toughest slots - 8:30 in the morning post-party. I think local guys are slightly more reliable too because they might have actually gone home the night\u2026","rel":"","context":"With 10 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"_links":{"self":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts\/1623","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=1623"}],"version-history":[{"count":0,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts\/1623\/revisions"}],"wp:attachment":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/media?parent=1623"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/categories?post=1623"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/tags?post=1623"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}