Apr 6: Topsy-Turvy
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 sectionAND pe.open_item_id IN (SELECT int_value
FROM v_extn_system_parameters
WHERE process_key = 'CAFTE003'
AND parameter_name LIKE 'ACCRUED%'))
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 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.
AND pe.open_item_id IN (1, 2, 10, 25))
That's not a reasonable solution for a production system, though - what happens when a new code appears in 18 months?
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
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%'))
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.
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 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.
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.
Since then I've been sleeping. Really, I've never slept so much in my life!
#3 - Doug Burns said:
2006-04-06 13:20 - (Reply)
Precisely.
I hesitated to post this because I didn't want to give too much detail, but wanted to pat myself on the back I also used the phrase 'sledgehammer to crack a nut' when I was talking to the developers. Using NO_UNNEST was a pretty blunt tool because I didn't say - 'here's what that table really looks like' - I just said - 'don't even think about joining this'.
Maybe this would be a better summary of what was tuned here ...
The CBO was getting the cardinality of the remote table wrong, deciding it was a good join candidate, when it wasn't, so I forced it not to consider it.
Maybe I'll add the plans later ...
#4 - Doug Burns said:
2006-04-06 13:24 - (Reply)
Ooops, Pete S jumped in while I was commenting
Nice idea, Pete. I must admit, there was an element of 'Oh dear, there's a remote access there - I bet that's the problem' prior to the in-depth analysis. I worried about that because it's similar to saying 'Oh dear, there's a full table scan there' when we both know that often isn't a problem.
Having said that, I've had similar previous experiences to yours and it did turn out that that was (at least part of) the source of the problem. So there was a mixture of prior experience and analysis here.
#12 - Doug Burns said:
2006-04-07 19:43 - (Reply)
The Sponge said...
"Oooh, I have another idea -- slapping the definition of the remote view into a subquery factoring clause, and optionally materializing the result set on the local database. "
Tee hee ... too late my bearded friend. That was the first thing I went for Having said that, I did give up on it before testing it when the UNNEST approach popped into my head
