Tuning
July 18th, 2005 by Peter Scott
I have a love hate relationship with query tuning. Part of me enjoys the challenge, the solving of a puzzle, but on the other hand there is that naked fear of failing to meet a customer’s expectations.
I prefer the type of problem where I am told the business objective of the query and then left to it – sometimes I can be very creative. Occasionally, like today I am handed a query and an explain plan and told “go, fix!” Today it was a simple data warehouse query joining two fact tables and five dimensions expressed through in-lists, but the explain plan had over 1500 steps. There were parallel processes kicking in all over the place and the two fact tables were old-style partition views (this was a Oracle 7.3 DW that had been pretty much left untouched until it arrived on 10g – the customer is going to put real partitioning in soon, but for now needs to get the current code working) The query was running in around an hour compared to the expectation (based on the previous system) of five minutes. A quick look around the system show some missing stats, easy to fix, but still not enough to reach my target. Then the realisation hit – get rid of the parallel – most of the query would be spent coordinating the results of all those parallel slaves. Things look brighter already.
20 July update
Brighter, yes but still not quite there – asked the customer to disable the bitmap indexes – now down to minutes and not hours. Good enough for now but one to revisit when we implement partitioning later in the year
July 18th, 2005 at 10:11 pm
I thought any query which fetches data for close to one hour will always benefit from parallel processing! There were two logical problems with the above statement. ‘thought’ and ‘always’. Guess will have to set up a test case and see if it takes more time in ‘consolidating the query results from the parallel slaves’
July 18th, 2005 at 11:20 pm
thats the point – it wasnt fetching data for an hour seemingly (was spent processing the parallel stuff)
July 19th, 2005 at 7:32 am
Pete-s said…
on the other hand there is that naked fear of failing to meet a customer’s expectations.
I know what you mean here. Sometimes you can work wonders and sometimes you have admit that the only way it’s going to get faster is to change the schema. They don’t like the second answer :)
Cheers
Tim…
July 19th, 2005 at 6:09 pm
When you say “old style partition views”. Are you referring to the Oracle 7.3 init.ora parameter partition_view_enabled, a set of “union all” views of tables with suitable check constraints?
I only ask as I read in the doco that this is deprecated in 10G. Is the code that implements included in 10G and does it still work?
I only ask as I know of a sytem that still used the old “partition view” approach and wondered what the options might be for a move to 10G.
Enjoying the blog.
Cheers.
July 19th, 2005 at 8:28 pm
Mathew:
Yes, the old 7.3 feature of union all views over multiple tables. The init.ora feature to enable this is not in 10g but in the background it is there with a default of ‘TRUE’; the code still seems to eliminate partitions – or the explain plans shows masses of ‘filter’ operations on the partitions which was a give away…
Changing to a partitioned table is easy for the storage, but obviously the data load routines need to be changed (simplified???). The easiest way would be to create an empty partitioned table with a partition for each table in the partition view then partition exchange each table into the new partitioned table. As a variation on a theme you do a few at a time and include the partitioned table in the partition view!
July 21st, 2005 at 4:56 pm
Part of me enjoys the challenge, the solving of a puzzle, but on the other hand there is that naked fear of failing to meet a customer’s expectations.
I just love going to a developer who hands me a query that runs in 2 hours and saying “Is 3 minutes fast enough?” An added bonus is when I can do that within a half hour.
July 21st, 2005 at 11:00 pm
Data warehome-makers ??? have a lot of scope to look good – three and four hours queries are common from the drag and drop classes!
July 23rd, 2005 at 5:52 am
This sounds like parallelism at the level of the tables constituting the partitioned view, so each table has multiple query slaves?