There has been a decent amount of buzz out there about Oracle’s Automatic SQL Plan Management. It’s not exactly new, this feature has its roots in 11. Starting in 19 for Autonomous databases it is on by default. Which isn’t necessarily a bad thing.
My concern is that folks may make a bad assumption about how this works.
The actually assumption that this feature works on is that the code is well written to begin with. This isn’t a mechanism that will be able to rewrite code to make it better, it will just make sure that the plan used for the code doesn’t get worse. Those are two very different things.
A good example would be code that doesn’t scale well. The classic example being correlated subqueries in the SELECT LIST (and the WHERE clause can be worse, just not as common). Any correlated sub query has the potential to run for each row returned by the main query. If the main query is only returning a relatively small set of rows, these types of queries can run well. (Yea this is a topic I talk about a lot, because I see it a lot in queries that run poorly.)
However, as the data grows these queries will by nature run slower.
Automatic Plan Management can only do so much. It can try to get the best plan for the code as written.
Also, this sort of automation tends to be reactionary, things have to run badly before it knows it’s bad. An analogy would be driving to a reasonably far away destination. If you and I were about to get on a highway and see that it is backed up, we could avoid getting on the highway and driving a slightly different route that still keeps on relativity on time to the destination. The optimizer would just get on the highway (following the plan) and get to the destination late and then realize that was a bad idea.
The bad assumption I referred to earlier that folks might make, is that this feature will make up for poor code. It can’t. Bad code will still have a less then optimal plans and this feature will do it’s best to try to use the best plan for it, but it will never be a good as a well written statement. I use the joke that a car designed to run on square wheels will never run well, no matter how good the suspension and how powerful the engine is, it will be ruff ride and be highly inefficient.
With a well written (and scalable) statement to start with, yes, this feature is likely to help. But with poor statements to begin with, this may just add a layer of complexity to figuring out why the performance is so bad.
But with enough beer, you might not notice there is a problem eh?
