test hinted queries in a proper tuning environment with Production-equivalent data.understand how the database works, and especially how the cost-based optimizer works.
So, in short, there is no universally applicable advice to when we should use hints.
Blindly putting /*+ parallel (t23, 16) */ will probably not make your query run sixteen times faster, and not infrequently will result in slower retrieval than a single-threaded execution. driving_site can be vital in tuning distributed queries.Ĭonversely other hints are almost always abused. append is often crucial for tuning bulk inserts. Oracle have had lots of very clever people working on the Cost-Based Optimizer for many years, so its decisions are usually better than ours.īut there are other hints we would not blink to see in Production. Some times there are times where we cannot resolve this conundrum, and have to keep the hints in Production. Does gathering fresh statistics solve the problem? Does rewriting the FROM clause solve the problem? If so, we can remove the hints and deploy the naked SQL. So one use of hints such as leading is to figure out the best execution path, then to figure out why the database doesn't choose that plan without the hint. Why is this necessary? Often it's because the optimal join order is not obvious, because the query is badly written or the database statistics are inaccurate. For instance, the leading hint you mention means join tables in this order. Most hints are a way of communicating our intent to the optimizer.
0 kommentar(er)
