{"id":820,"date":"2006-04-06T12:00:00","date_gmt":"2006-04-06T12:00:00","guid":{"rendered":"http:\/\/orcldoug.com\/blog\/?p=820"},"modified":"2006-04-06T12:00:00","modified_gmt":"2006-04-06T12:00:00","slug":"topsy-turvy","status":"publish","type":"post","link":"http:\/\/orcldoug.com\/blog\/2006\/04\/06\/topsy-turvy\/","title":{"rendered":"Topsy-Turvy"},"content":{"rendered":"

It’s been a topsy-turvy couple of days. I had my last night shift on Tuesday night\/Wednesday morning, which was the busiest of them all, although there was only one small problem in the last 8 hours of it.<\/p>\n

My favourite bit, earlier in the evening, was improving the performance of one of the weekly batch jobs from 5 hours down to less than a minute. The original job used 48Gb of temporary space too – in each of the 6 databases that run the job. As the customer said – cancel those disk deliveries 😉 I don’t want to produce the query here because I’m not sure my bosses would be keen, but the source of the problem was that the SQL statement included a reference to a table in a central repository via a database link. Here’s a small section<\/p>\n

AND pe.open_item_id IN (SELECT int_value      FROM v_extn_system_parameters      WHERE process_key = 'CAFTE003'      AND parameter_name LIKE 'ACCRUED%'))<\/pre>\n
The problem was that the CBO decided to take this subquery and convert it into a Hash Join against one of the other tables. What you can’t see here is that this was a long<\/span> statement, joining many tables together. The subquery itself should always return 4 numeric values and if those values were plugged in directly, like this, the problem disappeared.<\/p>\n
AND pe.open_item_id IN (1, 2, 10, 25))<\/pre>\n
That’s not a reasonable solution for a production system, though – what happens when a new code appears in 18 months?<\/p>\n
When you see the solution, the words ‘Silver’ and ‘Bullet’ might pop into your mind. If so, let me point out that I worked it out by poring over execution plans, trying different approaches in full-sized test environments and, when you look at the problem, it’s actually a very localised problem with a large query (although it was having a dramatic effect on the whole query). The problem is that Oracle was joining this remote table to a local table at a very early stage of the query, impacting later stages of the execution plan. All I really wanted to do was to stop it doing that. Here was the particular solution that worked for this particular query<\/p>\n
AND pe.open_item_id IN (SELECT \/*+ NO_UNNEST *\/ int_value       FROM v_extn_system_parameters       WHERE process_key = 'CAFTE003'       AND parameter_name LIKE 'ACCRUED%'))<\/pre>\n
This hint made the CBO treat this subquery as a seperate subquery and not a candidate for a join. i.e. It didn’t unnest the subquery.<\/p>\n
In a bad situation like this (the developers were frantic with the next weekend run approaching – the word embarassing was being thrown around) we were just happy to get the damn thing fixed. It still needs to be tested, change requests completed and the change rolled out, but I must admit I was quite pleased. It is<\/span> my job, but it’s very rare to get such a dramatic performance improvement from a small change that is genuinely valuable to the business.<\/p>\n
I went home and went to bed satisfied. Later on, I woke up and logged in to have a quick look at my emails from home. (This practice seems to be the subject of some ridicule at my work – I’m off work and at home, why am I checking my emails? Well, it only takes 10 minutes and I’d rather know everything is okay. Doesn’t everyone do that during critical work periods?) Regardless, I was absolutely (sarcasm on) delighted (sarcasm off) to discover that the night-shifts have been cancelled. Just my luck – being the last person to have to do that shift. Having said that, it’s a sign of how well things have gone overall and I wouldn’t really wish those shifts on anyone.<\/p>\n
Since then I’ve been sleeping. Really, I’ve never slept so much in my life!<\/p>\n","protected":false},"excerpt":{"rendered":"
It’s been a topsy-turvy couple of days. I had my last night shift on Tuesday night\/Wednesday morning, which was the busiest of them all, although there was only one small problem in the last 8 hours of it. My favourite bit, earlier in the evening, was improving the performance of one of the weekly batch… Continue reading Topsy-Turvy<\/span><\/a><\/p>\n","protected":false},"author":0,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-820","post","type-post","status-publish","format-standard","hentry","category-uncategorized","entry"],"jetpack_featured_media_url":"","jetpack-related-posts":[{"id":992,"url":"http:\/\/orcldoug.com\/blog\/2006\/06\/13\/production-call-out\/","url_meta":{"origin":820,"position":0},"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":866,"url":"http:\/\/orcldoug.com\/blog\/2006\/02\/08\/updates\/","url_meta":{"origin":820,"position":1},"title":"Updates","date":"February 8, 2006","format":false,"excerpt":"Just a couple of small updates on some previous blogs.The database that was suffering from network latency problems between it and the app servers was moved down South last week. It all went very smoothly and that particular problem has been resolved. The 20+ hour job runs over night very\u2026","rel":"","context":"Similar post","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1159,"url":"http:\/\/orcldoug.com\/blog\/2006\/12\/11\/a-more-complex-statspack-example-summary\/","url_meta":{"origin":820,"position":2},"title":"A More Complex Statspack Example – Summary","date":"December 11, 2006","format":false,"excerpt":"Looking back at the three blogs (and hopefully the comments, where others have made some very useful contributions), it's all quite unsatisfactory, isn't it? We haven't solved the problem. All we've proved is that the tests aren't equivalent, although I think there's value in that negative result because I was\u2026","rel":"","context":"With 3 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":893,"url":"http:\/\/orcldoug.com\/blog\/2005\/12\/19\/another-10046-success\/","url_meta":{"origin":820,"position":3},"title":"Another 10046 Success","date":"December 19, 2005","format":false,"excerpt":"We're implementing a new packaged application at work. It includes a history import job that takes data in a flat-file and loads it into database tables. It performs some degree of data transformation but, in essence it inserts about 500,000 rows into one table and thousands in to a few\u2026","rel":"","context":"With 7 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1371,"url":"http:\/\/orcldoug.com\/blog\/2007\/12\/24\/the-reality-gap-4-its-never-the-san\/","url_meta":{"origin":820,"position":4},"title":"The Reality Gap (4) – It’s never the SAN","date":"December 24, 2007","format":false,"excerpt":"I've left one of my favourite topics for the penultimate episode of this mini-series. The more sites you work at and the more performance problems you work on, the more you begin to learn the one essential truth of modern system architecture. It's never the SAN. Here's a true story.\u2026","rel":"","context":"With 11 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1155,"url":"http:\/\/orcldoug.com\/blog\/2006\/12\/06\/a-more-complex-statspack-example-part-1\/","url_meta":{"origin":820,"position":5},"title":"A More Complex Statspack Example – Part 1","date":"December 6, 2006","format":false,"excerpt":"Following on from the last Statspack example, up popped an example at work this week of another common reason I use Statspack - comparing the performance of different environments. It's also a nice illustration of some of Statspack's limitations.Because a Statspack report contains a lot of information and this particular\u2026","rel":"","context":"With 8 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"_links":{"self":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts\/820","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"}],"replies":[{"embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/comments?post=820"}],"version-history":[{"count":0,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts\/820\/revisions"}],"wp:attachment":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/media?parent=820"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/categories?post=820"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/tags?post=820"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}