Updated 27th Nov 2015:
Thanks for all the questions so far – there are plenty of useful topics coming out. At this rate I may have to pass some of these on to the round-table that Tony Hasler.
I’ve made a couple of comments in response, but generally I plan to avoid making any comments until after the panel.
Monday 7th Dec: 11:20 – 12:05
I’ve arranged a panel session on the Cost Based Optimizer for UKOUG Tech 2015, with Maria Colgan, Nigel Bayliss, and Chris Antognini joining me to answer (and maybe argue about) questions from the audience.
To keep things moving along, we aim to have a few questions available before the day and collect a few questions at the door as well as accepting questions from the floor. Martin Widlake and Neil Chandler will be acting as MCs, wielding the microphones, and collecting questions at the door.
So far we have a handful of questions – if you have a question you’d like to raise, add it to the comments below. Even if you can’t attend we may get to your question, and I may be able to write up a summary of questions and answers after the event.
Questions so far:
- Why do queries that have been working fine for months suddenly change to a very slow plan ?
- Are smaller, simpler queries less likely to have a bad plan or change from a good plan to a bad one ?
- What is the down-side of using SQL Profiles to lock in plans ?
- What’s the best way to collect stats on a partitioned table where you add a daily / weekly / monthly partition ?
- Is there a good way to make sure you’ve collected stats for interval partitions when you don’t know they’ve been added ?
- Why do the manuals talk about “prefixed” local indexes (on partitioned tables) as if they have some special effect ?
- Why does a query with literals run faster than one with bind variables? Stats are up to date on all tables concerned.
- For a single table an SQL Plan directive will create extended statistics, but how does the Optimizer resolve SPD for the group by or join queries ?
- I’ve heard that for the group by and join queries, SPD always forces dynamic sampling, is this true ?
- Will SPD with dynamic sampling take precedence over e.g. columns with histograms?
- What is the order of precedence the optimizer uses when resolving table cardinality?
- Will 12.2 allow us to combine column groups and expression as a single extended statistic?