4 ms·

I've worked at a place where running time-sensitive batch file submission was the company's main line of business. It is always fun when the optimizer heuristic

I've worked at a place where running time-sensitive batch file submission was the company's main line of business. It is always fun when the optimizer heuristic suddenly makes a 3-minute query take over 3 hours! And there's basically no way to defend against this while still using SQL, short of moving everything to incremental generation.

hobs 7y ago
Query Hints. Simplifying code. Compiled stored procedures. Plan guides/fixed plans. If your code may run from 3m to 3 hours, its probably the SQL at fault.
- SketchySeaBeast 7y ago
  Not having query hints isn't the "SQL at fault" - it's very much the optimizer, or you wouldn't need to override it with said hint. I've seen it where one index hit an arbitrary number of rows and then the query flipped to joining on another, non-indexed column, increasing the time to run massively - that sort of 3m to 3 hours magnitude. Had to throw on a hint but that's only because the optimizer decided it knew better.
  - mikepurvis 7y ago
    A slightly more charitable view of this situation is that an optimization which was previously triggering to make the query fast suddenly no longer applied.
    - SketchySeaBeast 7y ago
      I think that might be too optimistic - it went from a table with thousands of records to hundreds of millions of records. If I were to speak technically the oracle optimizer "did a derp".
      - PeterisP 7y ago
        So... stale statistics? I.e. the optimizer making the query plan assuming that the table has thousands of records (because that's what it had earlier) and now it has hundreds of millions of records? A sudden order of magnitude change in table sizes isn't normal operation, for most DB platforms that requires running some "everything's different now, please recalculate statistics" command to maintain proper functioning.
  - hobs 7y ago
    And that's why I listed a bunch of other options as well - simplifying code is probably the best one, but there will always be gaps in their heuristics. In my experience, you have one of three scenarios: You know ahead of time exactly the problem you are going to solve and so probably shouldnt choose SQL from a performance perspective. Your problem size changes and you get to pick a simple system that you understand well and has predictable performance but might be slower than other options. Or a complex system that's less well understood, and generally has great performance and productivity up to a tipping point, where the abstraction has leaked too much. In your case - your query sounds exactly like those heuristics worked up to a point, and then finally failed. Is that the optimizers "fault"? It's mostly a trade off between the time spent making the plan and the time spent getting your data back - and again, sometimes those tradeoffs are wrong too.
NikolaNovak 7y ago
Depending on why, static / manual statistics on tables (also normally a no-no) may also help. (This is assuming whatever applicable regular maintenance (stats, index or table reorgs if needed) is done prior to the time-sensitive batch... and understanding sometimes there's just not enough hours in a day to run batch AND maintenance :| )

hobs 7y ago

Query Hints. Simplifying code. Compiled stored procedures. Plan guides/fixed plans. If your code may run from 3m to 3 hours, its probably the SQL at fault.

SketchySeaBeast 7y ago
Not having query hints isn't the "SQL at fault" - it's very much the optimizer, or you wouldn't need to override it with said hint. I've seen it where one index hit an arbitrary number of rows and then the query flipped to joining on another, non-indexed column, increasing the time to run massively - that sort of 3m to 3 hours magnitude. Had to throw on a hint but that's only because the optimizer decided it knew better.
- mikepurvis 7y ago
  A slightly more charitable view of this situation is that an optimization which was previously triggering to make the query fast suddenly no longer applied.
  - SketchySeaBeast 7y ago
    I think that might be too optimistic - it went from a table with thousands of records to hundreds of millions of records. If I were to speak technically the oracle optimizer "did a derp".
    - PeterisP 7y ago
      So... stale statistics? I.e. the optimizer making the query plan assuming that the table has thousands of records (because that's what it had earlier) and now it has hundreds of millions of records? A sudden order of magnitude change in table sizes isn't normal operation, for most DB platforms that requires running some "everything's different now, please recalculate statistics" command to maintain proper functioning.
- hobs 7y ago
  And that's why I listed a bunch of other options as well - simplifying code is probably the best one, but there will always be gaps in their heuristics. In my experience, you have one of three scenarios: You know ahead of time exactly the problem you are going to solve and so probably shouldnt choose SQL from a performance perspective. Your problem size changes and you get to pick a simple system that you understand well and has predictable performance but might be slower than other options. Or a complex system that's less well understood, and generally has great performance and productivity up to a tipping point, where the abstraction has leaked too much. In your case - your query sounds exactly like those heuristics worked up to a point, and then finally failed. Is that the optimizers "fault"? It's mostly a trade off between the time spent making the plan and the time spent getting your data back - and again, sometimes those tradeoffs are wrong too.

NikolaNovak 7y ago

Depending on why, static / manual statistics on tables (also normally a no-no) may also help. (This is assuming whatever applicable regular maintenance (stats, index or table reorgs if needed) is done prior to the time-sensitive batch... and understanding sometimes there's just not enough hours in a day to run batch AND maintenance :| )