Reason: I started dealing with Oracle when it was at v8 and I think up to including v9 when I specified a hint it was usually followed. Then later with v10 and v11 it progressively became more difficult and nowadays with v12 I'm having a really hard time (I usually have to use some "tricks" that don't change the logic of the SQL but do change its technical execution, like placing somewhere a useless but tactical "distinct", to confuse it enough so that my hints are more or less followed/used).
Btw., if you want to state something like "using hints is wrong" then in general I agree (with some exceptions for special cases) but currently I'm taking care of a very old app that uses often quite big SQLs (involving up to ~15 tables) and as the maintenance budget is limited and the app will anyway be decommissioned in 1-2 years I cannot start rewriting half of the application to break down and improve the statements => when once per quarter the data distribution/quantity/etc... change and the optimizer thinks that it has a new brilliant idea about how to exec some SQL and then of course the SQL hangs then I usually just try to spend 1-2 hours trying to find some hint(s) that will bring back the old execution plan, but since we upgraded to Oracle12 I often see no change in the exec plan unless I do what I mentioned above.
> Btw., if you want to state something like "using hints is wrong" then in general I agree (with some exceptions for special cases) but currently I'm taking care of a very old app that uses often quite big SQLs (involving up to ~15 tables) and as the maintenance budget is limited
This is a very common truth, I've been in similar situations where budgets are tight and things are working so don't touch a thing. I think it's actually just a specific example of a very common problem pattern in tech - the way I usually push back on it is "The stuff that is is and I'm not going to waste time fixing it - but any stuff I'm adding a feature to or fixing a bug in, it's not worth the company's time to fix it the wrong way because it'll end up costing more when another bug or feature requires an adjustment near this in a year." All that said, especially within MySQL (and I haven't worked in Oracle so maybe there too) the query planner is a bit dumb, so sometime you really need to help it along.
> especially within MySQL (and I haven't worked in Oracle so maybe there too) the query planner is a bit dumb, so sometime you really need to help it along.
Yeah, I've seen it as well last year when working with MariaDB (but I'd guess that MySQL might be a bit more clever as maybe Oracle might have improved it a bit).
This is somewhat intentional I think to guide you toward buying Enterprise edition, which since 11G includes plan baselines ( see https://docs.oracle.com/cd/B28359_01/server.111/b28274/optpl... ) - which allows you to lock a SQL queryID to a specific plan.
At the company we're using EE, and I've heard about the plan locking functionality, but I never dared to use it.
Does it survive DB-restarts? Additionally we have a setup of an active/primary cluster that is replicated to a passive/secondary one in our secondary datacenter (which then becomes leading in case of a disaster in the primary datacenter) => I don't think that a locked plan is replicated to the secondary cluster (which, in a case of a disaster would become a 2nd disaster as many SQL all of a sudden would stop working).
is it only my (wrong) subjective feeling, or did the one of Oracle become much more stubborn about following "hints"? https://en.wikipedia.org/wiki/Hint_(SQL)
Reason: I started dealing with Oracle when it was at v8 and I think up to including v9 when I specified a hint it was usually followed. Then later with v10 and v11 it progressively became more difficult and nowadays with v12 I'm having a really hard time (I usually have to use some "tricks" that don't change the logic of the SQL but do change its technical execution, like placing somewhere a useless but tactical "distinct", to confuse it enough so that my hints are more or less followed/used).
Btw., if you want to state something like "using hints is wrong" then in general I agree (with some exceptions for special cases) but currently I'm taking care of a very old app that uses often quite big SQLs (involving up to ~15 tables) and as the maintenance budget is limited and the app will anyway be decommissioned in 1-2 years I cannot start rewriting half of the application to break down and improve the statements => when once per quarter the data distribution/quantity/etc... change and the optimizer thinks that it has a new brilliant idea about how to exec some SQL and then of course the SQL hangs then I usually just try to spend 1-2 hours trying to find some hint(s) that will bring back the old execution plan, but since we upgraded to Oracle12 I often see no change in the exec plan unless I do what I mentioned above.