Slowing queries
The Oracle Sponge makes a timely comment on statistics on partition keys. Having the right (and not just some!) statistics on a data warehouse table is often the factor that tips the balance between an efficient query and one that causes abject misery in attempts to find what is wrong and how to tune things for the best.
I am not a great user of query hints - most of our users use query tools that are not amenable to hinting, and I also have a nagging doubt about hints mixing with query rewrite. I do use hints, but only as tool to investigate tuning, they are not designed (sweeping DW generalisation number 57) for production use.
Today my team have been looking at a ETL package that has been slowing for a while. It still is quick enough, but the margin for error in the batch window is now too small for comfort; a late start to the data load and the users will not have their data ready by 07:30. The dataload process populates one or two partitions of a 820 partition table through partition exchange and a also populates a non-partitioned Index Organised Table (IOT) that we use to prevent duplicate invoice numbers being loaded - I'll explain that in another blog. With a bit of digging around we found that the IOT stats were seriously out of date. To be honest I had expected us not to collect stats on this table, it is an index after all. But by dropping the stats we got 75% reduction in load time. We use Oracle Warehouse Builder (a 9.2 version as this is an old DW) to run the load and after the load completed we found that that stats had been refreshed on the IOT, which lead us to the reason things where not working properly with the stats. The mapping had been deployed (wrongly) with the option to collect statistics on completion. This option is a conditional thing, if the table more than doubles or halves in size the stats are collected. But in this case we only adding 1/700 of the data volume each day so the stats are never refreshed but become grossly wrong over time as we only add new values at one end of the index.
The real fix for this is going to wait until the new year - we have a change freeze in place so we can can't touch the code yet.